萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ON DUPLICATE KEY UPDATE 只 INSERT 不 UPDATE?核心條件與批量寫入範例

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), ...;

實戰範例

範例 1:單鍵值更新

-- 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);

範例 2:複合主鍵計數器(如每日統計)

-- 表 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 + ?;

範例 3:多欄位同步更新

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(會刪除再插入)

效能優勢

  • 單條 SQL 完成「查詢→判斷→寫入」,減少網路往返與鎖競爭。
  • 批量寫入時,VALUES() 函數可引用對應列的值,避免重複參數綁定。

掌握唯一鍵前提與 VALUES() 用法,即可釋放 ON DUPLICATE KEY UPDATE 的全部威力,讓批量寫入既簡潔又高效。

copyright © 萬盛學電腦網 all rights reserved