In MySQL, the solution to the problems related to floating point comparison is noted that the following sections are mainly related to the DOUBLE and FLOAT columns, because the inaccuracy of floating point numbers is essential. 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:
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.