Why MySQL Auto‑Increment Primary Keys Can Become Non‑Continuous

The author describes a recent task that required creating two tables, one of which has a unique index on business_id and business_type . The example table definition is shown below:

CREATE TABLE `example_table` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', `business_id` bigint(20) unsigned NOT NULL COMMENT '业务ID', `business_type` tinyint(3) unsigned NOT NULL COMMENT '业务类型', `del` tinyint(1) unsigned DEFAULT '0' COMMENT '删除标识', `creator` varchar(50) NOT NULL COMMENT '创建人PIN', `modify_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_business_id_and_type` (`business_id`,`business_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例表';

During integration testing, many rows with duplicate business_id and business_type were mocked, causing the unique index to reject inserts and generate errors. After correcting the mock data, inserts succeeded, but the author observed that the auto‑increment primary key was no longer sequential.

The article then explains where MySQL stores auto‑increment counters: MyISAM keeps them in the data file, while InnoDB (pre‑5.7) kept them only in memory, recomputing from the maximum existing ID on restart; from MySQL 8.0 onward the counter changes are logged in the redo log.

Four situations that lead to non‑continuous IDs are discussed:

Explicitly inserting a specific ID value, which can break the sequence.

Unique‑index violations that cause an insert to fail after the ID has already been allocated.

Transaction rollbacks: once an ID is allocated, it is not rolled back, leaving a gap.

Batch inserts where the engine pre‑allocates a range of IDs; if the statement ends early or errors occur, the unused IDs become gaps.

Examples of failed and successful inserts are provided:

insert into example_table values (null,111,1,0,'mock',now(),now()); – succeeds, ID increments normally.

insert into example_table values (null,112,1,0,mock,now(),now()); – fails due to a type error, but the ID has already been consumed.

When a duplicate business_id / business_type pair is inserted, the unique index blocks the insert, yet the ID may already be taken, creating a gap (e.g., ID 27 disappears).

The article also covers the innodb_autoinc_lock_mode parameter:

0 – legacy behavior: lock released after the statement finishes.

1 – default: lock released immediately for simple INSERTs, retained for bulk inserts.

2 – lock released immediately for all INSERTs.

Because the auto‑increment lock is not a transaction lock, rolled‑back transactions do not reclaim IDs, which would require additional logging and would degrade performance.

Finally, the author notes that batch INSERT…SELECT statements allocate IDs in exponentially growing chunks (1, then 2, then 4, etc.), which can also produce gaps when the statement does not consume the entire allocated range.

References to the official MySQL documentation are provided for further reading.