核心差異一覽
| 語句 | 主鍵/唯一鍵衝突時行為 | 適用場景 |
INSERT INTO | 報錯(Duplicate entry),事務回滾 | 常規插入,業務要求唯一性強約束 |
INSERT IGNORE | 忽略錯誤,不插入,不報錯,返回 0 行受影響 | 批量導入、去重、日誌寫入「不重複即可」 |
REPLACE INTO | 刪除舊行,插入新行(實質 DELETE + INSERT) | 「覆蓋式」寫入,需保證主鍵存在即更新 |
實戰演示
CREATE TABLE `testtb` (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50),
`age` INT
);
-- 初始數據
INSERT INTO testtb VALUES (1, 'www.111cn.net', 13);
SELECT * FROM testtb;
-- +----+---------------+-----+
-- | id | name | age |
-- +----+---------------+-----+
-- | 1 | www.111cn.net | 13 |
-- +----+---------------+-----+
-- 1. INSERT IGNORE:主鍵衝突,靜默跳過
INSERT IGNORE INTO testtb VALUES (1, 'aa', 13);
SELECT * FROM testtb;
-- 結果無變化,仍為 (1, 'www.111cn.net', 13)
-- 2. REPLACE INTO:主鍵衝突,覆蓋整行
REPLACE INTO testtb VALUES (1, 'aa', 12);
SELECT * FROM testtb;
-- +----+------+-----+
-- | id | name | age |
-- +----+------+-----+
-- | 1 | aa | 12 |
-- +----+------+-----+
深度解析
INSERT IGNORE 隱藏風險
- 不僅忽略主鍵/唯一鍵衝突,也會忽略其他錯誤:如外鍵約束失敗、列非空違反、數據截斷等,全部變為 Warning,極易掩蓋數據質量問題。
- 返回值:
affected_rows = 0,無法區分「已存在」與「其他錯誤」。
REPLACE INTO 陷阱
- 觸發 DELETE + INSERT 兩個操作:
① 刪除舊行 → 觸發 ON DELETE 觸發器、外鍵級聯刪除。
② 插入新行 → 自增 ID 可能改變(若主鍵非自增則不變)。
- 若表有外鍵被引用,REPLACE 會導致子表關聯行被級聯刪除,極其危險。
ON DUPLICATE KEY UPDATE(推薦替代方案)
INSERT INTO testtb (id, name, age) VALUES (1, 'bb', 14)
ON DUPLICATE KEY UPDATE name='bb', age=14;
- ✅ 精確控制衝突時更新哪些列。
- ✅ 不觸發 DELETE,避免外鍵級聯、觸發器副作用。
- ✅ 返回值:1=新插入,2=更新現有行,可區分業務邏輯。
批量插入語法糖
-- 多值單條 INSERT(高效)
INSERT INTO `news` (title, body, time) VALUES
('title 1', 'body 1', NOW()),
('title 2', 'body 2', NOW()),
('title 3', 'body 3', NOW());
-- 配合 IGNORE / ON DUPLICATE KEY
INSERT IGNORE INTO `news` (title, body) VALUES (...), (...);
INSERT INTO `news` (title, body) VALUES (...), (...)
ON DUPLICATE KEY UPDATE body=VALUES(body);
選型決策樹
- 業務要求「不存在才插入,存在則保持原樣」 →
INSERT IGNORE(確保無其他約束風險)。
- 業務要求「不存在則插入,存在則更新部分列」 →
INSERT ... ON DUPLICATE KEY UPDATE(首選)。
- 業務要求「不存在則插入,存在則整行替換」且無外鍵依賴 →
REPLACE INTO。
- 業務要求「必須報錯讓上層感知衝突」 →
INSERT INTO。