Generating Sequence Values

11.3.1 Problem

Now that you have an AUTO_INCREMENT column, you want to use it to generate a new sequence value.

11.3.2 Solution

Insert NULL into the column, or just omit it from your INSERT statement. Either way, MySQL will create a new sequence number for you.

11.3.3 Discussion

One of the useful properties of an AUTO_INCREMENT column is that you don't have to assign its values yourselfMySQL 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.[1] The following statement inserts the first four of Junior's specimens into the insect table this way:

[1] Setting an AUTO_INCREMENT column to zero currently has the same effect as setting it to NULL. But that is not guaranteed to be true in the future, so it's better to use NULL.

mysql> INSERT INTO insect (id,name,date,origin) VALUES
 -> (NULL,'housefly','2001-09-10','kitchen'),
 -> (NULL,'millipede','2001-09-10','driveway'),
 -> (NULL,'grasshopper','2001-09-10','front yard'),
 -> (NULL,'stink bug','2001-09-10','front yard');

Second, you can omit the id column from the INSERT statement entirely. In MySQL, you can create new records without explicitly specifying values for every column. MySQL assigns default values to the missing columns automatically, and the default for an AUTO_INCREMENT column happens to be the next sequence number. Thus, you can insert records 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','2001-09-10','garden'),
 -> ('ant','2001-09-10','back yard'),
 -> ('ant','2001-09-10','back yard'),
 -> ('millbug','2001-09-10','under rock');

Whichever method you use, MySQL determines the next sequence number for each record 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 | 2001-09-10 | kitchen |
| 2 | millipede | 2001-09-10 | driveway |
| 3 | grasshopper | 2001-09-10 | front yard |
| 4 | stink bug | 2001-09-10 | front yard |
| 5 | cabbage butterfly | 2001-09-10 | garden |
| 6 | ant | 2001-09-10 | back yard |
| 7 | ant | 2001-09-10 | back yard |
| 8 | millbug | 2001-09-10 | under rock |
+----+-------------------+------------+------------+

As Junior collects more specimens, you can add more records 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 table types. 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:

  mysql> INSERT INTO insect (id,name,date,origin) VALUES
   -> (3,'cricket','2001-09-11','basement');
  ERROR 1062 at line 1: Duplicate entry '3' for key 1

  This happens because when you create an AUTO_INCREMENT column, you must declare it to be either a PRIMARY KEY or a UNIQUE index. As a result, it cannot contain duplicate values.

• If the value is not present in the table, MySQL inserts the record using that value. In addition, if it's larger than the current sequence counter, the table's counter is reset to the new 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.

Now let's look in more detail at how to define AUTO_INCREMENT columns and how they behave.