Managing & Using MySQL, 2nd Edition

Name

DECIMAL

Syntax

DECIMAL[(precision, [scale])] [ZEROFILL]

Storage

precision + 2 bytes

Description

Stores floating-point numbers where precision is critical, such as for monetary values. DECIMAL types require you to specify the precision and scale. The precision is the number of significant digits in the value. The scale is the number of those digits that come after the decimal point. For example, a BALANCE column declared as DECIMAL(9, 2) would store numbers with nine significant digits, two of which are to the right of the decimal point. The range for this declaration would be -9,999,999.99 to 9,999,999.99. If you specify a number with more decimal points, it is rounded to fit the proper scale. Values beyond the range of the DECIMAL are clipped to fit within the range.

MySQL actually stores DECIMAL values as strings, not as floating-point numbers. It uses one character for each digit, one character for the decimal points when the scale is greater than 0, and one character for the sign of negative numbers. When the scale is 0, the value contains no fractional part. Prior to MySQL 3.23, the precision actually had to include space for the decimal and sign. This requirement is no longer in place, in accordance with the ANSI specification.

ANSI SQL supports the omission of precision and/or scale where the omission of scale creates a default scale of zero and the omission of precision defaults to an implementation-specific value. In the case of MySQL, the default precision is 10.