MySQL REPLACE INTO 與 INSERT INTO 差異深度解析

核心差異

REPLACE 與 INSERT 語法相似，關鍵不同：若新行與現有行在 PRIMARY KEY 或 UNIQUE 索引上值重複，先刪除舊行，再插入新行。

前置條件

• 表必須擁有 PRIMARY KEY 或 UNIQUE 索引，否則 REPLACE 退化為普通 INSERT。
• 需同時具備表的 INSERT 和 DELETE 權限。

執行算法

1. 嘗試插入新行。
2. 若因主鍵/唯一鍵衝突失敗：
   1. 刪除衝突行。
   2. 再次嘗試插入新行。

同理應用於 LOAD DATA ... REPLACE。

返回值含義

REPLACE 返回受影響行數 = 刪除行數 + 插入行數：

• 1：純插入，無刪除。
• >1：發生替換（如 1 刪除 + 1 插入 = 2）。
• 多個唯一索引時，單行可能替換多行舊數據（不同索引衝突）。

列值賦值規則

• 所有列取自 REPLACE 語句指定值。
• 未指定列 → 默認值（同 INSERT）。
• 不可引用當前行舊值：SET col = col + 1 中右側 col 被視為 DEFAULT(col)，等價於 SET col = DEFAULT(col) + 1。

語法變體

-- VALUES 形式
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl [(col,...)] VALUES (...),(...),...;

-- SET 形式
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl SET col=expr, ...;

-- SELECT 形式
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl [(col,...)] SELECT ...;

修飾符影響

C API 獲取影響行數

mysql_affected_rows(conn);  // 返回刪除+插入總行數

限制與注意事項

• 不可在子查詢中對同表 REPLACE 同時 SELECT（如 REPLACE INTO t SELECT * FROM t WHERE ...）。
• 觸發器：BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT 均會觸發。
• 自增列：若刪除舊行並插入新行，自增值按新行指定值或自動生成，不保留舊自增值。
• 外鍵：若被引用行被刪除，受 ON DELETE 動作影響（RESTRICT 會報錯阻止 REPLACE）。

選型指南