MySQL 实战：从查重到删重，详解单字段与多字段重复数据处理全流程

1. 重复数据的定义与危害在日常数据库维护中重复数据就像仓库里堆积的冗余库存既占用空间又影响效率。我们先来看一个典型场景假设有个用户账户表accountinfo里面可能同时存在account字段重复单字段重复和accountdeviceId组合重复多字段重复的情况。单字段重复就像同一个身份证号对应多个用户记录而多字段组合重复则像是同一对姓名手机号反复出现。这两种情况都会导致存储空间浪费特别是当数据量达到百万级时查询结果不准确统计报表可能出现偏差业务逻辑混乱如重复发放优惠券或短信唯一性约束失效影响数据完整性我处理过一个电商项目因为用户表存在重复数据导致促销活动被同一用户重复参与直接损失了上万元预算。这就是为什么我们需要掌握系统的去重方法。2. 单字段重复数据处理2.1 查找单字段重复数据以accountinfo表的account字段为例查找重复数据的SQL思路就像在人群中找双胞胎分组计数先按account分组统计每组的数量SELECT account, COUNT(account) as count FROM accountinfo GROUP BY account;筛选重复项用HAVING过滤出数量大于1的分组SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) 1;获取完整记录用IN子查询获取所有重复记录的详情SELECT * FROM accountinfo WHERE account IN ( SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) 1 );2.2 删除单字段重复数据删除重复数据要像整理衣柜每类衣服只留一件其他的处理掉。这里我们保留id最小的记录找出要保留的记录每个account分组中id最小的记录SELECT MIN(id) as id FROM accountinfo WHERE account IN ( SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) 1 ) GROUP BY account;找出要删除的记录重复记录中不在保留列表的idSELECT t1.id FROM ( SELECT id FROM accountinfo WHERE account IN ( SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) 1 ) ) t1 WHERE t1.id NOT IN ( SELECT MIN(id) AS id FROM accountinfo WHERE account IN ( SELECT account FROM accountinfo GROUP BY account HAVING COUNT(account) 1 ) GROUP BY account );执行删除操作DELETE FROM accountinfo WHERE id IN (/*上面的查询语句*/);注意MySQL不允许直接在同一条语句中先SELECT再DELETE同一张表所以需要通过中间表的方式绕过这个限制。3. 多字段组合重复数据处理3.1 查找多字段重复数据当需要判断多个字段组合是否重复时如accountdeviceId方法类似但需要调整GROUP BY-- 查找重复的字段组合 SELECT account, deviceId, COUNT(*) as count FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1; -- 获取所有重复记录的详情 SELECT t.* FROM accountinfo t JOIN ( SELECT account, deviceId FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1 ) a ON t.account a.account AND t.deviceId a.deviceId;3.2 删除多字段重复数据保留每组重复数据中id最小的记录找出要保留的最小idSELECT MIN(id) as id FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1;删除其他重复记录DELETE FROM accountinfo WHERE (account, deviceId) IN ( SELECT account, deviceId FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1 ) AND id NOT IN ( SELECT MIN(id) FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1 );更安全的写法是使用临时表避免MySQL的限制-- 创建临时表存储要保留的id CREATE TEMPORARY TABLE temp_keep_ids AS SELECT MIN(id) as id FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1; -- 执行删除 DELETE FROM accountinfo WHERE (account, deviceId) IN ( SELECT account, deviceId FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1 ) AND id NOT IN (SELECT id FROM temp_keep_ids); -- 删除临时表 DROP TEMPORARY TABLE temp_keep_ids;4. 高级去重技巧与避坑指南4.1 窗口函数方案MySQL 8.0对于MySQL 8.0以上版本窗口函数让去重变得更简单-- 查找重复数据 WITH ranked_data AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY account, deviceId ORDER BY id) as row_num FROM accountinfo ) SELECT * FROM ranked_data WHERE row_num 1; -- 删除重复数据 DELETE FROM accountinfo WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY account, deviceId ORDER BY id) as row_num FROM accountinfo ) t WHERE t.row_num 1 );4.2 常见陷阱与解决方案自连接删除法DELETE a FROM accountinfo a INNER JOIN accountinfo b WHERE a.id b.id AND a.account b.account AND a.deviceId b.deviceId;临时表法适用于大数据量-- 创建去重后的临时表 CREATE TABLE temp_table AS SELECT * FROM accountinfo GROUP BY account, deviceId; -- 替换原表 RENAME TABLE accountinfo TO old_accountinfo, temp_table TO accountinfo; -- 重建索引等后续操作...INSERT IGNORE预防重复-- 创建唯一索引 ALTER TABLE accountinfo ADD UNIQUE INDEX idx_unique (account, deviceId); -- 插入时自动忽略重复 INSERT IGNORE INTO accountinfo VALUES (...);4.3 性能优化建议对于超千万级数据建议在业务低峰期执行先创建合适的索引如(account, deviceId)组合索引分批处理数据每次处理10万条左右考虑使用pt-archiver等专业工具大表操作前务必先备份数据5. 实战案例用户表去重全过程假设我们有一个包含500万条记录的用户表其中存在约10%的重复数据以下是完整的处理流程分析重复情况-- 查看重复数据分布 SELECT COUNT(*) as total_count, COUNT(DISTINCT CONCAT(account,|,deviceId)) as distinct_count, ROUND(100*(1-COUNT(DISTINCT CONCAT(account,|,deviceId))/COUNT(*)),2) as dup_percent FROM accountinfo;创建备份表CREATE TABLE accountinfo_backup LIKE accountinfo; INSERT INTO accountinfo_backup SELECT * FROM accountinfo;分批删除重复数据-- 每次处理10万条 SET batch_size 100000; SET offset 0; WHILE EXISTS (SELECT 1 FROM accountinfo LIMIT 1) DO DELETE FROM accountinfo WHERE id IN ( SELECT id FROM ( SELECT id FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1 LIMIT batch_size OFFSET offset ) tmp ); SET offset offset batch_size; END WHILE;验证结果-- 检查是否还有重复 SELECT account, deviceId, COUNT(*) FROM accountinfo GROUP BY account, deviceId HAVING COUNT(*) 1 LIMIT 10; -- 比较数据总量 SELECT COUNT(*) FROM accountinfo_backup; SELECT COUNT(*) FROM accountinfo;建立防重复机制-- 添加唯一约束 ALTER TABLE accountinfo ADD CONSTRAINT uk_account_device UNIQUE (account, deviceId); -- 或者使用触发器预防 DELIMITER // CREATE TRIGGER prevent_duplicates BEFORE INSERT ON accountinfo FOR EACH ROW BEGIN IF EXISTS ( SELECT 1 FROM accountinfo WHERE account NEW.account AND deviceId NEW.deviceId ) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Duplicate account and deviceId combination; END IF; END// DELIMITER ;在实际项目中我推荐使用窗口函数方案MySQL 8.0或临时表方案它们既安全又高效。对于特别大的表可以考虑使用pt-archiver工具分批处理避免长时间锁表影响业务。