1. 首页
  2. mysql基础

14-十四、雪上加霜的 MySQL 浮点类型 ( float 和 double )

MySQL 中,有一条忠告,就是 「 不要使用浮点类型,不要使用浮点类型,不要使用浮点类型 」,如果真要使用,那么也请使用 DECIMAL 类型。

为什么呢 ?

因为浮点类型的值是 「 近似 」精度,而且,因为它们都有使用 48 字节存储,而会带来另一些往往想不到的雪上加霜的问题。

近似值的浮点类型

浮点数有时会引起混淆,因为它们存储的是近似值而不是精确值,这就可能直接导致了在 SQL 语句中写入的浮点值可能与内部存储的值不同。

另一方面,在比较操作中,尤其是想等于操作 ( = ),尝试将浮点值视精确值可能会导致问题。

更惨的是,它们还受平台或实现依赖性的影响。

因为 FLOATDOUBLE 数据类型受这些问题的影响,所以, MySQL 推出了更好的数据类型 DECIMAL

对于 DECIMAL 列,MySQL 执行的是精度为 65 位十进制数的操作,这可以解决最常见的不准确问题。

下面的范例,使用 DOUBLE 演示浮点运算是如何受浮点错误的影响


mysql> DROP TABLE IF EXISTS t1; 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 可以不同地评估浮点数。

但如果我们将列 d1d2 定义为 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 | +------+-------+------+ 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 | +------+------+------+ 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 转储表内容并将转储文件重新加载到从服务器来创建复制从服务器,则包含浮点列的表可能在两个主机之间有所不同

希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:

  1. 关注我们的 GitHub 博客,让我们成为长期关系
  2. 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
  3. 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
  4. JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。

    本文著作权归作者所有,如若转载,请注明出处

    转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com

    标题:14-十四、雪上加霜的 MySQL 浮点类型 ( float 和 double )

    链接:https://www.javajike.com/article/1629.html

« 15-十五、详解 MySQL 中的位类型 ( bit )
13-十三、MySQL 数值类型溢出处理»

相关推荐

QR code