MySQL 如何查找并删除重复记录的实现
大家好，我是只谈技术不剪发的 Tony 老师。由于一些历史原因或者误操作，可能会导致数据表中存在重复的记录；今天我们就来谈谈如何查找 MySQL 表中的重复数据以及如何删除这些重复的记录。
创建示例表
首先创建一个示例表 people 并生成一些数据：
drop table if exists people;
create table people (
id int auto_increment primary key,
name varchar(50) not null,
email varchar(100) not null
);
insert into people(name, email)
values ('张三', 'zhangsan@test.com'),
('李四', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('李斯', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('王五', 'wangwu@test.com');
select * from people;
id|name |email |
--|------|-----------------|
1|张三 |zhangsan@test.com|
2|李四 |lisi@test.com |
3|王五 |wangwu@test.com |
4|李斯 |lisi@test.com |
5|王五 |wangwu@test.com |
6|王五 |wangwu@test.com |
其中，2 和 4 的 email 字段存在重复数据；3、5 和 6 的 name 和 email 字段存在重复数据。
此时，如果我们想要为 email 创建一个唯一约束，将会返回错误：
alter table people add constraint uk_people_email unique key (email); ERROR 1062 (23000): Duplicate entry 'wangwu@test.com' for key 'people.uk_people_email'
显然，我们必须找出并删除 email 字段中的重复记录才能创建唯一约束。
查找单个字段中的重复数据
如果想要找出 email 重复的数据，可以基于该字段进行分组统计，并且返回行数大于 1 的分组：
select email, count(email) from people group by email having count(email) > 1; email |count(email)| ---------------|------------| lisi@test.com | 2| wangwu@test.com| 3|
查询结果显示有两个邮箱地址存在重复情况。如果想要查看完整的重复数据，可以使用子查询或者连接查询：
select * from people where email in ( select email from people group by email having count(email) > 1) order by email; id|name |email | --|------|---------------| 2|李四 |lisi@test.com | 4|李斯 |lisi@test.com | 3|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 6|王五 |wangwu@test.com| select p.* from people p join ( select email from people group by email having count(email) > 1 ) d on p.email = d.email order by email; id|name |email | --|------|---------------| 2|李四 |lisi@test.com | 4|李斯 |lisi@test.com | 3|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 6|王五 |wangwu@test.com|
另一种查找重复记录的方法就是直接使用自连接查询和 distinct 操作符，例如：
select distinct p.* from people p join people d on p.email = d.email where p.id <> d.id order by p.email; id|name |email | --|------|---------------| 4|李斯 |lisi@test.com | 2|李四 |lisi@test.com | 6|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 3|王五 |wangwu@test.com|
注意，不能省略 distinct，否则会某些数据（3、5、6）会返回多次。
查找多个字段中的重复数据
如果我们想要找出 name 和 email 字段都重复的数据，实现方式也类似：
select * from people where (name, email) in ( select name, email from people group by name, email having count(1) > 1) order by email; id|name |email | --|------|---------------| 3|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 6|王五 |wangwu@test.com| select distinct p.* from people p join people d on p.name = d.name and p.email = d.email where p.id <> d.id order by email; id|name |email | --|------|---------------| 6|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 3|王五 |wangwu@test.com|
只有当 name 和 email 都相同时才是重复数据，所以 2 和 4 不是重复记录。
删除重复数据
找出重复数据之后，需要解决的就是如何删除了，通常我们需要保留其中的一条记录。
使用 DELETE FROM 删除重复数据
假如我们想要删除 email 重复的记录，只保留其中一条，可以使用 DELETE FROM 语句实现：
delete p from people p join people d on p.email = d.email and p.id < d.id;
delete 语句通过连接找出需要删除的记录，以上示例保留了重复数据中的最大 id 对应的数据行。再次查询 people 表：
select * from people; id|name |email | --|------|-----------------| 1|张三 |zhangsan@test.com| 4|李斯 |lisi@test.com | 6|王五 |wangwu@test.com |
想一想，如果想要保留重复数据中 id 最小的数据应该怎么实现呢？
利用子查询删除重复数据
通过子查询可以找出需要保留的数据，然后删除其他的数据：
delete from people where id not in ( select max(id) from people group by email );
在执行上面的语句之前，记得重新创建 people 表并生成测试数据。
通过中间表删除重复数据
通过使用中间表也可以实现重复记录的删除，例如：
-- 创建中间表 create table people_temp like people; -- 复制需要保留的数据行 insert into people_temp(id, name, email) select id, name, email from people where id in ( select max(id) from people group by email ); --删除原表 drop table people; -- 将中间表重命名为原表 alter table people_temp rename to people;
在执行上面的语句之前，记得重新创建 people 表并生成测试数据。
这种方式需要注意的一个问题就是 create table … like 语句不会复制原表上的外键约束，需要手动添加。
利用窗口函数删除重复数据
ROW_NUMBER() 是 MySQL 8.0 中新增的窗口函数，可以用于将数据进行分组，然后为每一条数据分配一个唯一的数字编号。例如：
select id, name, email, row_number() over (partition by email order by id) as row_num from people; id|name |email |row_num| --|------|-----------------|-------| 2|李四 |lisi@test.com | 1| 4|李斯 |lisi@test.com | 2| 3|王五 |wangwu@test.com | 1| 5|王五 |wangwu@test.com | 2| 6|王五 |wangwu@test.com | 3| 1|张三 |zhangsan@test.com| 1|
以上语句基于 email 分组（partition by email），同时按照 id 进行排序（order by id），然后为每个组内的数据分配一个编号；如果编号大于 1 就意味着存在重复的数据。
📝除了 ROW_NUMBER() 之外，RANK() 或者 DENSE_RANK() 函数也可以实现以上功能。关于窗口函数的介绍和使用案例，可以参考这篇文章。
基于该查询结果可以删除重复的记录：
delete
from people
where id in (
select id
from (
select id,
row_number() over (partition by email order by id desc) as row_num
from people) d
where row_num > 1);
在执行上面的语句之前，记得重新创建 people 表并生成测试数据。
基于多个字段的重复数据删除方法和单个字段非常类似，大家可以自行尝试，也欢迎留言讨论！
总结
栏 目：Mysql
下一篇：mysql代码执行结构实例分析【顺序、分支、循环结构】
本文标题：MySQL 如何查找并删除重复记录的实现
本文地址：http://www.codeinn.net/misctech/149865.html
