MySQL ON DUPLICATE KEY UPDATE 寫法與避坑指南

INSERT ... ON DUPLICATE KEY UPDATE 實戰

MySQL 4.1+ 支援此語法，將「查詢→決定→插入/更新」三步合為一條 SQL，極大簡化業務邏輯。

經典場景：IP 點擊計數

CREATE TABLE ipstats (
    ip VARCHAR(15) NOT NULL UNIQUE,
    clicks SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0
);

傳統 3 句寫法

IF (SELECT * FROM ipstats WHERE ip='192.168.0.1') {
    UPDATE ipstats SET clicks=clicks+1 WHERE ip='192.168.0.1';
} ELSE {
    INSERT INTO ipstats (ip, clicks) VALUES ('192.168.0.1', 1);
}

單句優化

INSERT INTO ipstats VALUES('192.168.0.1', 1)
ON DUPLICATE KEY UPDATE clicks=clicks+1;

前提：表必須有唯一索引或主鍵（此處 ip 為 UNIQUE）。

REPLACE INTO vs ON DUPLICATE KEY UPDATE 差異對比

結論：優先用 ON DUPLICATE KEY UPDATE，避免資料遺失與自增 ID 跳變。

批次多筆寫法

INSERT INTO t(a,b,c) VALUES
('a1','b1','c1'),('a2','b2','c2')
ON DUPLICATE KEY UPDATE c=VALUES(c);

VALUES(c) 參照插入值列表中對應的 c 值。