Problems and solutions of MySQL floating point computation

If you have any questions, please contact: onesoft007

In computers, floating-point numbers are often difficult to represent accurately, and the results of floating-point arithmetic are often difficult to represent accurately. MySQL also has this problem and is shown in the following areas.

Problem

1, the same input, may cause a different output (affected by CPU, compiler, etc.)

A) The following is an example from the official MySQL website

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 | | 6 |    -51.4 | 0 |+------+-------+------+

When the I=1, a=21.4, b=21.4, this does not meet a<>b this condition, but MySQL still decided that A and B are not equal.

b) When testing on local virtual machines (Centos 6.4 x86_64)

 +------+--------------------+------+  
  | I | A | B |  
 +------+--------------------+------+  
     | 1 | 21.400000000000006 | 21.4 |     
 |  2 | 76.80000000000001 | 76.8 |     
 |  3 |  7.399999999999999 | 7.4 |     
 | 4 | 15.399999999999999 | 15.4 |     
 |  5 |  7.199999999999999 | 7.2 |     
 |              6 |    -51.4 | 0 |  
 +------+--------------------+------+ 
 
 2 , the result is affected by the precision of the floating-point number itself 
 change the double to float, the result is as follows 
   

+------+---------------------+--------------------+
| I |    a                   | b                  |
+------+---------------------+--------------------+
|    1 |  21.400001525878906 | 21.399999618530273 |
|    2 |   76.79999828338623 |  76.80000114440918 |
|    3 |   7.399999618530273 |  7.400000095367432 |
|    5 |   7.200000762939453 |  7.199999809265137 |
|    6 | -51.400001525878906 |                  0 |
+------+---------------------+--------------------+

Solution Solutions
1. Decimal
In MySQL, precise operations with decimals can use the decimal type
CREATE TABLE T3 (i INT, D1 decimal (10,3), D2 Decimal (10,3));

+------+---------+-------+
| I |    a       | b     |
+------+---------+-------+
|    6 | -51.400 | 0.000 |
+------+---------+-------+

Limit
The exact representation of a decimal is decimal (m,d), where the maximum of M is 65,d maximum is 30. So the scope of its representation is far less than the range that a double can represent

2. Application-based solutions
For example, commodity prices related to decimals, with 20.95 yuan, then the price can be divided into units, that becomes 2095.

Conclusion
　　Do not use float, double, and its equivalent type for precise calculations. If you want to do a precise calculation in MySQL, it is recommended to use decimal or give the relevant computing task to the application.

If you have any questions, please contact: onesoft007

Problems and solutions of MySQL floating point computation