Movatterモバイル変換

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

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

IfD is omitted, the default is 0. IfM is omitted, the default is 10.

The maximum value of 65 forM means that calculations onDECIMAL 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 ofDECIMAL literals can be; seeSection 12.21.3, “Expression Handling”.)

Values forDECIMAL 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, aDECIMAL(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. ADECIMAL(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 leading0 digits. If you insert+0003.1 into aDECIMAL(5,1) column, it is stored as3.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, aDECIMAL(3,0) column supports a range of-999 to999. ADECIMAL(M,D) column permits up toM -D digits to the left of the decimal point.

The SQL standard requires that the precision ofNUMERIC(M,D) beexactlyM digits. ForDECIMAL(M,D), the standard requires a precision of at leastM digits but permits more. In MySQL,DECIMAL(M,D) andNUMERIC(M,D) are the same, and both have a precision of exactlyM digits.

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