Analysis of pitfalls in rounding operation of ROUND function in MySQL

This article uses examples to illustrate the pitfalls of rounding operations using the ROUND function in MySQL. Share with you for your reference, the details are as follows:

In MySQL, ROUND function is used to round query results. However, I recently discovered that when I used ROUND function to round the results, it did not work as expected. This article records this problem to prevent others from making the same mistake as I did.

Problem Description

Suppose we have a data table test, the table creation statement is as follows

CREATE TABLE test (
 id int(11) NOT NULL AUTO_INCREMENT,
 field1 bigint(10) DEFAULT NULL,
 field2 decimal(10,0) DEFAULT NULL,
 field3 int(10) DEFAULT NULL,
 field4 float(15,4) DEFAULT NULL,
 field5 float(15,4) DEFAULT NULL,
 field6 float(15,4) DEFAULT NULL,
 PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

We created a table named test, which contains multiple fields besides the id field, with different data types. Let's insert a piece of data into this table.

INSERT INTO test (field1, field2, field3, field4, field5, field6) VALUE (100, 100, 100, 1.005, 3.5, 2.5);

After inserting, the data in the table looks like this

mysql> select * from test;
+----+--------+--------+--------+--------+--------+--------+
| id | field1 | field2 | field3 | field4 | field5 | field6 |
+----+--------+--------+--------+--------+--------+--------+
| 1 | 100 | 100 | 100 | 1.0050 | 3.5000 | 2.5000 |
+----+--------+--------+--------+--------+--------+--------+
1 row in set (0.00 sec)

If we execute the following SQL now, what do you think the result will be?

SELECT
 round(field1 * field4),
 round(field2 * field4),
 round(field3 * field4),
 round(field1 * 1.005),
 round(field2 * 1.005),
 round(field3 * 1.005),
 round(field5),
 round(field6)
FROM test;

At first, I thought the result would be 101, because the six values ​​above are all rounded to 100 * 1.005, so the result must be 101, and the last two must be 4 and 3. However, the final result is quite different from what I expected.

*************************** 1. row ***************************
round(field1 * field4): 100
round(field2 * field4): 100
round(field3 * field4): 100
 round(field1 * 1.005): 101
 round(field2 * 1.005): 101
 round(field3 * 1.005): 101
    round(field5): 4
    round(field6): 2
1 row in set (0.00 sec)

Why is this happening?

It is the same 100*1.005, why is the result obtained by multiplying the fields in the database different from that obtained by directly multiplying the field and the decimal?

I have no idea how to solve this problem, and I have searched Baidu and Google but to no avail. . . There is no other way but to rely on myself. The most useful thing at this time is the official website documentation. So I searched the official MySQL documentation about the ROUND function, which contains the following two rules:

• For exact-value numbers, ROUND() uses the "round half up" rule .
• For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even " rule: A value with any fractional part is ROUND to the nearest even integer. (For approximate values, it depends on the underlying C function library. On many systems, the ROUND function uses the "round to nearest even number" rule)

From these two rules, we can see that when we multiply two fields, the final result is processed according to the float type, and the float type is not an exact number in the computer, so the processing result will be processed according to the second rule. The result of the direct integer field and the decimal operation such as 1.005 is because the two values ​​involved in the operation are exact numbers, so it is calculated according to the first rule. From the results of executing ROUND function on field5 and field6, it can be clearly seen that they are indeed converted to the nearest even number.

Summarize

From this example, we can see that we need to be very careful when using ROUND in MySQL, especially when the fields involved in the calculation contain floating-point numbers, the calculation results will be inaccurate.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.