INSERT后AUTO_INCREMENT修改已插入值的MariaDB异常问题排查

核心原因 #

这是MariaDB和MySQL在AUTO_INCREMENT列处理0值上的行为差异导致的：

• MySQL 8.0中，若先插入0值到主键列，再修改列属性为AUTO_INCREMENT，会保留0值；即便后续设置自增起始值，也不会覆盖已存在的0记录。
• 但MariaDB 10.6里，执行ALTER TABLE ... MODIFY ... AUTO_INCREMENT并指定起始值时，会把列中存在的0值判定为无效的“待填充”值，直接替换成你设置的自增起始值。

复现逻辑验证 #

你做的纯SQL测试刚好命中了这个差异点：

-- 1. 插入含0值的记录
INSERT INTO users (uid, name) VALUES (0, 'anonymous'), (1, 'admin');
-- 2. 添加主键约束
ALTER TABLE users ADD PRIMARY KEY (uid);
-- 3. 修改列为自增并指定起始值
ALTER TABLE users MODIFY uid int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

此时MariaDB会直接把uid=0的记录更新为17，而MySQL则会保留0值，仅将自增起始值设为17。

修复与预防方案 #

1. 调整dump文件执行顺序
   把ALTER TABLE ... AUTO_INCREMENT=xxx语句移到所有INSERT语句之前：先定义好表的自增属性和起始值，再插入数据。这样插入0值时，MariaDB不会触发后续的覆盖逻辑。

2. 临时修改sql_mode
   在恢复数据库前执行以下语句：

   SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
   

   这个参数会让MariaDB在插入0到AUTO_INCREMENT列时，保留0值而非自动生成自增ID。恢复完成后再改回原有sql_mode即可。

3. 事后修复已出问题的表
   如果已经恢复出错，直接手动修正：

   UPDATE users SET uid=0 WHERE name='anonymous'; -- 定位匿名用户记录并改回uid=0
   ALTER TABLE users AUTO_INCREMENT=(SELECT MAX(uid)+1 FROM users); -- 重置自增起始值为最大现有uid+1
   

其他表的风险排查 #

所有存在先插入0值到自增列、再执行ALTER TABLE设置AUTO_INCREMENT起始值逻辑的表，都会出现同样问题。建议：

• 批量检查dump文件中的ALTER TABLE ... AUTO_INCREMENT语句，确认它们的执行顺序是否在INSERT之前；
• 统一在恢复前设置NO_AUTO_VALUE_ON_ZERO，避免遗漏。

内容的提问来源于stack exchange，提问作者Francesco Marchetti-Stasi