+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
delete from tt where id=8;
Query OK, 1 row affected (0.01 sec)
delete from tt where id=9;
Query OK, 1 row affected (0.01 sec)
>insert into tt values(null);
Query OK, 1 row affected (0.01 sec)
select * from tt;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 10 |
+----+
看到新插入的记录是在10开始的不是在8开始,如果想要在8开始,需要在删除数据后,重置下自动增长的位置
delete from tt where id=10;
Query OK, 1 row affected (0.01 sec)
alter table tt auto_increment=8
-> ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
insert into tt values(null);
Query OK, 1 row affected (0.00 sec)
select * from tt;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.01 sec)
看到在重新设置后,自增主键是在8开始了,这样可以避免出现id断层