UPSERT 與衝突處理完全指南

目錄

• 什麼是 UPSERT？
• 為什麼需要 UPSERT？
• PostgreSQL：ON CONFLICT
• SQLite：ON CONFLICT 與 INSERT OR
• MySQL：ON DUPLICATE KEY 與 INSERT IGNORE
• SQL 標準：MERGE
• 各資料庫對照
• 陷阱與注意事項
• 最佳實踐
• 常見問題
• 總結

什麼是 UPSERT？

UPSERT 是 UPdate + inSERT 的合稱：嘗試插入一筆資料，若因為唯一鍵 / 主鍵衝突而無法插入，就改為「略過」或「更新既有資料」。

INSERT 一筆資料
├─ 沒衝突 → 正常插入
└─ 撞到唯一鍵 → 不報錯，改為「什麼都不做」或「更新」

ON CONFLICT DO NOTHING 就是其中一種：衝突時直接略過。

核心特點

• 冪等性（Idempotent）：重複執行同一筆 insert 不會報錯、結果一致
• 原子性：插入或更新在單一語句內完成，避免「先查再寫」的競態
• 語法各家不同：是 UPSERT 最麻煩的地方——沒有統一寫法

為什麼需要 UPSERT？

沒有 UPSERT 的問題

常見需求：「有就更新、沒有就新增」。傳統做法是先查再寫：

-- ❌ 有競態問題（race condition）
SELECT * FROM users WHERE email = 'a@x.com';
-- 若存在 → UPDATE，否則 → INSERT

問題：在「查」與「寫」之間，別的連線可能插入同一筆 → 造成重複或唯一鍵錯誤。

UPSERT 的解法

把判斷與寫入合併成單一原子語句，交給資料庫處理衝突：

-- ✅ PostgreSQL：一句話搞定，無競態
INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

前提：必須有唯一性約束

UPSERT 是針對唯一鍵 / 主鍵衝突。要觸發衝突處理，欄位上必須先有 PRIMARY KEY 或 UNIQUE 約束，否則 insert 永遠不會「衝突」。

PostgreSQL：ON CONFLICT

PostgreSQL 從 9.5 起支援 INSERT ... ON CONFLICT。

DO NOTHING：衝突就略過

INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email) DO NOTHING;
-- email 已存在 → 這筆被忽略，不報錯，也不更新

DO UPDATE：衝突就更新

INSERT INTO users (email, name, login_count)
VALUES ('a@x.com', 'Alice', 1)
ON CONFLICT (email)
DO UPDATE SET
    name        = EXCLUDED.name,
    login_count = users.login_count + 1;

• EXCLUDED：代表「原本想插入但被擋下的那筆值」（這裡 EXCLUDED.name = 'Alice'）
• 等號左邊用既有列（users.login_count），右邊可混用 EXCLUDED 與既有值

conflict target（衝突目標）

括號裡指定「以哪個唯一約束判斷衝突」：

ON CONFLICT (email) ...                 -- 指定欄位（須有對應 unique 約束）
ON CONFLICT ON CONSTRAINT uq_email ...  -- 直接指定約束名稱
ON CONFLICT DO NOTHING                  -- 不指定，任一衝突都略過

加條件與回傳

INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
WHERE users.name IS DISTINCT FROM EXCLUDED.name   -- 只在真的有變化時才更新
RETURNING id, name;                                -- 回傳結果

SQLite：ON CONFLICT 與 INSERT OR

SQLite 有兩套衝突處理語法。

1. ON CONFLICT（3.24+，與 PostgreSQL 相容）

INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = excluded.name;

INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

用法幾乎與 PostgreSQL 相同（excluded 小寫亦可）。

2. INSERT OR（SQLite 傳統語法）

INSERT OR IGNORE  INTO users (email, name) VALUES ('a@x.com', 'Alice');  -- 衝突略過
INSERT OR REPLACE INTO users (email, name) VALUES ('a@x.com', 'Alice');  -- 衝突就取代

⚠️ INSERT OR REPLACE 是先 DELETE 再 INSERT，不是 UPDATE。這會觸發 ON DELETE CASCADE、重設自增 ID、清掉沒指定的欄位——陷阱見後面章節。

MySQL：ON DUPLICATE KEY 與 INSERT IGNORE

MySQL 不支援 ON CONFLICT，改用自己的語法。

ON DUPLICATE KEY UPDATE：衝突就更新

INSERT INTO users (email, name, login_count)
VALUES ('a@x.com', 'Alice', 1)
ON DUPLICATE KEY UPDATE
    name        = VALUES(name),          -- 舊版：VALUES() 取插入值
    login_count = login_count + 1;

• 用 VALUES(欄位) 取「原本要插入的值」（相當於 PostgreSQL 的 EXCLUDED）
• MySQL 8.0.19+ 改推薦用別名語法：

INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice') AS new
ON DUPLICATE KEY UPDATE name = new.name;

• 注意：它對「任何唯一鍵衝突」都觸發，無法像 PostgreSQL 指定特定 conflict target

INSERT IGNORE：衝突就略過

INSERT IGNORE INTO users (email, name) VALUES ('a@x.com', 'Alice');

⚠️ INSERT IGNORE 會把多種錯誤都降級成警告（不只唯一鍵衝突，連型別錯誤、NOT NULL 違反等也可能被吞掉），容易掩蓋問題。用途要謹慎。

REPLACE INTO：衝突就取代

REPLACE INTO users (email, name) VALUES ('a@x.com', 'Alice');

與 SQLite 的 INSERT OR REPLACE 一樣，是 DELETE + INSERT，同樣有觸發 CASCADE、重設 ID 的陷阱。

SQL 標準：MERGE

MERGE 是 SQL 標準的 UPSERT 語法，功能最完整（可同時處理 matched / not matched）。

MERGE INTO users AS t
USING (SELECT 'a@x.com' AS email, 'Alice' AS name) AS s
ON t.email = s.email
WHEN MATCHED THEN
    UPDATE SET name = s.name
WHEN NOT MATCHED THEN
    INSERT (email, name) VALUES (s.email, s.name);

支援情況：

• SQL Server、Oracle：長期支援
• PostgreSQL：15 版起支援 MERGE
• MySQL、SQLite：不支援

MERGE 語法繁瑣，且早期某些資料庫實作有並行 / 競態問題。一般 UPSERT 需求用各家原生語法（ON CONFLICT / ON DUPLICATE KEY）更簡潔。

各資料庫對照

陷阱與注意事項

1. REPLACE / INSERT OR REPLACE 是「刪除再插入」

這是最大的陷阱。REPLACE（MySQL）與 INSERT OR REPLACE（SQLite）不是更新，而是先刪掉舊列再插入新列，後果：

• 觸發 ON DELETE CASCADE：可能連帶刪掉子表資料！
• 重設自增主鍵：舊列的 id 沒了，新列拿到新 id
• 未指定欄位被清空 / 設回預設值：因為是全新一列

-- 想「更新 name」卻用 REPLACE → email 以外的欄位全被重置
REPLACE INTO users (email, name) VALUES ('a@x.com', 'Bob');
-- 原本的 phone、created_at 等全沒了

需要「更新」就用 ON DUPLICATE KEY UPDATE / ON CONFLICT DO UPDATE，別用 REPLACE。

2. 必須有唯一約束

ON CONFLICT (email) 指定的欄位若沒有 UNIQUE / PRIMARY KEY 約束，PostgreSQL 會直接報錯。UPSERT 是建立在唯一約束上的。

3. INSERT IGNORE 會吞掉非預期錯誤

MySQL 的 INSERT IGNORE 不只忽略唯一鍵衝突，連資料截斷、型別不符、NOT NULL 違反等也會被降為警告而靜默通過，可能讓壞資料溜進去。

4. DO NOTHING 不回傳被略過的列

PostgreSQL ON CONFLICT DO NOTHING 搭配 RETURNING 時，被略過的列不會出現在回傳結果（因為它沒被寫入）。需要「拿到既有列」要另外查或改用 DO UPDATE。

5. 多唯一鍵時的行為差異

一張表有多個唯一鍵時：PostgreSQL 可用 conflict target 精準指定要處理哪個；MySQL 的 ON DUPLICATE KEY 則對任一唯一鍵衝突都觸發，行為較難預測。

最佳實踐

1. 「更新」用 DO UPDATE，別用 REPLACE

需要保留其他欄位與子資料時，永遠選 ON CONFLICT DO UPDATE / ON DUPLICATE KEY UPDATE，避免 REPLACE 的刪除副作用。

2. 用 UPSERT 取代「先查再寫」

消除競態、減少往返，並讓操作冪等：

-- ✅ 計數器累加，併發安全
INSERT INTO counters (key, count) VALUES ('hits', 1)
ON CONFLICT (key) DO UPDATE SET count = counters.count + 1;

3. 明確指定 conflict target

PostgreSQL 盡量寫出 ON CONFLICT (欄位)，意圖清楚、避免誤觸其他唯一鍵。

4. 慎用 INSERT IGNORE

若只想忽略「重複」，PostgreSQL/SQLite 的 DO NOTHING 比 MySQL 的 INSERT IGNORE 安全得多（後者吞太多錯）。

常見問題

問題 1：ON CONFLICT 是哪些資料庫的語法？

PostgreSQL（9.5+） 與 SQLite（3.24+）。MySQL 不支援，要用 ON DUPLICATE KEY UPDATE / INSERT IGNORE。

問題 2：DO NOTHING 和 DO UPDATE 差在哪？

• DO NOTHING：衝突就略過，既有資料原封不動
• DO UPDATE：衝突就更新既有列（可用 EXCLUDED 取想插入的值）

問題 3：REPLACE 和 ON DUPLICATE KEY UPDATE 一樣嗎？

不一樣。ON DUPLICATE KEY UPDATE 是就地更新；REPLACE 是刪除再插入，會觸發 CASCADE、重設 ID、清空未指定欄位。要更新就別用 REPLACE。

問題 4：UPSERT 一定要有唯一鍵嗎？

是。衝突處理建立在唯一鍵 / 主鍵約束上，沒有約束就不會發生「衝突」，UPSERT 無從觸發（PostgreSQL 指定不存在的 conflict target 會直接報錯）。

問題 5：EXCLUDED 是什麼？

PostgreSQL / SQLite 在 DO UPDATE 中用 EXCLUDED.欄位 代表「原本要插入、但因衝突被擋下的那筆值」。MySQL 對應的是 VALUES(欄位) 或 8.0.19+ 的別名語法。

總結

核心要點

• UPSERT = insert-or-update：插入撞到唯一鍵時，改為略過或更新，避免「先查再寫」的競態
• ON CONFLICT DO NOTHING / DO UPDATE 是 PostgreSQL 與 SQLite 的語法；MySQL 用 ON DUPLICATE KEY UPDATE / INSERT IGNORE
• 取插入值：PostgreSQL/SQLite 用 EXCLUDED，MySQL 用 VALUES() 或別名
• REPLACE / INSERT OR REPLACE 是刪除再插入，會觸發 CASCADE、重設 ID、清空欄位——要更新請用 DO UPDATE
• 前提是欄位上有 UNIQUE / PRIMARY KEY 約束

快速參考

建立日期：2026-06-17