Error Caused by Double Type Precision

Error Caused by Double Type Precision

Scenario Description

R & D colleagues asked me to round the value of a double type field to keep two decimal places. In mysql, round (col, 2) can be rounded to five and keep two decimal places, but the magic thing happened: it is found that some rounding are correct, and some are not the expected results, as follows: A simple simulation of 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, round (a, 2) from dd;
+ -------------- + ------------ +
| A | round (a, 2) |
+ -------------- + ------------ +
| 956.745 | 956.74 | # we can see that this is not the expected 956.75.
| 231.34243252/231.34 |
| 321.43534/321.44 |
| 5464.446/5464.45 |
+ -------------- + ------------ +
4 rows in set (0.00 sec)

Some of the above are correct, some are not our expected values, so the question is, why?

After querying and searching on the internet, it may be caused by the precision of double.

For more information about the double, Float, DECIMAL, and NUMERIC types, see MySQL:

FLOAT, DOUBLE

# In order to explain the problem, I have extracted some content from the official website. For more details, see Official website.

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

# The title indicates 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, see Section B .5.5.8, "Problems with Floating-Point Values

Because Float and Double store an approximate value instead of an exact value, trying to store an exact value can cause problems. They depend on different platforms and implementation methods, and the issue is illustrated in 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 shocould solve most common inaccuracy problems.

Floating Point Numbers store approximate values rather than exact values, which may lead to confusion in some cases. The value of a floating point may be different as an internal representation of the SQL statement. Trying to store exact values using float and double may cause problems. They also rely on different platforms and implementation methods. For the DECIMAL type, MySQL can operate with 65-bit precision to solve this type of precision problem.

The following example shows the error code returned when the Double operation is used.

This result is incorrect, although the values of a and B in the first five do not seem to meet the <> B condition.

This phenomenon depends on various factors, such as the computer architecture, compiler version, or optimization level. For example, different CPUs evaluate different floating point numbers.

If you change the fields d1 and d2 to the DECIMAL type, this problem does not exist. As follows:

In summary, If You Want To precisely store floating point values, you should use DECIMAL, such as the amount.

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 and NUMBERIC store exact values to ensure accuracy. For example, they are used to store amount data. In MySQL, NUMBERIC and DECIMAL are implemented in the same type.

Double on other platforms

The double type is not only inaccurate in MySQL, but also in Oracle, Java and other platforms, as follows:

Simple 0.2 + 0.4, but the returned result is not 0.6.

Back to problem start

If you change to the DECIMAL type, the result of round is correct, as shown below:

Yujx> drop table dd;
Yujx> create table dd (a double, B decimal (30,10 ));
Yujx> insert into dd

Values (956.745, 956.745), (231.34243252, 231.34243252), (321.43534, 321.43534 ),

(5464.446, 5464.446 );
Yujx> select a, round (a, 2) from dd;

To sum up

Only one problem is described. If you want to store decimals (such as the amount) accurately, we recommend that you use the DECIMAL type instead of the DOUBLE or float type.

This article permanently updates the link address: