前提：表有 PRIMARY KEY 或 UNIQUE INDEX
方案一：INSERT IGNORE
INSERT IGNORE INTO t (email,phone,user_id) VALUES ('a@b.com','123',1);
-- 重複靜默忽略，返回 0
方案二：REPLACE INTO
REPLACE INTO t (email,phone,user_id) VALUES ('a@b.com','123',1);
-- 衝突則刪舊插新，返回 影響行數（1=新增，2=替換）
方案三：ON DUPLICATE KEY UPDATE（推薦）
INSERT INTO t (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
-- 單行衝突更新，多行批量亦可用 VALUES(col)
方案四：應用層 SELECT 判斷
SELECT * FROM t WHERE phone='123';
IF rows==0 THEN INSERT ... ELSE UPDATE ...
對比總結
| 方案 | 行為 | 適用場景 |
|---|
| IGNORE | 衝突不報錯、不執行 | 單純去重、不關心舊數據 |
| REPLACE | 刪舊插新、自增 ID 會變 | 需全字段覆蓋、可接受 PK 變化 |
| ON DUPLICATE KEY UPDATE | 精確控制更新列、保留 PK | 計數器增量、部分字段更新、最通用 |
刪除重複數據 SQL
-- 單字段
DELETE FROM t WHERE id IN (SELECT id FROM t GROUP BY id HAVING COUNT(*)>1)
AND id NOT IN (SELECT MIN(id) FROM t GROUP BY id HAVING COUNT(*)>1);
-- 多字段
DELETE FROM t WHERE (a,b) IN (SELECT a,b FROM t GROUP BY a,b HAVING COUNT(*)>1)
AND rowid NOT IN (SELECT MIN(rowid) FROM t GROUP BY a,b HAVING COUNT(*)>1);
