针对已有表直接添加自增主键的操作，实际执行中比预想复杂得多。许多开发新手甚至资深工程师都曾在此踩坑：一条简单的 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 约束，因此在语法检查阶段即被拦截。
具体而言，以下几种情形几乎注定失败：
- 表已经存在主键（哪怕是业务组合主键）——MySQL 会直接拒绝再添加一个额外主键。
- 新列默认允许
NULL，不满足NOT NULL约束，语句在第一步就被阻止执行。 - 即便通过添加列并使用变量赋值（例如
@id := @id + 1）来填充序号，在多线程环境或未显式指定ORDER BY时，序号可能变得不可控。尤其在 MySQL 5.7 及更早版本中，这种处理方式非常脆弱。
MySQL 8.0+ 推荐做法：借助 ROW_NUMBER() 分步生成唯一序号
实际上，在 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+ 中对主键变更才真正可靠；若版本低于 8.0，LOCK=SHARED是必须守住的安全底线。 - 如果表中包含外键、全文索引或分区，
INPLACE有可能被降级为COPY。建议提前查询INFORMATION_SCHEMA.INNODB_TABLES，或在执行前使用EXPLAIN FORMAT=JSON来验证是否真正能够应用INPLACE。
最稳妥但侵入性较强的方案：新建表 + 原子重命名
如果 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 子句能够覆盖全部数据且不产生歧义——否则，序号可能出现重复或跳变，最终仍需返工。
