Understanding MySQL AUTO_INCREMENT Sequences and Their Behavior

MySQL provides the AUTO_INCREMENT attribute to generate unique numeric identifiers for rows, which is essential for scenarios such as product codes or transaction numbers. Only one column per table can have AUTO_INCREMENT, it must be an integer type, NOT NULL, and indexed (typically as a primary key or unique index).

The behavior of AUTO_INCREMENT can vary between storage engines; the examples below focus on the InnoDB engine.

mysql> CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );

After inserting six rows:

mysql> INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich'); mysql> SELECT * FROM animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+

When a row is deleted, the gap remains and new inserts continue with the next highest value rather than reusing the missing numbers:

mysql> DELETE FROM animals WHERE id = 2; mysql> SELECT * FROM animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 3 | penguin | | 5 | whale | +----+---------+ mysql> INSERT INTO animals (name) VALUES ('Kangaroo'); mysql> SELECT * FROM animals; +----+----------+ | id | name | +----+----------+ | 1 | dog | | 3 | penguin | | 5 | whale | | 7 | Horse | | 8 | Kangaroo | +----+----------+

MySQL does not simply use MAX(id) to compute the next value because concurrent transactions could insert rows simultaneously, leading to conflicts. Instead, it relies on LAST_INSERT_ID() to obtain the most recent AUTO_INCREMENT value safely.

System variables controlling the sequence are:

+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | +--------------------------+-------+ | auto_increment_offset | 1 | +--------------------------+-------+

Example of retrieving the last inserted id:

mysql> SELECT LAST_INSERT_ID(); +------------------+ | last_insert_id() | +------------------+ | 9 | +------------------+

Parallel transaction behavior is illustrated with two sessions that disable autocommit. Session 1 inserts a row and later rolls back, while Session 2 inserts two rows and commits, resulting in a non‑continuous sequence that avoids lock contention:

-- Session 1 mysql> SET autocommit=0; mysql> INSERT INTO animals (name) VALUES ('LISAT1'); -- Session 2 mysql> SET autocommit=0; mysql> INSERT INTO animals (name) VALUES ('LISAT2'); mysql> INSERT INTO animals (name) VALUES ('LISAT3'); mysql> COMMIT; -- After rollback of Session 1 and commit of Session 2 mysql> SELECT * FROM animals; +----+----------+ | id | name | +----+----------+ | 1 | dog | | 3 | penguin | | 5 | whale | | 7 | Horse | | 8 | Kangaroo | | 9 | ABC | |10 | DEFG | |12 | LISAT2 | |13 | LISAT3 | +----+----------+

Key practical notes for using AUTO_INCREMENT effectively:

It generates only positive integers; negative values are not supported.

Defining the column as UNSIGNED doubles the usable range and can start from 0 (the first generated id will still be 1).

Using TRUNCATE TABLE resets the AUTO_INCREMENT counter back to 1.