mysql浮点数据返回不同的值

Question

I am trying to create a float value with many decimals. My table

CREATE TABLE number_type_check (
    id INT(10) NULL DEFAULT NULL,
    floatNumber FLOAT(18,5) NULL DEFAULT NULL
)

When I run my query:

INSERT INTO number_type_check (floatNumber) VALUES (987654321.98765);

It returns 987654336.00000 not 987654321.98765

Answer 1

That's an usual rounding problem with FLOAT. You get a 4-byte-single precision FLOAT column, because your precision value is less than 24. You can't expect 14 digits precision from such a 4-byte-singe precision column, see Single precision floating point format

MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column.
[...]
MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001

Source: Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

Use either DECIMAL or DOUBLE if you need higher precision.

Have a look at this demo.