storing negative number in decimal field of mysql table as of version 5.0.3

Question

Hi,

1. My table has few fields with an amount column of type decimal.
2. This column will have either a deposited amount (a positive value) or a withdraw amount (a negative value).
3. I store the positive value as just 120 and the negative value as -50.
4. I sum the column and got the result as expected.
5. Mysql version is: 5.1.33-community.

6. When i checked mysql documentation about decimal i confused with their description.

7. Before MySQL 5.0.3, if you inserted +0003.1 into a DECIMAL(5,1) column, it was stored as +0003.1. As of MySQL 5.0.3, it is stored as 3.1. For negative numbers, a literal - character is no longer stored. Applications that rely on the older behavior must be modified to account for this change. http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

8. When i listed the rows in phpmyadmin i could see the negative sign and when i calculated the result was as expected. but the documentation said no sign will appear.

9. Is it good to store negative number in decimal(haven't i studied in school? confused)?... or do we have to use float.

10. I learn't that float would complicate calculations and was advised to stick with decimal for certain conditions.

    I would like to have suggestions for this.

Answer 1

From what I understand, the documentation is saying it won't store a literal "-" character, which means it's probably now doing what the other signed INTEGER fields have always done and it's storing a sign bit to denote negative numbers instead.

You're still seeing a minus sign preceding the number because it's being generated by MySQL as a result of that sign bit.

If you don't understand the sign bit, you can consider how a signed byte can store numbers from -128 to 127, while an unsigned byte can store numbers from 0 to 255. That's because one of the 8 bits in a signed number is being used to store +/- (1 is negative, 0 is positive), while the remaining bits offer numbers up to 2^7 (-128 or 127).

So, for example, if the bits 1111 had a sign bit they would equal -7 (negative+4+2+1), but if they were unsigned they'd equal 15 (8+4+2+1). It's still the same amount of bits being stored.

You may wonder why the negative bound in a signed number can use the 8th bit, while the positive bound is limited to the sum of the 7 bits (1 less than the 8th bit). This is because 10000000 is considered to be both negative and the 8th bit simultaneously, because its representation of -0 otherwise is redundant with 00000000 which represents 0. There's no distinction between negative and positive zero, so a negative most significant bit is always the value of that bit itself (but negative).