Morgan Tocker (mtocker) wrote,
Morgan Tocker
mtocker

Precision mathematics in MySQL

As documented, the FLOAT datatype does not guarantee precise storage of decimal values. But where the non-precision is apparent can be a little confusing - take the following example:

mysql> CREATE TABLE my_table (a FLOAT);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into my_table (a) VALUES ('2.2');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM my_table WHERE a = 2.2;
Empty set (0.05 sec)

mysql> SELECT a, IFNULL(a,0) FROM my_table;
+------+-----------------+
| a    | IFNULL(a,0)     |
+------+-----------------+
|  2.2 | 2.2000000476837 | 
+------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT a, a+0 FROM my_table;
+------+-----------------+
| a    | a+0             |
+------+-----------------+
|  2.2 | 2.2000000476837 | 
+------+-----------------+
1 row in set (0.00 sec)


Need precision? Try Decimal.
Tags: mysql, php, wtf
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 2 comments