MySQL supports an extension for optionally specifying the
display width of integer data types in parentheses following
the base keyword for the type. For example,
INT(4) specifies an
INT with a display width of
four digits. This optional display width may be used by
applications to display integer values having a width less
than the width specified for the column by left-padding them
with spaces. (That is, this width is present in the metadata
returned with result sets. Whether it is used or not is up to
the application.)
The display width does not constrain the
range of values that can be stored in the column. Nor does it
prevent values wider than the column display width from being
displayed correctly. For example, a column specified as
SMALLINT(3) has the usual
SMALLINT range of
-32768 to 32767, and
values outside the range permitted by three digits are
displayed in full using more than three digits.
When used in conjunction with the optional (nonstandard)
attribute ZEROFILL, the default padding of
spaces is replaced with zeros. For example, for a column
declared as INT(4) ZEROFILL, a
value of 5 is retrieved as
0005.
The ZEROFILL attribute is ignored when a
column is involved in expressions or
UNION queries.
If you store values larger than the display width in an
integer column that has the ZEROFILL
attribute, you may experience problems when MySQL generates
temporary tables for some complicated joins. In these cases,
MySQL assumes that the data values fit within the column
display width.
All integer types can have an optional (nonstandard) attribute
UNSIGNED. Unsigned type can be used to
permit only nonnegative numbers in a column or when you need a
larger upper numeric range for the column. For example, if an
INT column is
UNSIGNED, the size of the column's range is
the same but its endpoints shift from
-2147483648 and
2147483647 up to 0 and
4294967295.
Floating-point and fixed-point types also can be
UNSIGNED. As with integer types, this
attribute prevents negative values from being stored in the
column. Unlike the integer types, the upper range of column
values remains the same.
If you specify ZEROFILL for a numeric
column, MySQL automatically adds the
UNSIGNED attribute to the column.
Integer or floating-point data types can have the additional
attribute AUTO_INCREMENT. When you insert a
value of NULL (recommended) or
0 into an indexed
AUTO_INCREMENT column, the column is set to
the next sequence value. Typically this is
value+1value is the largest value for the
column currently in the table.
AUTO_INCREMENT sequences begin with
1.