mysql float data returning different value

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

Can you provided the Select or other query you are using to retrieve the value? — Grice
– Grice, Commented Sep 22, 2014 at 14:58

VMai · Accepted Answer · 2014-09-22 15:35:04Z

2

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.

edited Sep 22, 2014 at 15:35

answered Sep 22, 2014 at 15:11

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql float data returning different value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related