MySQL Data Type-detailed description of decimal

1. first, I will use the decimal type for high-precision items, such as money, and will not consider float and double, because they are prone to errors. numeric and decimal are synonymous, and numeric will automatically convert to decimal.

DECIMAL is introduced from MySQL 5.1. The column declaration syntax is DECIMAL (M, D ). In MySQL 5.1, the parameter value range is as follows:

· M is the maximum number (precision) of a number ). The range is 1 ~ 65 (in older MySQL versions, the allowed range is 1 ~ 254). The default value of M is 10.

· D is the number (scale) to the right of the decimal point ). The range is 0 ~ 30, but cannot exceed M.

Note: float occupies 4 bytes, double occupies 8 bytes, and decimail (M, D) occupies M + 2 bytes.

For example, the maximum value of DECIMAL () is 9 9 9. 9 9, because there are 7 bytes available.

Influence of M and D on DECIMAL (M, D) value range

Type description value range (MySQL <3.23) value range (MySQL> = 3.23)

MySQL <3.23 MySQL> = 3.23
DECIMAL (4, 1)-9.9 to 99.9-999.9 to 9999.9

DECIMAL (99.9)-999.9 to 9999.9-99999.9

DECIMAL (6, 1)-999.9 to 9999.9-99999.9 to 999999.9

DECIMAL (99.99)-999.99 to 9999.99-99999.99

DECIMAL (6, 3)-9.999 to 99.999-999.999 to 9999.999

# In MySQL 3.23 and later versions, the value range of DECIMAL (M, D) is equal to the value range of DECIMAL (M + 2, D) in earlier versions.

Conclusion:

If the numeric value is within the value range and has more decimal places, the decimal places are dropped.

If the value is out of the value range, fill it with the maximum (small) value.

Recommended reading:

Implementation of MySQL backup and recovery

Summary of three methods for MySQL backup and recovery

MySQL backup and restoration (views and stored procedures)