发现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)
本文地址：http://com.8s8s.com/it/it26989.htm