Mysql multiplication and division precision inconsistency problem (four decimal places after division)

question

When I was writing a project function today, I had to do a statistical calculation of the amount of money, and then I needed to convert the units. So I wrote a statement similar to the following function, but the data I got was 4 decimal places, and normally we only need 2 places.

select total_fee / 100 from orders

Continue to search for data and perform precision conversion. After searching for a while, I am not satisfied with the data. Continue to test

test

When testing bugs and unknown situations, we must minimize the reproduction, streamline 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 3/4 devices, and the results are the same in different MySQL version environments.

So we know that in MySQL, multiplication and division have different precision after the decimal point.

I couldn't find suitable information in domestic forums, so I went to foreign forums to look for it, ask questions, and communicate.

Answer

First of all, I would like to thank other seniors for their answers and advice. We will also record the troubleshooting of the problem as detailed as possible.

Civilizations survive because they have memory. I hope this article can help more friends.

• The default precision of division is 4 decimal places.
• The precision of multiplication is determined by the sum of the precision of the operands. For example, in the example, the precision of 1*0.01 is 0 and 2 decimal places, so 0+2 = 2 will also use 2 digits of precision.

test

select 1.00 * 0.01;
// Result 0.0100

Consistent with the conclusion of the appeal. Thanks to the foreign seniors for their guidance.

Division uses 2 digits of precision

If we insist on using division to solve our problem, we can use a function to convert the precision.

CAST( @x / @y AS DECIMAL(m,n) )

You can look up the parameters of DECIMAL on Baidu. Basically anyone who has created a table structure can understand it.

@x and @y are the divisor and dividend.

At the same time, I also wondered whether it is possible to set the default division precision in MySQL so that we don't have to use function calculations every time in SQL.

Senior’s reply: If you don’t want unexpected situations to occur sometimes, you need to force type conversion every time.

MySQL related documentation

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 for division

From the literature cited above, we know that when two numbers are used for calculation, the precision of the result is determined by the precision of the first operand + the value of the system variable div_precision_increment. For example, the precision of 1 in our example is 0, and the system variable precision is 4 digits by default, so the result is 4 digits of precision.

set div_precision_increment = 2;
// Run select 1 / 100 again; get 0.01 which meets the desired result

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

Now we test

select 1.0 / 100;
// The result 0.010 is consistent with the conclusions above

Summarize

This is the end of this article about the inconsistent precision of MySQL multiplication and division, and four decimal places after division. For more relevant content about MySQL multiplication and division precision, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!