數據去重是數據治理的高頻剛需。MySQL 中刪除重復記錄的方法主要有四類:**自聯結刪除、唯一索引去重、新建表去重、窗口函數去重 (MySQL 8.0+)**。本文通過標準化演示數據,詳細對比四大方案的適用場景、執行原理、性能特徵、數據保真風險 (索引/約束/自增 ID 丟失),並提供生產級操作規範 (備份、驗證、回滾、監控)。
CREATE TABLE `demo` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`site` VARCHAR(100) NOT NULL DEFAULT '',
KEY `idx_site` (`site`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `demo` (`site`) VALUES
('https://www.example.com'),
(''),
('https://www.example.com'),
('https://www.example.com'),
('https://www.example.com');
-- 數據狀態
-- id=1, site='https://www.example.com'
-- id=2, site=''
-- id=3, site='https://www.example.com' -- 重複
-- id=4, site='https://www.example.com' -- 重複
-- id=5, site='https://www.example.com' -- 重複
表別名自聯結,比較主鍵大小決定保留哪條:a.id > b.id 刪除較新 (保留最早),a.id < b.id 刪除較舊 (保留最新)。
DELETE a
FROM `demo` AS a
INNER JOIN `demo` AS b ON a.site = b.site
WHERE a.id < b.id;
DELETE a
FROM `demo` AS a
INNER JOIN `demo` AS b ON a.site = b.site
WHERE a.id > b.id;
SELECT a.*
FROM `demo` a
INNER JOIN `demo` b ON a.site = b.site
WHERE a.id > b.id; -- 保留最早,查看將刪除的較新記錄
| 優點 | 缺點 |
|---|---|
| 無需索引/建表權限 | 大表性能差 (O(n²) 自聯結),需分批處理 |
| 精確控制保留策略 (最早/最新) | 無法處理「完全重複行」(無主鍵表) |
| 原地刪除,無數據遷移風險 | 長事務、鎖升級、主從延遲風險 |
ALTER IGNORE TABLE ... ADD UNIQUE INDEX:構建唯一索引過程中自動刪除重複鍵行,**保留第一行 (按聚簇索引順序,通常是最小主鍵)**。
-- 1. 添加唯一索引 (IGNORE 關鍵字關鍵!)
ALTER IGNORE TABLE `demo` ADD UNIQUE INDEX `uk_site` (`site`);
-- 2. 驗證結果
SELECT * FROM `demo` ORDER BY id;
-- 保留 id=1,2,3 (site='' 和 site='http...' 首次出現)
-- 3. (可選) 刪除索引恢復原狀
ALTER TABLE `demo` DROP INDEX `uk_site`;
IGNORE 子句已移除 (5.7.4 棄用,8.0 移除)。新版本無法直接使用此法。INSERT IGNORE INTO new SELECT * FROM old → 重命名。CREATE TABLE `demo_new` AS
SELECT * FROM `demo` GROUP BY `site`; -- 隱式分組去重,保留每組第一行
-- 風險:丟失 PK、UK、FK、AUTO_INCREMENT、索引、分區等元數據!
-- 1. 獲取原表完整建表語句
SHOW CREATE TABLE `demo`;
-- 輸出: CREATE TABLE `demo` (...) ENGINE=InnoDB AUTO_INCREMENT=6 ...
-- 2. 按原結構建新表 (複製結構不複製數據)
CREATE TABLE `demo_new` LIKE `demo`;
-- 3. 去重插入 (保留最小 id)
INSERT INTO `demo_new`
SELECT * FROM `demo`
WHERE id IN (
SELECT MIN(id) FROM `demo` GROUP BY `site`
);
-- 或使用 LEFT JOIN 反模式 (舊版本優化)
INSERT INTO `demo_new`
SELECT d1.* FROM `demo` d1
LEFT JOIN `demo` d2 ON d1.site = d2.site AND d1.id > d2.id
WHERE d2.id IS NULL;
-- 4. 原子化切換 (RENAME 原子操作,無鎖阻塞)
RENAME TABLE `demo` TO `demo_old`, `demo_new` TO `demo`;
-- 5. 驗證無誤後清理舊表
DROP TABLE `demo_old`;
| 優點 | 缺點 |
|---|---|
| 大表高性能 (單次全掃描 + 批量插入) | 需建表權限、臨時佔用雙倍空間 |
| 完整保留表結構 (PK、UK、FK、索引、AUTO_INCREMENT) | 切換瞬間極短暫寫入阻塞 (RENAME 元數據鎖) |
| 可驗證新表數據後再切換 | 外鍵引用表需同步處理 (禁用 FK 或級聯) |
| 無長事務、鎖競爭小 |
-- 保留每組 site 的最小 id (最早記錄)
WITH ranked AS (
SELECT
id, site,
ROW_NUMBER() OVER (PARTITION BY site ORDER BY id ASC) AS rn
FROM `demo`
)
DELETE FROM `demo`
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
-- 保留每組 site 的最大 id (最新記錄)
WITH ranked AS (
SELECT
id, site,
ROW_NUMBER() OVER (PARTITION BY site ORDER BY id DESC) AS rn
FROM `demo`
)
DELETE FROM `demo`
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
-- 保留每組 site 的最新更新時間 (假設有 updated_at)
WITH ranked AS (
SELECT
id, site,
ROW_NUMBER() OVER (PARTITION BY site ORDER BY updated_at DESC) AS rn
FROM `demo`
)
DELETE FROM `demo`
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
優勢:標準 SQL、語義顯式、支持任意排序規則 (最新、最舊、優先級)、單條 SQL 完成、優化器友好。
<?php
// 適用於方案一、四 的大表分批執行
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "user", "pass", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
$batchSize = 1000;
$deletedTotal = 0;
while (true) {
// 使用 LIMIT 限制影響行數 (MySQL 支持 DELETE ... LIMIT)
$sql = "DELETE a FROM `demo` a
INNER JOIN `demo` b ON a.site = b.site
WHERE a.id > b.id
LIMIT $batchSize";
$affected = $pdo->exec($sql);
$deletedTotal += $affected;
echo "Batch deleted: $affected, Total: $deletedTotal\n";
if ($affected < $batchSize) break; // 刪完了
usleep(100000); // 0.1秒間隙,緩解主從壓力
}
echo "Done. Total deleted: $deletedTotal\n";
?>
Threads_running、Innodb_row_lock_waits、主從延遲 Seconds_Behind_Master。demo_old 表 24-48h,確認無誤再 Drop。MySQL 去重無銀彈,只有場景適配:
核心原則:**備份優先、分批執行、驗證可逆、監控全程**。數據無價,謹慎為上。