MySQL将表1数据upsert到表2后统一标记待删除行的实现方法

方案1：无需flag字段的简洁实现 （推荐） #

不需要额外维护标记字段，直接通过事务+关联删除完成操作，全程无需单独处理插入/更新场景的标记逻辑：

• 第一步：将所有操作包裹在同一个事务中，避免异常导致数据不一致
• 第二步：执行你已实现的表1到表2的新增/更新（upsert）逻辑，MySQL原生支持INSERT ... ON DUPLICATE KEY UPDATE语法完成upsert，示例：

INSERT INTO table2 (unique_key, x, y)
SELECT unique_key, x, y FROM table1
ON DUPLICATE KEY UPDATE x = VALUES(x), y = VALUES(y);

• 第三步：执行关联删除，直接删掉表1中已经同步到表2的所有行，匹配两个表的唯一关联键即可：

DELETE t1 FROM table1 t1
INNER JOIN table2 t2 ON t1.unique_key = t2.unique_key;

• 第四步：提交事务即可

方案2：保留flag字段标记的实现 #

如果你需要保留flag标记的逻辑，可按更新、插入场景拆分处理完成标记：

• 第一步：先处理更新场景，同步表2数据的同时标记表1对应行的flag

UPDATE table1 t1
INNER JOIN table2 t2 ON t1.unique_key = t2.unique_key
SET 
  t2.x = t1.x,
  t2.y = t1.y,
  t1.flag = 1;

• 第二步：处理插入场景，将表1中表2不存在的行插入到表2

INSERT INTO table2 (unique_key, x, y)
SELECT t1.unique_key, t1.x, t1.y FROM table1 t1
LEFT JOIN table2 t2 ON t1.unique_key = t2.unique_key
WHERE t2.unique_key IS NULL AND t1.flag = 0;

• 第三步：标记刚才插入场景对应的表1行的flag

UPDATE table1 t1
SET t1.flag = 1
WHERE t1.flag = 0 AND EXISTS (
  SELECT 1 FROM table2 t2 WHERE t2.unique_key = t1.unique_key
);

• 第四步：删除表1中所有已标记的行

DELETE FROM table1 WHERE flag = 1;

注意事项 #

• 所有操作建议放在同一个事务中执行，出现异常可直接回滚避免数据错乱
• 给两个表的关联唯一键添加索引，可大幅提升大表场景下的执行效率

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