MySQL returns floats rounded

Posted on

Question :

> CREATE TABLE test(foo FLOAT);
> INSERT INTO test VALUES(1.2899999);
> SELECT * FROM test;
+------+
| foo  |
+------+
| 1.29 |
+------+

While it’s obvious that FLOAT is not a precise data type, it is still rounding this data too arbitrarily. Let’s check if it’s actually able to store it:

> ALTER TABLE test MODIFY foo DOUBLE;
> SELECT * FROM test;
+--------------------+
| foo                |
+--------------------+
| 1.2899998426437378 |
+--------------------+

Whoops, only the 7th digit got corrupted due format limitations. Is there a way to prevent overzealous rounding in this situation, short of converting the column to DOUBLE?

Answer :

I hate precision-based issues. I dealt with one before: Data Truncated for Column

You may have do the ALTER TABLE manually

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT CONVERT(foo,DOUBLE) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

or

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT CONVERT(foo,DECIMAL(10,7)) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

or

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT FORMAT(foo,7) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

Not sure what will happen, but give it a try and see what happens.

Maybe this post could be somehow useful for your question – as far as I had experience with till now (even in ERP db we serviced for nearly 5 years) type FLOAT or DOUBLE was almost never used.
Instead of it all the fields storing values like cost prices, purchase prices, stock item characteristics, warehouse, production, accountance data – always had fixed decimal places count by using i.e. NUMERIC(20,8) (MSSQL) which of course in MySQL would be DECIMAL(20,8).
Thus, if any rounding were to be performed, it must have been done (mostly) in the application layer.

Leave a Reply

Your email address will not be published.