This section provides the basic background on how AUTO_INCREMENT
columns work, beginning with
a short example that demonstrates the sequence-generation mechanism.
The illustration centers around a bug-collection scenario: your son
(eight-year-old Junior) is assigned the task of collecting insects for
a class project at school. For each insect, Junior is to record its
name (“ant,”
“bee,” and so forth), and its
date and location of collection. You have expounded the benefits of
MySQL for record-keeping to Junior since his early days, so upon your
arrival home from work that day, he immediately announces the
necessity of completing this project and then, looking you straight in
the eye, declares that it’s clearly a task for which MySQL is
well-suited. Who are you to argue? So the two of you get to work.
Junior already collected some specimens after school while waiting for
you to come home and has recorded the following information in his
notebook:
Name | Date | Origin |
---|---|---|
millipede | 2006-09-10 | driveway |
housefly | 2006-09-10 | kitchen |
grasshopper | 2006-09-10 | front yard |
stink bug | 2006-09-10 | front yard |
cabbage butterfly | 2006-09-10 | garden |
ant | 2006-09-10 | back yard |
ant | 2006-09-10 | back yard |
millbug | 2006-09-10 | under rock |
Looking over Junior’s notes, you’re pleased to see that even at his tender age, he has learned to write dates in ISO format. However, you also notice that he’s collected a millipede and a millbug, neither of which actually are insects. You decide to let this pass for the moment; Junior forgot to bring home the written instructions for the project, so at this point it’s unclear whether these specimens are acceptable.
As you consider how to create a table to store this information,
it’s apparent that you need at least name
, date
, and origin
columns corresponding to the types of
information that Junior is required to record:
CREATE TABLE insect ( name VARCHAR(30) NOT NULL, # type of insect date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL # where collected );
However, those columns are not enough to make the table easy to
use. Note that the records collected thus far are not unique; both
ants were collected at the same time and place. If you put the
information into an insect
table
that has the structure just shown, neither ant row can be referred to
individually, because there’s nothing to distinguish them from one
another. Unique IDs would be helpful to make the rows distinct and to
provide values that make each row easy to refer to. An AUTO_INCREMENT
column is good for this
purpose, so a better insect
table
has a structure like this:
CREATE TABLE insect ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, # type of insect date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL # where collected );
Go ahead and create the insect
table using this second definition.
In Choosing the Data Type for a Sequence Column, we’ll discuss the
specifics of why the id
column is
declared the way it is.
Now that you have an AUTO_INCREMENT
column, you want to use it to
generate new sequence values. One of the useful properties of an
AUTO_INCREMENT
column is that you
don’t have to assign its values yourself: MySQL does so for you. There
are two ways to generate new AUTO_INCREMENT
values, demonstrated here
using the id
column of the insect
table. First, you can explicitly set
the id
column to NULL
. The following statement inserts the
first four of Junior’s specimens into the insect
table this way:
mysql>INSERT INTO insect (id,name,date,origin) VALUES
->(NULL,'housefly','2006-09-10','kitchen'),
->(NULL,'millipede','2006-09-10','driveway'),
->(NULL,'grasshopper','2006-09-10','front yard'),
->(NULL,'stink bug','2006-09-10','front yard'),
Second, you can omit the id
column from the INSERT
statement
entirely. In MySQL, you can create new rows without explicitly
specifying values for columns that have a default value. MySQL assigns
the default value to each missing column automatically, and the
default for an AUTO_INCREMENT
column happens to be the next sequence number. Thus, you can insert
rows into the insect
table without
naming the id
column at all. This
statement adds Junior’s other four specimens to the insect
table that way:
mysql>INSERT INTO insect (name,date,origin) VALUES
->('cabbage butterfly','2006-09-10','garden'),
->('ant','2006-09-10','back yard'),
->('ant','2006-09-10','back yard'),
->('millbug','2006-09-10','under rock'),
Whichever method you use, MySQL determines the next sequence
number for each row and assigns it to the id
column, as you can verify for
yourself:
mysql>SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2006-09-10 | kitchen |
| 2 | millipede | 2006-09-10 | driveway |
| 3 | grasshopper | 2006-09-10 | front yard |
| 4 | stink bug | 2006-09-10 | front yard |
| 5 | cabbage butterfly | 2006-09-10 | garden |
| 6 | ant | 2006-09-10 | back yard |
| 7 | ant | 2006-09-10 | back yard |
| 8 | millbug | 2006-09-10 | under rock |
+----+-------------------+------------+------------+
As Junior collects more specimens, you can add more rows to the table and they’ll be assigned the next values in the sequence (9, 10, ...).
The concept underlying AUTO_INCREMENT
columns is simple enough in
principle: each time you create a new row, MySQL generates the next
number in the sequence and assigns it to the row. But there are
certain subtleties to know about, as well as differences in how
AUTO_INCREMENT
sequences are
handled for different storage engines. By being aware of these issues,
you can use sequences more effectively and avoid surprises. For
example, if you explicitly set the id
column to a non-NULL
value, one of two things
happens:
If the value is already present in the table, an error
occurs if the column cannot contain duplicates. For the insect
table, the id
column is a PRIMARY
KEY
, so duplicates are not
allowed:
mysql>INSERT INTO insect (id,name,date,origin) VALUES
->(3,'cricket','2006-09-11','basement'),
ERROR 1062 (23000): Duplicate entry '3' for key 1
If the value is not present in the table, MySQL inserts the
row using that value. In addition, if the value is larger than the
current sequence counter, the table’s counter is reset to the
value plus one. The insect
table at this point has sequence values 1 through 8. If you insert
a new row with the id
column
set to 20, that becomes the new maximum value. Subsequent inserts
that automatically generate id
values will begin at 21. The values 9 through 19 become unused,
resulting in a gap in the sequence.
The next recipe looks in more detail at how to define AUTO_INCREMENT
columns and how they
behave.
3.144.91.47