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:

Maximum number of digits

Storage format

Storage requirements

The nonstandard MySQL extension to the upper range of

`DECIMAL`

columns

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(`

,
although the range of values for the arguments has changed
somewhat:
* M*,

*)*

`D`

is the maximum number of digits (the precision). It has a range of 1 to 65. This introduces a possible incompatibility for older applications, because previous versions of MySQL permit a range of 1 to 254. (The precision of 65 digits actually applies as of MySQL 5.0.6. From 5.0.3 to 5.0.5, the precision is 64 digits.)`M`

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`D`

.`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 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.

Leftover Digits | Number of Bytes |
---|---|

0 | 0 |

1–2 | 1 |

3–4 | 2 |

5–6 | 3 |

7–9 | 4 |

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(`

column permits at most * M*,

*)*

`D`

*-*

`M`

*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.*

`D`

The SQL standard requires that the precision of
`NUMERIC(`

be * M*,

*)*

`D`

*exactly*

*digits. For*

`M`

`DECIMAL(``M`

,`D`

)

,
the standard requires a precision of at least
*digits but permits more. In MySQL,*

`M`

`DECIMAL(``M`

,`D`

)

and
`NUMERIC(``M`

,`D`

)

are the same, and both have a precision of exactly
*digits.*

`M`

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.

For

`DECIMAL(`

, the maximum,`M`

)`D`

is 65, not 254.`M`

Calculations involving exact-value decimal numbers are accurate to 65 digits. This is fewer than the maximum number of digits permitted before MySQL 5.0.3 (254 digits), but the exact-value precision is greater. Calculations formerly were done with double-precision floating-point, which has a precision of 52 bits (about 15 decimal digits).

The nonstandard MySQL extension to the upper range of

`DECIMAL`

columns is no longer supported.Leading “

`+`

” and “`0`

” characters are not stored.

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.

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.