MySQL 查找並刪除重複數據保留一條的高效 SQL 方案

問題場景

表 schools 中 school_name 存在重複，需刪除重複行，保留 school_id 最小或最大的一條。

示例數據

school_id | school_name              | total_student | test_takers
----------|--------------------------|---------------|------------
1239      | Abraham Lincoln High     | 55            | 50
1240      | Abraham Lincoln High     | 70            | 35  ← 重複
1241      | Acalanes High            | 120           | 89
1242      | Academy Of The Canyons   | 30            | 30
1243      | Agoura High              | 89            | 40
1244      | Agoura High              | 100           | 50  ← 重複

方案一：保留 ID 最小的一條（推薦）

DELETE t1
FROM `test` t1
INNER JOIN (
    SELECT school_name, MIN(school_id) AS min_id
    FROM `test`
    GROUP BY school_name
    HAVING COUNT(*) > 1
) t2 ON t1.school_name = t2.school_name AND t1.school_id > t2.min_id;

執行流程解析

1. 子查詢找出重複的 school_name 及其最小 school_id
2. 主查詢連接原表，刪除同名但 ID 更大的行
3. 使用 INNER JOIN 語法，MySQL 支持多表刪除，性能優於子查詢 IN

方案二：保留 ID 最大的一條

DELETE t1
FROM `test` t1
INNER JOIN (
    SELECT school_name, MAX(school_id) AS max_id
    FROM `test`
    GROUP BY school_name
    HAVING COUNT(*) > 1
) t2 ON t1.school_name = t2.school_name AND t1.school_id < t2.max_id;

方案三：通用模板（任意去重鍵、任意保留策略）

-- 保留每組 row_number = 1 的行（MySQL 8.0+）
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY school_name ORDER BY school_id ASC) AS rn
    FROM `test`
)
DELETE FROM `test`
WHERE school_id IN (SELECT school_id FROM ranked WHERE rn > 1);

方案四：創建新表去重（大數據量推薦）

CREATE TABLE `test_dedup` LIKE `test`;

INSERT INTO `test_dedup`
SELECT * FROM `test`
WHERE school_id IN (
    SELECT MIN(school_id) FROM `test` GROUP BY school_name
);

RENAME TABLE `test` TO `test_backup`, `test_dedup` TO `test`;

性能對比與建議

預防重複的最佳實踐

1. 添加唯一索引：ALTER TABLE test ADD UNIQUE KEY uk_name (school_name);
2. INSERT IGNORE / ON DUPLICATE KEY UPDATE：應用層寫入時處理衝突
3. 業務主鍵設計：若 school_name 業務唯一，考慮作為主鍵或唯一鍵

驗證去重結果

-- 檢查是否還有重複
SELECT school_name, COUNT(*) 
FROM `test` 
GROUP BY school_name 
HAVING COUNT(*) > 1;

-- 對比行數
SELECT COUNT(*) FROM `test`;
SELECT COUNT(DISTINCT school_name) FROM `test`;