Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to solve the problem of inconsistent accuracy of Mysql multiplication and division. It is very detailed and has a certain reference value. Interested friends must read it!

problem

Today, when writing the project function, there is a statistical amount of money, and then need to carry out unit conversion, so write down the following function statement, but the data obtained is the accuracy of 4 decimal places, normally we only need 2 digits is enough.

Select total_fee / 100 from orders

Continue to search for data, carry out precision conversion, find a circle of data are not satisfied, continue to test

test

To test bug and unknown situations, we must minimize repetition, simplify the test, and prevent other statements from interfering with the results.

Select 1 / 100

/ / get 0.0100

Select 1 * 0.01

/ / get 0.01

And it runs on 4 devices, and this is the result of different mysql versions of the environment.

Therefore, it is preliminarily known that the accuracy of multiplication and division after the decimal point in Mysql is not the same.

No suitable information was found in the domestic forums, so I went to foreign forums to look for, ask questions and communicate.

Answer

First of all, I would like to thank other seniors for their answers and suggestions, and we will also record the troubleshooting of the problems in as much detail as possible.

Civilizations last because they have memories. I hope this article can also help more friends.

The precision of division defaults to 4 decimal places.

The accuracy of multiplication is judged by the sum of the precision of operands. For example, if the precision of 1x 0.01 in the example is 0 and 2 places after the decimal point respectively, then the result of 0 digit 2 = 2 will also use 2 places precision.

test

Select 1.00 * 0.01

/ / result 0.0100

In line with the conclusion of the appeal. Thank you for your advice from foreign seniors.

Division uses 2-digit precision

So if our problem is to insist on using division, we can use functions to convert precision.

CAST (@ x / @ y AS DECIMAL (mrecom n))

The parameters of DECIMAL can be seen by Baidu, and anyone who has created a table structure can understand it.

@ x and @ y are divisor and divisor.

At the same time, I also question whether we can set the default division precision in mysql so that we don't have to use a function every time sql.

The elder replied: if you don't want to have unexpected situations sometimes, you need to force the use of type conversion every time.

Mysql related explanatory literature

Https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html

In division performed with /, the scale of the result when using two exact-value operands is the scale of the first operand plus the value of the div_precision_increment system variable (which is 4 by default). For example, the result of the expression 5.05 / 0.014 has a scale of six decimal places (360.714286).

Precision rules of division

As can be seen from the literature quoted above: when two values are used for calculation, the accuracy of the result is determined by the accuracy of the first Operand + the value of the system variable div_precision_increment. For example, in our example, the precision of 1 is 0, and the accuracy of the system variable is 4-digit default, so the result is 4-bit accuracy.

Set div_precision_increment = 2

/ / run select 1 / 100 again; get the desired result

So we can also change the default precision of division by modifying the default variable.

At this point, we will test again.

Select 1.0 / 100

/ / result 0.010 is consistent with the above conclusion

The above is all the contents of this article entitled "how to solve the problem of inconsistent precision of Mysql multiplication and division". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.