Mysqlfloat precision and scope Summary

The FLOAT type is used to indicate the approximate numeric data type. The SQL standard allows you to specify the precision (but not the exponential range) with bits in parentheses after the keyword FLOAT ). MySQL also supports optional specifications that are only used to determine the storage size. The precision between 0 and 23 corresponds to the 4-byte single precision of the FLOAT column. The precision between 24 and 53 corresponds to the 8-byte dual precision of the DOUBLE column.

The FLOAT type is used to indicate the approximate numeric data type. The SQL standard allows you to specify the precision (but not the exponential range) with bits in parentheses after the keyword FLOAT ). MySQL also supports optional specifications that are only used to determine the storage size. The precision between 0 and 23 corresponds to the 4-byte single precision of the FLOAT column. The precision between 24 and 53 corresponds to the 8-byte dual precision of the DOUBLE column.

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

For the floating-point column type, the single-precision value in MySQL uses 4 bytes, and the double-precision value uses 8 bytes.

The FLOAT type is used to indicate the approximate numeric data type. The SQL standard allows you to specify the precision (but not the exponential range) with bits in parentheses after the keyword FLOAT ). MySQL also supports optional specifications that are only used to determine the storage size. The precision between 0 and 23 corresponds to the 4-byte single precision of the FLOAT column. The precision between 24 and 53 corresponds to the 8-byte dual precision of the DOUBLE column.

MySQL allows the use of non-standard syntax: FLOAT (M, D), 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 behind the decimal point. For example, a column defined as FLOAT (999.9999) can be displayed. MySQL returns a rounded value when saving the value. Therefore, if 999.00009 is inserted in the FLOAT (999.0001) column, the approximate result is.

MySQL treats DOUBLE as a synonym for double precision (non-standard extension. MySQL also treats REAL as a synonym for double precision (non-standard extension) unless the SQL Server mode includes the REAL_AS_FLOAT option.

To ensure maximum portability, FLOAT or double precision should be used for codes that require storing approximate numeric data values. PRECISION or digits are not specified.

The DECIMAL and NUMERIC types are treated as the same type in MySQL. They are used to save values that must be accurate, such as currency data. When declaring a column of this type, you can (and usually need to) 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 indicates the primary digits of the saved value, and the scale indicates the digits that can be saved after the decimal point.

In MySQL 5.1, DECIMAL and NUMERIC values are saved in binary format.

Standard SQL requires that the salary column be able to save any value with an integer of 5 digits and two decimal places. Therefore, the range of values that can be saved in the salary column is from-999.99 to 999.99.

In standard SQL, the syntax DECIMAL (M) is equivalent to DECIMAL (M, 0 ). Similarly, the syntax DECIMAL is equivalent to DECIMAL (M, 0). The M value can be determined by calculation. MySQL 5.1 supports DECIMAL and NUMERIC data types. The default M value is 10.

The maximum number of digits of a DECIMAL or NUMERIC is 65, but the actual range of a specific DECIMAL or NUMERIC column is subject to the precision or scale of a specific column. If the number of digits after the decimal point assigned by such columns exceeds the allowed range of the specified scale, the value is converted to this scale. (The specific operation is related to the operating system, but the general results are intercepted to the allowed digits ).

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

Instance

To attract everyone's attention, let's take an example before introducing floating point numbers and fixed points:

The Code is as follows:
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 can see that the value of column c1 is changed from 131072.32 to 131072.31, which is caused by the inaccuracy of floating point numbers.
In mysql, float, double (or real) are floating point numbers, and decimal (or numberic) are fixed points.
The advantage of a floating point over a fixed number of points is that a floating point can represent a larger data range under a certain length;
Its disadvantage is that it will cause precision problems.

Difference between Float type and double type

FLOAT [(M, D)] [ZEROFILL]
A small (single precision) floating point number. It cannot be unsigned. The allowed values are-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38. M indicates the display width, and D indicates the decimal digits. FLOAT without parameters or a parameter with <24 represents a single precision floating point number.
DOUBLE [(M, D)] [ZEROFILL]
A normal-sized (double-precision) floating point number. It cannot be unsigned. The allowed values are-1.7976931348623157E + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157E + 308. M indicates the display width, and D indicates the number of decimal places. A double or FLOAT (X) (25 <= X <= 53) without a parameter represents a DOUBLE-precision floating point number.