mysql控制主键每次自增3_mysql自增主键在大量删除后如何重新设置避免断层

###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)