Mysql floating point calculation error cause analysis and solution

In mysql, if we use the floating point type for result calculation, we will find that the calculation result is different from the actual result. why? This article introduces the cause analysis and solution of mysql floating point calculation error. For more information about the coders, see. When performing financial and other numeric operations, use the decimal type instead of the float and double types! Because decimal has the highest precision

The following C # code:

float a = 0.65f;float b = 0.6f;float c = a - b;

What is c at this time?

0.05? Error!

At this time, c is 0.0499999523!

Why?

The root cause is that the binary 01 code used by the computer cannot accurately represent certain decimal digits.

The following is an analysis:
We know that to convert a decimal value to a binary value, we need to use the following calculation method:
1. integer part: divide the integer by 2 consecutively, take the remainder, and divide the quotient by 2 until the quotient is equal to 0. Then, sort the remainder in the reverse order. Abbreviation: "Division 2 remainder method ".
2. decimal part: Convert decimal points to binary decimal places, and use the "take an integer of 2 and arrange them in sequence" method. Use 2 to multiply the decimal number, and then use 2 to multiply the remaining decimal number, and then extract the integer part of the product, until the fractional part of the product is 0 or the expected precision is reached. Then, sort the retrieved integer parts in order, that is, the first integer part is used as the high position of the binary decimal, and the last integer part is used as the low valid bit. For short, "Take the integer method of multiplication 2 ".
3. convert the decimal number containing decimals to binary. convert the integer and decimal part, and then add them together.

For example, to convert a decimal value 25.75 to a binary value, perform the following steps:

25 (integer)
25/2 = 12 ...... 1
12/2 = 6 ...... 0
6/2 = 3... 0
3/2 = 1... 1
1/2 = 0... 1
(25) 10 = (11001) 2

0.75 (decimal part)
0.75*2 = 1. 5... 1
0.5*2 = 1... 1
(0.75) 10 = (0.11) 2
(25.75) 10 = (11001) 2 + (0.11) 2 = (11001.11) 2

According to the above method, we convert 0.65 and 0.6 to binary code:
(0.65) 10 = (0. 101001100110011001100110011001100110011 ......) 2
(0.6) 10 = (0. 10011001100110011001100110011001100110011 ......) 2

The ellipsis below indicates that the calculation is not complete, and the binary value 0011 is infinitely repeated.
At the beginning of this article, we use the float type. let's see if the float type can store the converted binary code above.
Currently, floating point values stored on computers are stored according to the IEEE (Institute of Electrical and Electronics Engineers) 754 floating point storage format standard.
The IEEE single-precision floating point format consists of 32 digits, including three fields: 23 decimal places f, 8-bit offset index e, and 1-bit symbol s. Store these fields in a 32-bit character consecutively and encode them. The decimal point contains the 23 decimal places f; the decimal point contains the 8-digit index e; and the 31st-digit contains the symbol s. As shown in:

That is to say, we can only store 23 bits for the binary code converted from 0.65 and 0.5, even if the data type is double, we can only store 52 bits, so that you can see the cause of the problem.
The truncated binary code cannot correctly represent 0.65 and 0.5. According to this binary code, the result 0.05 cannot be obtained correctly.

How can this problem be solved? After knowing the root cause, we know that this problem cannot be fundamentally solved, but we can have some methods to save the country by the curve. below are a few examples:
1. because a binary value can accurately represent an integer (which can be converted to a binary value), you can multiply the decimal number by 10 or 100 to an integer, and then perform an integer operation, finally, the results are obtained by dividing by 10 or 100;
2. extract the valid decimal places of the result to obtain the best approximate result, and then process it.
3. for decimal values that can be expressed with a finite length binary value, you can use data types that store digits greater than their length.

The solution is being supplemented ......, If you have any good methods, you can also propose them!
The above solution depends on the actual situation.