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'), ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.