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

研发同事让把某个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官当关于doubleFloatDECIMAL, 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类型精度问题引起的错误

此结果是错误的,尽管前5条的a和b的值看起来是不满足a<>b条件的。

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

此现象取决于各种因素,如计算机架构、编译器版本或者优化级别等。例如,不同的CPU评估的浮点数不同

如果将字段d1和d2改成DECIMAL类型,将不会存在此问题。如下:

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

综上,如果想精确的存储浮点数值,应该使用DECIMAL.比如金额等。

DECIMAL, NUMERIC

11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it

is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is

implemented as DECIMAL。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/545ae48d0bd25add8affbf4411798be7.html