Usage error of float type in mysql _ MySQL

Usage of float type in mysql bitsCN.com

A single-precision floating point uses 4-byte (32bit) to represent a floating point.
A computer floating point number that adopts the IEEE754 standard is represented in binary format internally.
For example, 7.22 cannot be expressed in 32-bit binary.
So it is not accurate.

Summary of float data types in mysql

Float: when the data range is less than ± 131072 (65536 × 2), float data accuracy is correct, but data beyond this range is unstable, no related parameter settings are found. Suggestion: change float to double or decimal. The difference between the two is that double is a floating point computation, and decimal is a fixed point computation to obtain more accurate data.

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

Num float (0.12), insert into table (num) values (0.12); select * from table where num = 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: The field is out of the range and cannot be inserted.

The code is as follows:
Mysql> insert into tt (num) values (123456.8 );
Query OK, 1 row affected (0.00 sec)

Mysql> select * from tt;
+ ------------ +
| Num |
+ ------------ +
| 1, 123456.797 |
+ ------------ +
1 row in set (0.00 sec)

Note: If the number of decimal places is not enough, it is automatically filled. However, there is a problem that the approximate values above exist.

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

Mysql> select * from tt;
+ ------------ +
| Num |
+ ------------ +
| 1, 123456.797 |
| 1, 123456.797 |
| 1, 123456.867 |
+ ------------ +
3 rows in set (0.00 sec)

Mysql & gt; select * from tt where num = 123456.867;
+ ------------ +
| Num |
+ ------------ +
| 1, 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 |
+ ------------ +
| 1, 123456.797 |
| 1, 123456.797 |
| 1, 123456.867 |
| 1, 2.800 |
+ ------------ +
4 rows in set (0.00 sec)

Mysql & gt; select * from tt where num = 2.8;
+ ------- +
| Num |
+ ------- +
| 1, 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 |
+ ------------ +
| 1, 123456.797 |
| 1, 123456.797 |
| 1, 123456.867 |
| 1, 2.800 |
| 1, 2.889 |
+ ------------ +
5 rows in set (0.00 sec)

Note: If the number of decimal places exceeds the upper limit, the approximate value is automatically obtained.

I. concepts and errors of floating point numbers

A floating point number is a method used to represent a real number. it uses the E (exponential) power of M (tail number) * B (base number) to represent a real number, when the length is certain, it indicates that the data range is large. But there are also errors, which is a famous floating point number accuracy problem! Floating point numbers are implemented in multiple ways. most of the implementations of floating point numbers in computers comply with the IEEE754 standard. IEEE754 sets two types of single-precision floating point numbers and double-precision floating point numbers. single-precision floating point numbers use 4-byte (32bit) to represent floating point numbers, the format is: 1-bit symbol bit 8-bit indicates the index 23-bit indicates the tail number double-precision floating point number 8-byte (64bit) indicates the real number, the format is: 1-bit symbol bit 11-bit indicates the index 52-bit indicates the tail number at the same time, the IEEE754 standard also regulates the ending number format: d. dddddd ..., there is only one decimal point on the left and cannot be zero. the computer is binary. Therefore, the decimal point on the left is always 1. Obviously, this 1 can be omitted to improve the accuracy of the ending number. It can be seen from the above that the ending number of a single-precision floating point number is expressed in 24bit, and the Ending number of a double-precision floating point number is expressed in 53bit, which is converted to decimal:
2 ^ 24-1 = 16777215; 2 ^ 53-1 = 9007199254740991
As can be seen from the above, IEEE754 single-precision floating point's valid binary number is 24 bits, in decimal format, it is 8 bits; double-precision floating point's valid binary number is 53 bits, in decimal format, it is 16 bits. Obviously, if a real number has more than eight valid digits and is represented by a single-precision floating point number, it will produce an error! Similarly, if the valid number of a real number exceeds 16 bits, the double-precision floating point number will also produce an error! For the number 1310720000000000000000.66, the valid number is 24 bits, which are expressed by single-precision or double-precision floating-point numbers, but with different degrees:
Single-precision floating point number: 1310720040000000000000.00; double-precision floating point number: 1310720000000000000000.00
Obviously, the double precision is less than 0.66, and the single precision is less than 4 trillion!
The above shows the error caused by the length limit, but this is not all! A computer floating point number that adopts the IEEE754 standard is represented in binary internally. However, an error occurs when a decimal number is converted to a binary floating point number, the reason is that all numbers can be converted to binary numbers with a limited length. For the number 131072.32, the valid number is 8 bits. it can be accurately expressed with a single-precision floating point number. why is there a deviation? Let's take a look at the binary Ending number of this data. we can see that the number is 10000000000000000001010001 ...... apparently, the number of tails exceeds 24 bits. according to the rounding rule, the number of tails is only 100000000000000000010100, resulting in a "strange" phenomenon in the test! 131072.68 is represented as 131072.69 with a single-precision floating point number, for a similar reason. In fact, the number of valid digits is smaller than 8 digits, and the floating point number cannot be accurately expressed. the tail number of the number 7.22 cannot be expressed in the 24bit binary format, of course, there will be no problem in testing in the database (or 7.22 after rounding), but if you participate in some calculations, after the error accumulation, there may be a large deviation.

II. numeric types in mysql and oracle

Is it true that only mysql exists? Obviously not. as long as the implementation of floating point numbers conforms to the IEEE754 standard, there are the same problems.
Mysql numeric type (excluding integer type ):
IEEE754 floating point: float (single precision), double or real (double precision)
Number of points: decimal or numeric
Data types in oracle:
Oracle floating point number: number (do not specify the precision)
IEEE754 floating point: BINARY_FLOAT (single precision), BINARY_DOUBLE (double precision) FLOAT, FLOAT (n) (ansi-required data type)
Number of points: number (p, s)
If you use BINARY_FLOAT in oracle for testing, the results are the same. Therefore, for data that involves currency or other precision-sensitive data, you should use a specific number of points for storage. for mysql, it is decimal, and for oracle, it is number (p, s ). Double-precision floating point number, which is also a problem for relatively large data!

III. Floating point problems also exist in programming

Not only does the database have floating point numbers, but programming also exists, and even deserves more attention!
Through the above introduction, the error of floating point numbers should be clear. If complicated floating point calculation is performed in the program, the error will be further amplified. Therefore, if floating point numbers are used in programming, you must be aware of possible errors. Not only that, if the floating point is not properly processed, it will also lead to a program BUG! See the following statement: if (x! = Y) {z = 1/(x-y);} this statement seems no problem, but if it is a floating point number, it may be a problem! Then, let's see what the following statement will output: public class Test {public static void main (String [] args) throws Exception {System. out. print ("7.22-7.0 =" + (7.22f-7.0f);} we may assume that the output result is 0.22, but the actual result is 0.21999979!
Therefore, do not compare floating point numbers as much as possible in programming. otherwise, some potential problems may occur! In addition, pay attention to some special floating point values, such as NaN, + 0,-0, + infinity, and-infinity. although IEEE754 has made some conventions, however, specific implementations and different hardware structures may also be different. if you do not pay attention to them, errors may occur!

IV. Summary:

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

1. floating point numbers have errors;
2. precision-sensitive data such as currency should be expressed or stored in a specific number of points;
3. if floating point numbers are used in programming, pay special attention to the error and try to avoid floating point number comparison;
4. handle some special floating point values.

Notes

MYSQL 5.022,
If a field f is of the float type, the SQL statement is:
Select X from T where f = 2.2;
Even if the table contains 2.2 of the data, it cannot be queried.

There are two solutions:
1. changing float to the double type will not cause this problem. However, this method is not suitable if the data volume in the database is large or the modification volume is too large. this method is only suitable for the initial stage of database design.
2. set the float precision and perform the query.
To be accurate to three digits, select * from T where format (f, 3) = format (2.2, 3 );

However, the precision cannot exceed 6. Otherwise, an error occurs because the float type can be precise to up to 6 digits after the decimal point.

BitsCN.com