Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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'), ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata