24.2. DECIMAL Data Type Changes
This section discusses the characteristics of the
DECIMAL data type (and its synonyms) in MySQL
5.1, with particular regard to the following topics:
Possible incompatibilities with applications that are written for
older versions of MySQL are noted throughout this section.
The declaration syntax for a DECIMAL column is
DECIMAL(M,D).
The ranges of values for the arguments in MySQL 5.1
are as follows:
M is the maximum number of digits
(the precision). It has a range of 1 to 65. (Older versions of
MySQL allowed a range of 1 to 254.)
D is the number of digits to the
right of the decimal point (the scale). It has a range of 0 to
30 and must be no larger than M.
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 is different from before. (In older versions of
MySQL, decimal values could have up to 254 digits. However,
calculations were done using floating-point and thus were
approximate, not exact.)
Values for DECIMAL columns in MySQL
5.1 are stored using a binary format that packs nine
decimal digits into four bytes. The storage requirements for the
integer and fractional parts of each value are determined
separately. Each multiple of nine digits requires four bytes, and
any digits left over require some fraction of four bytes. 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 four bytes. A
DECIMAL(20,10) column has ten digits on either
side of the decimal point. Each part requires four bytes for nine
of the digits, and one byte for the remaining digit.
The storage required for leftover digits is given by the following
table:
Unlike some older versions of MySQL (prior to 5.0.3),
DECIMAL columns in MySQL 5.1 do
not store a leading + character or leading
0 digits. If you insert
+0003.1 into a DECIMAL(5,1)
column, it is stored as 3.1. Applications that
rely on the older behavior must be modified to account for this
change.
DECIMAL columns in MySQL 5.1 do
not allow 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 allows at most M –
D digits to the left of the decimal
point. This is not compatible with applications relying on older
versions of MySQL that allowed storing an extra digit in lieu of a
+ sign.
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 allows more. In MySQL,
DECIMAL(M,D)
and
NUMERIC(M,D)
are the same, and both have a precision of exactly
M digits.
For more detailed information about porting applications that rely
on the old treatment of the DECIMAL data type,
see the MySQL 5.0 Reference Manual.
