MySQL REPLACE INTO 實戰指南：UPSERT 語法、陷阱避坑與性能優化

核心語法

-- 形式 1：VALUES 列表
REPLACE INTO users (id, name, email, updated_at) 
VALUES (1, '張三', 'zhang@example.com', NOW());

-- 形式 2：SELECT 子查詢
REPLACE INTO users (id, name, email, updated_at)
SELECT 1, '張三', 'zhang@example.com', NOW();

-- 形式 3：SET 語法 (MySQL 特有)
REPLACE INTO users SET id=1, name='張三', email='zhang@example.com', updated_at=NOW();

執行邏輯：DELETE + INSERT

1. 嘗試插入新行
2. 若**主鍵或唯一索引衝突**：
   1. **刪除**衝突行 (觸發 DELETE 觸發器、外鍵級聯、Binlog 記錄 DELETE)
   2. **插入**新行 (觸發 INSERT 觸發器、自增列賦值、Binlog 記錄 INSERT)
3. 若無衝突：直接插入

關鍵前提：必須有主鍵或唯一索引

-- ❌ 無主鍵/唯一索引：REPLACE 退化為 INSERT，產生重複數據
CREATE TABLE t1 (id INT, name VARCHAR(20));
REPLACE INTO t1 VALUES (1, 'a'); -- 執行 2 次得到 2 行重複數據

-- ✅ 有主鍵：正常 UPSERT
CREATE TABLE t2 (id INT PRIMARY KEY, name VARCHAR(20));
REPLACE INTO t2 VALUES (1, 'a'); -- 執行 N 次始終 1 行

REPLACE INTO vs INSERT ... ON DUPLICATE KEY UPDATE (IODKU)

致命陷阱：未指定列被重置

CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT, c TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
REPLACE INTO t (id, a) VALUES (1, 10);  -- b 變為 0 (默認), c 變為當前時間
REPLACE INTO t (id, a) VALUES (1, 20);  -- b 再次歸零！數據丟失！

解決：必須指定所有列，或改用 IODKU：

INSERT INTO t (id, a, b, c) VALUES (1, 20, 5, '2023-01-01')
ON DUPLICATE KEY UPDATE a=VALUES(a);  -- b, c 保持不變

不支持的場景：基於舊值增減

-- ❌ REPLACE 不支持
REPLACE INTO counters SET id=1, cnt=cnt+1;  -- 語法錯誤或邏輯錯誤

-- ✅ IODKU 完美支持
INSERT INTO counters (id, cnt) VALUES (1, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

性能優化建議

1. 優先使用 IODKU：原地更新、保持自增值、支持部分列、Binlog 精簡
2. 批量 UPSERT：

   
     INSERT INTO t (id, val) VALUES (1,10),(2,20),(3,30)
     ON DUPLICATE KEY UPDATE val = VALUES(val);
     

3. 避免 REPLACE 觸發級聯刪除：若有 ON DELETE CASCADE 外鍵，REPLACE 會連帶刪除關聯表數據！
4. 主鍵設計：使用無意義自增主鍵，避免業務鍵衝突導致頻繁 REPLACE

複制環境注意事項

• Row 格式 Binlog：REPLACE 記錄為 Delete_rows_event + Write_rows_event
• Statement 格式：記錄為 REPLACE INTO ...，從庫重放邏輯一致
• **主從延遲風險**：大批量 REPLACE 產生雙倍 Binlog 事件，增加從庫回放壓力

總結

REPLACE INTO 是 MySQL 早期的 UPSERT 實現，**語法簡單但語義危險** (DELETE+INSERT、重置未指定列、不支持增量更新、觸發級聯刪除)。**現代最佳實踐：全面遷移至 INSERT ... ON DUPLICATE KEY UPDATE**，獲得精確控制、更好性能、更安全的複製行為。僅在「確需重置全行、無外鍵、無自增依賴、極簡邏輯」場景下考慮 REPLACE。