mysql float 误差_MySQL float类型存储误差测试

博客文章除注明转载外，均为原创。

1、创建测试表

root@localhost [dbwatcher]> create table float_table01(fund float(7,4));

Query OK, 0 rows affected (0.06 sec)

root@localhost [dbwatcher]> show tables;

+------------------+

| Tables_in_dbwatcher |

+------------------+

| float_table01    |

+------------------+

1 row in set (0.00 sec)

开始测试：

root@localhost [dbwatcher]> insert into float_table01 values(123.312);

Query OK, 1 row affected (0.04 sec)

root@localhost [dbwatcher]> insert into float_table01 values(123.3121);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> insert into float_table01 values(1234.214);

ERROR 1264 (22003): Out of range value for column 'fund' at row 1

root@localhost [dbwatcher]> insert into float_table01 values(1234.2142);

ERROR 1264 (22003): Out of range value for column 'fund' at row 1

root@localhost [dbwatcher]> insert into float_table01 values(1234.22);

ERROR 1264 (22003): Out of range value for column 'fund' at row 1

root@localhost [dbwatcher]> insert into float_table01 values(134.22002);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> insert into float_table01 values(134.22006);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> insert into float_table01 values(999.00009); --小数位数超了，自动取近似值，进位

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> insert into float_table01 values(999.00001); --小数位数超了，自动取近似值，进位

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> commit;

Query OK, 0 rows affected (0.00 sec)

root@localhost [dbwatcher]> select * from float_table01;

+----------+

| fund     |

+----------+

| 123.3120 |

| 123.3121 |

| 134.2200 |

| 134.2201 |

| 999.0001 |

| 999.0000 |

+----------+

说明：小数位超了，自动四舍五入

继续往下：

root@localhost [dbwatcher]> insert into float_table01 values(124.2); --小数点位数不够，自动补足

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> commit;

Query OK, 0 rows affected (0.00 sec)

结果如下：

root@localhost [dbwatcher]> select * from float_table01;

+----------+

| fund     |

+----------+

| 123.3120 |

| 123.3121 |

| 134.2200 |

| 134.2201 |

| 999.0001 |

| 999.0000 |

| 124.2000 |

+----------+

7 rows in set (0.00 sec)

说明：小数点位数不够，自动补足

root@localhost [dbwatcher]> insert into float_table01 values(1000.9999); --超出范围，无法插入

ERROR 1264 (22003): Out of range value for column 'fund' at row 1

root@localhost [dbwatcher]> insert into float_table01 values(999.9999);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> commit;

Query OK, 0 rows affected (0.00 sec)

结果如下：

root@localhost [dbwatcher]> select * from float_table01;

+----------+

| fund     |

+----------+

| 123.3120 |

| 123.3121 |

| 134.2200 |

| 134.2201 |

| 999.0001 |

| 999.0000 |

| 124.2000 |

| 999.9900 |

| 999.9999 |

+----------+

9 rows in set (0.00 sec)

说明：最大值为：999.9999

root@localhost [dbwatcher]> select * from float_table02;

+-----------+

| fund      |

+-----------+

| 999999.25 |

+-----------+

1 row in set (0.00 sec)

root@localhost [dbwatcher]> insert into float_table02 values(131071.21);

Query OK, 1 row affected (0.00 sec)

(root@localhost [dbwatcher]> insert into float_table02 values(131072.21);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> commit;

Query OK, 0 rows affected (0.00 sec)

查询结果：

root@localhost [dbwatcher]> select * from float_table02;

+-----------+

| fund      |

+-----------+

| 999999.25 |

| 131071.21 |

| 131072.20 |

+-----------+

3 rows in set (0.00 sec)

root@localhost [dbwatcher]> insert into float_table02 values(131072.00);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> select * from float_table02;

+-----------+

| fund      |

+-----------+

| 999999.25 |

| 131071.21 |

| 131072.20 |

| 131072.00 |

+-----------+

4 rows in set (0.00 sec)

说明：最大值为131072，超过最大值发现存储出现误差

root@localhost [dbwatcher]> create table q(

-> money float(10,3));

Query OK, 0 rows affected (0.07 sec)

root@localhost [dbwatcher]> insert into q values(1234567.3);

Query OK, 1 row affected (0.00 sec)

root@localhost [dbwatcher]> commit;

Query OK, 0 rows affected (0.00 sec)

root@localhost [dbwatcher]> select * from q;

+-------------+

| money       |

+-------------+

| 1234567.250 |

+-------------+

1 row in set (0.00 sec)

总结：

float列类型默认长度查不到结果，必须指定精度，否则可能查不到记录

FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。

例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001；

MySQL数据精度控制相关数据类型如下三种：

float(M,D)，如果D不指定的话，默认是3位精度(小数点位数)

double(M,D)，如果D不指定的话，默认是14位精度(小数点位数)

DECIMAL(M,D)，如果D不指定的话，默认是0位精度(小数点位数)

---the end

阅读(931) | 评论(0) | 转发(0) |