MySQL新增字段时因transaction_id唯一键约束报错的原因与解决

1. 表中存在隐性重复的transaction_id记录：尽管transaction_id配置了唯一约束，但可能是之前通过绕过约束的操作（比如临时关闭unique_checks插入数据、数据导入时约束未生效、早期MySQL bug导致约束失效），使得表中实际存在重复值。ALTER TABLE操作会重新全量校验唯一约束，这些隐性重复此时就会触发报错。
2. MySQL 5.7的ALTER TABLE校验机制：对大表执行ADD COLUMN时，默认采用Online DDL，但在构建或验证唯一索引的过程中会扫描全表数据，原本隐藏的重复键值会被检测出来，导致操作中断。
3. 重复数据并非个例：修改一个重复ID后又出现新报错，说明表中存在多组重复的transaction_id，不是单一数据异常。

1. 先清理重复数据（最彻底的方案） #

首先定位所有重复的transaction_id：

SELECT transaction_id, COUNT(*) AS cnt
FROM wallet_transactions
GROUP BY transaction_id
HAVING cnt > 1;

根据业务逻辑处理重复数据：

• 保留一条有效记录，删除其他重复项（操作前务必备份数据）：

  DELETE t1 FROM wallet_transactions t1
  JOIN wallet_transactions t2 
  ON t1.transaction_id = t2.transaction_id 
  AND t1.id > t2.id; -- 假设id是自增主键，保留id较小的记录
  

• 或者修改重复的transaction_id为唯一值（比如拼接自增id后缀）：

  UPDATE wallet_transactions t1
  JOIN (
      SELECT transaction_id, id, 
             ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY id) AS rn
      FROM wallet_transactions
  ) t2 ON t1.id = t2.id
  SET t1.transaction_id = CONCAT(t1.transaction_id, '_', t2.rn)
  WHERE t2.rn > 1;
  

清理完成后再执行ALTER TABLE命令。

2. 临时关闭唯一约束校验执行ALTER #

适合紧急加字段、之后再处理重复数据的场景：

-- 临时关闭当前会话的唯一约束校验
SET session unique_checks = 0;
-- 执行加字段操作
ALTER TABLE wallet_transactions ADD COLUMN entity_type varchar(100) NULL, ADD COLUMN entity_value varchar(100) NULL;
-- 恢复唯一约束校验
SET session unique_checks = 1;

操作完成后必须尽快清理重复数据，避免后续业务操作出现异常。

3. 使用Percona在线表结构修改工具 #

对于3400万行的大表，pt-online-schema-change可以避免锁表，同时灵活处理数据异常：

pt-online-schema-change \
  --alter "ADD COLUMN entity_type varchar(100) NULL, ADD COLUMN entity_value varchar(100) NULL" \
  D=你的数据库名,t=wallet_transactions \
  --execute

该工具会创建临时表，逐行复制数据到临时表，完成后替换原表，过程中不阻塞业务读写，还可通过参数跳过重复数据（需结合业务场景配置）。

4. 检查并升级MySQL版本 #

MySQL 5.7.38存在部分Online DDL相关bug，可能导致对唯一索引的误判。可以升级到5.7系列的最新稳定版（如5.7.44），再尝试执行ALTER操作。

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