場景一:完全重複列 (所有欄位相同)
-- 1. 建立去重暫存表
CREATE TABLE tmp AS SELECT DISTINCT * FROM source_table;
-- 2. 替換原表
DROP TABLE source_table;
RENAME TABLE tmp TO source_table;
-- 3. 加上主鍵/唯一索引防止再重複
ALTER TABLE source_table ADD PRIMARY KEY (id);
場景二:關鍵欄位重複 (如 name+address),保留第一筆
-- 方案 A: 使用 ROW_NUMBER() (MySQL 8.0+)
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY name, address ORDER BY id) AS rn
FROM source_table
)
DELETE FROM source_table
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
-- 方案 B: 自增欄位 + 子查詢 (舊版通用)
ALTER TABLE source_table ADD COLUMN auto_id INT AUTO_INCREMENT PRIMARY KEY;
DELETE t1 FROM source_table t1
JOIN (
SELECT MIN(auto_id) AS keep_id
FROM source_table
GROUP BY name, address
) t2 ON t1.auto_id > t2.keep_id
AND t1.name = t2.name
AND t1.address = t2.address;
-- 清理
ALTER TABLE source_table DROP COLUMN auto_id;
查找重複資料
-- 找出重複鍵值
SELECT name, address, COUNT(*)
FROM source_table
GROUP BY name, address
HAVING COUNT(*) > 1;
-- 顯示所有重複列 (含第一筆)
SELECT * FROM source_table
WHERE (name, address) IN (
SELECT name, address FROM source_table
GROUP BY name, address HAVING COUNT(*) > 1
);
預防重複的最佳實踐
- 建表時加
UNIQUE INDEX (name, address)
- 寫入前用
INSERT IGNORE 或 ON DUPLICATE KEY UPDATE
- 定期排程檢查
HAVING COUNT(*) > 1