三者核心差異速查
| 語句 | 主鍵/唯一鍵衝突時 | 非衝突列行為 | 返回值 |
| INSERT INTO | **報錯 1062**,終止 | 插入新行 | 受影響行數 |
| INSERT IGNORE | **忽略**,不插入、不報錯 | 插入新行 | 受影響行數(忽略的不計) |
| REPLACE INTO | **刪除舊行 → 插入新行** | 新行覆蓋(未指定列置 NULL) | 刪除+插入行數和(通常 2) |
實戰演示
CREATE TABLE testtb (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50),
age INT
);
INSERT INTO testtb VALUES (1,'bb',13);
-- 已有 id=1
INSERT IGNORE INTO testtb VALUES (1,'aa',13); -- 靜默忽略,數據仍 (1,'bb',13)
REPLACE INTO testtb VALUES (1,'aa',12); -- 變為 (1,'aa',12),age 更新
-- 若 REPLACE 不指定 age:INSERT (1,'aa',NULL) → age 變 NULL!
跨表導入場景
-- 來源表 table2 有 id=1,2,3
-- 目標表 table1 已有 id=1,2
INSERT IGNORE INTO table1 SELECT * FROM table2;
-- 結果:id=1,2 保留舊值,id=3 新增
REPLACE INTO table1 SELECT * FROM table2;
-- 結果:id=1,2 被 table2 覆蓋,id=3 新增
-- 注意:table2 缺少的列在 table1 中會變 NULL
效能與鎖行為
INSERT IGNORE:衝突時仍獲取排他鎖再釋放,高並發下鎖爭用較大
REPLACE INTO:本質是 DELETE + INSERT,**觸發器會火兩次**(BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT),外鍵級聯也會觸發
- 大批量導入建議:
INSERT INTO ... ON DUPLICATE KEY UPDATE ... 精確控制更新列,避免全列覆蓋
最佳實踐選型
- **嚴禁重複、要報錯** →
INSERT INTO
- **重複即跳過、保留舊值** →
INSERT IGNORE
- **重複即覆蓋、以新為準** →
REPLACE INTO(小心 NULL 覆蓋)
- **重複按業務邏輯合併** →
INSERT ... ON DUPLICATE KEY UPDATE col=VALUES(col)