多表插入数据故障排查：批量插入13000条触发约束错误

嘿，这种批量插入踩坑的情况我太熟悉了！10条样本正常但批量就炸，大概率是主键生成逻辑的漏洞或者数据里藏着不符合约束的记录，咱们一步步捋：

首先，先抓准具体的错误信息 #

别忽略数据库抛出的具体错误提示！是UNIQUE constraint failed（主键重复）还是FOREIGN KEY constraint failed（外键不存在）？不同的错误方向完全不一样，先把这个搞清楚是关键。

可能性1：主键重复（最常见） #

因为你要手动维护主键递增，批量操作时很容易出现下面这些问题：

• 待插入的主键范围和目标表已有的主键重叠了
• 待插入的13000条数据内部出现了重复主键
• 生成主键的逻辑在批量时“掉链子”（比如循环计数器没正确累加，或者被其他并发插入干扰）

排查&解决方法： #

1. 先查目标表当前的最大主键值：

   SELECT MAX(your_primary_key_column) FROM target_table;
   

2. 检查待插入数据的主键范围：
   • 确认你生成的最小主键值 > 上面查到的最大值
   • 排查数据内部有没有重复主键：

     SELECT your_new_pk_column, COUNT(*) 
     FROM (
         -- 这里放你用来生成待插入数据的查询语句
         SELECT (SELECT MAX(pk) FROM target_table) + ROW_NUMBER() OVER () AS your_new_pk_column, ...
         FROM source_table1 s1 JOIN source_table2 s2 ON ...
     ) AS temp_data
     GROUP BY your_new_pk_column
     HAVING COUNT(*) > 1;
     

3. 优化主键生成的原子性：
   如果你是用ROW_NUMBER()生成主键，建议直接在INSERT ... SELECT里完成，避免中间步骤被其他操作干扰：

   INSERT INTO target_table (pk_col, col1, col2, ...)
   SELECT 
       COALESCE((SELECT MAX(pk_col) FROM target_table), 0) + ROW_NUMBER() OVER (ORDER BY s1.id) AS new_pk,
       s1.col1, s2.col2, s3.col3, s4.col4
   FROM source_table1 s1
   JOIN source_table2 s2 ON s1.id = s2.s1_id
   JOIN source_table3 s3 ON s1.id = s3.s1_id
   JOIN source_table4 s4 ON s1.id = s4.s1_id;
   

   用COALESCE处理目标表为空的情况，ORDER BY保证ROW_NUMBER()的稳定性。
4. 避免并发干扰：
   如果有其他程序也往目标表插数据，要在事务里加锁保证原子性：

   BEGIN TRANSACTION;
   -- 锁定目标表，防止其他插入操作干扰主键计算
   SELECT * FROM target_table FOR UPDATE;
   -- 执行上面的INSERT语句
   COMMIT;
   

可能性2：其他约束违规（比如外键、非空） #

10条样本刚好都符合约束，但13000条里可能混了个别“坏数据”：

• 比如某条数据的外键字段在关联表里不存在
• 某个非空字段是空值

排查方法： #

如果错误是外键相关，用下面的SQL找不符合的记录：

-- 举个例子，假设目标表的外键是s1_id，关联source_table1的id
SELECT s1.id
FROM source_table1 s1
LEFT JOIN target_related_table trt ON s1.id = trt.s1_id
WHERE trt.s1_id IS NULL;

把各个关联表都检查一遍，找出那些不符合外键约束的记录，要么修正要么过滤掉。

可能性3：批量插入的批次问题 #

一次性插13000条如果失败，很难定位具体哪条数据出问题。可以试试分批次插入，比如每次插1000条，这样哪一批失败就重点排查那一批的数据，更容易找到根源。

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