Three types of float, double, and decimal in MySQL, and the cause of the error in numerical values

Single-precision floating-point number with 4 bytes (32bit) for floating-point numbers, using the IEEE754 standard computer floating-point number, in the interior is a binary representation, such as: 7.22 with 32-bit binary is not the case, so it leads to inaccuracy, access will be error.

Summary of issues with the float data type in MySQL:

For single-precision floating-point float: When the data range is within ±131072 (65536x2), the float data accuracy is correct, but data beyond this range is unstable.

Recommendation: To change float to double or decimal, the difference is double is dual precision floating point calculation, decimal is fixed-point calculation, will get more accurate data.

The default length of the float column type does not find the result, and precision must be specified.

The length of the inserted data is not sufficient to automatically complement the decimal place, and the fractional value is approximate.

For example, num (9,3) is a total length of 9 bits, containing 3 decimal places, if the inserted data is 12345678.9, the length is 9 bits, plus the padded two decimal place becomes 12345678.897, beyond the field range, cannot be inserted.

If the insertion is 123456.9, it will be filled with decimals, the approximate value of 123456.797, just can be inserted, but then taken out when it is not the one you inserted, it becomes 123456.797 (approximate).

The number of decimal digits is super, and the approximate value is automatically taken.

For example: Insert 2.888888, insert the result into the database is 2.889

First, the concept of floating point number and error problems

A floating-point number is a method used to represent real numbers, which represent real numbers in terms of the E (exponent) of M (mantissa) * B (Radix), which, in relation to fixed-point numbers, has a characteristic that indicates a large range of data in the case of a certain length of time. But at the same time there are error problems, this is the famous floating point precision problem!    Floating point number has a variety of implementation methods, the computer's implementation of floating-point numbers are mostly in accordance with the IEEE754 standard, IEEE754 the single-precision floating-point number and double-precision floating-point number of two specifications, single-precision floating point number with 4 bytes (32bit) for floating-point number, the format is: 1 bit sign bit 8 bits represents the exponent A double-precision floating-point number of 8 bytes (64bit) represents a real number, in the form of a 1-bit sign bit 11 bit indicating that the exponent 52 bits represents the mantissa at the same time, the IEEE754 standard also regulates the format of the mantissa: d.dddddd ..., only 1 bits to the left of the decimal point and cannot be zero, the internal computer is binary The left part of the mantissa is always 1. Obviously, this 1 can be omitted to improve the accuracy of the mantissa. From the above, the mantissa of a single-precision floating-point number is represented by 24bit, and the mantissa of a double-precision floating-point number is expressed in 53bit and converted to decimal:
2^24-1 = 16777215; 2^53-1 = 9007199254740991
From the above visible, the IEEE754 single-precision floating-point number of valid digits binary is 24 bits, in decimal terms, is 8 bits, double-precision floating-point number of valid binary is 53 bits, in decimal terms, is 16 bits. Obviously, if a real number has more than 8 digits, it will produce an error if it is represented by a single-precision floating point. Similarly, if a valid number of real numbers exceeds 16 bits, a double-precision floating-point is used to represent the error! For 1310720000000000000000.66 This number, the valid number is 24 bits, the single-precision or double-precision floating-point numbers will produce error, but the degree is different:
Single-precision floating-point number: 1310720040000000000000.00; double-precision floating-point number: 1310720000000000000000.00
Visible, double accuracy is 0.66, the single precision is nearly 4 trillion!
The above shows the error caused by the length limit, but this is not all! Computer floating-point numbers using the IEEE754 standard are internally represented by binary, but when a decimal number is converted to a binary floating point, the error is also caused because not all the numbers can be converted to a finite-length binary number. For 131072.32 of this number, the effective number is 8 bits, should be able to use a single-precision floating point to accurately express, why will there be deviation?     Take a look at this data the binary mantissa is clear 10000000000000000001010001 ... Obviously, its mantissa exceeds 24bit, according to the rounding rule, the tail number only takes 100000000000000000010100, the result causes the "strange" phenomenon which encounters in the test! 131072.68 a single-precision floating-point number is represented as 131072.69, as is the reason for this. In fact, the number of valid numbers is less than 8 digits, the floating-point number is not necessarily accurate representation, 7.22 of the mantissa can not be used in 24bit binary representation, of course, testing in the database will not be a problem (rounding or 7.22), but if you participate in some calculations, the error accumulation, can produce a large deviation.

Ii. numeric types in MySQL and Oracle

The question is, does MySQL exist only? Obviously not, as long as the implementation of floating-point numbers conforming to the IEEE754 standard, there are the same problems.
Type of numeric value in MySQL (not including integral type):
IEEE754 floating point number: float (single), double or real (double)
Fixed-point number: decimal or numeric
numeric Types in Oracle:
Oracle Floating-point number: number (note that precision is not specified)
IEEE754 floating point: binary_float (single precision), binary_double (double precision) float,float (n) (ANSI required data type)
Fixed points: number (p,s)
If you are testing in Oracle with Binary_float and so on, the result is the same. Therefore, in the database, for data involving currency or other precision sensitive, should use fixed-point number to store, for MySQL is a decimal, for Oracle is number (P,S). Double-precision floating-point numbers are also problematic for larger data!

Third, there are floating point problem in programming

Not only the database has floating point number problem, programming also exist, even more worthy of attention!
Through the above introduction, floating-point number error should be more clear. If you do complex floating-point arithmetic in your program, the error will be amplified further. Therefore, in the program design, if the use of floating-point numbers, you must be aware of the possible error problems. Not only that, floating-point numbers, if handled poorly, can also cause the program to bug! Look at the following statement: if (x! = y) {z = 1/(x-y);} This statement looks fine, but if it is a floating-point number, there may be a problem! Look at the following statement to output what result: public class Test {public static void main (String[]args) throws Exception {System.out.print ("7.22-7.0=" + (7.22f-7.0f)); We might take it for granted that the output should be 0.22, and the actual result would be 0.21999979!
Therefore, in programming should try to avoid the comparison of floating-point numbers, otherwise it may lead to some potential problems! In addition to these, you should also pay attention to some of the floating point number of special values, such as NaN, +0,-0, + infinity,-infinity, etc., although the IEEE754 made some agreement, but the specific implementation, different hardware structure, there will be some differences, if not pay attention will also cause errors!

Iv. Summary:

From the above analysis, we can draw the following conclusions:

1, floating point number has the error problem;
2, the currency and other precision-sensitive data, should be expressed or stored in fixed-point numbers;
3, programming, if the use of floating-point numbers, to pay special attention to the error problem, and try to avoid floating-point comparison;
4. Pay attention to the handling of some special values in floating-point numbers

Precautions

In MYSQL 5.022,
If a field F is a float type, then at query time, the SQL statement is:
SELECT * from T where f = 2.2;
Then even 2.2 of the data in the table cannot be queried.

There are 2 ways to solve this problem:
1. Change float to a double type without this problem. However, this method is not appropriate if the amount of data in the database is large, or if the amount of modification is too large. This method is only suitable for the initial stage of designing a database.
2. Set the precision of float and then query it.
If you want to be accurate to 3-bit, then: SELECT * from T where format (f,3) = Format (2.2,3);

However, the accuracy cannot exceed 6. Otherwise, an error occurs. Because the float type allows for a maximum of 6 digits after the decimal point.

Source:

Http://www.jb51.net/article/31723.htm

Http://www.linuxidc.com/Linux/2013-07/88032.htm

Three types of float, double, and decimal in MySQL, and the cause of the error in numerical values