MySql FLOAT data type and problems with more than seven-digit scale

Question

We are using MySql 5.0 on Ubuntu 9.04. Full version: 5.0.75-0ubuntu10

I created a test database. and a test table in it. I see the following output from the insert statement:

mysql> CREATE TABLE test (floaty FLOAT(8,2)) engine=InnoDb; Query OK, 0 rows affected (0.02 sec) mysql> insert into test value(858147.11); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM test; +-----------+ | floaty | +-----------+ | 858147.12 | +-----------+ 1 row in set (0.00 sec) 

There seems to be a problem with adjusting scale / accuracy in mySql ... or am I missing anything?

UPDATE:

Found the border for one of the numbers we inserted, here is the code:

 mysql> CREATE TABLE test (floaty FLOAT(8,2)) engine=InnoDb; Query OK, 0 rows affected (0.03 sec) mysql> insert into test value(131071.01); Query OK, 1 row affected (0.01 sec) mysql> insert into test value(131072.01); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test; +-----------+ | floaty | +-----------+ | 131071.01 | | 131072.02 | +-----------+ 2 rows in set (0.00 sec) mysql> 

Answer 1

Face Palm !!!!

Floats are 32-bit numbers stored as mantissa and exponents. I'm not 100% sure how MySql will share the repository, but instead of Java it will use 24 bits for a signed mantissa and 8 bits for an exponent (scientific notation). This means that the maximum value that FLOAT can have is +8388608 * 10 ^ 127, and the minimum is -8388608 * 10 ^ 127. This means only 7 significant digits, and my definition of FLOAT uses 8.

We are going to switch all of these 8.2 to DOUBLE from FLOAT.

Answer 2

MySQL docs mention "MySQL performs rounding when storing values", and I suspect this is a problem. I duplicated your problem, but changed the storage type to DOUBLE:

 CREATE TABLE test (val, DOUBLE); 

and the resulting value corresponds to the specified test value.

My suggestion, for what it's worth, is using DOUBLE or, possibly, DECIMAL. I tried the same original test with:

 CREATE TABLE test (val, DECIMAL(8,2)); 

and he received the meaning that I gave him: 858147.11.