MySQL Cookbook

Generating Sequence Values

Problem

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

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.

Discussion

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.^[49] 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','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'), ...