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'), ...