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 |

此时，如果我们想要为 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 重复的数据，可以基于该字段进行分组统计，并且返回行数大于 1 的分组：

select email, count(email)from peoplegroup by emailhaving count(email) > 1;email   |count(email)|---------------|------------|lisi@test.com |   2|wangwu@test.com|   3|

select *from peoplewhere 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 pjoin ( select email from people group by email having count(email) > 1) d on p.email = d.emailorder 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 distinct p.*from people pjoin people d on p.email = d.emailwhere p.id <> d.idorder 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|

查找多个字段中的重复数据

如果我们想要找出 name 和 email 字段都重复的数据，实现方式也类似：

select *from peoplewhere (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 pjoin people d on p.name = d.name and p.email = d.emailwhere p.id <> d.idorder by email;id|name |email   |--|------|---------------| 6|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 3|王五 |wangwu@test.com|

删除重复数据

找出重复数据之后，需要解决的就是如何删除了，通常我们需要保留其中的一条记录。

使用 DELETE FROM 删除重复数据

假如我们想要删除 email 重复的记录，只保留其中一条，可以使用 DELETE FROM 语句实现：

delete pfrom people pjoin people d on p.email = d.email and p.id < d.id;

select * from people;id|name |email   |--|------|-----------------| 1|张三 |zhangsan@test.com| 4|李斯 |lisi@test.com | 6|王五 |wangwu@test.com |

利用子查询删除重复数据

通过子查询可以找出需要保留的数据，然后删除其他的数据：

deletefrom peoplewhere id not in (  select max(id)  from people  group by email  );

通过中间表删除重复数据

通过使用中间表也可以实现重复记录的删除，例如：

-- 创建中间表create table people_temp like people;-- 复制需要保留的数据行insert into people_temp(id, name, email)select id, name, emailfrom peoplewhere id in (  select max(id)  from people  group by email  );--删除原表drop table people;-- 将中间表重命名为原表alter table people_temp rename to 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|

📝除了 ROW_NUMBER() 之外，RANK() 或者 DENSE_RANK() 函数也可以实现以上功能。关于窗口函数的介绍和使用案例，可以参考这篇文章。

基于该查询结果可以删除重复的记录：

deletefrom peoplewhere 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);

基于多个字段的重复数据删除方法和单个字段非常类似，大家可以自行尝试，也欢迎留言讨论！

总结

本文介绍了如何在 MySQL 中查找并删除重复记录，包括使用 GROUP BY 分组、子查询或者连接查询等方法查找单个字段或者多个字段中的重复数据，以及使用 DELETE FROM 语句、子查询、中间表和窗口函数等方法实现重复数据的删除。更多相关MySQL 查找并删除重复记录内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

更多相关文章

随机推荐