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 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)
ZEROFILL
attribute, 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 for
columns 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)
UNSIGNED
attribute. An 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 up, from
-2147483648
and 2147483647
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.
Integer or floating-point data types can have the
AUTO_INCREMENT
attribute. When you insert a
value of NULL
into an indexed
AUTO_INCREMENT
column, the column is set to
the next sequence value. Typically this is
, where
value
+1value
is the largest value for the
column currently in the table.
(AUTO_INCREMENT
sequences begin with
1
.)
Storing 0
into an
AUTO_INCREMENT
column has the same effect as
storing NULL
, unless the
NO_AUTO_VALUE_ON_ZERO
SQL mode
is enabled.
Inserting NULL
to generate
AUTO_INCREMENT
values requires that the
column be declared NOT NULL
. If the column is
declared NULL
, inserting
NULL
stores a NULL
. When
you insert any other value into an
AUTO_INCREMENT
column, the column is set to
that value and the sequence is reset so that the next
automatically generated value follows sequentially from the
inserted value.
Negative values for AUTO_INCREMENT
columns
are not supported.