Why does MySQL return some floats in scientific notation, but not others?

Question

I am writing a PHP script that looks at a DB table of float values ​​that tend to be somewhat small, for example:

0.00052 0.00134 0.00103 0.00149 0.00085 0.00068 0.00077 0.00088 0.00169 0.00063 

For reasons unknown to me, some values ​​appear in the database in scientific notation, for example:

1.12305e-06

The table is set to float, and I tried all kinds of functions in PHP to make the numbers appear as decimal, but to no avail. Try, as I could, I cannot get this table of numbers to be sequentially decimal in all cases.

Any suggestions to fix this? We tried typcasting on (float) and used number_format() and several other parameters, but did not change it every time.

Answer 1

There seems to be a six-digit limit on what is shown in the CLI (and probably elsewhere). The example you have is 1.12305e-06, which is 0.00000112305, which will display as 0.00000 - although obviously it is not zero.

If you insist on using floats or doubles, you will have to preempt them using something like round(columnName,5) to force the display to a decimal value. Otherwise, perhaps switch to the decimal data type.

From http://dev.mysql.com/doc/refman/5.0/en/floating-point-types.html

Since floating point values ​​are approximate and not stored as exact values, attempts to treat them as accurate in comparison can lead to problems. They also depend on platform or implementation dependencies. See Section C.5.5.8, “ Problems with Floating-Point Values ” for more information.

Also see this thread in the mysql forums about this exact issue.

Answer 2

The solution in my case turned out to be that it changed from float to decimal type in the database, so thanks to Romain for the comment, which made me look into this solution!