MySQL 秒級去重：ALTER IGNORE TABLE 加唯一索引法

MySQL 秒級去重：ALTER IGNORE TABLE 加唯一索引法

面對幾百萬筆資料需要去重，DELETE JOIN 或臨時表法耗時較久。MySQL 5.7 前提供一招極簡做法：加唯一索引並忽略錯誤，自動刪除重複列，秒級完成。

實戰演示

-- 1. 建測試表並插入重複資料
DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, NAME VARCHAR(10));
INSERT INTO test VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(1,'a1');

-- 2. 查看重複
SELECT * FROM test;
-- +------+------+
-- | id   | NAME |
-- +------+------+
-- | 1    | a1   |
-- | 2    | a2   |
-- | 3    | a3   |
-- | 4    | a4   |
-- | 1    | a1   |  ← 重複
-- +------+------+

-- 3. 一鍵去重（保留第一筆）
ALTER IGNORE TABLE test ADD UNIQUE INDEX idx_id_name (id, NAME);
-- Query OK, 5 rows affected, 1 warning (0.02 sec)
-- Records: 5  Duplicates: 1  Warnings: 1

-- 4. 驗證結果
SELECT * FROM test;
-- +------+------+
-- | id   | NAME |
-- +------+------+
-- | 1    | a1   |
-- | 2    | a2   |
-- | 3    | a3   |
-- | 4    | a4   |  ← 重複已移除
-- +------+------+

核心機制

• IGNORE：告訴 MySQL 遇到重複鍵錯誤不中斷，直接丟棄當前行並繼續。
• UNIQUE INDEX：定義唯一性約束，重複行違反約束被 IGNORE 丟棄。
• 保留策略：先遇到的行保留，後遇到的刪除（依聚簇索引順序）。

版本與限制

MySQL 8.0 替代方案

-- 1. 使用 ROW_NUMBER() 保留首筆
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id, NAME ORDER BY id) AS rn
  FROM test
)
DELETE FROM test WHERE (id, NAME) IN (
  SELECT id, NAME FROM ranked WHERE rn > 1
);

-- 2. 或建新表去重再 RENAME
CREATE TABLE test_dedup AS
SELECT * FROM test GROUP BY id, NAME;
RENAME TABLE test TO test_old, test_dedup TO test;

適用場景與風險

• ✅ 適合：中小表（< 5000 萬行）、一次性清理、可接受「保留首筆」策略。
• ❌ 不適合：需保留「最新筆」或依業務邏輯保留、表有外鍵被引用（加索引會鎖表）、MySQL 8.0 環境。
• ⚠️ 風險：操作期間鎖表，生產庫建議低峰期或從庫執行。

這招「加索引去重」是 DBA 工具箱裡的利器，用對版本、用對場景，能把小時級清理壓縮到秒級。

1. 上一篇文章： MySQL 行轉列實戰：CASE WHEN 配合 UNION 實現動態樞紐分析
2. 下一篇文章： MySQL 備份與還原完整指南：mysqldump 參數、錯誤排查與導入最佳實踐

MySQL 綜合教學

MySQL 子查詢全類型詳解：標量、列、行、表子查詢與 EXISTS 實戰模式

MySQL 子查詢全類型詳解：標量、列、行、表子查詢與 EX

MySQL 中文亂碼根因分析與三層編碼設定解決方案

亂碼問題的三大核心層面 MySQL 亂碼問題看似複雜，實則源

Windows 下 MySQL 圖形化安裝配置與中文亂碼徹底解決

MySQL Server + Workbench 圖形化安裝

Sun 推出伺服器專用 MySQL 解決方案：CoolThreads + Open Storage + ZFS 整合效能倍增

方案概覽 Sun Microsystems 整合 MySQL