MySQL使用INSERT ON DUPLICATE KEY迁移数据时重复插入问题如何解决？

问题核心原因 #

ON DUPLICATE KEY UPDATE 仅在插入操作违反目标表的唯一主键/唯一索引时才会触发。你当前没有为permission_index_bkp的四个匹配字段设置联合唯一索引，所以即使四个字段值完全重复，MySQL也不会判定为冲突，会直接插入新行，导致重复数据。

解决步骤 #

1. 先处理目标表现有数据并创建联合唯一索引
   如果目标表当前已经存在四个字段重复的行，需要先去重后再创建索引，否则索引创建会报错：

   -- 去重示例：假设表有自增主键id，保留id更小的重复行，可根据实际需求调整逻辑
   DELETE t1 FROM permission_index_bkp t1
   INNER JOIN permission_index_bkp t2 
   WHERE 
     t1.id > t2.id AND
     t1.b_app = t2.b_app AND
     t1.b_perm_type = t2.b_perm_type AND
     t1.b_perm_type_id = t2.b_perm_type_id AND
     t1.b_perm_3 = t2.b_perm_3;
   
   -- 创建四个字段的联合唯一索引
   ALTER TABLE permission_index_bkp ADD UNIQUE INDEX uk_4fields_unique (b_app, b_perm_type, b_perm_type_id, b_perm_3);
   

2. 调整插入语句
   你的需求是重复行直接跳过，不需要更新已存在数据，用INSERT IGNORE更简洁：

   INSERT IGNORE INTO permission_index_bkp
     (b_app, b_perm_type, b_perm_type_id, b_perm_3)
   SELECT app, perm_type, perm_type_id, perm_3
   FROM permission_index
   WHERE app = 'module'
     AND perm_type = '1'
     AND perm_type_id = '2'
     AND perm_3 IS NOT NULL;
   

   如果你要保留原ON DUPLICATE KEY UPDATE的写法也可以，调整后效果一致：

   INSERT INTO permission_index_bkp
     (b_app, b_perm_type, b_perm_type_id, b_perm_3)
   SELECT app, perm_type, perm_type_id, perm_3
   FROM permission_index
   WHERE app = 'module'
     AND perm_type = '1'
     AND perm_type_id = '2'
     AND perm_3 IS NOT NULL
   ON DUPLICATE KEY UPDATE b_app = b_app; -- 仅需写一个无意义赋值即可，不需要更新所有字段
   

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