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