Why is MySQL round float so much larger than expected?

Question

UPDATE some_table SET some_float_field=1919.987 WHERE id=123 SELECT * FROM some_table WHERE id=123 

where some_float_field is a field defined as a "float" (without any specific size values).

The expected final value will be 1919.987; instead, it is rounded to 1919.99

Why? The 32-bit (single-point) float has sufficient accuracy for proper storage!

Answer 1

When running the query:

 SELECT * FROM some_table WHERE id = 123 

You rely on the user interface to format floating point numbers. The interface used uses two characters, not more. In the end, there is no information about the “right” show number.

You can convince the interface to display the correct number by formatting it as a string or decimal. For example:

 select format(some_float_field, 3) 

converts this to a string with three decimal places. One caveat: it will also add commas that you might not want. This should also work:

 select cast(some_float_field as decimal(8, 3)) 

should also do the trick.

Note that you can easily verify the correctness of the data by doing something like:

 select * from some_table where some_float_field between 1919.987 - 0.0001 and 1919.987 + 0.0001; 

Note that you do not want to use = for floating point values, but you already understand that.

Answer 2

from the FLOAT manual:

The types FLOAT and DOUBLE represent approximate numerical values . MySQL uses four bytes for values ​​with one precision.

The emphasis here is on the approximate . If you need to keep the values accurate , you should really use the DECIMAL data DECIMAL .

For FLOAT , MySQL uses the IEEE 754 standard for binary floating-point arithmetic to “compress” any fraction of a 4-byte number (or 8, for DOUBLE) .

Note that this applies to any value, regardless of whether the value (decimal and fraction) is represented in 4 bytes! For example, a floating point representation of 0.01 is exactly 0.009999999776482582092285156250 - although 0.01 fits perfectly in 32-bit memory using a different storage format.

Wikipedia describes the concept of floating point pretty well .

Note that the algorithm is affected by the precision specified in the column definition.
See this sample SQL script.