###alter table tt auto_increment=8;
表tt
mysql> select * from tt;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
9 rows in set (0.00 sec)
删除两条数据,再次添加后断层了
mysql> delete from tt where id=8;
Query OK, 1 row affected (0.05 sec)
mysql> delete from tt where id=9;
Query OK, 1 row affected (0.04 sec)
mysql> insert into tt values(null);
Query OK, 1 row affected (0.08 sec)
mysql> select * from tt;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 10 |
+----+
8 rows in set (0.00 sec)
如何重新从8开始呢?这个时候AUTO_INCREMENT已经变为了11,不管你删除多少条,它都将从11开始增加。我们修改一下这个数值。
mysql > show create table tt;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tt auto_increment=8;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tt values(null);
Query OK, 1 row affected (0.08 sec)
mysql> select * from tt;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)