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.