Why is MySQL is returning some floats in scientific notation, but not others?

Question

I'm writing a PHP script that looks through a DB table of float values, that tend to be somewhat small, such as:

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 DB in scientific notation, such as:

1.12305e-06

The table is set to float, and I've tried all manner of functions in PHP to force the numbers to display as decimal, to no avail. Try as I might, I'm unable to get this table of numbers to be consistently decimal in all cases.

Any suggestions on how to resolve this? Have tried typcasting to (float) and using number_format() and several other options, but no change every time.

Answer 1

There seems to be a six digit limit on what is shown out of the CLI (and probably elsewhere). The example you have is 1.12305e-06 which is 0.00000112305 which would be shown as 0.00000 - though clearly it isn't zero.

If you are insisting on using floats or doubles, you will have to force them out using something like round(columnName,5) to force the display in a decimal value. Otherwise, maybe switch to a decimnal data type.

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

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section C.5.5.8, “Problems with Floating-Point Values”

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