如何在MySQL中安全地给已存在的表添加自增主键？

作者：袖梨 2026-07-16

直接添加自增主键会失败，因为MySQL要求自增列必须是主键或唯一键且整张表不能已有主键，而现有表通常已存在主键、含NULL值或重复数据，违反约束。

不能直接 ALTER TABLE ... ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY —— 这会报错、锁表、甚至阻塞线上读写。根本原因是 MySQL 要求自增列必须是主键（或唯一键），且整张表不能已有主键；而现有表几乎总存在主键、NULL 值或重复数据，违反约束。

为什么直接加自增主键会失败？

常见错误现象包括：ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key，或 ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL。

表已存在主键（哪怕只是业务组合主键）→ 直接拒绝添加新主键
新列默认为 NULL → 不满足 NOT NULL 约束，语句在语法校验阶段就被拦截
即使绕过加列，后续用变量赋值（如 @id := @id + 1）填充序号，在多线程或未显式 ORDER BY 时会产生非确定顺序，尤其在 MySQL 5.7 及更早版本中极易出错

MySQL 8.0+ 推荐：用 `ROW_NUMBER()` 分步生成唯一序号

窗口函数天然支持稳定排序与全局唯一编号，避免变量竞态，且配合 ALGORITHM=INPLACE 可大幅降低锁影响。

先加普通列：ALTER TABLE your_table ADD COLUMN tmp_id BIGINT;
用排序保证稳定性（例如按时间戳 + 原主键）：UPDATE your_table t1 JOIN (SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn FROM your_table) t2 ON t1.id = t2.id SET t1.tmp_id = t2.rn;
删旧主键（如有）、改名并设约束：ALTER TABLE your_table DROP PRIMARY KEY, DROP COLUMN old_pk, CHANGE tmp_id id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

关键点：整个过程可在低峰期执行；UPDATE 阶段是行级锁，不影响其他查询（除非显式 SELECT ... FOR UPDATE）。

大表必须强制使用 `ALGORITHM=INPLACE`

如果原表无主键但已有大量数据，直接 MODIFY COLUMN id ... PRIMARY KEY 默认触发 COPY 算法，锁表数小时。必须显式指定在线 DDL：

ALTER TABLE your_table ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST, ALGORITHM=INPLACE, LOCK=NONE;
LOCK=NONE 仅在 MySQL 8.0+ 对主键变更才真正可靠；低于该版本，LOCK=SHARED 是底线
若表含外键、全文索引或分区，INPLACE 可能被降级为 COPY，需提前检查 INFORMATION_SCHEMA.INNODB_TABLES 或执行前加 EXPLAIN FORMAT=JSON 验证

最稳妥但侵入性强的方案：建新表 + 原子重命名

当无法升级 MySQL 版本，或表结构复杂（含外键、触发器、全文索引等）时，这是唯一能 100% 规避锁风险的方式：

新建结构一致的表：CREATE TABLE your_table_new LIKE your_table;
添加自增主键：ALTER TABLE your_table_new ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
迁移数据（带排序）：INSERT INTO your_table_new (col1, col2, ...) SELECT col1, col2, ... FROM your_table ORDER BY created_at, id;
原子切换：RENAME TABLE your_table TO your_table_bak, your_table_new TO your_table;

注意：外键需手动重建；应用层需短暂停写，或通过双写+校验过渡；RENAME 是原子操作，但切换瞬间有极短不可见窗口。

真正容易被忽略的是：无论哪种方式，都必须验证目标字段（或组合）的非空性与唯一性——哪怕用了 ROW_NUMBER()，也要确认 ORDER BY 子句能覆盖全量数据且无歧义，否则序号可能重复或跳变。