how can I disable exponential notation when selecting a float from MySQL?

Question

I have a FLOAT column that contains some very small numbers like 0.00000000000234

When I select them in MySQL, I get them back in exponential notation like 2.34e-13. Is there any way I can modify the formatting of these numbers to force MySQL to return it as 0.00000000000234 ?

Ultimately what I'd be looking to do is modify the display, not the underlying representation of the number. So it would probably be a display or formatting setting. MySQL has a FORMAT() function, but it's nothing like what I want (it puts numbers in format like 123,456.78).

Answer 1

You can coerce the number back to the desired display format. Cast it to a large enough DECIMAL value and then cast the DECIMAL to a CHAR in order to get the display form you are after. This is purely display and allows you to continue to store floats in mysql (if so desired).

mysql> SELECT CAST(CAST(f AS DECIMAL(14,14)) AS CHAR) AS example from test;
+------------------+
| example          |
+------------------+
| 0.00000000000234 |
+------------------+
1 row in set (0.00 sec)

You will lose some precision of course, depending on the value.

Answer 2

You should consider using DECIMAL to store those values, as float values are all approximated, and are inaccurate when dealing with such small numbers. A DECIMAL would be displayed as you want.