業務開發中常需「不存在則插入,存在則更新」的 upsert 語義。MySQL 提供三種主要實現方式,本文詳細對比其語法、行為差異與適用場景。
MySQL 專有擴展,基於主鍵/唯一索引衝突判斷,性能最佳。
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c = c + 1;
若 a 為唯一鍵且值 1 已存在,等價於:
UPDATE table SET c = c + 1 WHERE a = 1;
-- 多行插入,VALUES() 引用插入值
INSERT INTO table (a,b,c) VALUES (1,2,3), (4,5,6)
ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);
-- 第1行衝突:c=1+2=3;第2行衝突:c=4+5=9
-- 配合 AUTO_INCREMENT 保持 LAST_INSERT_ID() 有效
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), c = 3;
DELAYED 選項會被忽略WHERE 子句進一步過濾跨數據庫兼容性好,但性能較差,需子查詢掃描。
INSERT INTO clients (client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (
SELECT 1 FROM clients WHERE clients.client_id = suppliers.supplier_id
);
INSERT INTO clients (client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM clients WHERE client_id = 10345
);
dual 是虛擬表,用於無表單行查詢。
語法簡單但行為激進:衝突時先刪除舊行再插入新行。
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;
1:純插入(無衝突)>1:發生替換(刪除 + 插入行數之和)| 維度 | ON DUPLICATE KEY UPDATE | INSERT ... WHERE NOT EXISTS | REPLACE INTO |
|---|---|---|---|
| 性能 | ⭐⭐⭐ 最優 | ⭐⭐ 子查詢開銷 | ⭐⭐ 刪除+插入 |
| 保持主鍵值 | ✅ 是 | ✅ 是 | ❌ 變更 |
| 觸發器友好 | ✅ 僅 UPDATE | ✅ 僅 INSERT | ❌ DELETE+INSERT |
| 標準 SQL | ❌ MySQL 專有 | ✅ 跨庫兼容 | ✅ 標準 SQL |
| 適用場景 | 高併發 upsert、計數器更新 | 異構遷移、無唯一索引表 | 全行覆蓋、無外鍵依賴 |
IF NOT EXISTS (SELECT 1 FROM t WHERE phone = '1')
INSERT INTO t (phone, update_time) VALUES ('1', GETDATE());
ELSE
UPDATE t SET update_time = GETDATE() WHERE phone = '1';
-- 或使用 MERGE (SQL Server 2008+)
MERGE INTO t AS target
USING (VALUES ('1', GETDATE())) AS source (phone, update_time)
ON target.phone = source.phone
WHEN MATCHED THEN UPDATE SET update_time = source.update_time
WHEN NOT MATCHED THEN INSERT (phone, update_time) VALUES (source.phone, source.update_time);
ON DUPLICATE KEY UPDATE:性能最好、語義清晰、保持主鍵穩定REPLACE INTO