Databases Reference
In-Depth Information
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
SELECT * FROM numbers;
+-----------+
| my_number |
+-----------+
| 0003 |
| 0033 |
| 0333 |
| 3333 |
| 33333 |
| 333333 |
+-----------+
6 rows in set (0.00 sec)
You can see that numbers shorter than four digits wide are zero-padded to four
digits; once the numbers are longer than four digits long, they are shown unaffected
by the
width
and the
ZEROFILL
parameters.
If you use
ZEROFILL
, MySQL automatically adds
UNSIGNED
to the declaration (since
zero filling makes sense only in the context of positive numbers).
DECIMAL[(
width
[,
decimals
])] [UNSIGNED] [ZEROFILL]
A commonly used numeric type. Stores a fixed-point number such as a salary or
distance, with a total of
width
digits of which some smaller number are
decimals
that follow a decimal point. For example, a column declared as
price DECI
MAL(4,2)
should be used to store values in the range -99.99 to 99.99. If you try to
store a value that's outside this range, it will be stored as the closest value in the
allowed range. For example, 100 would be stored as 99.99, and -100 would be
stored as -99.99. Note that MySQL versions before 5.03 would allow an extra digit
for positive values (numbers from -99.99 to 999.99 could be stored). The
width
is
optional, and a value of 10 is assumed when this is omitted. The maximum value
of
width
is 255.
The number of
decimals
is optional and, when omitted, a value of 0 is assumed;
the maximum value of
decimals
should be two less than the value of
width
. If you're
storing only positive values, use the
UNSIGNED
keyword as described for
INT
. If you
want zero padding, use the
ZEROFILL
keyword for the same behavior as described
for
INT
. The keyword
DECIMAL
has three identical, interchangeable alternatives:
DEC
,
NUMERIC
, and
FIXED
.
Prior to MySQL version 5.0.3, a
DECIMAL
column was stored as a string, and so
required exactly the number of bytes of storage space as the length of the value
(plus up to two bytes for a minus sign and a decimal point if required). Beginning
with version 5.0.3, a binary format was introduced that uses four bytes for every
nine digits. Under both approaches, the value retrieved is identical to the value
stored; this isn't always the case with other types that contain decimal points, such
as the
FLOAT
and
DOUBLE
types described later.
