【翻译】MySQL 浮点数问题

因为浮点数是近似值，不会存储为精确值，有时会造成混乱。在 SQL 语句中写入的浮点值可能与内部表示的值并不相同。当尝试在比较中精确地处理浮点值时，可能会导致一些问题。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 |
+------+-------+------+
6 rows in set (0.00 sec)

结果是正确的。虽然前五个记录看起来不应该满足比较（a 和 b 的值看起来不是不同），但是它们可以这样做，因为数字之间的差异显示在大约第十个十进制左右，这取决于如计算机体系结构或编译器版本或优化级别之类的因素。例如，不同的 CPU 可以不同地运算浮点数。

实测结果（Ubuntu x64, MySQL 5.7）如下，很好的说明了这个问题：

+------+--------------------+------+
| i    | a                  | b    |
+------+--------------------+------+
|    1 | 21.400000000000006 | 21.4 |
|    2 |  76.80000000000001 | 76.8 |
|    3 |  7.399999999999999 |  7.4 |
|    4 | 15.399999999999999 | 15.4 |
|    5 |  7.199999999999999 |  7.2 |
|    6 |              -51.4 |    0 |
+------+--------------------+------+

如果 d1 和 d2 列已定义为 DECIMAL 而不是 DOUBLE，则 SELECT 查询的结果将只包含上面显示的最后一行。

进行浮点数比较的正确方法是首先确定数字之间差异的可接受容差（acceptable tolerance），然后与容差值进行比较。例如，如果我们同意浮点数在一万分之一（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.02 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.01 sec)

浮点值受平台或实现依赖性的制约。假设您执行以下语句：

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.01 sec)

上面是我的环境返回的结果，在某些平台上，SELECT 语句返回 inf 和 -inf。而其他的，可能返回 0 和 -0。

上述问题的含义是，如果您尝试在主节点上通过使用 mysqldump 转储表的内容来创建复制从属节点，并将转储文件重新加载到从属节点，则包含浮点列的表在两个主机之间可能不同。

原文地址：
MySQL :: MySQL 5.7 Reference Manual :: B.5.4.8 Problems with Floating-Point Values