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.
 
Search WWH ::




Custom Search