INSERT ... ON DUPLICATE KEY UPDATE 是 MySQL 特有的「有則更新、無則插入」語法,極適合批量寫入與計數器場景。但許多開發者發現它「只插入不更新」,根因在於觸發更新的欄位必須是 PRIMARY KEY 或 UNIQUE INDEX。本文釐清原理並提供實戰範例。
只有當插入資料導致 PRIMARY KEY 或 UNIQUE INDEX 衝突時,才會觸發 UPDATE 子句。若表無主鍵、無唯一索引,或衝突欄位非唯一鍵,MySQL 視為無衝突,僅執行 INSERT。
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...), (val1, val2, ...), ... ON DUPLICATE KEY UPDATE col2 = VALUES(col2), col3 = VALUES(col3), ...;
-- a 為 PRIMARY KEY 或 UNIQUE KEY
INSERT INTO xinhuazidian (a, b, c)
VALUES ('aaa','bbb','111cn.net'), ('aaa2','bbb2','m.111cn.net')
ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
-- 表 osc_visit_stats 擁有複合主鍵 (stat_date, type, id) INSERT INTO osc_visit_stats (stat_date, type, id, view_count) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE view_count = view_count + ?;
INSERT INTO osc_space_visit_records (space, user, visit_count, ip, visit_time) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE visit_count = visit_count + 1, ip = ?, visit_time = NOW();
| 現象 | 原因 | 對策 |
|---|---|---|
| 只 INSERT 不 UPDATE | 衝突欄位無 PRIMARY/UNIQUE KEY | 加上 ALTER TABLE ... ADD UNIQUE KEY(idx_col) |
| UPDATE 不生效 | VALUES() 引用錯誤或欄位名拼寫錯誤 | 檢查拼寫,確認 VALUES(col) 對應 INSERT 列表 |
| 自增 ID 跳號 | 每次衝突仍消耗自增值 | 接受或改用 REPLACE INTO(會刪除再插入) |
VALUES() 函數可引用對應列的值,避免重複參數綁定。掌握唯一鍵前提與 VALUES() 用法,即可釋放 ON DUPLICATE KEY UPDATE 的全部威力,讓批量寫入既簡潔又高效。