Database Reference
In-Depth Information
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
| 7 | ant | 2014-09-10 | back yard |
| 8 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
As Junior collects more specimens, 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 different storage engines handle
AUTO_INCREMENT
sequences. Awareness of these
issues enables you to 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
, which
prohibits duplicates:
mysql>
INSERT INTO insect (id,name,date,origin) VALUES
->
(3,'cricket','2014-09-11','basement');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
• 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.
13.2. Choosing the Definition for a Sequence Column
Problem
You want to know more about how to define a sequence column.
Solution
Use the guidelines given here.
