萬盛學電腦網

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

MySQL INSERT IGNORE vs INSERT INTO vs REPLACE INTO:語義、效能、陷阱全解析

三者核心差異速查

語句主鍵/唯一鍵衝突時非衝突列行為返回值
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)
copyright © 萬盛學電腦網 all rights reserved