How to disable exponential notation when choosing float from MySQL?

Question

I have a FLOAT column that contains very small digits like 0.00000000000234

When I select them in MySQL, I return them to exponential notation, for example 2.34e-13. Is there a way to change the formatting of these numbers to force MySQL to return it as 0.00000000000234?

Ultimately, what I would like to do is change the mapping, not the basic representation of the number. Thus, it is likely to be a display or format setting. MySQL has a FORMAT () function, but it doesn't look like what I want (it puts numbers in a format like 123,456.78).

Answer 1

You can force the number to return to the desired display format. Pass it to a sufficiently large DECIMAL value, and then enter DECIMAL in CHAR to get the display form you are using. This is pure mapping and allows you to continue to store floats in mysql (if necessary).

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) 

Of course, you will lose some accuracy, depending on the value.

Answer 2

You should use DECIMAL to store these values, since floating point values ​​are all approximate and inaccurate when dealing with such small numbers. A DECIMAL will be displayed the way you want.

Answer 3

You can use the round. i.e.

select a round (column, 10) from foo;