表 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;
ALTER IGNORE TABLE `v_togo` ADD UNIQUE INDEX `uk_tel` (`tel`);
IGNORE 讓 MySQL 自動刪除重複行(保留第一行)。IGNORE 子句,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 自動跳過重複鍵,避免大事務。pt-online-schema-change 或 gh-ost 線上無鎖變更。