MySQL 去除重複數據實戰：導出-清空-加唯一索引-導入覆蓋/保留舊數據

場景

表 user 無唯一約束，name 列出現重複（含大小寫差異），需去重並加唯一索引。直接 ALTER TABLE ADD UNIQUE 會報錯。

表結構與樣本數據

CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` CHAR(10) NOT NULL DEFAULT '',
  `extra` CHAR(10) NOT NULL DEFAULT ''
);

-- 數據含重複：user3、user2、USER2、user6、USER6（大小寫視為不同值）

四步去重法

1. 導出全量數據

   SELECT * INTO OUTFILE '/tmp/user.txt' FROM `user`;

2. 清空表

   TRUNCATE TABLE `user`;

3. 修改列類型區分大小寫 + 加唯一索引

   
   ALTER TABLE `user` MODIFY `name` CHAR(10) BINARY NOT NULL DEFAULT '';
   ALTER TABLE `user` ADD UNIQUE KEY (`name`);
   

   BINARY 使 CHAR 按二進制比較，user2 ≠ USER2。

4. 導入數據，兩種去重策略二選一

   
   -- 策略 A：新數據覆蓋舊數據（保留最新記錄）
   LOAD DATA INFILE '/tmp/user.txt' REPLACE INTO TABLE `user`;
   -- 結果：刪除 2 條重複，保留 id=8(user2)、id=6(user3) 等後寫入記錄
   
   -- 策略 B：跳過新數據（保留最舊記錄）
   LOAD DATA INFILE '/tmp/user.txt' IGNORE INTO TABLE `user`;
   -- 結果：跳過 2 條重複，保留 id=2(user2)、id=3(user3) 等首寫入記錄
   

關鍵點總結

• REPLACE：唯一鍵衝突時刪除舊行 + 插入新行（自增 ID 會變）。
• IGNORE：唯一鍵衝突時忽略新行，保留舊行。
• BINARY 屬性對大小寫敏感去重至關重要。
• 導出文件權限需 mysqld 進程用戶可寫，目標文件不可存在（安全限制）。
• 大表操作建議低峰期執行，TRUNCATE 瞬時完成，LOAD DATA 速度極快。

替代方案（不停機）

-- 創建新表結構
CREATE TABLE `user_new` LIKE `user`;
ALTER TABLE `user_new` MODIFY `name` CHAR(10) BINARY NOT NULL DEFAULT '';
ALTER TABLE `user_new` ADD UNIQUE KEY (`name`);

-- 去重寫入（保留最舊）
INSERT IGNORE INTO `user_new` SELECT * FROM `user`;

-- 原子切換
RENAME TABLE `user` TO `user_old`, `user_new` TO `user`;

適合無法承受寫入停機的場景，需處理切換期間增量同步（觸發器/應用雙寫/CDC）。