MySQL under the float type some error detailed

Single-precision floating-point numbers are 4 bytes (32bit) representing floating point numbers
Uses the IEEE754 standard computer floating-point number, in the interior is uses the binary system to represent
For example: 7.22 with a 32-bit binary is not enough.
So it's not accurate.
A summary of the problem of float data type in MySQL

For single-precision floating-point number float: When the data range is within ±131072 (65536x2), float data accuracy is correct, but the data beyond this range is unstable and no relevant parameter settings are found: Change float to double or decimal , the difference is that a double is a floating-point calculation, and decimal is a fixed-point calculation, which results in more accurate data.

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

The code is as follows	Copy Code
Num Float (9,7), insert into table (num) values (0.12); This record is found in the SELECT * from table where num=0.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); ERROR 1264 (22003): Out of Range value for column ' num ' at row 1

Note: Beyond field range, cannot be inserted

The code is as follows	Copy Code
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, automatic completion, but there is a problem is such as the approximate value.

The code is as follows

Copy Code

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: The number of decimal places is super, the approximate value is automatically taken.
--------------------------------------------------------------------------------------
The concept and error of floating point number

A floating-point number is a method used to represent real numbers, which uses the E (exponent) of the M (mantissa) * B (cardinality) to represent real numbers and, in the case of fixed-point numbers, has a characteristic representation of a large range of data in a certain length of time. But also has the error problem, this is the famous floating-point precision problem!    There are several ways to implement floating-point numbers, the realization of the floating-point number in the computer mostly complies with the IEEE754 Standard, IEEE754 stipulates the single-precision floating point number and the double-precision floating-point number two kinds of specifications, the Single-precision floating-point number uses 4 byte (32bit) to represent the floating point number, the format is: 1 bit sign bit 8 bit exponent 23 digits indicates the mantissa The double-precision floating-point number 8 bytes (64bit) represents the real number, the format is: 1-bit sign bit 11-bit indicates the mantissa at the same time, the IEEE754 standard is also the format of the mantissa: d.dddddd ..., only 1 digits to the left of the decimal point and cannot be zero, the computer is binary inside, so The mantissa to the left of the decimal point 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 expressed in 24bit, and the mantissa of the double-precision floating-point number is expressed in 53bit and converted to decimal:
2^24-1 = 16777215; 2^53-1 = 9007199254740991
Visible from the above, the IEEE754 single precision floating-point number of effective digital binary is 24 bits, in decimal terms, is 8 digits; The valid digital binary for double-precision floating-point numbers is 53 digits, and 16 digits in decimal. Obviously, if a real number of more than 8 digits, with a single precision floating point to express, it will produce errors! Similarly, if a real number of valid digits exceeds 16 digits, it can be represented by double-precision floating-point numbers, and it will also produce errors! For 1310720000000000000000.66 this number, the effective number is 24 bits, the single or double-precision floating-point numbers will produce errors, but different degrees:
Single-precision floating point number: 1310720040000000000000.00; double-precision floating point numbers: 1310720000000000000000.00
Visible, the double precision is 0.66, the single precision is nearly 4 trillion!
The above explains the error caused by the length limit, but this is not all! The use of IEEE754 standard computer floating-point number, in the internal is expressed in binary, but in the conversion of a decimal number to binary floating-point numbers, it will also cause errors, because not all the numbers can be converted to a finite length of binary numbers. For 131072.32 of this number, its effective number is 8 digits, should be able to use single-precision floating-point numbers accurately, why there are deviations?     Look at this data binary mantissa to understand 10000000000000000001010001 ... Obviously, its mantissa exceeds 24bit, according to the rounding rule, the tail number only takes 100000000000000000010100, the result causes the test to encounter "strange" phenomenon! This is similar to the fact that 131072.68 uses a single-precision floating-point number representation to become 131072.69. In fact, the number of valid digits is less than 8 digits, floating point numbers are not necessarily accurate, 7.22 of the number of the mantissa can not be used in the 24bit binary, of course, the test in the database will not be a problem (after rounding or 7.22), but if you participate in some calculations, error accumulation, it may produce a large deviation.

Ii. numerical types in MySQL and Oracle

The question is, does MySQL exist only? Obviously not, as long as the floating-point implementations conforming to the IEEE754 standard have the same problem.
numeric type in MySQL (excluding integral type):
IEEE754 floating-point number: float (single precision), double or real (double precision)
Fixed-point number: Decimal or numeric
numeric Types in Oracle:
Oracle Floating-point number: number (note that no precision is specified)
IEEE754 floating-point number: binary_float (single precision), binary_double (double precision) float,float (n) (ANSI required data type)
Fixed-point: Number (P,s)
If you use Binary_float to test in Oracle, the result is the same. Therefore, in the database, for money or other precision-sensitive data, you should use fixed-point numbers to store, for MySQL is decimal, for Oracle is number (P,S). Double-precision floating-point number, for the larger data also have problems!

Third, there are floating point numbers in programming

There are not only floating point numbers in the database, but also in programming, even more worthy of attention!
Through the above introduction, the error of floating point number should be more clear. If you do a complex floating-point operation in a program, the error will be magnified further. Therefore, in the program design, if the use of floating-point numbers, we must be aware of the possible error problems. Not only that, floating-point numbers, if handled poorly, can also result in program bug! Look at the following statement: if (x!= y) {z = 1/(x-y);} This statement does not seem to be a problem, but if it is a floating-point number, there may be a problem! Look at 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 take it for granted that the output should be 0.22, but the actual result is 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, also pay attention to some special values in floating point numbers, such as NaN, +0,-0, + infinity,-infinity, IEEE754 although some of the agreement, but each specific implementation, different hardware structure, there will be some differences, if not attention will also cause errors!

Four, Summary:

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

1. There are errors in floating point numbers;
2, to the currency and so on the precision sensitive data, should use the fixed-point number representation or the storage;
3, programming, if the use of floating point, we should pay special attention to the error problem, and try to avoid doing floating-point comparison;
4, should pay attention to the floating point number some special value processing

Attention matters

In MYSQL 5.022,
If a field F is a float type, then in the query, 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. Changing float to double is not a problem. However, if the data in the database is large, or the amount of modification is too large, this method is not appropriate. This method is only suitable for the initial phase of designing the database.
2. Set the precision of float and then query on it.
If you want to be accurate to 3 digits, then: SELECT * from T where format (f,3) = Format (2.2,3);

However, the precision can not exceed 6. Otherwise there will be an error. Because the float type allows up to 6 digits to the decimal point.