前言

删了几条数据，再插入新数据，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 协助处理

前言

做后端开发的同学，建表的时候都喜欢用自增主键，觉得简单方便。但是很多同学不知道，自增主键是有最大值的，用完了就不能再插入数据了。

很多新手同学建表的时候，主键直接用INT，也不知道INT的最大值是多少。结果项目跑了几年，数据量涨上来了，自增主键快用完了，才发现要出问题了。

这篇文章就把MySQL自增主键的知识点讲清楚：不同整型的最大值是多少？用完了会怎么样？怎么避免？已经用完了怎么办？看完之后你就彻底搞懂了。

一、不同整型的最大值

MySQL的整型类型有好几种，每种的最大值都不一样。我们常用的有这几个：

重点看INT和BIGINT

我们建表的时候，主键一般用INT或者BIGINT。

INT的最大值

INT有符号的最大值是 21亿多（2147483647），无符号的是 42亿多（4294967295）。

很多同学觉得21亿很多，肯定用不完。但是对于一些业务量大的网站，比如电商、社交，用户量很大，一张表可能就有几亿条数据，INT真的不够用。

BIGINT的最大值

BIGINT有符号的最大值是 922亿亿（9223372036854775807），这个数太大了，基本上一辈子都用不完。

所以建议：主键直接用BIGINT，不要用INT，省得以后不够用了还要改。

二、自增主键用完了会怎么样

如果自增主键用到了最大值，再插入数据会怎么样？

MySQL会报错：

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

或者：

ERROR: Duplicate entry '2147483647' for key 'PRIMARY'

意思就是：自增主键已经到最大值了，不能再插入新数据了。

这时候业务就受影响了，不能插入新数据了，必须赶紧处理。

三、怎么避免自增主键用完

最好的方法就是：建表的时候主键直接用BIGINT，不要用INT。

BIGINT的最大值太大了，基本上不可能用完。这样就不用以后担心主键不够用的问题了。

很多同学觉得BIGINT占空间大，其实BIGINT是8字节，INT是4字节，一张表也就多了几个字节，根本不算什么。但是换来的是永远不用担心主键不够用，这太值了。

怎么查看当前自增值到多少了

想知道当前自增主键已经用到多少了，可以用这个命令：

SHOW TABLE STATUS LIKE '表名';

执行完之后，看Auto_increment那列，就是当前自增值。

然后你对比一下这个类型的最大值，看看还有多少余量。如果已经用了一半了，那就得注意了。

四、已经用完了怎么办

如果你的主键已经快用完了，或者已经用完了，怎么办？

方法1：把INT改成BIGINT

这是最直接的方法。把主键的类型从INT改成BIGINT，这样最大值就变成922亿亿了，够用很久了。

执行的SQL：

ALTER TABLE 表名 MODIFY id BIGINT AUTO_INCREMENT;

注意：这个操作会重建表，如果表很大的话，会花很长时间，而且会锁表。最好在业务低峰期执行，或者用pt-online-schema-change这种工具在线改。

方法2：分库分表

如果你的表已经很大了，改主键类型也不能根本解决问题，那就分库分表，把数据分散到多个表或者多个数据库里。

不过分库分表成本很高，一般都是到万不得已才做。

五、自增主键的其他坑

除了用完的问题，自增主键还有一些其他的坑：

坑1：删除数据之后自增值不回收

很多同学以为删除了数据，自增值会重新开始。其实不是，自增值只会涨，不会降。就算你把表清空了，自增值还是接着之前的继续涨。

比如你插入了100条数据，id到了100，然后你把这100条都删了，再插入一条新数据，id还是101，不是1。

坑2：自增值不连续

因为有事务回滚的情况，自增值可能会不连续。比如一个事务插入了一条数据，id是100，然后事务回滚了，那100这个id就浪费了，下一条数据的id是101。

所以不要指望自增主键是连续的，它只是唯一的，不是连续的。

坑3：用了INT UNSIGNED，觉得够大了

很多同学觉得用了INT UNSIGNED，最大值是42亿，肯定够了。其实对于一些大业务，42亿也不够。还是用BIGINT最保险。

常见坑

坑1：建表的时候用INT主键，后来不够用了

很多同学建表的时候图简单，直接用INT主键，结果项目跑了几年，数据量涨上来了，INT快用完了，才慌慌张张要改成BIGINT。这时候表已经很大了，改起来很麻烦。一开始就用BIGINT，省得以后麻烦。

坑2：不知道自增主键的最大值

很多新手同学根本不知道INT的最大值是多少，觉得反正自增，肯定用不完。其实21亿听起来很多，但是对于大业务来说，真的可能用完。

坑3：改主键类型的时候锁表了

很多同学直接在线上执行ALTER TABLE改主键类型，结果表很大，改了几个小时，一直锁表，业务直接卡住了。改之前一定要评估表大小，最好在业务低峰期执行，或者用在线改表工具。

坑4：以为删除数据之后自增值会降

很多同学删了很多数据，以为自增值会降下来，就不用改类型了。其实不是，自增值只会涨，不会降。删了数据之后，自增值还是原来的，不会变。

坑5：用UUID做主键

很多同学觉得自增主键不够用，就想用UUID做主键。其实UUID做主键问题更多：占空间大、无序导致索引分裂、查询慢。真的不够用的话，改BIGINT就行，没必要用UUID。

总结

MySQL自增主键的核心知识点：

1. 不同整型的最大值：INT是21亿（有符号）/42亿（无符号），BIGINT是922亿亿
2. 主键直接用BIGINT：不要用INT，省得以后不够用了还要改
3. 自增主键用完会报错：不能再插入新数据了
4. 怎么查看当前自增值：SHOW TABLE STATUS，看Auto_increment列
5. 用完了怎么办：把INT改成BIGINT，或者分库分表
6. 自增主键的坑：删除数据自增值不回收、自增值不连续

记住：建表的时候主键直接用BIGINT，这是最稳妥的做法。不要一开始图省事用INT，以后改起来更麻烦。

遇到问题加QQ23979811 协助处理