MySQL 「不存在則插入，存在則更新」兩大核心方案：REPLACE INTO vs INSERT ... ON DUPLICATE KEY UPDATE

業務場景

記錄分類多語言名稱，cat_id + lang_id 為聯合主鍵。需實現：記錄不存在 → 插入；存在 → 更新名稱/關鍵詞/描述。

方案一：INSERT ... ON DUPLICATE KEY UPDATE（推薦）

INSERT INTO `cat_lang` (cat_id, lang_id, cat_name, keywords, cat_desc)
VALUES ({$cat_id}, {$k}, '{$val['cat_name']}', '{$val['keywords']}', '{$val['cat_desc']}')
ON DUPLICATE KEY UPDATE
  cat_name = '{$val['cat_name']}',
  cat_desc = '{$val['cat_desc']}',
  keywords = '{$val['keywords']}';

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

方案二：REPLACE INTO（慎用）

REPLACE INTO `cat_lang` (cat_id, lang_id, cat_name, keywords, cat_desc)
VALUES ({$cat_id}, {$k}, '{$val['cat_name']}', '{$val['keywords']}', '{$val['cat_desc']}');

深度對比：核心差異在「存在時如何處理」

實戰示例：計數器場景

-- 表：ikey PK, value, icount
-- 需求：無記錄插入 icount=0；有記錄 icount+1

-- REPLACE 做不到（會重置 icount）
REPLACE INTO t_test SET ikey=1, value='a', icount=0; -- 錯誤邏輯

-- INSERT ... ON DUPLICATE KEY UPDATE 完美支持
INSERT INTO t_test SET ikey=1, value='a', icount=0
ON DUPLICATE KEY UPDATE icount = icount + 1;

多唯一索引陷阱演示

表有 ikey PK、value UNIQUE，現有數據：

ikey=2, value='a', icount=10
ikey=1, value='b', icount=40

執行：REPLACE INTO t_test SET ikey=1, value='a', icount=0;

結果： 兩行**全部刪除**，插入新行 (1, 'a', 0) —— 極其危險！

而 INSERT ... ON DUPLICATE KEY UPDATE 僅更新 ikey=1 那行。

選型建議

1. 默認首選 INSERT ... ON DUPLICATE KEY UPDATE — 語義精確、性能更好、支持基於舊值計算、不誤刪多行
2. 僅在「完全覆蓋替換」語義且單主鍵時 考慮 REPLACE INTO
3. 表設計階段**務必定義業務主鍵/唯一索引**，否則兩方案均無法工作