1
> 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?

2 Answers 2

1

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.

0

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.

2
  • It's for geographical coordinates, so floating point makes more sense here. Definitely not going to use FP anywhere near money:)
    – MaxSem
    Dec 16, 2014 at 23:15
  • Yeah, those kind of info is not so error-defensive somehow :)
    – T_G
    Dec 16, 2014 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.