MySQL 避免重複插入的三種方法

問題背景

業務常見需求：資料不存在則插入，存在則更新 (或忽略)。MySQL 提供原生語法支援，無需應用層先查後寫，避免競態條件。

方法一：INSERT IGNORE (忽略重複，不報錯)

-- 唯一鍵衝突時忽略，不插入，不報錯，返回 0 rows affected
INSERT IGNORE INTO users (id, username, email) VALUES (1, 'alice', 'alice@example.com');

• 適用：不需要更新，僅保證不重複插入。
• 副作用：其他錯誤 (如外鍵失敗、欄位過長) 也會被忽略並轉為警告，需謹慎使用。

方法二：REPLACE INTO (刪除舊行再插入新行)

-- 若唯一鍵衝突，先 DELETE 舊行，再 INSERT 新行
REPLACE INTO users (id, username, email, updated_at) VALUES (1, 'alice', 'new@example.com', NOW());

• 本質：DELETE + INSERT，會觸發 ON DELETE 觸發器、外鍵級聯、自增 ID 可能改變。
• 適用：全行覆蓋，且可接受副作用。

方法三：INSERT ... ON DUPLICATE KEY UPDATE (Upsert，推薦)

-- 唯一鍵衝突時執行 UPDATE，不衝突則 INSERT
INSERT INTO users (id, username, email, login_count, last_login)
VALUES (1, 'alice', 'alice@example.com', 1, NOW())
ON DUPLICATE KEY UPDATE
  login_count = login_count + 1,
  last_login = NOW(),
  email = VALUES(email);  -- VALUES(col) 取得插入值

• 精確控制衝突時的更新邏輯，不刪除舊行，保留自增 ID、觸發器不觸發 DELETE。
• VALUES(col) 可引用插入值，避免重複寫字面量。
• 可用 LAST_INSERT_ID() 獲取自增 ID (衝突時返回現有 ID)。

對比總結

前置條件

表必須有 PRIMARY KEY 或 UNIQUE KEY，否則無法判定「重複」，語法會報錯或全表掃描。

進階技巧

-- 批次 Upsert
INSERT INTO users (id, name, score) VALUES 
  (1, 'a', 10), (2, 'b', 20), (3, 'c', 30)
ON DUPLICATE KEY UPDATE score = VALUES(score);

-- 條件更新：僅當新值更大時更新
INSERT INTO stats (date, metric, value) VALUES ('2024-01-15', 'pv', 1000)
ON DUPLICATE KEY UPDATE value = GREATEST(value, VALUES(value));

-- 使用 LAST_INSERT_ID(expr) 使 ON DUPLICATE 也能返回自增 ID
INSERT INTO t (id, val) VALUES (NULL, 100)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), val = VALUES(val);
SELECT LAST_INSERT_ID(); -- 返回 INSERT 或 UPDATE 的 id

最佳實踐

1. 優先使用 INSERT ... ON DUPLICATE KEY UPDATE，語義清晰、副作用最小。
2. 確保唯一鍵設計合理 (業務鍵 vs 自增鍵)。
3. 大批次操作分塊 (如 1000 行/批)，避免長事務、鎖升級、Binlog 膨脹。
4. 監控 SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted/updated/deleted'; 觀察寫入模式。