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 non-standard MySQL extension to the upper range of

`DECIMAL`

columns

Some of these changes 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 allow a range of 1 to 254.`M`

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.

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 is different 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 one byte per digit or sign
character. Instead, a binary format is used that packs nine
decimal digits into four 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 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:

Leftover Digits | Number of Bytes |

0 | 0 |

1 | 1 |

2 | 1 |

3 | 2 |

4 | 2 |

5 | 3 |

6 | 3 |

7 | 4 |

8 | 4 |

9 | 4 |

As a result of the change from string to numeric format for
`DECIMAL`

storage, `DECIMAL`

columns no longer store a leading `+`

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`

. 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
non-standard extension that allowed values larger than the range
implied by the column definition. Formerly, one byte was allocated
for storing the sign character. For positive values that needed no
sign byte, MySQL allowed 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 allow 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 no longer is
allowed. In MySQL 5.0.3 and up, a
`DECIMAL(`

column allows at most
* M*,

`D`

`M`

`D`

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

be * M*,

`D`

`M`

`DECIMAL(``M`

,`D`

)

,
the standard requires a precision of at least
`M`

`DECIMAL(``M`

,`D`

)

and
`NUMERIC(``M`

,`D`

)

are the same, and both have a precision of exactly
`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 allowed 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 non-standard MySQL extension to the upper range of

`DECIMAL`

columns no longer is 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 you
have `DECIMAL`

columns in tables that were
created before 5.0.3, the old behavior still applies to those
columns. To convert the tables to the newer
`DECIMAL`

format, dump them with
**mysqldump** and reload them.