MySQL Float Approximation and Precision

Question

I've got a column set as a FLOAT(32,11) and I'm getting some odd results with storing values. Now I know that floats are only an approximation however this seems to be a horrible approximation especially with the given precision.

mysql> CREATE TABLE test (a FLOAT(32,11));
mysql> INSERT INTO test (a) VALUES (300000.08);
mysql> SELECT * FROM test;
+--------------------+
| a                  |
+--------------------+
| 300000.09375000000 |
+--------------------+
1 row in set (0.00 sec)

Seems odd that it isn't using all the available precision. Any ideas on what I'm missing?

MySQL Version 5.1.73 on CentOS

You're probably running in a storage limitation. if you insert 30,000.08 (small by factor of 10), you get 30,000.08007812500 back out. — Marc B
– Marc B, Commented May 19, 2015 at 20:56

Barmar · Accepted Answer · 2015-05-19 21:11:24Z

2

FLOAT is single-precision floating point. This only has 24 bits of mantissa. This isn't enough to hold 8 decimal digits accurately, so you get an error in the lowest digit.

If you use DOUBLE instead of FLOAT, you get a more accurate result.

mysql> CREATE TABLE test (a DOUBLE(32,11));    
mysql> INSERT INTO test (a) VALUES (300000.08);    
mysql> select * from test;
+--------------------+
| a                  |
+--------------------+
| 300000.08000000002 |
+--------------------+

answered May 19, 2015 at 21:11

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Comments

Barranka · Accepted Answer · 2015-05-19 20:57:51Z

1

I don't know why the loss of precision is generated, but, quoting from the reference manual:

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

So, the alternative I suggest you is: Use DECIMAL instead of FLOAT (or DOUBLE) to enforce precission

answered May 19, 2015 at 20:57

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Collectives™ on Stack Overflow

MySQL Float Approximation and Precision

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related