語法回顧

INSERT INTO `table` (col1, col2, ...)
VALUES (v11, v12, ...), (v21, v22, ...), ...
ON DUPLICATE KEY UPDATE
    col2 = VALUES(col2),
    col3 = VALUES(col3);

• 若 UNIQUE 或 PRIMARY KEY 衝突 → 執行 UPDATE
• 無衝突 → 插入新行
• 影響行數：插入返回 1，更新返回 2

核心技巧：VALUES() 函數

在 ON DUPLICATE KEY UPDATE 子句中，VALUES(col_name) 可引用 VALUES 列表中對應欄位的當前行值，實現「以插入值更新舊行」。

實戰範例

假設表 stats：

CREATE TABLE `stats` (
    `id` INT PRIMARY KEY,
    `counter` INT NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

初始資料：

INSERT INTO `stats` (`id`, `counter`) VALUES (1, 10), (2, 20);

批次 upsert：新增 id=3，遞增 id=1,2 的 counter

INSERT INTO `stats` (`id`, `counter`)
VALUES (1, 5), (2, 3), (3, 100)
ON DUPLICATE KEY UPDATE
    `counter` = `counter` + VALUES(`counter`),
    `updated_at` = CURRENT_TIMESTAMP;

執行結果：

• id=1：舊 counter=10 → 新增值 5 → 更新為 15
• id=2：舊 counter=20 → 新增值 3 → 更新為 23
• id=3：無衝突 → 插入 counter=100

進階用法

1. 使用條件更新

INSERT INTO `t` (`k`, `v`) VALUES ('a', 10), ('b', 20)
ON DUPLICATE KEY UPDATE
    `v` = IF(VALUES(`v`) > `v`, VALUES(`v`), `v`);  -- 只更新較大值

2. 更新多欄位，部分欄位不變

INSERT INTO `users` (`email`, `name`, `login_count`, `last_login`)
VALUES ('a@b.com', 'Alice', 1, NOW()),
       ('c@d.com', 'Bob', 1, NOW())
ON DUPLICATE KEY UPDATE
    `login_count` = `login_count` + VALUES(`login_count`),
    `last_login` = VALUES(`last_login`);
-- name 欄位不在 UPDATE 列表，保持原值

3. 搭配 LAST_INSERT_ID() 取得自增 ID

INSERT INTO `logs` (`msg`) VALUES ('msg1'), ('msg2')
ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`);
SELECT LAST_INSERT_ID();  -- 返回第一行衝突/插入的 ID

注意事項與限制

• 僅支援 MySQL，非標準 SQL（PostgreSQL 用 ON CONFLICT，SQL Server 用 MERGE）
• 觸發 UPDATE 時，BEFORE INSERT/AFTER INSERT 觸發器不會再次觸發，但 BEFORE UPDATE/AFTER UPDATE 會觸發
• AUTO_INCREMENT 計數器在衝突更新時不會遞增
• 大量資料 (＞1000 行) 建議分批處理，避免封包過大 (max_allowed_packet) 或鎖定過久
• 需確保表有 PRIMARY KEY 或 UNIQUE INDEX，否則永不觸發更新

效能建議

1. 批次大小控制在 500-1000 行
2. 確保衝突鍵有索引（PK/UK 本身即有）
3. 避免在 UPDATE 子句中執行複雜子查詢
4. 使用 LOAD DATA INFILE + 暫存表 處理超大量匯入，再合併