Gaps in MySQL Auto Increment Values

关于MySQL的AUTO_INCREMENT列出现不连续的原因，本文列出了几个比较常见的场景。MySQL 5.1.42, InnoDB Plugin 1.0.6, innodb_autoinc_lock_mode = 1

 

Scenario 1
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO test VALUES(NULL, '1');
ROLLBACK;
INSERT INTO test VALUES(NULL, '2');
SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  2 | 2    |
+----+------+

Scenario 2
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO test VALUES(NULL, '1');
INSERT INTO test VALUES(3, '3');
SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  3 | 3    |
+----+------+

Scenario 3
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
ALTER TABLE test AUTO_INCREMENT=100;
INSERT INTO test VALUES(NULL, '100'), (99, '99'), (NULL, '101');
INSERT INTO test VALUES(NULL, '103');
SELECT * FROM test;
+-----+------+
| id  | name |
+-----+------+
|  99 | 99   |
| 100 | 100  |
| 101 | 101  |
| 103 | 103  |
+-----+------+

Scenario 4
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
CREATE TABLE t(id INT) ENGINE=InnoDB;
INSERT INTO t VALUES(1), (2), (3), (4), (5);
INSERT INTO test SELECT NULL, id FROM t;
INSERT INTO test VALUES(NULL, '8');
SELECT * FROM test;

+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 5    |
|  8 | 8    |
+----+------+

点评：在这种场景下，InnoDB无法在执行Insert语句之前知道确切的插入记录数，因此会使用表级的AUTO_INC锁（该锁比较特殊，并不像通常的锁那样，在事务结束时释放，而是在该语句执行完毕后释放）。对于AUTO_INCREMENT值，目前InnoDB会采取预分配的策略，即首先分配1，如果用尽则double，如果用尽再double，即1，2，4，8...。需要注意的是，如果innodb_autoinc_lock_mode =2，那么InnoDB不会使用AUTO_INC锁。

Scenario 5
CREATE TABLE test (a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT, UNIQUE INDEX(b)) ENGINE=InnoDB;
INSERT INTO test values(NULL, 1, 1), (NULL, 2, 2);
INSERT INTO test (a,b,c) VALUES (NULL,2,3) ON DUPLICATE KEY UPDATE c=c+1;
INSERT INTO test values(NULL, 4, 4);
SELECT * FROM test;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    2 |    3 |
| 4 |    4 |    4 |
+---+------+------+

点评：在INSERT语句执行之前，InnoDB无法知道数据最终是被插入还是更新，因此可能会导致InnoDB预分配的AUTO_INCREMENT值最终没有被使用。

 

@see also: http://dinglin.iteye.com/blog/1279536