Learning Mysql

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 inset 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 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 fora 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.

Download 4.24 Mb.

Share with your friends: