发现mysql 4.0.18 for nt 的sum函数的计算误差

最新推荐文章于 2021-01-27 13:32:44 发布

l1t

最新推荐文章于 2021-01-27 13:32:44 发布

阅读量2.6k

点赞数

文章标签： mysql warnings query duplicates insert table

本文链接：https://blog.csdn.net/l1t/article/details/122777

版权

发现mysql 4.0.18 for nt 的sum函数的计算误差
mysql> create table ch3(ID char(32), V decimal(18,2));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into ch3 select * from vc2;
Query OK, 8192 rows affected (1.24 sec)
Records: 8192 Duplicates: 0 Warnings: 0

mysql> select max(v) from ch3;
+-------------+
| max(v) |
+-------------+
| 99999999.99 |
+-------------+
1 row in set (1.17 sec)

mysql> select min(v) from ch3;
+-------------+
| min(v) |
+-------------+
| 99999999.99 |
+-------------+
1 row in set (1.17 sec)
mysql> select sum(v) from ch3;
+-----------------+
| sum(v) |
+-----------------+
| 819199999918.02 |
+-----------------+
1 row in set (0.08 sec)

mysql> update ch3 set v=100000000;
Query OK, 8192 rows affected (0.12 sec)
Rows matched: 8192 Changed: 8192 Warnings: 0

mysql> select sum(v) from ch3;
+-----------------+
| sum(v) |
+-----------------+
| 819200000000.00 |
+-----------------+
1 row in set (0.06 sec)

mysql> update ch3 set v=100000000-0.01;
Query OK, 8192 rows affected (0.25 sec)
Rows matched: 8192 Changed: 8192 Warnings: 0

mysql> select sum(v) from ch3;
+-----------------+
| sum(v) |
+-----------------+
| 819199999918.02 |
+-----------------+
1 row in set (0.08 sec)

mysql> update ch3 set v=100000000+0.01;
Query OK, 8192 rows affected (0.23 sec)
Rows matched: 8192 Changed: 8192 Warnings: 0

mysql> select sum(v) from ch3;
+-----------------+
| sum(v) |
+-----------------+
| 819200000081.98 |
+-----------------+
1 row in set (0.09 sec)
mysql> select 99999999.99*8192;
+------------------+
| 99999999.99*8192 |
+------------------+
| 819199999918.08 |
+------------------+
1 row in set (0.00 sec)