Shulou(Shulou.com)06/01 Report--

Scene description

Research and development colleagues asked the value of a double field to be rounded to retain 2 decimal places. Round (col,2) in mysql can achieve rounding and retain 2 decimal places, but something amazing happens: it is found that some of the rounding is correct, while others are not the results we want, as follows: simply simulate this scenario:

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 dint round (ameme 2) from dd

+-+ +

| | a | round (aPol 2) |

+-+ +

| | 956.745 | 956.74 | # you can see that it is not the 956.75 we expected |

| | 231.34243252 | 231.34 | |

| | 321.43534 | 321.44 | |

| | 5464.446 | 5464.45 | |

+-+ +

4 rows in set (0.00 sec)

As mentioned above, some are correct and some are not the values we expect, so the question is, why?

After inquiring and searching on the Internet, it is found that it may be caused by the accuracy of double.

Check out the introduction of double, Float, DECIMAL, and NUMERIC types by MySQL official, as follows:

FLOAT, DOUBLE# in order to explain the problem, extracted part of the content of Guandang, for a more detailed introduction, please check Guandang directly.

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

# you can see that the title has indicated that Float and double are approximate values

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"

Because Float and Double store an approximate value rather than an exact value, trying to use them to store an exact value can cause problems. They depend on different platforms and different implementations, and Guandang illustrates this problem in chapter Section B.5.5.8, "Problems with Floating-Point Values."

Reference: http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html

"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.

Because floating-point numbers store approximate values rather than exact values, it can sometimes lead to confusion. A floating-point value may be represented differently as an internal value in the SQL statement. Trying to use float and double to store exact values can be problematic, and they also rely on different platforms and implementations. Corresponding to the DECIMAL type, MySQL operates as a 65-bit precision, which can solve this kind of precision problem.

The following example demonstrates the exception error encountered using the Double operation

This result is incorrect, although the values of an and b in the first five do not seem to satisfy the ab condition.

This phenomenon depends on a variety of factors, such as computer architecture, compiler version, or optimization level. For example, different CPU evaluations have different floating-point numbers.

If you change the fields D1 and D2 to DECIMAL type, this problem will not exist. As follows:

To sum up, if you want to store floating-point values accurately, you should use DECIMAL. Such as the amount of money, etc.

DECIMAL, NUMERIC11.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 and NUMBERIC store exact values, and use them to ensure accuracy, such as for storing amount data. In MySQL, NUMBERIC and DECIMAL are implemented in the same type.

Double has the problem of precision error not only in MySQL, but also in oracle, Java and other platforms for double types of other platforms, as follows:

A simple 0.2-0.4, but the result returned is not 0.6.

Going back to the beginning of the question, if you change to DECIMAL type, the result of round will be correct, as follows:

Yujx > drop table dd

Yujx > create table dd (a double,b decimal (301.10))

Yujx > insert into dd

Values (956.745956.745), (231.34243252231.34243252), (321.43534321.43534)

(5464.446)

Yujx > select a dint round (ameme 2) from dd

To sum up

Just to illustrate, if you want to store decimals exactly (for example, amount, etc.), it is recommended that you use the DECIMAL type instead of the DOUBLE, float type.

Reference:

Http://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html

Http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html

Http://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html

Http://justjavac.com/codepuzzle/2012/11/11/codepuzzle-float-who-stole-your-accuracy.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.