Understanding MySQL AUTO_INCREMENT Storage, Modification Mechanism, and Causes of Gaps

1. Introduction

The author noticed that the id column of the user table, defined as AUTO_INCREMENT, does not always produce a continuous sequence in the database.

Table definition:

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '递增id',
  `name` varchar(20),
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user表';

2. AUTO_INCREMENT Storage Explanation

MyISAM : The auto‑increment counter is stored in the data file.

InnoDB : Prior to MySQL 8.0 the counter resides only in memory; after a restart the engine scans the table for MAX(id) and sets the next value to MAX(id)+1. Starting with MySQL 8.0 the counter changes are persisted in the redo log, allowing recovery of the exact value after a restart.

3. AUTO_INCREMENT Modification Mechanism

When inserting a row into a table whose id column is defined with AUTO_INCREMENT:

If the id is omitted, set to 0, or NULL, MySQL uses the current auto‑increment value.

If a specific id is supplied, MySQL stores that value directly.

Depending on the relationship between the supplied value X and the current auto‑increment value Y:

If X < Y, the counter remains unchanged.

If X ≥ Y, the counter is advanced to a new value calculated using auto_increment_offset and auto_increment_increment (both default to 1).

4. When the Counter Is Updated

insert into user values (null, '张三');

The executor passes the row as (0, "张三") to InnoDB.

InnoDB detects that no explicit id was given and fetches the current counter (e.g., 2).

The row is rewritten to (2, "张三").

The table’s auto‑increment value is then set to 3.

The insert completes.

5. Reasons for Non‑Continuous IDs

5.1 Unique‑Key Conflict

If an insert attempts to use an id that already exists, the statement fails, the allocated number is lost, and the next successful insert starts from the next counter value, creating a gap.

5.2 Transaction Rollback

When a transaction that includes inserts into multiple tables fails, all allocated auto‑increment numbers are discarded, leaving gaps in each affected table.

5.3 Batch Insert Operations

MySQL allocates IDs in exponentially growing batches for a single multi‑row INSERT: 1 ID for the first request, 2 for the second, 4 for the third, and so on. Unused IDs from these batches are abandoned, causing gaps (e.g., inserting four rows may allocate IDs 1‑7, but only 1‑4 are stored, and the next insert starts at 8).

6. References

https://time.geekbang.org/column/intro/139