MySQL 多表數據組合插入新表：ON DUPLICATE KEY UPDATE 分批處理大數據量方案

百萬級多表數據合併入庫：分步驟 ON DUPLICATE KEY UPDATE 方案

場景：表 a(1000萬+)、表 b(1000萬+) 需關聯合併寫入表 c。直接 SELECT JOIN 後應用層組裝會導致內存溢出、耗時過長。

表結構

-- 表 a
CREATE TABLE a (id INT PRIMARY KEY, a INT, b INT, c INT);

-- 表 b
CREATE TABLE b (aID INT, d INT, e INT, f INT);  -- aID 關聯 a.id

-- 目標表 c
CREATE TABLE c (
  aID INT PRIMARY KEY,
  a INT, b INT, c INT, d INT, e INT, f INT
);

分步驟合併策略

1. 先寫入主維度表 a 的數據：

   INSERT INTO c (aID, a, b, c)
   SELECT id, a, b, c FROM a
   ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);

2. 再更新關聯表 b 的數據：

   INSERT INTO c (aID, d, e, f)
   SELECT aID, d, e, f FROM b
   ON DUPLICATE KEY UPDATE d=VALUES(d), e=VALUES(e), f=VALUES(f);

核心優勢

• 純 SQL 完成，無需應用層遍歷，避免內存壓力
• 可分批提交：配合 LIMIT 分片執行，控制事務大小
• 冪等安全：ON DUPLICATE KEY UPDATE 保證重複運行不產生髒數據
• 並行可控：可開多個會話處理不同 ID 範圍

分批執行模板

-- 每次處理 10 萬行
INSERT INTO c (aID, a, b, c)
SELECT id, a, b, c FROM a
WHERE id > @last_id ORDER BY id LIMIT 100000
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);

應用層循環執行直到影響行數為 0。

適用條件

• 目標表有主鍵/唯一鍵（aID）
• 源表數據量大，不可一次性加載內存
• 合併邏輯為「覆蓋式更新」或「補全缺失列」