mysql float 多少位_MySql FLOAT数据类型和超过7位数字刻度的问题

We are using MySql 5.0 on Ubuntu 9.04. The full version is: 5.0.75-0ubuntu10

I created a test database. and a test table in it. I see the following output from an 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 the scale/precision set up in mySql...or did I miss anything?

UPDATE:

Found a boundary for one of the numbers we were inserting, 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>

解决方案

Face Palm!!!!

Floats are 32 bit numbers stored as mantissa and exponents. I am not 100% sure how MySql will split the storage but taking Java as an example they would use 24 bits for a signed mantissa and 8 bits for an exponent (scientific notation). This means that the maximum value a FLOAT can have is +8388608*10^127 and the minimum is -8388608*10^127. This means only 7 significant digits, and my FLOAT definition used 8.

We are going to switch all of these 8,2 to DOUBLE from FLOAT.