MariaDB中AUTO_INCREMENT主键无法按序生成问题求助

我之前在MariaDB 10.1版本里碰到过一模一样的固定步长跳号问题，比如你说的6、9、12这种序列，大概率是自增步长和偏移量参数被误修改导致的，下面给你一步步排查和解决的方法：

1. 先确认问题根源 #

首先执行这条SQL，查看当前的自增相关参数：

SHOW VARIABLES LIKE 'auto_increment%';

如果返回结果里，auto_increment_increment不是1，或者auto_increment_offset不是1，那就是问题所在。比如你看到auto_increment_increment=3、auto_increment_offset=6，就会出现每次自增+3、从6开始的序列。

这两个参数通常是配置主从复制时用来避免节点间自增冲突的，但单节点环境下被误改就会出现你遇到的情况。

2. 临时修复（重启数据库后失效） #

执行以下SQL修改全局参数，注意修改后需要重新连接数据库才能生效：

SET GLOBAL auto_increment_increment = 1;
SET GLOBAL auto_increment_offset = 1;

重新连接后，你可以再创建测试表插入数据，应该就能看到id从1开始连续生成了。

3. 永久修复（重启后依然生效） #

找到你的MariaDB配置文件（通常是/etc/my.cnf、/etc/mysql/my.cnf或者Windows下的my.ini），在[mysqld]段落里添加或修改以下两行：

auto_increment_increment = 1
auto_increment_offset = 1

保存配置文件后，重启MariaDB服务，这样参数就会永久生效。

其他可能的排查方向 #

如果上面的方法没解决，再检查这两个点：

• 有没有事务回滚的操作：如果之前有插入数据后回滚，InnoDB的自增值不会回滚，会导致单次跳号，但不会是固定步长跳。
• 检查innodb_autoinc_lock_mode参数：执行SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';，如果值是2（interleaved），在并发插入场景下可能会跳号，但单线程插入一般不会出现固定步长的情况。可以临时设置为1试试：SET GLOBAL innodb_autoinc_lock_mode = 1;

最后，如果你之前删除过同名表，重新创建时即使指定了AUTO_INCREMENT=1，也可能继承之前的自增计数器，这时候可以用ALTER TABLE mytable AUTO_INCREMENT=1;强制重置起始值。

内容的提问来源于stack exchange，提问作者Ven