Detailed explanation of the usage of DECIMAL in MySQL data type

Detailed explanation of the usage of DECIMAL in MySQL data type

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

Normally values ​​assigned to floating-point columns are rounded to the decimal number specified for the column. If you store 1. 2 3 4 5 6 in a FLOAT(8, 1) column, the result is 1. 2. If the same value is stored in a FLOAT(8, 4) column, the result is 1. 2 3 4 6.

This means that you should define floating point columns with enough bits to get the most precise value possible. If you want accuracy to one thousandth, don't define the type so that it has only two decimal places.

This handling of floating-point values ​​was exempted in MySQL 3.23, when the performance of FLOAT(4) and FLOAT(8) changed. These two types are now single-precision (4-byte) and double-precision (8-byte) types, which are true floating-point types in the sense that their values ​​are stored as given (subject only to hardware limitations).

The DECIMAL type is different from FLOAT and DECIMAL, where DECIMAL is actually stored as a string. The maximum possible range of DECIMAL values ​​is the same as that of DOUBLE, but its effective range is determined by the values ​​of M and D. If M is changed and D is fixed, its value range will increase as M increases. The first three rows of Table 2-7 illustrate this. If M is fixed and D is changed, its value range will become smaller as D increases (but the accuracy increases). The last three rows of Table 2-7 illustrate this.

The range of values ​​for a given DECIMAL type depends on the version of the MySQL data type. For versions prior to MySQL 3.23, each value of a DECIMAL(M, D) column occupies M bytes, and the sign (if necessary) and decimal point are included in the M bytes. Thus, a column of type DECIMAL(5, 2) can have values ​​in the range -9.99 to 9 9 . 9 9 because they cover all possible 5-character values.

As of MySQL 3.23, DECIMAL values ​​are processed according to the ANSI specification, which states that DECIMAL(M, D) must be able to represent any value with M digits and D decimal places.

For example, DECIMAL(5, 2) must be able to represent all values ​​from -999.99 to 999.99. Also, the sign and decimal point must be stored, so DECIMAL values ​​take up M + 2 bytes since MySQL 3.23. For DECIMAL(5, 2), the "longest" value (-999.99) requires 7 bytes.

At one end of the positive range, a positive sign is not required, so the MySQL data type uses it to extend the range of values ​​beyond that required by the ANSI specification. For example, the maximum value of DECIMAL(5, 2) is 9 9 9 9. 9 9, because there are 7 bytes available.

In short, in MySQL 3.23 and later, the range of DECIMAL(M, D) is equal to the range of DECIMAL(M + 2, D) in earlier versions. In all versions of the MySQL data type, if D is 0 for a DECIMAL column, no decimal point is stored. The result of this is that the range of values ​​in the column is expanded because the bytes that were previously used to store the decimal point can now be used to store other numbers.

If you have any questions, please leave a message or come to the community to discuss. Thank you for reading and I hope it can help you. Thank you for your support of this site!