MySQL 如何查找并删除重复记录

概述介绍了如何在 MySQL 中查找并删除重复记录，包括使用 GROUP BY 分组、子查询或者连接查询等方法查找单个字段或者多个字段中的重复数据，以及使用 DELETE FROM 语句、子查询、中间表和窗口函数等方法实现重复数据的删除。

文章目录 创建示例表查找单个字段中的重复数据查找多个字段中的重复数据删除重复数据使用 DELETE FROM 删除重复数据利用子查询删除重复数据通过中间表删除重复数据利用窗口函数删除重复数据总结

大家好，我是只谈技术不剪发的 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 ('张三', '[email protected]'),       ('李四', '[email protected]'),       ('王五', '[email protected]'),       ('李斯', '[email protected]');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 '[email protected]' for key 'people.uk_people_email'

显然，我们必须找出并删除 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|

另一种查找重复记录的方法就是直接使用自连接查询和 distinct *** 作符，例如：

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|

注意，不能省略 distinct，否则会某些数据（3、5、6）会返回多次。

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

如果我们想要找出 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|

只有当 name 和 email 都相同时才是重复数据，所以 2 和 4 不是重复记录。

删除重复数据

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

使用 DELETE FROM 删除重复数据

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

delete pfrom people pjoin 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 最小的数据应该怎么实现呢？

利用子查询删除重复数据

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

deletefrom peoplewhere 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, emailfrom peoplewhere 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, 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 就意味着存在重复的数据。

总结

以上是内存溢出为你收集整理的MySQL 如何查找并删除重复记录全部内容，希望文章能够帮你解决MySQL 如何查找并删除重复记录所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: https://outofmemory.cn/sjk/1152168.html