Did you know this about AUTO_INCREMENT?

The AUTO_INCREMENT feature of MySQL is the easiest way to generate an automatic sequence of incrementing values for a primary key. It’s very easy to use, too:

create table t (
    i integer auto_increment,
    other_field varchar(100),
    primary key(id)

There some features and “features” that you have to keep in mind when using AUTO_INCREMENT, though.

AUTO_INCREMENT never gives a value that is less than one already present in the table.

If you add a row with ID explicitly set to a higher value, the next value produced by AUTO_INCREMENT will be one higher. Yes, this means that there will be gaps. Yes, this means that if for some reason the greatest possible value of the field is inserted, the following inserts will fail:

mysql> insert into t values (2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t values ();
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

How to reset the AUTO_INCREMENT counter.

The values are produced by a counter associated with the table, and the counter only grows. Except that sometimes you may want to reset the counter to some old value.

alter table t auto_increment = 0;

If you find yourself doing this after emptying a testing table with DELETE, try using the TRUNCATE statement next time. It deletes all the data, and resets the auto increment counter.

If records are deleted, AUTO_INCREMENT may jump back on server restart.

In the InnoDB storage engine the auto increment counter is only stored in memory, not on disk. This means that the auto increment counter is initialized to the maximum value the column has when MySQL is restarted. This may be a problem for applications that use one table to archive data that has been deleted from another: suddenly IDs are repeated.

A table can have only one AUTO_INCREMENT field, and it has to be key.

The auto increment column does not have to be the primary key; it’s enough that there’s an index of some kind on it. It does not even have to be a unique index.

In the MyISAM and BDB storage engines you can have the auto increment column as a secondary column in a multi-column index, and then the auto increment starts from 1 for each group:

create temporary table t (
    v varchar(100), 
    i integer(2) auto_increment, 
    primary key(v, i)
) engine=myisam;
insert into t (v) values ('a'),('a'),('b'),('b'),('b');
select * from t;
| v | i |
| a | 1 |
| a | 2 |
| b | 1 |
| b | 2 |
| b | 3 |