B.4.4.8 浮点值问题
浮点数有时会引起混淆,因为它们是近似值而不是作为精确值存储的。 SQL 语句中编写的浮点值可能与内部 table 示的值不同。尝试在比较中将浮点值视为精确值可能会导致问题。它们还受平台或实现依赖性的约束。 FLOAT和DOUBLE数据类型受这些问题的影响。对于DECIMAL列,MySQL 执行的精度为 65 个十进制数字,这应该可以解决最常见的不准确性问题。
下面的示例使用DOUBLE演示使用浮点运算完成的计算如何受到浮点错误的影响。
mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
-> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
-> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
-> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
-> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
-> (6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+-------+------+
| i | a | b |
+------+-------+------+
| 1 | 21.4 | 21.4 |
| 2 | 76.8 | 76.8 |
| 3 | 7.4 | 7.4 |
| 4 | 15.4 | 15.4 |
| 5 | 7.2 | 7.2 |
| 6 | -51.4 | 0 |
+------+-------+------+
结果是正确的。尽管前五个记录看起来不应该满足比较要求(a
和b
的值看起来并不相同),但它们之所以会这样做,是因为数字之间的差异出现在十分之一左右,具体取决于因素例如计算机体系结构或编译器版本或优化级别。例如,不同的 CPU 可能会不同地评估浮点数。
如果将d1
和d2
列定义为DECIMAL而不是DOUBLE,则SELECT查询的结果将仅包含一行-上面显示的最后一行。
进行浮点数比较的正确方法是,首先确定数字之间差异的可接受公差,然后再与公差值进行比较。例如,如果我们同意,如果浮点数在万分之一(0.0001)的精度内相同,则应将它们视为相同,那么应进行比较以找出大于公差值的差异:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+-------+------+
| i | a | b |
+------+-------+------+
| 6 | -51.4 | 0 |
+------+-------+------+
1 row in set (0.00 sec)
相反,要获得数字相同的行,测试应在公差值内找到差异:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) <= 0.0001;
+------+------+------+
| i | a | b |
+------+------+------+
| 1 | 21.4 | 21.4 |
| 2 | 76.8 | 76.8 |
| 3 | 7.4 | 7.4 |
| 4 | 15.4 | 15.4 |
| 5 | 7.2 | 7.2 |
+------+------+------+
5 rows in set (0.03 sec)
浮点值取决于平台或实现的依赖性。假设您执行以下语句:
CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;
在某些平台上,SELECT
语句返回inf
和-inf
。在其他情况下,它返回0
和-0
。
前述问题的含义是,如果您尝试通过在源上使用mysqldump转储 table 内容并将转储文件重新加载到副本中来创建副本,则两个主机之间包含浮点列的 table 可能会有所不同。