MySQL float precision and range summary

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 floating-point column types, a single value in MySQL uses 4 bytes, and a double value of 8 bytes.

The float type is used to represent approximate numeric data types. The SQL standard allows you to select a bit-specified precision (but not an exponential range) within parentheses behind the keyword float. MySQL also supports optional precision rules that are used only to determine storage size. The precision of 0 to 23 corresponds to the 4-byte single precision of the float column. The precision of 24 to 53 corresponds to the 8-byte double of the Double column.

MySQL allows for nonstandard syntax: FLOAT (M,D) or real (m,d) or double PRECISION (m,d). Here, "(M,d)" indicates that the value displays a total of M-bit integers, where D is positioned after the decimal point. For example, a column defined as float (7,4) can be displayed as-999.9999. MySQL is rounded when the value is saved, so if you insert 999.00009 in float (7,4) aligns, the approximate result is 999.0001.

MySQL treats double as a synonym for a double PRECISION (nonstandard extension). MySQL also treats real as a synonym for a double PRECISION (nonstandard extension), unless the SQL Server mode includes the Real_as_float option.

To ensure maximum portability, code that needs to be stored with approximate numeric data values should use float or double PRECISION, without specifying the precision or number of digits.

The decimal and numeric types are considered the same type in MySQL. They are used to save values that must be accurate, such as currency data. When declaring a column of that type, you can (and usually will) specify the precision and scale, for example:

Salary DECIMAL (5,2)
In this example, 5 is the precision, and 2 is the scale. The precision represents the primary number of digits of the saved value, and the scale represents the number of digits that can be saved after the decimal point.

Save decimal and numeric values in binary format in MySQL 5.1.

Standard SQL requires the Salary column to hold any value with 5-bit integer digits and two decimal digits. Therefore, in this case the values that can be saved in the Salary column range from 999.99 to 999.99.

In standard SQL, Syntax decimal (M) is equivalent to decimal (m,0). Similarly, the syntax decimal is equivalent to decimal (m,0) and can be computed to determine the value of M. Variable forms for decimal and numeric data types are supported in MySQL 5.1. The m default value is 10.

The maximum number of digits in decimal or numeric is 65, but the actual range of the specific decimal or numeric columns is constrained by the precision or scale of the specific columns. If the number of digits after the decimal point assigned by such a column exceeds the specified scale, the value is converted to that scale. (The actual operation is related to the operating system, but the general result is intercepted to the allowed number of digits).

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, pay attention to some special values of floating point processing.

Instance

In order to arouse the attention of everyone, before introducing floating-point numbers and fixed-point number, let us look at an example:

The code is as follows	Copy Code
mysql> CREATE TABLE Test (C1 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; +-----------+-----------+ | c1| 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) is a floating-point number, and decimal (or numberic) is the fixed-point count.
The advantage of floating point number relative to fixed-point numbers is that floating-point numbers can represent a larger range of data in a certain length.
Its disadvantage is that it can cause precision problems.

difference between float type and double type

float[(m,d)] [Zerofill]
A small (single precision) floating-point number. cannot be unsigned. The allowable values are -3.402823466E+38 to -1.175494351e-38,0 and 1.175494351E-38 to 3.402823466E+38. M is the display width and d is the number of digits. A float with no parameters or a parameter with <24 represents a single precision floating-point number.
double[(m,d)] [Zerofill]
A normal size (double precision) floating-point number. cannot be unsigned. The allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the width of the display and D is the decimal digit. Double or float (x) with no arguments (< = x < = 53) represents a double-precision floating-point number.