MySQL数据迁移如何临时禁用auto increment ID保留旧ID并重置初始值

前提说明 #

以下操作默认适配MySQL生态，其他数据库核心逻辑一致，仅语法存在少量差异。你提到的插入语句本身不需要对自增属性做额外禁用，可直接执行，具体细节如下： #

问题1：插入指定ID到自增列的实现 #

自增列本身支持手动指定值插入，不需要禁用自增属性，你写的插入语句可直接运行，仅需要满足两个前置条件：

• 插入的ID值不与新表new_table中已有的ID重复，不违反主键/唯一键约束
• 插入语句中覆盖所有没有默认值的非空字段，避免因结构不一致触发非空约束报错

如果数据量较大，建议分批插入避免锁表，示例分批插入语句（每次插入1000条）：

-- 按旧表ID范围分批，调整起始ID和步长即可
INSERT INTO new_table(id, new_field1) 
SELECT id, old_field1 FROM old_table 
WHERE id BETWEEN 1 AND 1000;

问题2：重置自增起始值为当前最大ID #

插入完成后执行两步操作即可：

1. 先查询当前表的最大ID值

SELECT MAX(id) FROM new_table;

2. 执行ALTER语句重置自增起始值，将语句中的[你查到的最大ID+1]替换为实际值即可

ALTER TABLE new_table AUTO_INCREMENT = [你查到的最大ID+1];

如果需要单条语句自动计算，可执行如下动态SQL：

SET @max_id = (SELECT IFNULL(MAX(id),0) FROM new_table);
SET @alter_sql = CONCAT('ALTER TABLE new_table AUTO_INCREMENT = ', @max_id + 1);
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意：AUTO_INCREMENT的值不能小于当前表中已存在的最大ID，小于的情况下ALTER语句不会生效。

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