Understanding Auto‑Increment Gaps with REPLACE INTO and INSERT … ON DUPLICATE KEY UPDATE in MySQL

In a typical data‑sync service, a table stores user‑specific information with an INT UNSIGNED AUTO_INCREMENT primary key. Over time the DBA noticed the auto‑increment value approaching 1.6 billion, even though the actual row count was only about 11 million, indicating large gaps.

The root cause is the use of statements that affect the auto‑increment counter without inserting a visible row. Two statements are discussed:

REPLACE INTO

When a REPLACE INTO finds an existing row with the same unique key, MySQL deletes the old row and inserts a new one, causing the auto‑increment value to increase even though the logical operation is an update.

Example table definition:

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID，自增',
  `uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户uid',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户昵称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试replace into';

Sample operations:

INSERT INTO t1 VALUES (NULL, 100, "test1"), (NULL, 101, "test2");
REPLACE INTO t1 VALUES (NULL, 100, "test3");

After the REPLACE , the row with uid=100 is deleted and a new row is inserted, so the auto‑increment counter jumps.

INSERT … ON DUPLICATE KEY UPDATE

This statement behaves similarly but does not change the primary key value of the existing row. However, MySQL still increments the auto‑increment counter because the statement is treated as a mixed‑mode insert.

INSERT INTO t1 VALUES (NULL, 100, "test4")
ON DUPLICATE KEY UPDATE name = VALUES(name);

The execution reports “2 rows affected” – one for the attempted insert and one for the update – and the auto‑increment value increases even though no new row was added.

innodb_autoinc_lock_mode

The behavior is controlled by the innodb_autoinc_lock_mode system variable, which can be 0, 1, or 2:

Mode 0: Table‑level lock for every insert, guaranteeing sequential IDs but reducing concurrency.

Mode 1 (default): “consecutive” mode – simple inserts calculate the number of rows beforehand and increment the counter without a lock.

Mode 2: “interleaved” mode – no lock is taken, which can cause gaps and replication inconsistencies under statement‑based binlog.

Checking the current mode:

SELECT @@innodb_autoinc_lock_mode;

In the example, the default mode 1 causes the auto‑increment counter to increase for both REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements, even when no new row is stored.

Mixed‑mode Inserts

When an INSERT statement mixes explicit primary‑key values with NULL (e.g., INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d') ), InnoDB analyses the statement and may allocate several auto‑increment IDs, some of which remain unused, leading to further gaps.

Illustrative sequence:

TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL,100,"test1"), (NULL,101,"test2"), (NULL,102,"test2"), (NULL,103,"test2"), (NULL,104,"test2");
-- next auto‑increment is 7
DELETE FROM t1 WHERE id IN (2,3,4);
-- rows left: 1,5,6; auto‑increment stays 7
INSERT INTO t1 VALUES (2,106,"test1"), (NULL,107,"test2"), (3,108,"test2");
-- next auto‑increment becomes 10

Thus, even deletions do not reset the counter; allocated IDs are consumed.

Solutions

Two practical approaches are suggested:

Change business logic: Replace the INSERT … ON DUPLICATE KEY UPDATE with a separate SELECT ‑then‑ UPDATE flow, ensuring the primary key is not unintentionally incremented.

Remove the auto‑increment primary key: Use a natural unique key (e.g., uid ) as the primary key, eliminating the need for an auto‑increment column and avoiding the gap issue altogether.

Both options have trade‑offs: the first adds extra round‑trips, while the second may affect insert performance but simplifies ID management.

Conclusion

Understanding how REPLACE INTO and INSERT … ON DUPLICATE KEY UPDATE interact with innodb_autoinc_lock_mode explains why MySQL can report “2 rows affected” and why auto‑increment values may jump. Adjusting the lock mode or redesigning the schema/logic can prevent uncontrolled ID growth.