MySQL REPLACE INTO 語法詳解：主鍵衝突自動刪舊插新、權限要求與影響行數語義

REPLACE 核心邏輯

REPLACE = INSERT + 衝突時 DELETE 舊行 + INSERT 新行。
前提：表必須有 PRIMARY KEY 或 UNIQUE 索引，否則退化為普通 INSERT (無衝突檢測)。

語法三態

-- 1. VALUES 列表
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_list)]
VALUES (val_list), (val_list), ...;

-- 2. SET 形式
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name
SET col_name = val, ...;

-- 3. SELECT 來源
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_list)]
SELECT ...;

權限要求

需同時擁有表的 INSERT 權限 + DELETE 權限 (因內部執行刪除舊行)。

列值來源規則

• 所有列值取自 REPLACE 語句指定值。
• 未指定列 → 各自 DEFAULT 值 (同 INSERT)。
• 不可引用當前行舊值：SET col = col + 1 中右側 col 被視為 DEFAULT(col)，等效 SET col = DEFAULT(col) + 1。

影響行數回傳語義 (關鍵)

判斷邏輯：
affected_rows == 1 → 新增
affected_rows > 1 → 替換 (含更新)

C API 獲取影響行數

mysql_affected_rows(conn);  -- 返回上述語義值

限制與注意

• 不支援子查詢中對同表 REPLACE 並 SELECT。
• 觸發器：會激活 BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT 四類觸發器。
• 外鍵：若被引用 (父表)，ON DELETE CASCADE 會級聯刪除子表行；若引用他表 (子表)，需滿足外鍵約束。
• 自增 ID：舊行刪除，新行獲取新自增值 (不復用舊 ID)。
• DELAYED 在 5.6+ 棄用，8.0 移除。

算法流程 (源碼級)

1. 嘗試 INSERT 新行。
2. 若因主鍵/唯一鍵衝突失敗 (ER_DUP_KEY)：
     a. DELETE 衝突行 (所有衝突唯一索引對應的行)。
     b. 再次 INSERT 新行 (此時必成功)。

對比：REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE

最佳實踐建議

1. 優先用 INSERT ... ON DUPLICATE KEY UPDATE 實現 UPSERT，效能更好、語義更精準。
2. 僅在業務確需「完全替換、重置所有欄位為默認值」時用 REPLACE。
3. 避免在多唯一索引表使用 REPLACE (可能刪除多行非預期數據)。
4. 應用層檢查 affected_rows 區分新增/更新，用於日誌/統計/緩存失效。