MySQL data type decimal (m,d) usage

In MySQL data types, such as Int,float,double,char,decimal, and so on, they all have their own role, below we will mainly introduce the MySQL data type of the role and use of the decimal type.

The value that is normally given to a floating-point column is rounded to the decimal number specified by the column. If 1 is stored in a column of float (8, 1). 2 3 4 5 6, the result is 1. 2. If the same value is stored in a column of float (8, 4), the result is 1. 2 3 4 6.

This means that a floating-point column with sufficient digits should be defined in order to get as accurate a value as possible. If you want to be accurate to 1 per thousand, then do not define the type to have only two decimal places.

This processing of floating-point values has an exception in MySQL3.23, and the performance of float (4) and float (8) varies. Both types are now single-precision (4-byte) and double-precision (8-byte) types, where their values are stored in the form given (limited by hardware only), the two types are true floating-point types.

The decimal type differs from float and decimal, where decimal is actually stored as a string. The maximum possible range of DECIMAL values is the same as a double, but the valid range of values is determined by the values of M and D. If M is changed and the D is fixed, the range of values will be larger with M. This is illustrated in the first three lines of table 2-7. If M is fixed and D is changed, the range of values will be smaller (but with increased accuracy) as d becomes larger. The following three lines of table 2-7 illustrate this point.

The range of values for a given decimal type depends on the version of the MySQL data type. For previous versions of MySQL3.23, each value of the decimal (M, D) column occupies m bytes, while the symbol (if necessary) and the decimal point are included in M bytes. Therefore, a column of type decimal (5, 2) has a value ranging from 9.99 to 9 9. 9 9, because they override all possible 5-character values.

As with MySQL3.23, decimal values are handled according to the ANSI specification, and the ANSI specification specifies that decimal (m, d) must be able to represent any value of M-digits and D-decimal digits.

For example, DECIMAL (5, 2) must be able to represent all values from 999.99 to 999.99. Also, symbols and decimal points must be stored, so the decimal value has been 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 range of positive values, a plus sign is not required, so the MySQL data type uses it to extend the range of values beyond what is required by ANSI specifications. The maximum value for decimal (5, 2) is 9 9 9 9. 9 9, because there are 7 bytes available.

In short, in MySQL3.23 and later versions, the value range of decimal (M, d) equals the range of decimal (M + 2, D) in earlier versions. In all versions of the MySQL data type, if D of a decimal column is 0, the decimal point is not stored. The result of this is to expand the range of values for the column, since the bytes used to store the decimal point are now used to hold other numbers.

MySQL data type decimal (m,d) usage