MySQL重复数据清理策略：基于条件保留与高效删除实战

引言

在数据库管理中，重复数据是常见的痛点问题。它们可能因业务逻辑漏洞、批量导入错误或并发操作产生，导致存储浪费、查询效率下降甚至分析结果失真。本文通过一个真实案例，系统讲解如何通过SQL实现多条件重复数据检测与基于业务规则的智能删除，涵盖以下核心内容：

重复数据的精准定位
复杂业务规则的SQL转化
分步验证与数据安全策略
性能优化建议

问题背景

表结构与业务需求

CREATE TABLE `fa_household_to_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `household_id` int(11) DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  `master` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否户主',
  `relation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '与户主关系',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `member_id` (`member_id`),
  KEY `household_id` (`household_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

核心需求：

检测并删除在 (household_id, member_id, master) 三个字段上完全重复的数据
保留规则：
- 若组内存在 relation 不为空的记录，则保留 relation 不为空且 id 最大的记录
- 若组内所有 relation 均为空，则保留 id 最大的记录

解决方案设计

步骤1：重复数据检测

通过分组统计定位重复数据组：

SELECT household_id, member_id, master, COUNT(*) AS duplicate_count
FROM fa_household_to_member
GROUP BY household_id, member_id, master
HAVING duplicate_count > 1;

步骤2：构建保留规则逻辑

使用子查询预计算每组需要保留的记录：

SELECT 
    household_id, 
    member_id, 
    master,
    MAX(CASE WHEN relation IS NOT NULL THEN id END) AS max_non_null_id,
    MAX(id) AS max_id
FROM fa_household_to_member
GROUP BY household_id, member_id, master;

步骤3：生成删除验证SQL

在实际删除前，通过SELECT语句验证待删除数据：

SELECT t1.*
FROM fa_household_to_member t1
LEFT JOIN (
    -- 同上子查询
) AS keep
ON t1.household_id = keep.household_id
AND t1.member_id = keep.member_id
AND t1.master = keep.master
WHERE 
    (keep.max_non_null_id IS NOT NULL AND t1.id != keep.max_non_null_id)
    OR 
    (keep.max_non_null_id IS NULL AND t1.id != keep.max_id);

步骤4：执行安全删除

DELETE t1
FROM fa_household_to_member t1
LEFT JOIN (
    -- 同上子查询
) AS keep
ON t1.household_id = keep.household_id
AND t1.member_id = keep.member_id
AND t1.master = keep.master
WHERE 
    (keep.max_non_null_id IS NOT NULL AND t1.id != keep.max_non_null_id)
    OR 
    (keep.max_non_null_id IS NULL AND t1.id != keep.max_id);

示例验证

测试数据集

id	household_id	member_id	relation
1	503	1292	NULL
2	503	1292	'father'
3	503	1292	'mother'
4	504	1293	NULL
5	504	1293	NULL
6	505	1294	'son'
7	505	1294	'daughter'

执行结果

删除前数据分布：

组 (503,1292,0)：3条记录（含2条relation非空）
组 (504,1293,0)：2条记录（relation均为空）
组 (505,1294,0)：2条记录（relation均非空）

删除后保留数据：

id	household_id	member_id	relation
3	503	1292	'mother'
5	504	1293	NULL
7	505	1294	'daughter'

关键技术点解析

1. 条件优先级处理

通过 CASE WHEN 实现业务规则的权重控制：

MAX(CASE WHEN relation IS NOT NULL THEN id END) AS max_non_null_id

该语句确保在存在有效relation时，优先选择非空记录的最大ID。

2. 安全删除策略

预验证机制：通过SELECT语句提前查看待删除数据
事务回滚：在MySQL 5.7中可使用 START TRANSACTION 和 ROLLBACK 进行测试

备份建议：

CREATE TABLE fa_household_to_member_backup AS 
SELECT * FROM fa_household_to_member;

3. 性能优化

索引优化：确保 (household_id, member_id, master) 字段有联合索引
分批次处理：对大数据表使用 LIMIT 子句分批删除
```
DELETE ... WHERE ... LIMIT 1000;
```

总结

本文通过真实业务场景，完整演示了从问题定位到解决方案落地的全过程。关键收获包括：

复杂业务规则的SQL转化方法
数据操作前的安全验证流程
MySQL 5.7环境下条件删除的优化技巧

建议读者在实施类似操作时：

始终进行数据备份
先用SELECT验证再执行DELETE
对生产环境操作进行事务包裹
定期检查数据质量避免重复积累

通过系统化的数据清洗策略，可显著提升数据库的健康度和业务系统的可靠性。