萬盛學電腦網

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

MySQL 刪除重複資料高效方案:臨時表法與 ALTER IGNORE

問題背景

表 v_togo 中 tel 欄位有重複,需保留每組 tel 的最小 togoid,刪除其餘行。直接寫 DELETE ... WHERE tel IN (subquery) 會報錯:You can't specify target table 'v_togo' for update in FROM clause。

方案一:臨時表三步走(通用、安全)

-- 1. 建立臨時表,存每組 tel 的最小 togoid
CREATE TABLE tmp AS
SELECT MIN(togoid) AS toid FROM v_togo GROUP BY tel;

-- 2. 刪除不在臨時表中的行
DELETE FROM v_togo WHERE togoid NOT IN (SELECT toid FROM tmp);

-- 3. 清理臨時表
DROP TABLE tmp;
  • 適用於任何版本、任何儲存引擎。
  • 大表時建議分批刪除(每次 5000~10000 行),避免長事務鎖表、Undo Log 膨脹。

方案二:ALTER IGNORE ADD UNIQUE(MySQL 5.7 以前,極簡)

ALTER IGNORE TABLE `v_togo` ADD UNIQUE INDEX `uk_tel` (`tel`);
  • 原理: 建立唯一索引時,IGNORE 讓 MySQL 自動刪除重複行(保留第一行)。
  • 限制: MySQL 5.7+ 移除 IGNORE 子句,8.0 完全不支援。僅限舊版本緊急去重。
  • 風險: 鎖表時間長、不可控、無法指定保留哪一行(通常保留最早插入的)。

方案三:CTE + ROW_NUMBER()(MySQL 8.0+,靈活可控)

WITH ranked AS (
  SELECT togoid,
         ROW_NUMBER() OVER (PARTITION BY tel ORDER BY togoid) AS rn
  FROM v_togo
)
DELETE FROM v_togo
WHERE togoid IN (SELECT togoid FROM ranked WHERE rn > 1);
  • 可精確控制保留策略(如保留最新、保留特定狀態)。
  • 大表同樣建議分批:DELETE ... WHERE togoid IN (SELECT togoid FROM ranked WHERE rn > 1 LIMIT 5000) 循環執行。

方案四:新建表換名(超大表、停機窗口允許時最快)

CREATE TABLE v_togo_new LIKE v_togo;
ALTER TABLE v_togo_new ADD UNIQUE INDEX uk_tel (tel);
INSERT IGNORE INTO v_togo_new SELECT * FROM v_togo;
RENAME TABLE v_togo TO v_togo_old, v_togo_new TO v_togo;
DROP TABLE v_togo_old;
  • 利用 INSERT IGNORE 自動跳過重複鍵,避免大事務。
  • 需預留雙倍磁碟空間。

實戰建議

  1. 小表(<10萬行): 方案一或三均可,秒級完成。
  2. 中表(10萬~千萬): 方案三分批刪除,或方案四換表。
  3. 大表(億級+): 僅用方案四,配合 pt-online-schema-change 或 gh-ost 線上無鎖變更。
  4. 去重前務必 備份,驗證保留行正確性後再提交。
copyright © 萬盛學電腦網 all rights reserved