Quickly solve the problem related to floating point comparison in MySQL

Note that the following sections are mainly related to DOUBLE and FLOAT columns because of the inaccuracy of floating point numbers. MySQL uses a 64-bit DECIMAL value to perform the DECIMAL operation. when processing a DECIMAL column, it should be able to solve most common inaccuracy problems.

Floating-point numbers sometimes cause confusion because they cannot be saved in the computer architecture with accurate values. The value you see on the screen is generally not the exact value of the value. This is the case for FLOAT and DOUBLE column types. DECIMAL columns can store accurate values because they are represented by strings.

The following example describes the problem of using DOUBLE:

Reference content is as follows: Mysql> create table t1 (I INT, d1 DOUBLE, d2 DOUBLE ); Mysql> insert into t1 VALUES (1,101.40, 21.40), (1,-80.00, 0.00 ), -> (2, 0.00, 0.00), (2,-13.20, 0.00), (2, 59.60, 46.40 ), -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3,-29.60, 0.00 ), -> (4, 60.00, 15.40), (4,-10.60, 0.00), (4,-34.00, 0.00 ), -> (5, 33.00, 0.00), (5,-25.80, 0.00), (5, 0.00, 7.20 ), -> (6, 0.00, 0.00), (6,-51.40, 0.00 );   Mysql> SELECT I, SUM (d1) AS a, SUM (d2) AS B -> FROM t1 group by I HAVING a <> B;   + ------ + ------- + ------ + | I | a | B | + ------ + ------- + ------ + | 1 | 21.4 | 21.4 | | 2 | 76.8 | 76.8 | | 3 | 7.4 | 7.4 | | 4 | 15.4 | 15.4 | | 5 | 7.2 | 7.2 | | 5 |-51.4 | 0 | + ------ + ------- + ------ +

The result is correct. Although the first five records do not seem to be able to be compared (the values of a and B do not seem to be different), they can be compared, this is because the differences between the displayed values are very different, depending on the computer architecture.

If columns d1 and d2 are defined as DECIMAL rather than DOUBLE, the SELECT query result contains only one row, that is, the last row shown above.