// create table
mysql> create table t(a int unsigned primary key,b char(1) not null)engine=innodb charset=utf8;
Query OK, 0 rows affected (0.15 sec)
// insert data
mysql> insert into t(a,b) values(1,'a'),(2,'b'),(4,'c'),(5,'c');
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
// select all
mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 4 | c |
| 5 | c |
+---+---+
4 rows in set (0.00 sec)
// 在有1 存在的时候,
mysql> select min(a)+1 as missing from t as A where not exists (select * from t as B where A.a+1 = B.a);
+---------+
| missing |
+---------+
| 3 |
+---------+
1 row in set (0.04 sec)
// delete 1,2,
mysql> delete from t where a in(1,2);
Query OK, 2 rows affected (0.11 sec)
// repeate select
mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 4 | c |
| 5 | c |
+---+---+
2 rows in set (0.00 sec)
mysql> select min(a)+1 as missing from t as A where not exists(select * from t as B where A.a+1 = B.a);
+---------+
| missing |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
// 在这种情况下,就出现了问,应该从1 开始, 需要考虑特殊的情况,
// 对应的解决方案