MySQL under Float type use some error explanations

Think a lot of friends are not how to use the float type in MySQL, especially when it comes to money we may use double precision, we know that the type of float of MySQL is single precision floating point type is careless will cause data error.

Single-precision floating-point numbers represent floating-point numbers with 4 bytes (32bit)
Computer floating-point numbers using the IEEE754 standard are internally represented by binary
such as: 7.22 with 32-bit binary is not to be represented.
So it's not accurate.

Summary of problems with float data types in MySQL

For single-precision floating-point float: When the data range is within ±131072 (65536x2), the float data accuracy is correct, but the data beyond this range is not stable, there is no relevant parameter setting recommendations: Change float to double or decimal , the difference between the two is that double is a floating-point calculation, and decimal is a fixed-point calculation that gets more accurate data.

1.float type
Float column Type default length cannot find results, you must specify precision,
For example, Num float, insert into table (num) values (0.12); SELECT * FROM table where num=0.12, empty set.

float (9,7),  insert INTO  table (num) VALUES (0.12), and select  * from table where num=0.  This record will be found in 12.  mysql> CREATE TABLE TT     --       -num  float(9,3  )     ; Query OK, 0 rows affected (0.03 sec)mysql> INSERT into TT (NUM) VALUES (1234567.8   Range for column ' num ' at row 1

Note: Out of field range, unable to insert

Code to copy code as follows
Mysql> INSERT INTO TT (NUM) values (123456.8);
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from TT;
+------------+
| num |
+------------+
| 123456.797 |
+------------+
1 row in Set (0.00 sec)

Note: The number of decimal digits is not enough, auto-fill, but there is a problem is the approximate value above.

MySQL> INSERT into TT (NUM) VALUES (123456.867); Query OK, 1 row affected (0.04sec)MySQL> select *From TT;+------------+  | Num |+------------+| 123456.797 | | 123456.797 | | 123456.867 |+------------+3 rows in Set (0.00sec)MySQL> select * from TT where num=123456.867;+------------+| Num |+------------+| 123456.867 |+------------+1 row in Set (0.00sec)MySQL> INSERT into TT (NUM) VALUES (2.8); Query OK, 1 row affected (0.04sec)MySQL> select *From TT;+------------+| Num |+------------+| 123456.797 | | 123456.797 | |      123456.867 | | 2.800 |+------------+4 rows in Set (0.00sec)MySQL> select * from TT where num=2.8;+-------+  | Num |+-------+| 2.800 |+-------+1 row in Set (0.00sec)MySQL> INSERT into TT (NUM) VALUES (2.888888); Query OK, 1 row affected (0.00sec)MySQL> select *From TT;+------------+| Num |+------------+| 123456.797 | | 123456.797 | |      123456.867 | |      2.800 | | 2.889 |+------------+5 rows in Set (0.00 sec)

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

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.

The above content is reproduced part. But I did the test myself.

MySQL float (10,2), C2 decimal (10,2)); Query OK, 0 rows affected (0.29 sec)mysql> insert into Test values (131072.32,131072.32); Query OK, 1 row affected (0.07 sec)mysql> select * from test; +-----------+-----------+| d1[ c2|+-----------+-----------+| 131072.31 | 131072.32 |+-----------+-----------+1 row in Set (0.00 sec)

From the above example, we see that the value of the C1 column is changed from 131072.32 to 131072.31, which is caused by the inaccuracy of the floating-point number.
In MySQL, float, double (or real) are floating-point numbers, and decimal (or numberic) is the fixed-point number.

In addition, a C1 float (5,2) is set to have an approximate number when it exceeds the range. Such as

Insert 1234.22, the database will produce a warning, but it will also be inserted, insert the result is 999.99 the majority of people need to be careful when developing.

MySQL under Float type use some error explanations