測試環境準備
CREATE TABLE test1(id INT PRIMARY KEY, name VARCHAR(5), type INT);
CREATE TABLE test2(id INT PRIMARY KEY, name VARCHAR(5), type INT);
INSERT INTO test1 VALUES (101,'aaa',1),(102,'bbb',2),(103,'ccc',3);
INSERT INTO test2 VALUES (201,'aaa',1),(202,'bbb',2),(203,'ccc',3),(101,'xxx',5);
方案對比表
| 語法 | 衝突行為 | 非衝突列處理 | 適用場景 |
|---|
REPLACE INTO | 刪除舊行 → 插入新行 | 未指定列置 NULL | 需完全覆蓋、主鍵/唯一鍵衝突 |
INSERT ... ON DUPLICATE KEY UPDATE | 更新指定列 | 保留原值 | 增量同步、計數器、部分更新 |
INSERT IGNORE | 忽略衝突，不插入 | 保留原值 | 冪等導入、去重、日志寫入 |
實戰演示
1. REPLACE INTO —— 全量覆蓋
-- 僅指定 id,name → type 變 NULL
REPLACE INTO test1(id,name) SELECT id,name FROM test2;
-- 結果：id=101 的 type 變 NULL
-- 全列指定 → 完整覆蓋
REPLACE INTO test1(id,name,type) SELECT id,name,type FROM test2;
-- 結果：id=101 變為 (101,'xxx',5)
2. INSERT ... ON DUPLICATE KEY UPDATE —— 精準更新
-- 僅同步 name，保留 test1 原有 type
INSERT INTO test1(id,name,type) SELECT id,name,type FROM test2
ON DUPLICATE KEY UPDATE test1.name = test2.name;
-- 結果：id=101 變為 (101,'xxx',1) ← type 保持原值 1
3. INSERT IGNORE —— 靜默跳過
INSERT IGNORE INTO test1(id,name,type) SELECT id,name,type FROM test2;
-- 結果：id=101 保持 (101,'aaa',1) 不變，新 id 201~203 寫入
進階技巧與避坑
VALUES(col) 引用插入值：ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt)REPLACE 觸發 DELETE + INSERT 觸發器，外鍵級聯需謹慎IGNORE 會吞噬所有錯誤（不僅重複鍵），調試難，建議配合應用層檢查- 批量操作時，
ON DUPLICATE KEY UPDATE 性能優於逐條 SELECT 判斷
選型決策樹
需要保留舊行其他列？ → 是 → ON DUPLICATE KEY UPDATE
↓ 否
允許靜默丟棄新數據？ → 是 → INSERT IGNORE
↓ 否
需完全用新數據覆蓋？ → 是 → REPLACE INTO
