Database Reference
In-Depth Information
Discussion
You should follow certain principles when creating
AUTO_INCREMENT
columns. As an
illustration, consider how
Recipe 13.1
declared the
id
column in the
insect
table:
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
The
AUTO_INCREMENT
keyword informs MySQL that it should generate successive se‐
quence numbers for the column's values, but the other information is important, too:
•
INT
is the column's base data type. You need not necessarily use
INT
, but the column
should be one of the integer types:
TINYINT
,
SMALLINT
,
MEDIUMINT
,
INT
, or
BIGINT
.
•
UNSIGNED
prohibits negative column values. This is not a required attribute for
AUTO_INCREMENT
columns, but sequences consist only of positive integers (normally
beginning at 1), so there is no reason to permit negative values. Furthermore,
not
declaring the column to be
UNSIGNED
cuts the range of your sequence in half. For
example,
TINYINT
has a range of -128 to 127. Because sequences include only pos‐
itive values, the effective range of a
TINYINT
sequence is 1 to 127.
TINYINT
UN
SIGNED
has a range of 0 to 255, which increases the upper end of the sequence to
255. The specific integer type determines the maximum sequence value. The fol‐
lowing table shows the maximum unsigned value of each type; use this information
to choose a type big enough to hold the largest value you'll need:
Data type
Maximum unsigned value
255
TINYINT
65,535
SMALLINT
16,777,215
MEDIUMINT
4,294,967,295
INT
18,446,744,073,709,551,615
BIGINT
Sometimes people omit
UNSIGNED
so that they can create rows that contain negative
numbers in the sequence column (using -1 to signify “has no ID,” for example.)
This is a bad idea. MySQL makes no guarantees about how negative numbers will
be treated in an
AUTO_INCREMENT
column, so by using them you're playing with fire.
For example, if you resequence the column, all your negative values get turned into
positive sequence numbers.
•
AUTO_INCREMENT
columns cannot contain
NULL
values, so
id
is declared as
NOT
NULL
. (It's true that you can specify
NULL
as the column value when you insert a new
row, but for an
AUTO_INCREMENT
column, that really means “generate the next se‐
quence value.”) MySQL automatically defines
AUTO_INCREMENT
columns as
NOT
NULL
if you forget.