萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL INSERT IGNORE vs INSERT INTO vs REPLACE INTO:語義差異與選型指南

核心差異一覽

語句主鍵/唯一鍵衝突時行為適用場景
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);

選型決策樹

  1. 業務要求「不存在才插入,存在則保持原樣」 → INSERT IGNORE(確保無其他約束風險)。
  2. 業務要求「不存在則插入,存在則更新部分列」 → INSERT ... ON DUPLICATE KEY UPDATE(首選)。
  3. 業務要求「不存在則插入,存在則整行替換」且無外鍵依賴 → REPLACE INTO。
  4. 業務要求「必須報錯讓上層感知衝突」 → INSERT INTO。
copyright © 萬盛學電腦網 all rights reserved