Detailed explanation of usage examples of DECIMAL in MySQL data types

In MySQL data types, such as INT, FLOAT, DOUBLE, CHAR, DECIMAL, etc., they all have their own functions. Below we will mainly introduce the functions and usage of the DECIMAL type in MySQL data types. This article mainly introduces relevant information about the detailed usage examples of DECIMAL in the MySQL data type. I hope this article can help everyone, and friends in need can refer to it.

Generally the value assigned to a floating point column is rounded to the decimal number specified by this 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 the column of FLOAT(8, 4), the result is 1. 2 3 4 6.

This means that a floating point column should be defined with enough digits to get the most accurate value possible. If you want accuracy to the thousandth, don't define the type so that it 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), in the sense that their values ​​are stored in the form given (subject only to hardware limitations). True floating point type.

The DECIMAL type is different from FLOAT and DECIMAL, in which 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. The first three rows of Table 2-7 illustrate this point. If M is fixed and D is changed, its value range will become smaller as D becomes larger (but the accuracy increases). The last three rows of Table 2-7 illustrate this point.

The value range of a 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 ​​ranging from -9.99 to 9 9 . 9 9 because they cover all possible 5-character values.

Just like MySQL3.23, DECIMAL values ​​are processed according to the ANSI specification. The ANSI specification stipulates 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. And the sign and decimal point must be stored, so since MySQL3.23 the DECIMAL value occupies M + 2 bytes. For DECIMAL(5, 2), the "longest" value (-9 9 9 . 9 9) requires 7 bytes.

At one end of the positive value range, the plus sign is not required, so the MySQL data type uses it to expand the value range beyond the value range 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 MySQL3.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. In all versions of the MySQL data type, if D of a DECIMAL column is 0, no decimal point is stored. The effect of this is to expand the value range of the column, because the bytes used to store the decimal point can now be used to store other numbers.

Related recommendations:

10 recommended articles about decimal type

MySQL data type-detailed explanation of decimal

Note that decimal must be used to identify currency values ​​in mysql

The above is the detailed content of Detailed explanation of usage examples of DECIMAL in MySQL data types. For more information, please follow other related articles on the PHP Chinese website!