Shulou(Shulou.com)06/01 Report--

This paper gives an example of the trap of rounding the ROUND function in MySQL. Share with you for your reference, the details are as follows:

In MySQL, the ROUND function is used to round query results, but recently I found that rounding using the ROUND function was not what I expected. This article documents this problem so that you don't make the same mistake as I did.

Problem description

If we have the following data table test, the table statement is as follows

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

We created a table called test, which contains multiple fields in addition to the id field, with these different data types. We insert a piece of data into this table

INSERT INTO test (field1, field2, field3, field4, field5, field6) VALUE (100,100,100,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 | 1.0050 | 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, I always thought that the result must be 101. because the above six values are all rounded to 1.005, and the result must be 101. the latter two must be 4 and 3, but the final result is quite different from what was supposed.

* * 1. Row * * round (field1 * field4): 100round (field2 * field4): 100round (field3 * field4): 100round (field1 * 1.005): 101round (field2 * 1.005): 101round (field3 * 1.005): 101round (field5): 4 round (field6 ): 21 rowin set (0.00 sec) Why is this happening?

Why is the result obtained by multiplying fields in the database different from that obtained by multiplying direct fields and decimals?

I am puzzled by this problem, and all kinds of Baidu Google are fruitless. I can't help it. I have to rely on myself. The most useful thing at this time is the official website document, so I inquired about the ROUND function in the official mysql document, which contains the following two rules.

For exact-value numbers, ROUND () uses the "round half up" rule (for exact values, the ROUND function is rounded) 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 rounded to the nearest even integer. (for approximate values, it depends on the underlying C function library, and in many systems the ROUND function uses the rule of "take the nearest even number")

Through these two rules, we can see that because when we multiply the two fields, the final result is processed according to the float type, while the float type in the computer is not an exact number, so the processing result will follow the second rule, and the result of decimal operation such as direct integer field and 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 the ROUND function executed by field5 and field6, it is clear that it is indeed converted to the nearest even number.

Summary

As you can see from this example, it is important to pay attention to the use of ROUND in MySQL, especially when the field involved in the calculation contains floating-point numbers, when the result is inaccurate.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.