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

Some of these characteristics result in possible incompatibilities for applications that are written for older versions of MySQL. These incompatibilities are noted throughout this section.

The declaration syntax for a DECIMAL column remains DECIMAL(M,D), although the range of values for the arguments has changed somewhat:

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. (Prior to MySQL 5.0.3, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.) This change in the range of literal values is another possible source of incompatibility for older applications.

Values for DECIMAL columns no longer are represented as strings that require 1 byte per digit or sign character. Instead, a binary format is used that packs nine decimal digits into 4 bytes. This change to DECIMAL storage format changes the storage requirements as well. 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 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.

As a result of the change from string to numeric format for DECIMAL storage, DECIMAL columns no longer store a leading + or - character or leading 0 digits. Before MySQL 5.0.3, if you inserted +0003.1 into a DECIMAL(5,1) column, it was stored as +0003.1. As of MySQL 5.0.3, it is stored as 3.1. For negative numbers, a literal - character is no longer stored. Applications that rely on the older behavior must be modified to account for this change.

The change of storage format also means that DECIMAL columns no longer support the nonstandard extension that permitted values larger than the range implied by the column definition. Formerly, 1 byte was allocated for storing the sign character. For positive values that needed no sign byte, MySQL permitted an extra digit to be stored instead. For example, a DECIMAL(3,0) column must support a range of at least –999 to 999, but MySQL would permit storing values from 1000 to 9999 as well, by using the sign byte to store an extra digit. This extension to the upper range of DECIMAL columns is no longer supported. As of MySQL 5.0.3, a DECIMAL(M,D) column permits at most M - D digits to the left of the decimal point. This can result in an incompatibility if an application has a reliance on MySQL permitting “too-large” values.

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.

Summary of incompatibilities:

The following list summarizes the incompatibilities that result from changes to DECIMAL column and value handling. You can use it as guide when porting older applications for use with MySQL 5.0.3 and up.

The behavior used by the server for DECIMAL columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but a table that was created before 5.0.3 has DECIMAL columns, the old behavior still applies to those columns. To convert the table to the newer DECIMAL format, dump it with mysqldump and reload it.