小编点评

**DECIMAL Data Type** The  DECIMAL  data type is a fixed-point data type that stores numeric values with a specified number of digits and precision. **Key Characteristics:** * **Maximum number of digits:** The maximum number of digits allowed for the precision and scale parts is determined by the arguments passed to the `DECIMAL()` function. * **Storage format:** Values are stored in a binary format that packs nine decimal digits into 4 bytes. * **Scale:** The number of digits to the right of the decimal point (scale) is specified by the `D` argument, with a range of 0 to 30. * **Precision:** The number of digits to the left of the decimal point (precision) is specified by the `M` argument, with a range of 1 to 65. * **Values:** * Integer part: 4 bytes for up to 9 digits * Fractional part: 3 bytes for up to 6 digits **Declaration Syntax:** ```sql DECIMAL(M,D) ``` **Examples:** *  DECIMAL(18,9) : 18 digits in the integer part and 9 digits in the fractional part. *  DECIMAL(20,6) : 20 integer digits and 6 fractional digits. **Note:** * The maximum value of 65 for the `M` argument means calculations on decimal values are accurate up to 65 digits. * Leading and trailing zeros are not stored. *  DECIMAL columns do not support leading or trailing + or - signs or leading 0s.

正文

This section discusses the characteristics of the DECIMAL data type (and its synonyms), with particular regard to the following topics:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

If D is omitted, the default is 0. If M is omitted, the default is 10.

The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals differs from before. (There is also a limit on how long the text of DECIMAL literals can be; see Section 12.24.3, “Expression Handling”.)

Values for DECIMAL columns are stored using a binary format that packs nine decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table.

For example, a DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require 4 bytes. A DECIMAL(20,6) column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and 3 bytes for the remaining five digits. The six fractional digits require 3 bytes.

DECIMAL columns do not store a leading + character or - character or leading 0 digits. If you insert +0003.1 into a DECIMAL(5,1) column, it is stored as 3.1. For negative numbers, a literal - character is not stored.

DECIMAL columns do not permit values larger than the range implied by the column definition. For example, a DECIMAL(3,0) column supports a range of -999 to 999. A DECIMAL(M,D) column permits up to M - D digits to the left of the decimal point.

The SQL standard requires that the precision of NUMERIC(M,D) be exactly M digits. For DECIMAL(M,D), the standard requires a precision of at least M digits but permits more. In MySQL, DECIMAL(M,D) and NUMERIC(M,D) are the same, and both have a precision of exactly M digits.

For a full explanation of the internal format of DECIMAL values, see the file strings/decimal.c in a MySQL source distribution. The format is explained (with an example) in the decimal2bin() function.