MySQL不同主键的表间如何实现源表到目标表的插入或更新

先澄清对INSERT ... ON DUPLICATE KEY UPDATE的认知误区 #

该语法并非要求两张表的主键结构保持一致，核心触发规则是向目标表插入数据时，若插入值与目标表上任意主键/唯一索引字段的已有值产生冲突，就会自动执行预设的更新逻辑。之前的适配问题，本质是没有把两表关联的匹配字段在目标表配置对应的唯一约束，而非语法本身不支持跨主键结构的同步场景。

方案一：基于唯一索引的单步同步（性能最优，生产推荐） #

当前的匹配规则是「test1与test2的Empid值相等即判定为已存在匹配行」，只需要先给test1的关联匹配字段加上唯一索引，就能复用原语法实现完整逻辑：

• 提前校验test1中不存在重复Empid的脏数据，避免加索引失败，校验SQL如下：

  SELECT Empid, COUNT(*) 
  FROM test1 
  GROUP BY Empid 
  HAVING COUNT(*) > 1;
  

• 给test1的Empid字段添加唯一约束：

  ALTER TABLE test1 ADD UNIQUE KEY uk_empid (Empid);
  

• 执行同步逻辑，插入test2数据时自动处理冲突更新：

  -- MySQL 8.0.20及以上版本推荐使用临时别名写法，避免VALUES()函数的废弃警告
  INSERT INTO test1 (EmpName, Empid)
  SELECT * FROM (
      SELECT
          CONCAT(t2.EmpFName, ' ', t2.EmpLName) AS EmpName,
          t2.Empid
      FROM test2 t2
  ) AS src
  ON DUPLICATE KEY UPDATE
      -- 匹配到重复Empid时，更新test1对应的员工姓名字段
      EmpName = src.EmpName;
  

  该方案为单条SQL完成全量同步，性能远高于多步操作，且基于数据库层面的唯一约束可避免并发下的重复数据问题。

方案二：无额外约束的两步同步（适配不能加唯一索引的场景） #

如果业务规则本身允许test1中存在同一Empid的多条历史记录，无法为Empid添加唯一约束，可以通过「先更新匹配行、再插入无匹配行」的逻辑实现同步，所有操作需要放在事务中保证原子性：

• 先关联两表，更新test1中与test2 Empid匹配的记录：

  UPDATE test1 t1
  INNER JOIN test2 t2
      ON t1.Empid = t2.Empid
  SET t1.EmpName = CONCAT(t2.EmpFName, ' ', t2.EmpLName);
  

• 过滤出test2中未在test1匹配到的记录，执行插入：

  INSERT INTO test1 (EmpName, Empid)
  SELECT
      CONCAT(t2.EmpFName, ' ', t2.EmpLName) AS EmpName,
      t2.Empid
  FROM test2 t2
  LEFT JOIN test1 t1
      ON t1.Empid = t2.Empid
  WHERE t1.id IS NULL;
  

  该方案灵活度更高，完全基于自定义的关联条件做匹配，不需要修改原表的索引结构，但两步操作的执行效率低于方案一，并发场景下需要手动加锁避免插入重复数据。

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