SQL插入操作如何合并重复数据以解决主键重复报错问题

报错原因 #

• 主键重复报错是因为table_main的主键为X+Y的联合主键，待插入的X=286、Y=111111的组合已经存在于表中，触发了唯一性约束校验。
• 你原SQL里的GROUP BY student只能保证本次查询结果里的学号不重复，无法过滤掉已经存在于table_main中的重复组合，所以依然会报错。

额外注意：你当前的SQL中INNER JOIN没有写关联条件，会生成笛卡尔积，如果table_A中符合id=286的记录不止1条，会生成大量无效组合，建议补充合理的JOIN关联条件。

解决方案 #

方案1：使用INSERT IGNORE（最简单，仅插入新记录） #

遇到重复主键组合时直接跳过，不报错也不会覆盖已有数据，适合仅需要新增不存在的记录的场景：

INSERT IGNORE INTO `table_main`(`X`,`Y`) 
SELECT table_A.id, 
       table_B.student 
FROM       `table_A` 
INNER JOIN `table_B` 
WHERE table_A.id = '286' 
  AND table_B.created_at >= '2021-08-25 00:00' 
GROUP BY `student`;

方案2：使用ON DUPLICATE KEY UPDATE（重复时更新指定字段） #

如果遇到重复主键时需要同步更新表内其他字段，可以用这个语法，示例如下（可根据实际需求修改要更新的字段）：

INSERT INTO `table_main`(`X`,`Y`, `update_time`) 
SELECT table_A.id, 
       table_B.student,
       NOW()
FROM       `table_A` 
INNER JOIN `table_B` 
WHERE table_A.id = '286' 
  AND table_B.created_at >= '2021-08-25 00:00' 
GROUP BY `student`
ON DUPLICATE KEY UPDATE `update_time` = NOW();

方案3：提前过滤已存在的记录（兼容通用SQL标准） #

如果不想使用MySQL特有的语法，可以在查询时提前排除已经存在于table_main的组合：

INSERT INTO `table_main`(`X`,`Y`) 
SELECT a.id, b.student 
FROM `table_A` a
INNER JOIN `table_B` b 
WHERE a.id = '286' 
  AND b.created_at >= '2021-08-25 00:00' 
  AND NOT EXISTS (
    SELECT 1 FROM `table_main` m WHERE m.X = a.id AND m.Y = b.student
  )
GROUP BY b.student;

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