語法核心

INSERT INTO `table` (pk_col, col1, col2) 
VALUES (1, 'a', 'b')
ON DUPLICATE KEY UPDATE 
    col1 = VALUES(col1), 
    col2 = VALUES(col2);

觸發條件

• 表有 PRIMARY KEY 或 UNIQUE INDEX
• 插入數據導致鍵衝突
• 衝突時執行 UPDATE 部分，否常插入新行

影響行數語義

情況	ROW_COUNT()	LAST_INSERT_ID()
新插入 1 行	1	新自增 ID
更新現有 1 行	2	舊自增 ID（除非用 LAST_INSERT_ID(id) 技巧）
多行插入，部分更新	插入行數 + 2×更新行數	第一行插入的自增 ID

VALUES() 函數：引用插入值

INSERT INTO `stats` (date, type, count) 
VALUES 
    ('2024-01-15', 'pv', 1),
    ('2024-01-15', 'uv', 1)
ON DUPLICATE KEY UPDATE 
    `count` = `count` + VALUES(`count`);

• VALUES(col) 返回 VALUES 子句中該列的值
• 僅在 ON DUPLICATE KEY UPDATE 子句中有效，其他上下文返回 NULL
• 極大簡化批量 upsert 邏輯

進階用法

條件更新

INSERT INTO `inventory` (sku, qty, version) 
VALUES ('SKU001', 10, 1)
ON DUPLICATE KEY UPDATE 
    qty = IF(VALUES(qty) > qty, VALUES(qty), qty),  -- 只增不減
    version = version + 1;

忽略特定衝突

-- 僅在新插入時設置 created_at，更新時保持原值
INSERT INTO `users` (email, name, created_at) 
VALUES ('a@b.com', 'Alice', NOW())
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    updated_at = NOW();  -- created_at 不變

返回插入/更新標識

INSERT INTO `logs` (msg) VALUES ('test')
ON DUPLICATE KEY UPDATE 
    id = LAST_INSERT_ID(id),  -- 關鍵技巧
    cnt = cnt + 1;
SELECT LAST_INSERT_ID();  -- 統一返回該行 ID

與 REPLACE INTO 對比

特性	INSERT ... ON DUPLICATE KEY UPDATE	REPLACE INTO
機制	衝突時 UPDATE	衝突時 DELETE + INSERT
自增 ID	保持不變（更新）	生成新 ID
外鍵關聯	安全	級聯刪除風險
觸發器	觸發 UPDATE	觸發 DELETE + INSERT
性能	優（就地更新）	差（雙重操作）

注意事項

• DELAYED 忽略：INSERT DELAYED ... ON DUPLICATE KEY 中 DELAYED 無效
• 多唯一鍵風險：若表有多個 UNIQUE KEY，衝突行不確定（任一匹配即更新），盡量避免
• 分區表限制：分區鍵必須包含在唯一鍵中，否則報錯
• 外鍵檢查：更新時仍檢查外鍵約束
• 權限：需目標表 INSERT + UPDATE 權限

實戰模板：計數器原子增減

CREATE TABLE `counter` (
    `name` VARCHAR(64) PRIMARY KEY,
    `value` BIGINT NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 原子增量
INSERT INTO `counter` (`name`, `value`) VALUES ('page_view', 1)
ON DUPLICATE KEY UPDATE 
    `value` = `value` + VALUES(`value`),
    `updated_at` = NOW();

總結

ON DUPLICATE KEY UPDATE 是 MySQL 實現 Upsert 的標準、高效、原子方式。善用 VALUES() 引用插入值、LAST_INSERT_ID(id) 統一返回 ID，可構建簡潔健壯的寫入邏輯。避免多唯一鍵表使用，配合合理索引設計，是高併發寫入場景的利器。