REPLACE 語法與語義

REPLACE [INTO] tbl_name [(col_list)] VALUES (val_list), ...;
REPLACE [INTO] tbl_name SET col=val, ...;
REPLACE [INTO] tbl_name [(col_list)] SELECT ...;

本質：DELETE（舊行）+ INSERT（新行）的原子組合。前提：表必須有 主鍵或非空唯一索引，否則退化為普通 INSERT。

執行流程

1. 嘗試插入新行。
2. 若無唯一鍵衝突 → 同 INSERT，影響行數 = 1。
3. 若有衝突 → 刪除所有衝突行（可能多行），插入新行，影響行數 = 刪除行數 + 1。

返回值解讀

影響行數	含義
1	無衝突，純插入
2	1 行衝突：1 刪除 + 1 插入
>2	多行衝突（多個唯一索引各命中不同行）：N 刪除 + 1 插入

多唯一索引的「連鎖刪除」陷阱

CREATE TABLE t (
  a INT NOT NULL UNIQUE,
  b INT NOT NULL UNIQUE,
  c INT NOT NULL UNIQUE
);
INSERT INTO t VALUES (1,1,1), (2,2,2), (3,3,3);

REPLACE INTO t (a,b,c) VALUES (1,2,3);
-- 結果：3 行被刪除，1 行插入，影響行數 = 4
-- 表中僅剩 (1,2,3)

原因：新行 (1,2,3) 與現有三行分別在 a、b、c 三個唯一索引上衝突，REPLACE 會刪除所有衝突行。這極易導致數據意外丟失！

REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE

維度	REPLACE	INSERT ... ON DUPLICATE KEY UPDATE
語義	刪除舊行 + 插入新行	就地更新指定列
自增 ID	重新分配新 ID	保留原 ID
外鍵引用	破壞引用（舊行刪除）	保持引用完整
觸發器	觸發 DELETE + INSERT	觸發 UPDATE
部分列更新	不可行（全列替換）	精確控制更新列
多唯一索引安全	危險（連鎖刪除）	安全（僅更新匹配行）
性能	較重（刪除+插入+索引維護）	較輕（就地更新）

強烈建議：生產環境優先使用 INSERT ... ON DUPLICATE KEY UPDATE，除非確確實實需要「完全替換行」且無外鍵依賴。

實戰示例

場景 1：用戶配置表（單主鍵，適合 REPLACE）

CREATE TABLE user_config (
  user_id BIGINT PRIMARY KEY,
  theme VARCHAR(20) NOT NULL DEFAULT 'light',
  lang VARCHAR(10) NOT NULL DEFAULT 'zh-CN',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 保存配置（不存在則插入，存在則全替換）
REPLACE INTO user_config (user_id, theme, lang) VALUES (1001, 'dark', 'en-US');

場景 2：商品價格歷史（複合唯一鍵，必須用 ON DUPLICATE KEY UPDATE）

CREATE TABLE product_price (
  product_id BIGINT,
  channel_id INT,
  price DECIMAL(10,2),
  valid_from DATETIME,
  PRIMARY KEY (product_id, channel_id, valid_from)
);

INSERT INTO product_price (product_id, channel_id, price, valid_from)
VALUES (100, 1, 99.00, '2024-01-01')
ON DUPLICATE KEY UPDATE price = VALUES(price);  -- 僅更新價格，保留主鍵

避坑指南

1. 不要在有外鍵引用的表上用 REPLACE → 會導致外鍵約束失效或報錯。
2. 不要在有多個唯一索引的表上用 REPLACE → 除非完全理解連鎖刪除語義並經過測試。
3. REPLACE 不觸發 BEFORE/AFTER UPDATE 觸發器，觸發 DELETE/INSERT 觸發器，邏輯可能跑偏。
4. 自增 ID 會改變，依賴 ID 的業務（如 URL、外部系統映射）會斷裂。
5. 權限需求：同時需要 DELETE 和 INSERT 權限。

REPLACE 是「核彈級」UPSERT：簡單粗暴但副作用大。現代應用請擁抱 INSERT ... ON DUPLICATE KEY UPDATE（MySQL）、MERGE（SQL Server/Oracle）、ON CONFLICT DO UPDATE（PostgreSQL），實現精準、安全、高性能的合併寫入。