Recipe 11.1. Creating a Sequence Column and Generating Sequence Values
ProblemYou want to include a sequence column in a table. SolutionUse an AUTO_INCREMENT column. DiscussionThis section provides the basic background on how AUTO_INCREMENT columns work, beginning with a short example that demonstrates the sequence-generation mechanism. The illustration centers around a bug-collection scenario: your son (eight-year-old Junior) is assigned the task of collecting insects for a class project at school. For each insect, Junior is to record its name ("ant," "bee," and so forth), and its date and location of collection. You have expounded the benefits of MySQL for record-keeping to Junior since his early days, so upon your arrival home from work that day, he immediately announces the necessity of completing this project and then, looking you straight in the eye, declares that it's clearly a task for which MySQL is well-suited. Who are you to argue? So the two of you get to work. Junior already collected some specimens after school while waiting for you to come home and has recorded the following information in his notebook:
Looking over Junior's notes, you're pleased to see that even at his tender age, he has learned to write dates in ISO format. However, you also notice that he's collected a millipede and a millbug, neither of which actually are insects. You decide to let this pass for the moment; Junior forgot to bring home the written instructions for the project, so at this point it's unclear whether these specimens are acceptable. As you consider how to create a table to store this information, it's apparent that you need at least name, date, and origin columns corresponding to the types of information that Junior is required to record: CREATE TABLE insect ( name VARCHAR(30) NOT NULL, # type of insect date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL # where collected ); However, those columns are not enough to make the table easy to use. Note that the records collected thus far are not unique; both ants were collected at the same time and place. If you put the information into an insect table that has the structure just shown, neither ant row can be referred to individually, because there's nothing to distinguish them from one another. Unique IDs would be helpful to make the rows distinct and to provide values that make each row easy to refer to. An AUTO_INCREMENT column is good for this purpose, so a better insect table has a structure like this: CREATE TABLE insect ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, # type of insect date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL # where collected ); Go ahead and create the insect table using this second definition. In Section 11.2, we'll discuss the specifics of why the id column is declared the way it is. Now that you have an AUTO_INCREMENT column, you want to use it to generate new sequence values. 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. 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','2006-09-10','kitchen'), -> (NULL,'millipede','2006-09-10','driveway'), -> (NULL,'grasshopper','2006-09-10','front yard'), -> (NULL,'stink bug','2006-09-10','front yard'); Second, you can omit the id column from the INSERT statement entirely. In MySQL, you can create new rows without explicitly specifying values for columns that have a default value. MySQL assigns the default value to each missing column automatically, and the default for an AUTO_INCREMENT column happens to be the next sequence number. Thus, you can insert rows 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','2006-09-10','garden'), -> ('ant','2006-09-10','back yard'), -> ('ant','2006-09-10','back yard'), -> ('millbug','2006-09-10','under rock'); Whichever method you use, MySQL determines the next sequence number for each row 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 | 2006-09-10 | kitchen | | 2 | millipede | 2006-09-10 | driveway | | 3 | grasshopper | 2006-09-10 | front yard | | 4 | stink bug | 2006-09-10 | front yard | | 5 | cabbage butterfly | 2006-09-10 | garden | | 6 | ant | 2006-09-10 | back yard | | 7 | ant | 2006-09-10 | back yard | | 8 | millbug | 2006-09-10 | under rock | +----+-------------------+------------+------------+ As Junior collects more specimens, you can add more rows 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 storage engines. 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:
The next recipe looks in more detail at how to define AUTO_INCREMENT columns and how they behave. |
