MySQL REPLACE INTO 完整指南：語法、原理與實戰應用

MySQL REPLACE INTO 深度解析

REPLACE 語句的運作機制與 INSERT 非常相似，但有一個關鍵差異：當表中已存在的記錄與新記錄在 PRIMARY KEY 或 UNIQUE 索引 上具有相同值時，舊記錄會在新記錄插入前被刪除。這使得 REPLACE 成為「不存在則插入，存在則更新」的便捷工具。

核心前提條件

除非表擁有 PRIMARY KEY 或 UNIQUE 索引，否則使用 REPLACE 語句毫無意義——它將表現得與 INSERT 完全一致，因為沒有索引可用於判斷新行是否複製了其他行。

列值處理規則

• 所有列的值均取自 REPLACE 語句中指定的值
• 缺失的列會被設置為各自的默認值（同 INSERT）
• 無法從當前行引用值，也不能在新行中使用舊值
• 若使用 SET col_name = col_name + 1 這類賦值，右側列名會被視為 DEFAULT(col_name)，等價於 SET col_name = DEFAULT(col_name) + 1

權限要求

使用 REPLACE 需同時具備表的 INSERT 和 DELETE 權限。

返回值與影響行數判斷

REPLACE 返回一個數字，表示受影響行數（被刪除行數 + 被插入行數）：

• 返回 1：僅插入一行，無刪除
• 返回 > 1：插入前刪除了一或多行舊數據
• 若表含多個唯一索引，且新行在不同索引中匹配不同舊行，單行可能替換多行

在 C API 中可用 mysql_affected_rows() 獲取此值。

限制事項

目前不支持在子查詢中向同一表替換數據的同時從該表選擇數據。

執行算法詳解

1. 嘗試將新行插入表中
2. 若因主鍵或唯一鍵重複導致插入失敗：
   • 刪除含有重複鍵值的衝突行
   • 再次嘗試插入新行

此算法亦用於 LOAD DATA ... REPLACE。

語法格式

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...