MySQL REPLACE INTO 詳解：實現「不存在則插入，存在則更新」的最佳實踐

核心語法

REPLACE INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
-- 或
REPLACE INTO table_name (col1, col2, ...) SELECT ...;
-- 或
REPLACE INTO table_name SET col1=val1, col2=val2, ...;

關鍵前提： 表**必須有主鍵或唯一索引**。否則 REPLACE INTO 退化為純 INSERT，導致重複數據。

執行邏輯

1. 嘗試插入新行。
2. 若發生 **主鍵衝突** 或 **唯一索引衝突**：
   1. 刪除衝突的舊行 (觸發 ON DELETE 觸發器、外鍵級聯)。
   2. 插入新行 (自增 ID 會改變)。
3. 若無衝突：直接插入新行。

對比：INSERT ... ON DUPLICATE KEY UPDATE (IODKU)

實戰示例

場景：用戶最後登錄時間記錄表

CREATE TABLE user_last_login (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    last_login_time DATETIME NOT NULL,
    login_count INT UNSIGNED DEFAULT 0,
    UNIQUE KEY uk_user (user_id)
);

-- 方案 A: REPLACE INTO (會重置 login_count 為 0，除非顯式帶上)
REPLACE INTO user_last_login (user_id, last_login_time) 
VALUES (1001, NOW());
-- 結果：login_count 變為 0 (默認值)，user_id 同但內部行 ID 可能變

-- 方案 B: IODKU (推薦，保留原有列值，原子累加)
INSERT INTO user_last_login (user_id, last_login_time, login_count)
VALUES (1001, NOW(), 1)
ON DUPLICATE KEY UPDATE 
    last_login_time = VALUES(last_login_time),
    login_count = login_count + 1;
-- 結果：last_login_time 更新，login_count 遞增，主鍵 ID 不變

三種寫法詳解

1. VALUES 形式 (最常用)

REPLACE INTO users (id, name, email, updated_at) 
VALUES (1, '張三', 'zhangsan@example.com', NOW());

2. SELECT 形式 (異構表同步)

REPLACE INTO settings SET 
    `key` = 'site_title',
    `value` = '我的博客',
    updated_at = NOW();

避坑指南

1. 必須有主鍵/唯一索引，否則無法判斷「存在」。
2. 慎用於有外鍵的表：刪除舊行會觸發 ON DELETE CASCADE/SET NULL，導致關聯數據誤刪/誤改。
3. 自增主鍵會跳變/重用：高頻 REPLACE 導致自增 ID 快速耗盡或碎片化。
4. 默認值陷阱：未在 VALUES 中指定的列會被重置為默認值，而非保留舊值。
5. 權限要求：需同時擁有 INSERT 和 DELETE 權限。

總結建議

90% 以上場景請使用 INSERT ... ON DUPLICATE KEY UPDATE。
僅在以下極少數情況考慮 REPLACE INTO：
① 需要物理上完全替換行（如表結構變更後的數據遷移）。
② 表無外鍵、無觸發器、業務不依賴自增 ID、且確確實實想「刪舊插新」。
③ 兼容極老版本 MySQL (3.22 前) 不支持 IODKU 的場景（現已絕跡）。