MySQL5.6环境下使用Delta表全量替换更新Base主表方案咨询

REPLACE INTO仅会对主键/唯一键匹配的行做覆盖操作，无法自动删除不在结果集中的旧数据，要实现Base表完全替换为目标查询结果，推荐以下两种适配MySQL 5.6版本的方案：

方案1：临时表原子交换（生产环境首选） #

该方案锁表时间极短（仅毫秒级），不会影响业务正常读写，安全性最高。

1. 创建和Base表结构完全一致的临时表

CREATE TABLE base_temp LIKE Base;

2. 将验证无误的目标查询结果写入临时表（注意修正原查询中空值判断的语法问题，MySQL中判断空值需用IS NULL而非= NULL）

INSERT INTO base_temp
SELECT Base.* FROM
  (SELECT * FROM Base) AS Base
LEFT OUTER JOIN
  (SELECT * FROM Delta WHERE action = 'term') AS Delta
ON Base.id = Delta.id 
WHERE Delta.id IS NULL

UNION

SELECT a.* FROM
 (SELECT * FROM Delta WHERE action = 'add') AS a
LEFT OUTER JOIN
 (SELECT * FROM Delta WHERE action = 'term') AS b
ON a.id = b.id
WHERE b.id IS NULL

UNION

SELECT Case2_1.* FROM
 (SELECT a.* FROM
  (SELECT * FROM Delta WHERE action = 'add') AS a
INNER JOIN
  (SELECT * FROM Delta WHERE action = 'term') AS b
ON a.id = b.id) AS case2_1
INNER JOIN
 (SELECT * FROM Base) AS Case2_2
ON case2_1.id = case2_2.id;

3. 原子交换新旧表，瞬间完成替换

RENAME TABLE Base TO Base_old, base_temp TO Base;

4. 验证新Base表数据符合预期后，删除旧表释放空间

DROP TABLE Base_old;

方案2：先清空后插入（适合小数据量测试场景） #

该方案操作简单，但清空表到插入完成的全程会锁表，生产环境业务不可中断的情况下不推荐使用。

1. 清空Base表原有全部数据

TRUNCATE TABLE Base;

2. 直接将目标查询结果写入Base表

INSERT INTO Base
-- 此处填入上述修正后的查询逻辑即可

注意事项 #

• 操作前务必提前备份原Base表数据，避免查询逻辑错误导致数据丢失
• 如果ID是Base表的主键，建议插入前先对比临时表和预期结果的行数、关键字段值，确认无误再执行交换操作

内容的提问来源于stack exchange，提问作者r.v.s. Suneeth