MySQL INSERT ... ON DUPLICATE KEY UPDATE 多行批量寫入詳解

單行基礎語法

INSERT INTO tbl(a,b,c) VALUES(1,2,3)
ON DUPLICATE KEY UPDATE c = c + 1;
-- 等價於：若 a=1 存在則 UPDATE，否則 INSERT
-- 返回：INSERT=1 行，UPDATE=2 行

多行批量寫入：VALUES() 函數引用插入值

INSERT INTO tbl(a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b = VALUES(b);

執行邏輯：

• 逐行嘗試插入
• 衝突行（a=2, a=3）執行 UPDATE b = VALUES(b)，VALUES(b) 取對應行的插入值（5 和 3）
• 非衝突行（a=1, a=4）正常插入

VALUES(col) 進階用法

-- 計數器增量
ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt)

-- 多列更新
ON DUPLICATE KEY UPDATE 
    b = VALUES(b),
    c = VALUES(c),
    updated_at = NOW()

-- 條件更新（MySQL 8.0.20+）
ON DUPLICATE KEY UPDATE 
    b = IF(VALUES(b) > b, VALUES(b), b)  -- 只增不減

影響行數語義

⚠️ 關鍵限制與避坑

• 僅 MySQL 支持，非標準 SQL（PostgreSQL 用 ON CONFLICT，SQL Server 用 MERGE）
• 唯一鍵衝突才觸發：主鍵、唯一索引；普通索引不觸發
• 外鍵約束：父表 ON DUPLICATE KEY UPDATE 可能導致子表外鍵檢查失敗
• 觸發器順序：BEFORE INSERT → AFTER INSERT → BEFORE UPDATE → AFTER UPDATE
• 自增 ID：衝突更新不消耗自增值，但預分配的自增值可能留空洞
• 分區表：需包含分區鍵，否則報錯

生產最佳實踐

-- 1. 批量 Upsert 模板
INSERT INTO metrics(day, key, val) VALUES 
    ('2024-01-01','cpu',0.5),
    ('2024-01-01','mem',0.8)
ON DUPLICATE KEY UPDATE 
    val = VALUES(val),
    updated_at = CURRENT_TIMESTAMP;

-- 2. 配合 rewriteBatchedStatements=true (JDBC) 自動合併多條為批量

-- 3. 大批量分片：每批 1000-5000 行，避免超大 SQL 文本解析開銷