前言

删了几条数据，再插入新数据，id跳了一大截，不连续了。怎么让自增主键从1开始？

一、为什么自增主键会不连续

原因1：删除了数据

删了中间的几条，id就跳了。这是正常的，InnoDB的自增计数器不会回退。

原因2：事务回滚

插入一半回滚了，自增id已经用了，不会退回来。

原因3：批量插入部分失败

插100条，第50条失败了，前49条成功，后面的id就跳了。

原因4：TRUNCATE和DELETE的区别

TRUNCATE会重置自增计数器，DELETE不会。

二、查看当前自增值

SHOW TABLE STATUS LIKE 'users';

看 Auto_increment 字段，就是下一条要插入的id。

三、重置自增主键

方法1：修改自增值

ALTER TABLE users AUTO_INCREMENT = 1;

注意： 如果表里已有数据，会设为当前最大id+1。

方法2：清空表后重置

-- 清空表（重置自增）
TRUNCATE TABLE users;

TRUNCATE会把自增计数器重置为1。

方法3：DELETE后手动重置

-- 删除所有数据
DELETE FROM users;

-- 手动重置自增
ALTER TABLE users AUTO_INCREMENT = 1;

四、怎么让自增主键连续

问题：真的需要连续吗？

其实不需要。

自增主键只要唯一就行，不连续不影响业务。用户看到的id跳了也没关系。

如果非要连续

那只能：

1. 导出数据
2. 清空表
3. 重新导入

# 导出数据
mysqldump -u root -p mydb users > users.sql

# 清空表
mysql -u root -p mydb -e "TRUNCATE TABLE users"

# 重新导入
mysql -u root -p mydb < users.sql

注意： 重新导入后id会重新从1开始，但如果表之间有外键关联，会出问题。

五、InnoDB自增的坑

坑1：重启后自增值会变

MySQL 5.7之前，InnoDB把自增值存在内存里，重启后会从当前max(id)+1重新算。

如果删了最大id的那条，重启后自增值就变小了，可能重复。

MySQL 8.0修复了这个问题，把自增值持久化到redo log了。

坑2：批量插入会跳很多id

一次性插入很多数据，中间失败了，id跳了一大截。

解决： 不要批量插，一条条插。

坑3：自增步长

默认步长是1，可以改：

SHOW VARIABLES LIKE 'auto_increment_increment';

一般主从复制时会设不同步长，防止id冲突。

六、常见问题

问题1：自增主键突然跳到很大

突然从100跳到10000，怎么回事？

可能原因：

1. 批量插入失败了
2. 有人手动改了AUTO_INCREMENT
3. 主从同步导致的

问题2：自增主键重复了

ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

原因： 重启后自增值变小了，和已有数据冲突。

解决： 手动设大一点：

ALTER TABLE users AUTO_INCREMENT = 10000;

问题3：删除数据后id不连续

这是正常现象，不用管。

常见坑

坑1：TRUNCATE和DELETE搞混

DELETE FROM users 不会重置自增。
TRUNCATE TABLE users 会重置自增。

记住： 想重置自增用TRUNCATE。

坑2：外键约束导致TRUNCATE失败

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

解决： 先关外键检查：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

坑3：重置自增后插入报错

设AUTO_INCREMENT=1，但表里已经有id=1的数据了。

解决： 设成max(id)+1：

SELECT MAX(id) FROM users;
-- 假设max(id)=100
ALTER TABLE users AUTO_INCREMENT = 101;

总结

记住几点：

1. 自增主键不连续是正常的，不用纠结
2. TRUNCATE会重置自增，DELETE不会
3. 重置自增用 ALTER TABLE t AUTO_INCREMENT = n
4. MySQL 8.0之前重启会丢自增值，要注意

别追求id连续，没意义，唯一就行。

遇到问题加QQ23979811 协助处理