萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 刪除重複資料完整策略:完全重複與關鍵欄位重複的標準解法

場景一:完全重複列 (所有欄位相同)

-- 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
);

預防重複的最佳實踐

  1. 建表時加 UNIQUE INDEX (name, address)
  2. 寫入前用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE
  3. 定期排程檢查 HAVING COUNT(*) > 1
copyright © 萬盛學電腦網 all rights reserved