Double类型精度问题引起的错误

日期：2020-06-02 栏目：程序人生浏览：次

研发同事让把某个double类型字段的值四舍五入保留2位小数，mysql中round(col,2)可以实现四舍五入并且保留2位小数，但是神奇的事情发生了：发现有的四舍五入是正确的，而有的不是我们想要的结果，如下：简单模拟此场景：

yujx>drop table dd;
yujx>create table dd (a double);
yujx>insert into dd values(956.745),(231.34243252),(321.43534),(5464.446);
yujx>select a,round(a,2) from dd;
+--------------+------------+
| a | round(a,2) |
+--------------+------------+
| 956.745 | 956.74 | #可以看到并不是我们期望的956.75
| 231.34243252 | 231.34 |
| 321.43534 | 321.44 |
| 5464.446 | 5464.45 |
+--------------+------------+
4 rows in set (0.00 sec)

如上，有的是正确的，有的不是我们期望的值，那么问题来了，为什么呢？

通过询问和网上搜索后，发现可能是因为double的精度问题导致的

查阅MySQL官当关于double、Float、DECIMAL, NUMERIC类型的介绍，如下：

FLOAT, DOUBLE

#为了说明问题，摘了官当的部分内容，关于更详细的介绍请直接查看官当。

12.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

#可以看到标题已经指出 Float和double是近似值

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.5.8, “Problems with Floating-Point Values

因为Float、Double存储的是一个近似值而不是确切的值，试图使用它们存储一个确切的值可以会导致问题。它们依赖于不同平台和不同实现方式，而官当在章节Section B.5.5.8, “Problems with Floating-Point Values中举例说明了此问题

参考：

“Problems with Floating-Point Values”

B.5.4.8 Problems with Floating-Point Values

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. The FLOAT and DOUBLE data types are subject to these issues. For DECIMALcolumns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.

由于浮点数存储的是近似值而不是确切的值，某些时候可能导致混乱。一个浮点数值在SQL语句作为内部表示的值可能不同。试图使用float、double来存储确切的值可能会出现问题，他们也依赖不同平台和实现方式。而对应DECIMAL类型，MySQL作为65位精度进行操作，可以解决此类精度问题。

如下例子证明使用Double运算遇到的异常错误

Double类型精度问题引起的错误