Using an Auto Increment Field | Mastering MySQL 4

An auto increment field is a useful feature that allows the value of a field to be automatically incremented each time a new record is inserted. Only one field in a record can be auto incremented, and this field must be a numeric primary key or a numeric unique index.

Creating an Auto Increment Field

The syntax to create an auto increment field when creating a new table is:

CREATE TABLE tablename(fieldname INT AUTO_INCREMENT, [fieldname2...,]     PRIMARY KEY(fieldname)); Query OK, 0 rows affected (0.00 sec)

To create an auto increment field in an already existing table, use this syntax:

ALTER TABLE tablename MODIFY fieldname columntype AUTO_INCREMENT; Query OK, 0 rows affected (0.00 sec)

The customer table has an ideal candidate for an auto increment field, the id field:

mysql> SHOW COLUMNS FROM customer;  +------------+-------------+------+-----+---------+-------+ | Field      | Type        | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id         | int(11)     |      | PRI | 0       |       | | first_name | varchar(30) | YES  |     | NULL    |       | | surname    | varchar(40) | YES  | MUL | NULL    |       | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

The id field is a numeric field that is already a primary key, and because you've been assigning the id in sequence to date, turning the id into an auto increment field will allow MySQL to automate this process for you. The following code makes the id field auto increment:

mysql> ALTER TABLE customer MODIFY id INT AUTO_INCREMENT; Query OK, 7 rows affected (0.01 sec) Records: 7  Duplicates: 0  Warnings: 0 mysql> SHOW COLUMNS FROM customer; +------------+-------------+------+-----+---------+----------------+ | Field      | Type        | Null | Key | Default | Extra          | +------------+-------------+------+-----+---------+----------------+ | id         | int(11)     |      | PRI | NULL    | auto_increment | | first_name | varchar(30) | YES  |     | NULL    |                | | surname    | varchar(40) | YES  | MUL | NULL    |                | +------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

Inserting Records Containing an Auto Increment Field

Now, if you add a record, you don't need to specify the value of the id; MySQL will automatically add the next highest value:

mysql> SELECT * FROM customer; +----+------------+-------------+ | id | first_name | surname     | +----+------------+-------------+ |  1 | Yvonne     | Clegg       | |  2 | Johnny     | Chaka-Chaka | |  3 | Winston    | Powers      | |  4 | Patricia   | Mankunku    | |  5 | Francois   | Papo        | |  7 | Winnie     | Dlamini     | |  6 | Neil       | Beneke      | +----+------------+-------------+ 7 rows in set (0.00 sec) mysql> INSERT INTO customer(first_name,surname) VALUES('Breyton','Tshbalala'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM customer; +----+------------+-------------+ | id | first_name | surname     | +----+------------+-------------+ |  1 | Yvonne     | Clegg       | |  2 | Johnny     | Chaka-Chaka | |  3 | Winston    | Powers      | |  4 | Patricia   | Mankunku    | |  5 | Francois   | Papo        | |  7 | Winnie     | Dlamini     | |  6 | Neil       | Beneke      | |  8 | Breyton    | Tshbalala   | +----+------------+-------------+ 8 rows in set (0.00 sec)

An important feature is that MySQL's auto increment counter remembers the most recently added number, even if this record is deleted. This ensures that the newly inserted record has a new id value and does not clash with any records related to the old entry:

mysql> DELETE FROM customer WHERE id=8; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO customer(first_name,surname)     VALUES('Breyton','Tshbalala'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM customer; +----+------------+-------------+ | id | first_name | surname     | +----+------------+-------------+ |  1 | Yvonne     | Clegg       | |  2 | Johnny     | Chaka-Chaka | |  3 | Winston    | Powers      | |  4 | Patricia   | Mankunku    | |  5 | Francois   | Papo        | |  7 | Winnie     | Dlamini     | |  6 | Neil       | Beneke      | |  9 | Breyton    | Tshbalala   | +----+------------+-------------+ 8 rows in set (0.01 sec)

The id is now 9. Even though the next highest remaining record is 7, the most recently inserted value was 8.

Returning and Resetting the Auto Increment Value

You can return the most recently inserted auto increment value with the LAST_INSERT_ID() function:

mysql> SELECT LAST_INSERT_ID() FROM customer LIMIT 1; +------------------+ | last_insert_id() | +------------------+ |                9 | +------------------+ 1 row in set (0.00 sec)

This can be useful for updates where you need to create a new auto increment value. For example, the following code finds the most recently inserted auto increment value, and adds 1 to it in order to set a new id value for Breyton Tshbalala:

mysql> UPDATE customer set id=LAST_INSERT_ID()+1 WHERE  first_name='Breyton' AND surname='Tshbalala'; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM customer; +----+------------+-------------+ | id | first_name | surname     | +----+------------+-------------+ |  1 | Yvonne     | Clegg       | |  2 | Johnny     | Chaka-Chaka | |  3 | Winston    | Powers      | |  4 | Patricia   | Mankunku    | |  5 | Francois   | Papo        | |  7 | Winnie     | Dlamini     | |  6 | Neil       | Beneke      | | 10 | Breyton    | Tshbalala   | +----+------------+-------------+ 8 rows in set (0.00 sec)

Warning

LAST_INSERT_ID() has problems when you reset the auto increment counter. See the "Problems with LAST_INSERT_ID()" section later in this chapter.

If you want to reset the auto increment counter to start at a particular value, such as back to 1 after you've deleted all the records, you can use this:

ALTER TABLE tablename AUTO_INCREMENT=auto_inc_value;

Let's create a test to examine the behavior:

mysql> CREATE TABLE ai_test(id INT NOT NULL AUTO_INCREMENT,  f1 VARCHAR(10),PRIMARY KEY (id)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ai_test(f1) VALUES('one'),('two'); Query OK, 2 rows affected (0.00 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM ai_test; +----+------+ | id | f1   | +----+------+ |  1 | one  | |  2 | two  | +----+------+ 2 rows in set (0.00 sec) mysql> DELETE FROM ai_test; Query OK, 2 rows affected (0.00 sec) mysql> INSERT INTO ai_test(f1) VALUES('three'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM ai_test; +----+-------+ | id | f1    | +----+-------+ |  3 | three | +----+-------+ 1 row in set (0.00 sec)

The auto increment counter has maintained its value, even though the table has been emptied. You can use TRUNCATE to clear the table, and this will reset the auto increment counter:

mysql> DELETE FROM ai_test; Query OK, 1 row affected (0.00 sec) mysql> ALTER TABLE ai_test AUTO_INCREMENT=1; Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0

This statement has specifically reset the auto increment counter to 1:

mysql> INSERT INTO ai_test(f1) VALUES('four'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ai_test; +----+------+ | id | f1   | +----+------+ |  1 | four | +----+------+ 1 row in set (0.00 sec) mysql> TRUNCATE ai_test; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ai_test(f1) VALUES('five'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM ai_test; +----+------+ | id | f1   | +----+------+ |  1 | five | +----+------+ 1 row in set (0.00 sec)

By using TRUNCATE rather than DELETE, the auto increment counter is reset.

Warning

Currently, this only works with MyISAM tables. You'll need to manually set the auto increment counter with the other table types.

Setting the auto increment value to something besides 1 is also easy. You can set it when the table is created, for example:

mysql> CREATE TABLE ai_test2(id INT NOT NULL AUTO_INCREMENT,  f1 VARCHAR(5),PRIMARY KEY(id)) AUTO_INCREMENT=50; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ai_test2(f1) VALUES('one'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ai_test2; +----+------+ | id | f1   | +----+------+ | 50 | one  | +----+------+ 1 row in set (0.00 sec)

Or you can set the counter once the table is in existence:

mysql> DELETE FROM ai_test; Query OK, 3 rows affected (0.00 sec) mysql> ALTER TABLE ai_test AUTO_INCREMENT=1000; Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> INSERT INTO ai_test(f1) VALUES('one'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ai_test; +------+------+ | id   | f1   | +------+------+ | 1000 | one  | +------+------+ 1 row in set (0.01 sec)

In most cases you'd use this feature when the table is emptied, but this is not necessary; you can reset the counter even while there are records in the table:

mysql> ALTER TABLE ai_test2 AUTO_INCREMENT=500; Query OK, 1 row affected (0.01 sec) Records: 1  Duplicates: 0  Warnings: 0 mysql> INSERT INTO ai_test2(f1) VALUES('two'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM ai_test2; +-----+------+ | id  | f1   | +-----+------+ |  50 | one  | | 500 | two  | +-----+------+ 2 rows in set (0.00 sec)

Note

Currently this too only works for MyISAM tables. You cannot set the auto increment counter to anything except 1 with an InnoDB table, for example.

The previous examples have inserted records without specifying the id field. If you prefer to use an alternative syntax where the value for the auto increment field is specified, make the value of the auto incremented field either NULL or 0:

mysql> INSERT INTO ai_test VALUES(NULL,'two'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ai_test VALUES(0,'three'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ai_test; +------+-------+ | id   | f1    | +------+-------+ | 1000 | one   | | 1001 | two   | | 1002 | three | +-------+------+ 3 rows in set (0.00 sec)

Going Out of Bounds

Note that the auto increment counter can only be a positive number, even though the field it is attached to is a signed field. If you try to set it to a negative number, you'll run into strange problems:

mysql> ALTER TABLE ai_test2 AUTO_INCREMENT=-500; Query OK, 2 rows affected (0.01 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> INSERT INTO ai_test2(f1) VALUES('three'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ai_test2; +------------+-------+ | id         | f1    | +------------+-------+ |         50 | one   | |        500 | two   | | 2147483647 | three | +------------+-------+ 3 rows in set (0.00 sec)

Because the –500 was outside the positive range of values permissible for an auto increment, MySQL has set it to the maximum allowed for an INT: 2147483647. If you try to add another record, you'll get a duplicate key error because MySQL cannot make an INT value any higher:

mysql> INSERT INTO ai_test2(f1) VALUES('four'); ERROR 1062: Duplicate entry '2147483647' for key 1

Warning

Be careful to ensure that you always have enough space for your records. If you create an auto increment on a SIGNED TINYINT field, once you hit 127 you'll start getting duplicate key errors.

Problems with LAST_INSERT_ID()

The LAST_INSERT_ID() function has a number of features that could cause problems when using it:

The value returned by LAST_INSERT_ID() is not reset to the same value to which you reset the auto increment counter. Instead, it returns to 1.
The number is maintained on a per-connection basis, so if new inserts are added from another connection, the number returned by this function will not be updated.

The following are some examples:

mysql> SELECT * FROM ai_test2; +------------+-------+ | id         | f1    | +------------+-------+ |         50 | one   | |        500 | two   | | 2147483647 | three | +------------+-------+ 3 rows in set (0.00 sec) mysql> ALTER TABLE ai_test2 AUTO_INCREMENT=501; Query OK, 3 rows affected (0.00 sec) Records: 3  Duplicates: 0  Warnings: 0 mysql> UPDATE ai_test2 SET id=LAST_INSERT_ID()+1 WHERE f1='three'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

Here you would expect the id to have a value of 501. However, you'd receive a rude shock! Look at the following:

mysql> SELECT * FROM ai_test2; +-----+-------+ | id  | f1    | +-----+-------+ |  50 | one   | | 500 | two   | |   1 | three | +-----+-------+ 3 rows in set (0.00 sec)

The LAST_INSERT_ID has reset to 1 when you reset the auto increment counter. Even worse follows if you try it again:

mysql> ALTER TABLE ai_test2 AUTO_INCREMENT=501; Query OK, 3 rows affected (0.01 sec) Records: 3  Duplicates: 0  Warnings: 0 mysql> UPDATE ai_test2 SET id=LAST_INSERT_ID()+1 WHERE f1='two'; ERROR 1062: Duplicate entry '1' for key 1

Now you have a duplicate key, and the UPDATE fails. The other error occurs when multiple connections are made. Open two windows to get two connections to the database.

From Window1:

mysql> TRUNCATE ai_test2; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO ai_test2(f1) VALUES('one'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ai_test2; +----+------+ | id | f1   | +----+------+ |  1 | one  | +----+------+ 1 row in set (0.01 sec) mysql> SELECT LAST_INSERT_ID() FROM ai_test2; +------------------+ | last_insert_id() | +------------------+ |                1 | +------------------+ 1 row in set (0.00 sec)

So far, so good. Now go to the second window, and insert another record. From Window2:

mysql> INSERT INTO ai_test2(f1) VALUES('two'); Query OK, 1 row affected (0.00 sec) Window1: mysql> SELECT LAST_INSERT_ID() FROM ai_test2; +------------------+ | last_insert_id() | +------------------+ |                1 | |                1 | +------------------+ 2 rows in set (0.00 sec)

The value returned is still 1, when it should be 2. So now if you try and use the value for an update, you'll get the familiar duplicate key error:

mysql> UPDATE ai_test2 SET id=LAST_INSERT_ID()+1 WHERE f1='one'; ERROR 1062: Duplicate entry '2' for key 1

Multicolumn Indexes and Auto Increment Fields

With MyISAM and BDB tables, you can also make the second index field of a multicolumn index be an auto increment field. This is mainly useful when you're creating groupings of data. For this example, you're going to create a table for staff, where staff can be ranked according to whether they are a manager, an employee, or a contractor and then assigned a specific position on top of that:

mysql> CREATE TABLE staff(rank ENUM('Employee','Manager',  'Contractor') NOT NULL,position VARCHAR(100),  id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(rank,id)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO staff(rank,position) VALUES   ('Employee','Cleaner'),  ('Contractor','Network maintenance'),  ('Manager','Sales manager'); Query OK, 3 rows affected (0.01 sec) mysql> SELECT * FROM staff; +------------+---------------------+----+ | rank       | position            | id | +------------+---------------------+----+ | Employee   | Cleaner             |  1 | | Contractor | Network maintenance |  1 | | Manager    | Sales manager       |  1 | +------------+---------------------+----+ 3 rows in set (0.00 sec)

All three records have the same id value, as the primary key consists of two fields: rank and id. Once you start adding other records to each rank, you'll see the familiar incremental behavior:

mysql> INSERT INTO staff(rank,position) VALUES  ('Employee','Security guard'),  ('Employee', 'Receptionist'),  ('Manager','Head of security'); Query OK, 3 rows affected (0.00 sec) Records: 3  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM staff; +------------+---------------------+----+ | rank       | position            | id | +------------+---------------------+----+ | Employee   | Cleaner             |  1 | | Contractor | Network maintenance |  1 | | Manager    | Sales manager       |  1 | | Employee   | Security guard      |  2 | | Employee   | Receptionist        |  3 | | Manager    | Head of security    |  2 | +------------+---------------------+----+ 6 rows in set (0.01 sec)

In this example, you have an employee 1, 2, and 3; a manager 1 and 2; and a contractor 1. The auto increment counter is maintained correctly for each group.

In this situation you cannot, however, reset the auto increment counter:

mysql> ALTER TABLE staff AUTO_INCREMENT=500; Query OK, 6 rows affected (0.01 sec) Records: 6  Duplicates: 0  Warnings: 0 mysql> INSERT INTO staff(rank,position) VALUES  ('Employee','Stationary administrator'),  ('Manager','Personnel manager'),  ('Contractor','Programmer'); Query OK, 3 rows affected (0.01 sec) Records: 3  Duplicates: 0  Warnings: 0 mysql> SELECT * FROM staff; +------------+--------------------------+----+ | rank       | position                 | id | +------------+--------------------------+----+ | Employee   | Cleaner                  |  1 | | Contractor | Network maintenance      |  1 | | Manager    | Sales manager            |  1 | | Employee   | Security guard           |  2 | | Employee   | Receptionist             |  3 | | Manager    | Head of security         |  2 | | Employee   | Stationary administrator |  4 | | Manager    | Personnel manager        |  3 | | Contractor | Programmer               |  2 | +------------+--------------------------+----+ 9 rows in set (0.00 sec)

The auto increments continue from where they left off, regardless of the ALTER statement.