5.7. Using the AUTO_INCREMENT Column Attribute The AUTO_INCREMENT attribute may be added to an integer column definition to create a column for which MySQL automatically generates a new sequence number each time you create a new row. There may be only one AUTO_INCREMENT column per table, the column must be indexed, and the column must be defined as NOT NULL. The AUTO_INCREMENT attribute is used in conjunction with an index (usually a primary key) and provides a mechanism whereby each value is a unique identifier that can be used to refer unambiguously to the row in which it occurs. MySQL also provides a LAST_INSERT_ID() function that returns the most recently generated AUTO_INCREMENT value. The value returned by LAST_INSERT_ID() is specific to the client that generates the AUTO_INCREMENT value. It cannot be affected by other clients. The LAST_INSERT_ID() function is useful for determining the identifier when you need to look up the record just created, or when you need to know the identifier to create related records in other tables. The following scenario illustrates how you can set up and use an AUTO_INCREMENT column. Assume that you're organizing a conference and need to keep track of attendees and the seminars for which each attendee registers. (When someone submits a registration form for the conference, the form must indicate which of the available seminars the person wants to attend.) Your task is to record seminar registrations and associate them with the appropriate attendee. Unique ID numbers provide a way to keep track of attendees and an AUTO_INCREMENT column makes the implementation for the task relatively easy: 1. | Set up an attendee table to record information about each person attending the conference. The table shown here includes columns for ID number, name, and job title:
mysql> CREATE TABLE attendee -> ( -> att_id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> att_name CHAR(100), -> att_title CHAR(40), -> PRIMARY KEY (att_id) -> );
The att_id column is created as a PRIMARY KEY because it must contain unique values, and as an AUTO_INCREMENT column because it's necessary for MySQL to generate values for the column automatically.
| 2. | Set up a seminar table to record the seminars for which each attendee registers. Assume that there are four seminars: Database Design, Query Optimization, SQL Standards, and Using Replication. There are various ways in which these seminars can be represented; an ENUM column is one that works well because the seminar titles form a small fixed list of values. The table also must record the ID of each attendee taking part in the seminar. The table can be created with this statement:
mysql> CREATE TABLE seminar -> ( -> att_id INT UNSIGNED NOT NULL, -> sem_title ENUM('Database Design','Query Optimization', -> 'SQL Standards','Using Replication'), -> INDEX (att_id) -> );
Note both the differences and similarities of the att_id column declarations in the two tables. In attendee, att_id is an AUTO_INCREMENT column and is indexed as a PRIMARY KEY to ensure that each value in the column is unique. In seminar, att_id is indexed for faster lookups, but it isn't indexed as a PRIMARY KEY. (There might be multiple records for a given attendee and a PRIMARY KEY does not allow duplicates.) Nor is the column declared in the seminar table with the AUTO_INCREMENT attribute because ID values should be tied to existing IDs in the attendee table, not generated automatically. Aside from these differences, the column is declared using the same data type (INT) and attributes (UNSIGNED, NOT NULL) as the att_id column in the attendee table.
| 3. | Each time a conference registration form is received, enter the attendee information into the attendee table. For example:
mysql> INSERT INTO attendee (att_name,att_title) -> VALUES('Charles Loviness','IT Manager');
Note that the INSERT statement doesn't include a value for the att_id column. Because att_id is an AUTO_INCREMENT column, MySQL generates the next sequence number (beginning with 1) and sets the att_id column in the new row to that value. You can use the new att_id value to look up the record just inserted, but how do you know what value to use? The answer is that you don't need to know the exact value. Instead, you can get the ID by invoking the LAST_INSERT_ID() function, which returns the most recent AUTO_INCREMENT value generated during your current connection with the server. Thus, the record for Charles Loviness can be retrieved like this:
mysql> SELECT * FROM attendee WHERE att_id = LAST_INSERT_ID(); +--------+------------------+------------+ | att_id | att_name | att_title | +--------+------------------+------------+ | 3 | Charles Loviness | IT Manager | +--------+------------------+------------+
This output indicates that the Loviness form was the third one entered.
| 4. | Next, enter new records into the seminar table for each seminar marked on the entry form. The att_id value in each of these records must match the att_id value in the newly created attendee record. Here again, the LAST_INSERT_ID() value can be used. If Loviness will participate in Database Design, SQL Standards, and Using Replication, create records for those seminars as follows:
mysql> INSERT INTO seminar (att_id,sem_title) -> VALUES(LAST_INSERT_ID(),'Database Design'); mysql> INSERT INTO seminar (att_id,sem_title) -> VALUES(LAST_INSERT_ID(),'SQL Standards'); mysql> INSERT INTO seminar (att_id,sem_title) -> VALUES(LAST_INSERT_ID(),'Using Replication');
To see what the new seminar records look like, use the LAST_INSERT_ID() value to retrieve them:
mysql> SELECT * FROM seminar WHERE att_id = LAST_INSERT_ID(); +--------+-------------------+ | att_id | sem_title | +--------+-------------------+ | 3 | Database Design | | 3 | SQL Standards | | 3 | Using Replication | +--------+-------------------+
| 5. | When you receive the next registration form, repeat the process just described. For every new attendee record, the value of LAST_INSERT_ID() will change to reflect the new value in the att_id column.
|
The preceding description shows how to use an AUTO_INCREMENT column: how to declare the column, how to generate new ID values when inserting new records, and how to use the ID values to tie together related tables. However, the description glosses over some of the details. These are presented in the following discussion, beginning with declaration syntax and then providing further information about how AUTO_INCREMENT columns work. The att_id-related declarations in the attendee table look like this: att_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (att_id)
These declarations involve the following factors, which you should consider when creating an AUTO_INCREMENT column: The column must have an integer data type. Choose the specific type based on the number of values the column must be able to hold. For the largest range, use BIGINT. However, BIGINT requires 8 bytes per value. If you want to use less storage, INT requires only 4 bytes per value and provides a range that's adequate for many applications. You can use integer types smaller than INT as well, but it's a common error to choose one that's too small. For example, TINYINT has a range that allows very few unique numbers, so you'll almost certainly run into problems using it as an AUTO_INCREMENT column for identification purposes. An AUTO_INCREMENT sequence contains only positive values. For this reason, it's best to declare the column to be UNSIGNED. Syntactically, it isn't strictly required that you declare the column this way, but doing so doubles the range of the sequence because an UNSIGNED integer column has a larger maximum value. Defining the column as UNSIGNED also serves as a reminder that you should never store negative values in an AUTO_INCREMENT column. The most common way to use an AUTO_INCREMENT column is as a primary key, which ensures unique values and prevents duplicates. The column should thus be defined to contain unique values, either as a PRIMARY KEY or a UNIQUE index. (MySQL allows you to declare an AUTO_INCREMENT column with a non-unique index, but this is less common.) An AUTO_INCREMENT column must be NOT NULL.
After setting up an AUTO_INCREMENT column, use it as follows: Inserting NULL into an AUTO_INCREMENT column causes MySQL to generate the next sequence value and store it in the column. Omitting the AUTO_INCREMENT column from an INSERT statement is the same as inserting NULL explicitly. In other words, an INSERT statement that does not provide an explicit value for an AUTO_INCREMENT column also generates the next sequence value for the column. For example, if id is an AUTO_INCREMENT column in the table t, the following two statements are equivalent: INSERT INTO t (id,name) VALUES(NULL,'Hans'); INSERT INTO t (name) VALUES('Hans');
A positive value can be inserted explicitly into an AUTO_INCREMENT column if the value isn't already present in the column. If this value is larger than the current sequence counter, subsequent automatically generated values begin with the value plus one: mysql> CREATE TABLE t (id INT AUTO_INCREMENT, PRIMARY KEY (id)); mysql> INSERT INTO t (id) VALUES(NULL),(NULL),(17),(NULL),(NULL); mysql> SELECT id FROM t; +----+ | id | +----+ | 1 | | 2 | | 17 | | 18 | | 19 | +----+
After an AUTO_INCREMENT value has been generated, the LAST_INSERT_ID() function returns the generated value. LAST_INSERT_ID() will continue to return the same value, regardless of the number of times it's invoked, until another AUTO_INCREMENT value is generated. The value returned by LAST_INSERT_ID() is specific to the client that generates the AUTO_INCREMENT value. That is, it's connection-specific, so the LAST_INSERT_ID() value is always correct for the current connection, even if other clients also generate AUTO_INCREMENT values of their own. One client cannot change the value that LAST_INSERT_ID() returns to another, nor can one client use LAST_INSERT_ID() to determine the AUTO_INCREMENT value generated by another. If you update an AUTO_INCREMENT column to NULL or 0 in an UPDATE statement, the column is set to 0. By default, inserting 0 into an AUTO_INCREMENT column has the same effect as inserting NULL: The next sequence value is generated. However, if the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, inserting 0 causes 0 to be stored instead of the next sequence number. AUTO_INCREMENT behavior is the same for REPLACE as it is for INSERT. Any existing record is deleted, and then the new record is inserted. Consequently, replacing an AUTO_INCREMENT column with NULL causes it to be set to the next sequence value. This also occurs if you replace the column with 0 unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. When you reach the upper limit of an AUTO_INCREMENT column, an attempt to generate the next sequence value results in a duplicate-key error. This is a manifestation of MySQL's general out-of-range value clipping behavior. For example, assume that you have a TINYINT UNSIGNED column as an AUTO_INCREMENT column and that it currently contains 254 as the maximum sequence value. The upper limit for this data type is 255, so the next insert generates a sequence value of 255 and successfully stores it in the new record. However, the insert after that fails because MySQL generates the next sequence value, which is 256. Because 256 is higher than the column's upper limit of 255, MySQL clips 256 down to 255 and attempts to insert that value. But because 255 is already present in the table, a duplicate-key error occurs. If you delete rows containing values at the high end of a sequence, those values are not reused for MyISAM or InnoDB tables when you insert new records. For example, if an AUTO_INCREMENT column contains the values from 1 to 10 and you delete the record containing 10, the next sequence value is 11, not 10.
The MyISAM storage engine supports composite indexes that include an AUTO_INCREMENT column. This allows creation of independent sequences within a single table. Consider the following table definition: CREATE TABLE multisequence ( name CHAR(10) NOT NULL, name_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (name, name_id) );
Inserting name values into the multisequence table generates separate sequences for each distinct name: mysql> INSERT INTO multisequence (name) -> VALUES('Petr'),('Ilya'),('Ilya'),('Yuri'),('Ilya'),('Petr'); mysql> SELECT * FROM multisequence ORDER BY name, name_id; +------+---------+ | name | name_id | +------+---------+ | Ilya | 1 | | Ilya | 2 | | Ilya | 3 | | Petr | 1 | | Petr | 2 | | Yuri | 1 | +------+---------+
Note that for this kind of AUTO_INCREMENT column, values deleted from the high end of any sequence are reused. This differs from MyISAM behavior for single-column AUTO_INCREMENT sequences. |
