mysql and float types use some error resolution

Single-precision floating-point Numbers are represented in 4 bytes (32bit)
It is a floating point computer number based on IEEE754 standard, which is expressed in base 2 internally
7.22 cannot be expressed in 32-bit base 2.
So it's not exact.

Problem summary of float data types in mysql

For single precision floating point Numbers Float: when the data range within 131072 (65536 x 2) plus or minus, float data accuracy is correct, but beyond the scope of data is not stable, did not find relevant parameter Settings suggestion: change float to double or decimal, the difference is double is floating point calculations, decimal is fixed-point calculation, will give a more accurate data.

1. float type
The default length of the float column type cannot be found, the precision must be specified,
For example, num float, insert into table (num) values (0.12); select * from table where num=0.12, empty set.

 
num float(9,7),  insert into  table (num) values (0.12); select  * from table where num=0.12 You will find this record. 

mysql> create table tt
    -> (   
    -> num  float(9,3)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tt(num)values(1234567.8);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

Note: cannot insert beyond field scope

The code is copied 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 places is not enough, but there is a problem with the above approximation.

mysql> insert into tt(num)values(123456.867);
Query OK, 1 row affected (0.04 sec)

mysql> select * from   tt;
+------------+  
| num        |
+------------+
| 123456.797 |
| 123456.797 |
| 123456.867 |
+------------+
3 rows in set (0.00 sec)

mysql> select  * from tt where  num=123456.867;
+------------+
| num        |
+------------+
| 123456.867 |
+------------+
1 row in set (0.00 sec)

mysql> insert into tt(num)values(2.8);
Query OK, 1 row affected (0.04 sec)

mysql> select * from   tt;
+------------+
| num        |
+------------+
| 123456.797 |
| 123456.797 |
| 123456.867 |
|      2.800 |
+------------+
4 rows in set (0.00 sec)

mysql> select  * from tt where  num=2.8;
+-------+  
| num   |
+-------+
| 2.800 |
+-------+
1 row in set (0.00 sec)

mysql> insert into tt(num)values(2.888888);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  tt;
+------------+
| num        |
+------------+
| 123456.797 |
| 123456.797 |
| 123456.867 |
|      2.800 |
|      2.889 |
+------------+
5 rows in set (0.00 sec)

Note: if there are too many decimal places, the approximate value will be automatically taken.

1. Concept and error of floating point number

Floating point number is used to represent the real number 1 method, it USES M(tail number) * B(base number) E(exponent) power to represent the real number, compared to the fixed point number, in the case of length 1 fixed point, has the characteristics of representing a large range of data. But there is also the error problem, which is known as the floating point precision problem! Floating point Numbers have many implementation method, a computer with the implementation of floating point Numbers are mostly IEEE754 standard, IEEE754 specifies single-precision floating-point and double-precision floating-point number two specifications, the single precision floating point number 4 bytes (32 bit) floating point number, format is: 1 the sign bit 8-bit index 23 mantissa double-precision floating-point number 8 bytes (64 bit) said the real number, format is: At the same time, IEEE754 standard has also standardized the format of mantailings: d.dddddd... , the decimal point to the left of only 1 and can not be zero, the computer is internal 2, therefore, the decimal point to the left of the part is always 1. Obviously, this 1 can be left out to improve the precision of the mantissa. As can be seen from the above, the mantisses of single-precision floating point Numbers are represented by 24bit, and the mantisses of double-precision floating point Numbers are represented by 53bit, which is converted into decimal:
2 to the 24th minus 1 is 16777215; 2 to the 53rd minus 1 is 9007199254740991
As can be seen above, the significant digits of IEEE754 floating point Numbers are 24 bits in base 2 and 8 bits in base 10. The significant digits of a double-precision floating point number are 53 bits in base 2 and 16 bits in base 10. Obviously, if a real number has more than 8 significant digits, which are represented as single-precision floating point Numbers, there will be an error! Similarly, if a real number has more than 16 significant digits, it will be represented as a double-precision floating point number. For 1310720000000000000000.66 this number, valid number is 24, with a single precision or double-precision floating-point representation will produce error, just different levels:
Single precision floating point Numbers: 1310720040000000000000.00; Double-precision floating-point number: 1310720000000000000000.00
It can be seen that the accuracy of double is off by 0.66, and the accuracy of single is off by nearly 4 trillion!
The above shows the errors due to length restrictions, but that's not all! The floating point number of computer based on IEEE754 standard is expressed in base 2 internally. However, when converting a decimal number into a base 2 floating point number, the error will be caused. The reason is that not all Numbers can be converted into a finite length base 2 number. For 131072.32, the significant digit is 8 bits, which should be accurately represented as a single-precision floating point number. Why the deviation? Think 1 2 under the data into the system terminal will understand the 10000000000000000001010001... Obviously, its mantailings exceed 24bit, and according to the rounding rule, the mantailings only take 100000000000000000010100, resulting in the "strange" phenomenon encountered in the test! 131072.68 becomes 131072.69 as a single-precision floating point number for a similar reason. In fact, the significant number is less than an 8-bit number, and the floating point number cannot be accurately represented. The manus of 7.22 cannot be represented in 24bit2. Of course, there is no problem in testing in the database (it is still 7.22 after rounding).

2. Numeric types in mysql and oracle

Is mysql the only problem? Obviously not, as long as it is a floating point number implementation that conforms to the standard of IEEE754, the same problem exists.
Numeric types in mysql (not including integer types) :
IEEE754 floating point number: float (single precision), double or real (double precision)
Number of fixed points: decimal or numeric
Value types in oracle:
oracle floating point: number (note that precision is not specified)
IEEE754 floating point number: BINARY_FLOAT (single precision), BINARY_DOUBLE (double precision) FLOAT, FLOAT(n) (data type required by ansi)
Number of fixed points: number(p,s)
If you test oracle with BINARY_FLOAT and so on, the result will be like 1. Therefore, in a database, data involving currency or other precision sensitive data should be stored using fixed-point Numbers, decimal for mysql and number for oracle (p,s). Double precision floating point number, for the larger data is also a problem!

3. Floating point problems also exist in programming

Floating point problems exist not only in databases, but also in programming, which is even more noteworthy!
Through the above introduction, the floating point error problem should be more clear. If you do complex floating-point arithmetic in your program, the error will be magnified by one more step. Therefore, in programming, if floating point Numbers are used, 1 must be aware of possible errors. Not only that, but floating point Numbers, if not handled well, can result in a program called 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! public class Test {public static void main(String[]args) throws Exception {System.out.print ("7.22-7.0=" + (7.22 f-7.0 f)); We may take it for granted that the output should be 0.22, but the actual result is 0.21999979!
Therefore, you should try to avoid doing floating point comparisons in your programming, which can lead to some potential problems! Besides these, we should also pay attention to some special values of 1 in floating point Numbers, such as NaN, +0, -0, + infinity, -infinity and so on. Although IEEE754 has made some conventions on this, there will be some differences in each specific implementation and different hardware structure. If we don't pay attention to this, we will also make mistakes!

4. Conclusion:

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

1. Error problems exist in floating point Numbers;
2. Data that are sensitive to accuracy, such as currency, should be represented or stored by fixed-point Numbers;
3. In programming, if floating point Numbers are used, special attention should be paid to errors and floating point comparisons should be avoided;
4. Pay attention to the handling of special values of 1 in floating point Numbers

Matters needing attention

MYSQL 5.022,
If a field f is of type float, the sql statement is:
select * from T where f = 2.2;
Then even if there is 2.2 data in the table, it cannot be queried.

At this point, there are two solutions:
1. Changing float to double type will not cause this problem, but it is not appropriate if the amount of data in the database is too large or the amount of modification is too large.
2. Set the precision of float and then query it.
To be accurate to 3 bits, select * from T where format(f,3) = format(2.2,3);

However, the accuracy should not be more than 6. Otherwise, an error will occur, because the float type allows accuracy up to 6 decimal places.