Analysis of the Trap of Rounding Operation by ROUND Function in MySQL

In this paper, an example is given to describe the trap of ROUND function in MySQL. Share it for your reference, as follows:

In MySQL, ROUND Function is used to round up the query results by 4, but recently used ROUND The unexpected discovery of function 4 instead of 5 is not as expected. This paper records this 1 problem in order to avoid everyone making the same mistake as me.

Problem description

If we have the following 1 data table test, the table building 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 in addition to the id field, with different data types. We insert 1 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);

The data in the table after insertion is like this

mysql> select * from test;
+----+--------+--------+--------+--------+--------+--------+
| id | field1 | field2 | field3 | field4 | field5 | field6 |
+----+--------+--------+--------+--------+--------+--------+
| 1 |  100 |  100 |  100 | 1.0050 | 3.5000 | 2.5000 |
+----+--------+--------+--------+--------+--------+--------+
1 rowin 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, 1 thought that all the results must be 101, because the above six values are all about 100 * 1.005, and the results must be 101, while the latter two must be 4 and 3, but the final result is quite different from the assumption

*************************** 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 rowin set (0.00 sec)

Why is this happening?

Also 100*1. 005, why is the result of multiplying fields in the database not the same as that of multiplying direct fields with decimals?

I can't understand this problem, and all kinds of Baidu and Google have failed. . . No way, still have to rely on their own, this time the most useful is the official website document, so the query mysql official document on the ROUND function part, which contains the following two rules

For exact-value numbers, ROUND () uses the "round half up" rule (For exact values, the ROUND function uses 4 rounded 5 inputs) For approximate-value numbers, the result depends the C library. On many systems, this that that ROUND () uses the "round to with with any any any any C C C C C EN function library, in many systems ROUND Function uses the "take the nearest even number" rule)

Through these two rules, We can see that, Because when we multiply two fields, the final result is processed according to float type, and float type is not an exact number in the computer, the processing result will be processed according to Article 2, while the result of direct integer field and decimal operation such as 1.005 is calculated according to Rule 1 because both values involved in the operation are exact numbers. Executed from field5 and field6 ROUND The result of the function can be clearly seen to be converted to the nearest even number.

Summarize

As you can see from this example, in MySQL, the ROUND It is important to note, especially when the fields involved in the calculation contain floating-point numbers, and the calculation results are inaccurate at this time.

More readers interested in MySQL can see the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.