MySQL数据迁移不使用存储过程如何捕获Insert失败记录存入错误表

以下是几种无需存储过程的实现方式，可根据你的场景选择：

方案1：差集比对法（最通用，兼容所有MySQL版本） #

INSERT IGNORE执行完成后，插入失败的记录就是源表T1存在但目标表T2不存在的差集，直接查询差集写入错误表即可。

操作步骤： #

1. 先执行原有迁移逻辑

INSERT IGNORE INTO T2 (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM T1;

2. 计算差集写入错误表（错误表需提前创建，字段与T1一致，额外增加err_reason、create_time字段即可）

INSERT INTO err_table (col1, col2, col3, ..., err_reason, create_time)
SELECT 
  t1.col1, t1.col2, t1.col3, ...,
  '唯一键冲突/约束校验失败',
  NOW()
FROM T1 t1
LEFT JOIN T2 t2 ON t1.唯一键字段 = t2.唯一键字段
WHERE t2.唯一键字段 IS NULL;

适用场景： #

迁移过程中T2无其他业务写入，插入失败原因以唯一键冲突、常规约束违反为主。

方案2：EXCEPT运算符法（仅支持MySQL 8.0及以上版本） #

如果你的数据库版本支持EXCEPT，差集查询逻辑更简洁，无需手动关联唯一键：

-- 先执行INSERT IGNORE迁移逻辑
INSERT IGNORE INTO T2 SELECT * FROM T1;

-- 差集写入错误表
INSERT INTO err_table 
SELECT t.*, '约束校验失败', NOW() 
FROM (
  SELECT * FROM T1
  EXCEPT
  SELECT * FROM T2
) AS failed_rows;

方案3：前置校验法（适合插入失败原因复杂的场景） #

如果除了唯一键冲突外，还有字段长度超限、非空约束违反、类型不匹配等问题，可以先做前置校验把不符合要求的记录先筛入错误表，再执行迁移：

-- 1. 筛选非空约束违反的记录
INSERT INTO err_table SELECT *, '非空字段colX为空', NOW() FROM T1 WHERE colX IS NULL;

-- 2. 筛选字段长度超限的记录
INSERT INTO err_table SELECT *, 'colY长度超出T2字段限制', NOW() FROM T1 WHERE LENGTH(colY) > 64;

-- 3. 筛选类型不匹配的记录
INSERT INTO err_table SELECT *, 'colZ数值超出T2字段取值范围', NOW() FROM T1 WHERE colZ > 99999;

-- 4. 剩余未进入错误表的记录执行迁移
INSERT IGNORE INTO T2 SELECT * FROM T1 WHERE t1.唯一键 NOT IN (SELECT 唯一键 FROM err_table);

注意事项 #

• 数据量大的场景建议分批处理，避免长事务锁表影响业务。
• 迁移过程中建议给T1、T2加读锁或者使用事务快照，避免其他写入操作导致差集计算错误。

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