Double类型精度问题造成的错误
发布时间：2022-04-01 12:22:45 所属栏目：MySql教程 来源：互联网
导读：研发同事让把某个double类型字段的值四舍五入保留2位小数，mysql中round(col,2)可以实现四舍五入并且保留2位小数，但是神奇的事情发生了：发现有的四舍五入是正确的，而有的不是我们想要的结果，如下：简单模拟此场景： yujxdrop table dd; yujxcreate tab
|
研发同事让把某个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) 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, seeSection 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位精度进行操作，可以解决此类精度问题。 综上，如果想精确的存储浮点数值，应该使用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。 DECIMAL和NUMBERIC存储的是确切的数值，使用它们可以保证精确度，例如用于存储金额数据。在MySQL中,NUMBERIC和DECIMAL以同样的类型实现。 （编辑：云计算网_泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330476号