MySQL ON DUPLICATE KEY UPDATE 實戰：插入不存在、存在則更新的原子化寫入模式

業務常遇到「若記錄不存在則插入，存在則更新」的 upsert 需求。MySQL 提供 INSERT ... ON DUPLICATE KEY UPDATE 語法，單條 SQL 即可完成原子化操作，避免「先查再寫」的競態條件與雙倍往返開銷。本文通過完整建表、建立唯一索引、測試驗證的流程，詳細講解用法細節與避坑指南。

場景：書目庫存管理

• 表結構：id (PK)、name (書名)、count (庫存)
• 需求：同一書名只能有一筆記錄，重複插入時累加庫存

建表：唯一索引是前提

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE test;

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `count` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)  -- 關鍵：唯一索引觸發 DUPLICATE KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心機制：ON DUPLICATE KEY UPDATE 觸發條件是「插入導致主鍵或唯一索引衝突」。必須在目標欄位建立 PRIMARY KEY 或 UNIQUE KEY，否則不會觸發更新，反而報錯或產生重複資料。

驗證索引建立

mysql> SHOW INDEX FROM books;
+-------+------------+----------+--------------+-------------+-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Index_type |
+-------+------------+----------+--------------+-------------+-----------+
| books | 0          | PRIMARY  | 1            | id          | BTREE      |
| books | 0          | uk_name  | 1            | name        | BTREE      |
+-------+------------+----------+--------------+-------------+-----------+

測試流程

1. 首次插入

INSERT INTO `books` VALUES ('', 'Windows', 5);
-- Query OK, 1 row affected
mysql> SELECT * FROM books;
+----+---------+-------+
| id | name    | count |
+----+---------+-------+
| 1  | Windows | 5     |
+----+---------+-------+

2. 重複插入觸發更新

INSERT INTO `books` VALUES ('', 'Windows', 1)
ON DUPLICATE KEY UPDATE `count` = `count` + VALUES(`count`);
-- Query OK, 2 rows affected

關鍵語法解析：

• VALUES(count)：引用 INSERT 子句中擬插入的值（此處為 1）
• count = count + VALUES(count)：原值 5 + 新值 1 = 6
• 回傳 2 rows affected：1 row inserted (隱含) + 1 row updated

3. 驗證結果

mysql> SELECT * FROM books;
+----+---------+-------+
| id | name    | count |
+----+---------+-------+
| 1  | Windows | 6     |
+----+---------+-------+

單條記錄，庫存正確累加為 6。

進階用法與變體

多欄位唯一索引

UNIQUE KEY `uk_category_name` (`category_id`, `name`)
-- 同分類下書名唯一

更新多個欄位

INSERT INTO books (name, count, price) VALUES ('Linux', 10, 99)
ON DUPLICATE KEY UPDATE 
  count = count + VALUES(count),
  price = VALUES(price),           -- 直接覆蓋新價格
  updated_at = NOW();              -- 記錄更新時間

條件式更新（MySQL 8.0+）

INSERT INTO books (name, count) VALUES ('Windows', 1)
ON DUPLICATE KEY UPDATE 
  count = IF(VALUES(count) > count, VALUES(count), count);  -- 只增不減

搭配 LAST_INSERT_ID() 取得 ID

INSERT INTO books (name, count) VALUES ('NewBook', 1)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), count = count + 1;
SELECT LAST_INSERT_ID();  -- 無論插入或更新，都能取得該行 id

效能與鎖行為

• 單條 SQL，減少網路往返
• 行級鎖：InnoDB 僅鎖衝突的索引記錄（next-key lock）
• 高併發下優於「SELECT → IF EXISTS UPDATE ELSE INSERT」模式
• 大量批次 upsert 可考慮 LOAD DATA ... ON DUPLICATE KEY UPDATE（8.0+）

避坑清單

ON DUPLICATE KEY UPDATE 是 MySQL 特有的高效 upsert 方案，配合唯一索引設計，能優雅解決「插入或更新」的業務場景，是每個 MySQL 開發者必須掌握的核心語法。