MySQL REPLACE INTO 實戰指南：Upsert 語義的簡潔實現

REPLACE INTO 是 MySQL 特有的「插入或替換」語句，簡化了「存在則更新、不存在則插入」(Upsert) 的邏輯。本文詳細說明語法、行為、三種形式、與 INSERT ... ON DUPLICATE KEY UPDATE 的差異、效能考量。

核心邏輯

1. 嘗試插入新行
2. 若主鍵或唯一索引衝突：先刪除舊行，再插入新行
3. 無衝突：直接插入

關鍵前提： 表必須有主鍵或唯一索引，否則退化為單純 INSERT，產生重複數據。

語法三種形式

-- 1. VALUES 形式 (最常用)
REPLACE INTO tbl_name (col1, col2) VALUES (val1, val2);

-- 2. SELECT 形式
REPLACE INTO tbl_name (col1, col2) SELECT col1, col2 FROM other_table;

-- 3. SET 形式
REPLACE INTO tbl_name SET col1 = val1, col2 = val2;

-- INTO 關鍵字可省略，建議保留以提高可讀性
REPLACE tbl_name (col1, col2) VALUES (val1, val2);

實戰範例：用戶最後登入時間更新

-- 表結構
CREATE TABLE user_login (
    user_id BIGINT PRIMARY KEY,
    last_login DATETIME NOT NULL,
    login_count INT DEFAULT 0
);

-- 邏輯：記錄登入時間，次數 +1
-- REPLACE INTO 寫法 (注意：會重置 login_count 為 0！)
REPLACE INTO user_login (user_id, last_login) VALUES (1001, NOW());

-- 正確寫法：INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO user_login (user_id, last_login, login_count) 
VALUES (1001, NOW(), 1)
ON DUPLICATE KEY UPDATE 
    last_login = VALUES(last_login),
    login_count = login_count + 1;

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

效能與鎖行為

• REPLACE INTO 實際執行 DELETE + INSERT，持有排他鎖 (X Lock)時間較長
• 高併發下易產生死鎖或鎖等待超時
• 批量操作建議使用 IODKU 或 LOAD DATA ... REPLACE

最佳實踐建議

1. 優先使用 IODKU：語義精確、效能更好、行為可預期
2. 僅在「全行覆蓋、無外鍵、無自增業務依賴」場景用 REPLACE
3. 必須有主鍵/唯一索引，否則失去去重功能
4. 測試驗證：檢查自增 ID 變化、觸發器執行順序、外鍵級聯行為
5. 監控指標：Innodb_rows_deleted、Innodb_rows_inserted 觀察 REPLACE 實際行為

PostgreSQL / SQL Server 對應寫法

-- PostgreSQL 9.5+
INSERT INTO tbl (id, val) VALUES (1, 'new')
ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

-- SQL Server (MERGE)
MERGE INTO tbl AS target
USING (VALUES (1, 'new')) AS source (id, val)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET val = source.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val);