preface

This article is a translation of the official MySql documentation for the author to read and expand the path.

Introduction to the

The official documentation

Data values for the DECIMAL and NUMERIC types (fixed-point types) that store precision values. Use these types when maintaining precision is important, such as currency data. In MYSQL, NUMERIC is implemented as DECIMAL, so the following comments about DECIMAL also apply to NUMERIC.

MYSQL stores values in binary format. See 12.25 Precision Math.

In a DECIMAL column declaration, you can specify (and usually do) specify precision and DECIMAL places. Such as:

Salary is a DECIMAL (5, 2)Copy the code

In this case, 5 is precision and 2 is a decimal place. Precision represents the number of significant digits of a stored value, and decimals represent the number of digits that can be stored after the decimal point.

Standard SQL requires DECIMAL(5,2) to be able to store any five-digit and two-digit DECIMAL value, so values can be stored in the salary column from -999.99 to 999.99.

In standard SQL, syntactic DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), MySQL supports both syntax, and the default bit of M is 10.

If scale is 0, the DECIMAL value does not contain the DECIMAL point and fractional part.

The maximum number of DECIMAL digits is 65, but the actual range of a given DECIMAL column is limited by the specified precision and DECIMAL places. So when the column is allocated beyond the specified range, the value is converted to within the specified range. (The exact behavior is specific to the operating system, but the effect is usually truncated to an allowable number of bits.)

DECIMAL data type characteristics

The official documentation

This section discusses the characteristics of DECIMAL data types (and their synonyms), with special attention to the following topics:

• The largest number
• Storage format
• Storage requirements
• Non-standard MySQL extends to the upper limit of DECIMAL columns.

The declarative syntax for DECIMAL columns is DECIMAL(M,D). The value can be:

• M is the largest number (precision). It ranges from 1 to 65.
• D is the number of digits to the right of the decimal point. It ranges from 0 to 30 and cannot be greater than M.

If D is omitted, it defaults to 0. If M is omitted, the default is 10.

The maximum value of M, 65, means that the calculation of DECIMAL values is accurate to 65 bits. The 65-bit precision limit also applies to exact numeric literals, so the maximum range of such literals is different than before. (The length of DECIMAL text is also limited; See section 12.25.3, “Expression processing.”)

DECIMAL column values are stored in binary format, packing nine DECIMAL digits into four bytes. The storage requirements for the integer and fractional parts of each decimal are specified separately. Each multiple of nine digits requires four bytes, and any remaining digits require a portion of four bytes. The following table shows the storage space required for the remaining numbers.

For example, the DECIMAL column (18,9) has nine digits on each side of the DECIMAL point, so the integer part and the DECIMAL part require four bytes each. The DECIMAL(20,6) column has 14 integer bits and 6 DECIMAL places. The nine digits of an integer require four bytes, the remaining five require three bytes, and the six decimal digits require three bytes.

The DECIMAL column does not store leading + characters or leading – characters or leading 0 digits. If +0003.1 is inserted into DECIMAL(5,1) column, it stores 3.1. For negative numbers, the – character is not stored.

A DECIMAL column does not allow values greater than the range implied by the column definition. For example, the DECIMAL(3,0) column supports a range of -999 to 999. DECIMAL(M,D) At most M-D bits are allowed to the left of the DECIMAL point.

The SQL standard requires the accuracy of NUMERIC(M,D) to be the exact M bits. For DECIMAL(M,D), the standard requires an accuracy of at least M bits, but allows more. In MySQL, DECIMAL(M,D) and NUMERIC(M,D) are identical, and both have m-bit precision.

For a complete explanation of the internal format of DECIMAL values, see the strings/ Decimal.c file of the source code distributed by MySQL. This format is explained (by example) in the decimal2bin() function.

decimal_t

The following data structure for Decimal does not represent actual storage.

typedef int32 decimal_digit_t; 

struct decimal_t { 
    int intg, frac, len; 
    bool sign; 
    decimal_digit_t *buf; 
};
Copy the code

• Intg indicates the number of digits in the integer part
• Frac represents the number of decimal digits
• Sign means sign (plus or minus)
• Len represents the length of the array, which in MySQL implementations is always 9 and represents the upper limit of storage, while the actual valid part of buF is determined by intG and FRAC
• *buf represents the number to store decimal

For example,

Decimal_t dec_123_45 = {int intg = 3; // 123.45 Decimal (5, 2) integer part 3; decimal_t dec_123_45 = {int intg = 3; int frac = 2; int len = 9; bool sign = false; decimal_digit_t *buf = {123, 450000000, ... }; };Copy the code

conclusion

• DECIMAL point type, which can accurately represent specified precision
• DECIMAL(M,D) M represents precision and D represents DECIMAL places.
• M The default value is 10. With standard MySQL, M is up to 65, and portions beyond that are usually truncated.
• D The default value is 0. The maximum value is 30 and cannot be greater than M.
• DECIMAL storage uses the binary format, where integers and decimals are stored separately, and nine DECIMAL numbers are represented by four bytes.