MySQL Aurora AUTO_INCREMENT设置值被忽略问题咨询

核心原因 #

MySQL的AUTO_INCREMENT值本质依赖表中现有最大ID动态维护：

• InnoDB引擎默认仅在内存中缓存自增值，重启、表重建（如OPTIMIZE TABLE）、备份恢复后会重新扫描表内最大ID，覆盖手动设置的值；
• 若删除过B段的大ID记录，后续自增会自动回落到当前最大ID+1，忽略之前的手动配置。

具体解决方法 #

1. 用触发器强制ID生成规则 #

绕过AUTO_INCREMENT的自动维护逻辑，通过触发器确保新插入记录的ID始终从100002开始：

DELIMITER //
CREATE TRIGGER enforce_min_id BEFORE INSERT ON [TABLE_NAME]
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL THEN
    SET NEW.id = GREATEST(100002, (SELECT COALESCE(MAX(id), 0) + 1 FROM [TABLE_NAME]));
  END IF;
END //
DELIMITER ;

• 效果：无论AUTO_INCREMENT值如何变化，未指定ID的插入操作都会自动使用≥100002的ID；
• 可选优化：将ID字段改为普通INT类型（移除AUTO_INCREMENT属性），完全由触发器控制ID生成，彻底避免冲突。

2. 持久化InnoDB自增值（仅针对InnoDB引擎） #

MySQL 8.0.1及以上版本可开启innodb_autoinc_persistent参数，让自增值持久化到磁盘，避免重启后重置：

SET GLOBAL innodb_autoinc_persistent = ON;

• 局限：仅解决重启导致的重置问题，无法处理表重建、备份恢复或删除大ID记录后的自增回落。

3. 禁用会重置自增值的操作 #

禁止执行以下触发自增值重新计算的操作：

• 避免对目标表执行OPTIMIZE TABLE、ALTER TABLE ... ENGINE=InnoDB等重建表的命令；
• 备份恢复时，先执行ALTER TABLE [TABLE_NAME] AUTO_INCREMENT = 100002，再导入数据（不要依赖备份文件中的自增值配置）；
• 不要删除B段的大ID记录，若必须删除，删除后立即重新设置AUTO_INCREMENT值。

4. 自定义序列生成ID（最可靠方案） #

完全脱离MySQL的AUTO_INCREMENT机制，用独立序列表控制ID生成：

1. 创建序列表：

CREATE TABLE id_sequence (
  seq_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 100002;

2. 插入数据时先从序列表获取ID：

INSERT INTO id_sequence VALUES (NULL);
SELECT 896419 INTO @new_id;
INSERT INTO [TABLE_NAME] (id, ...) VALUES (@new_id, ...);

• 效果：完全自主控制ID生成逻辑，不受任何表操作影响；
• 简化调用：可封装为存储过程，减少重复代码。

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