Functions and usage of DECIMAL in MySQL Data Type

In MySQL data types, such as INT, FLOAT, DOUBLE, CHAR, and DECIMAL, they all have their respective functions, next we will mainly introduce the role and usage of the DECIMAL type in the MySQL data type.

Generally, the value of the floating point column is rounded to the specified decimal number. If 1. 2 3 4 5 6 is stored in a FLOAT (8, 1) column, the result is 1. 2. If the same value is stored in the FLOAT (8, 4) column, the result is 1. 2 3 4 6.

This indicates that a floating-point column with enough digits should be defined to obtain the value as accurate as possible. To be accurate to 1‰, do not define that the type has only two decimal places.

This processing of floating point values has exceptions in MySQL3.23, and the performance of FLOAT (4) and FLOAT (8) has changed. These two types are now single-precision 4 bytes) and double-precision 8 bytes. The value is only limited by hardware in the given format, these two types are real floating point types.

DECIMAL type is different from FLOAT and DECIMAL type, where DECIMAL is actually stored in strings. The maximum possible value range of DECIMAL is the same as that of DOUBLE, but the valid value range is determined by the values of M and D. If M is changed and D is fixed, the value range increases with M. The first three rows in Table 2-7 illustrate this. If M is fixed and D is changed, the value range decreases with D but the accuracy increases ). The last three rows in Table 2-7 illustrate this.

The value range of the given DECIMAL type depends on the MySQL Data Type version. For versions earlier than MySQL3.23, each value in the DECIMAL (M, D) column occupies M bytes, and if needed, the symbols and DECIMAL points are included in M bytes. Therefore, for columns of the DECIMAL type (5, 2), the value range is-9.99 to 9 9. 9 9, because they overwrite all possible 5 character values.

Like MySQL3.23, DECIMAL values are processed in accordance with ANSI standards. ANSI standards stipulate that DECIMAL (M, D) must be able to represent any value of M-bit numbers and D-DECIMAL places.

For example, DECIMAL (5, 2) must be able to represent all values from-999.99 to 999.99. The symbols and DECIMAL points must be stored. Therefore, DECIMAL values have occupied M + 2 bytes since MySQL3.23. For DECIMAL (5, 2), the "longest" value-9 9 9. 9 9) requires 7 bytes.

At one end of the positive value range, a positive number is not required. Therefore, the MySQL Data Type expands the value range so that it exceeds the value range specified by ANSI. For example, the maximum value of DECIMAL (5, 2) is 9 9 9. 9 9, because there are 7 bytes available.

In short, in MySQL 3.23 and later versions, the value range of DECIMAL (M + 2, D) is equal to that of DECIMAL (M + 2, D) in earlier versions. In all versions of MySQL Data Types, if the D of a DECIMAL column is 0, the DECIMAL point is not stored. The result is that the value range of the column is expanded, because the bytes used to store the decimal point can now be used to store other numbers.

1. MySQL two-way replication technology classic edition
2. Two Methods for setting MySQL Access Permissions
3. Use the MySQL built-in replication function to optimize the actual operation
4. How to lock the MySQL database table?
5. High-quality and high-performance compiling of MySQL syntax