關于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