What type is decimal?

Decimal is a precise data type that exists in MySQL, with the syntax format "DECIMAL(M,D)". Among them, M is the maximum number of numbers (precision), and its range is "1 to 65", and the default value is 10; D is the number of digits to the right of the decimal point (scale), and its range is "0 to 30", but it must not More than M.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The types of floating point numbers supported in MySQL include FLOAT, DOUBLE and DECIMAL types. The DECIMAL type is different from FLOAT and DOUBLE. DECIMAL is actually stored as a string. The maximum possible value range of DECIMAL is the same as that of DOUBLE, but its effective value range is determined by the values ​​of M and D. If M is changed and D is fixed, its value range will become larger as M becomes larger.

For things with relatively high precision, such as money, it is recommended to use the decimal type. Do not consider float and double, because they are prone to errors. Numeric and decimal are synonymous, and numeric will be automatically converted to decimal.

DECIMAL was introduced from MySQL 5.1, and the column declaration syntax is DECIMAL(M,D). In MySQL 5.1, the value range of parameters is as follows:

• M is the maximum number of numbers (precision). Its range is 1 to 65 (in older MySQL versions, the allowed range is 1 to 254), and the default value of M is 10.

• D is the number of digits to the right of the decimal point (scale). Its range is 0~30, but it must not exceed M.

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

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

So M and D are the key to affecting the value range of DECIMAL(M, D)

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

DECIMAL(4,1) -9.9 to 99.9 -999.9 to 9999.9

DECIMAL(5,1) -99.9 to 999.9 -9999.9 to 99999.9

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

DECIMAL(6,2) -99.99 to 999.99 -9999.99 to 99999.99

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

(Recommended tutorial: mysql video tutorial)

The value range of the given DECIMAL type depends on the version of the MySQL data type. For versions prior to MySQL3.23, each value of the DECIMAL(M, D) column occupies M bytes, and the sign (if required) and decimal point are included in the M bytes. Therefore, columns of type DECIMAL(5, 2) have values ​​in the range -9.99 to 99.99 because they cover all possible 5-character values.

# 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:

When the value is within its value range and there are too many decimal places, the decimal places will be truncated directly.

If the value is outside its value range, fill it with the largest (smallest) value.

For more programming related knowledge, please visit: Programming Video! !

The above is the detailed content of What type is decimal?. For more information, please follow other related articles on the PHP Chinese website!