MySql FLOAT数据类型和超过7位数字刻度的问题 [英] MySql FLOAT datatype and problems with more then 7 digit scale
问题描述
我们在Ubuntu 9.04上使用MySql 5.0.完整版本为:5.0.75-0ubuntu10
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)
在mySql中设置的缩放比例/精度似乎有问题...还是我错过了任何事情?
There seems to be a problem with the scale/precision set up in mySql...or did I miss anything?
更新:
找到我们要插入的数字之一的边界,这是代码:
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 !!!!
Face Palm!!!!
浮点数是存储为尾数和指数的32位数字.我不确定100%MySql将如何拆分存储,但是以Java为例,他们将使用24位有符号尾数和8位为指数(科学记法).这意味着FLOAT可以具有的最大值是+ 8388608 * 10 ^ 127,最小值是-8388608 * 10 ^ 127.这意味着只有7位有效数字,而我的FLOAT定义使用的是8位.
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.
我们将把所有8,2都从FLOAT切换为DOUBLE.
We are going to switch all of these 8,2 to DOUBLE from FLOAT.
这篇关于MySql FLOAT数据类型和超过7位数字刻度的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!