MySQL returns floats rounded

floating pointMySQL

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

Best 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;

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;

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.

BAD NEWS : I do not think the value will be preserved !!!

I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.

GOOD NEWS : Here is how you can safely compare the data conversion:

If your table has these characteristics

table is called moneytable
field you wish to convert is called moneyfield
primary key is called moneyid

Then run these commands:

DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.

Give it a Try !!!

MySQL execute statement returns ‘NULL’

Whenever you are trying to concatenate a string with NULL, NULL is always returned, and for some reason @ is interpreted as NULL in MySQL. That is why the query returns NULL.

Since you wrap the string in the shell with " and you use it inside the query, the ones inside the query will be removed (literally, it is 3 strings that is being passed to MySQL).

You need to escape the quotation marks inside the query.

Like this:

# mysql test -e "SELECT CONCAT(mail_name,\"@\",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"

# mysql test -e "SELECT CONCAT(mail_name,'@',name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"

Best Answer

Related Solutions

MySQL conversion from float (10,2) to fixed(10,2)

BAD NEWS : I do not think the value will be preserved !!!

GOOD NEWS : Here is how you can safely compare the data conversion:

MySQL execute statement returns ‘NULL’

Related Question