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

2023-07-18 12:28:03 54

大家好，我是只谈技术不剪发的Tony老师。由于一些历史原因或者误操作，可能会导致数据表中存在重复的记录；今天我们就来谈谈如何查找MySQL表中的重复数据以及如何删除这些重复的记录。

创建示例表

首先创建一个示例表people并生成一些数据：

droptableifexistspeople;
createtablepeople(
idintauto_incrementprimarykey,
namevarchar(50)notnull,
emailvarchar(100)notnull
);

insertintopeople(name,email)
values('张三','zhangsan@test.com'),
('李四','lisi@test.com'),
('王五','wangwu@test.com'),
('李斯','lisi@test.com'),
('王五','wangwu@test.com'),
('王五','wangwu@test.com');

select*frompeople;
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创建一个唯一约束，将会返回错误：

altertablepeopleaddconstraintuk_people_emailuniquekey(email);
ERROR1062(23000):Duplicateentry'wangwu@test.com'forkey'people.uk_people_email'

显然，我们必须找出并删除email字段中的重复记录才能创建唯一约束。

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

如果想要找出email重复的数据，可以基于该字段进行分组统计，并且返回行数大于1的分组：

selectemail,count(email)
frompeople
groupbyemail
havingcount(email)>1;
email|count(email)|
---------------|------------|
lisi@test.com|2|
wangwu@test.com|3|

查询结果显示有两个邮箱地址存在重复情况。如果想要查看完整的重复数据，可以使用子查询或者连接查询：

select*
frompeople
whereemailin(
selectemail
frompeople
groupbyemail
havingcount(email)>1)
orderbyemail;
id|name|email|
--|------|---------------|
2|李四|lisi@test.com|
4|李斯|lisi@test.com|
3|王五|wangwu@test.com|
5|王五|wangwu@test.com|
6|王五|wangwu@test.com|

selectp.*
frompeoplep
join(
selectemail
frompeople
groupbyemail
havingcount(email)>1
)donp.email=d.email
orderbyemail;
id|name|email|
--|------|---------------|
2|李四|lisi@test.com|
4|李斯|lisi@test.com|
3|王五|wangwu@test.com|
5|王五|wangwu@test.com|
6|王五|wangwu@test.com|

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

selectdistinctp.*
frompeoplep
joinpeopledonp.email=d.email
wherep.id<>d.id
orderbyp.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*
frompeople
where(name,email)in(
selectname,email
frompeople
groupbyname,email
havingcount(1)>1)
orderbyemail;
id|name|email|
--|------|---------------|
3|王五|wangwu@test.com|
5|王五|wangwu@test.com|
6|王五|wangwu@test.com|

selectdistinctp.*
frompeoplep
joinpeopledonp.name=d.nameandp.email=d.email
wherep.id<>d.id
orderbyemail;
id|name|email|
--|------|---------------|
6|王五|wangwu@test.com|
5|王五|wangwu@test.com|
3|王五|wangwu@test.com|

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

删除重复数据

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

使用DELETEFROM删除重复数据

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

deletep
frompeoplep
joinpeopledonp.email=d.emailandp.id
delete语句通过连接找出需要删除的记录，以上示例保留了重复数据中的最大id对应的数据行。再次查询people表：


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

想一想，如果想要保留重复数据中id最小的数据应该怎么实现呢？
利用子查询删除重复数据
通过子查询可以找出需要保留的数据，然后删除其他的数据：


delete
frompeople
whereidnotin(
selectmax(id)
frompeople
groupbyemail
);

在执行上面的语句之前，记得重新创建people表并生成测试数据。
通过中间表删除重复数据
通过使用中间表也可以实现重复记录的删除，例如：


--创建中间表
createtablepeople_templikepeople;

--复制需要保留的数据行
insertintopeople_temp(id,name,email)
selectid,name,email
frompeople
whereidin(
selectmax(id)
frompeople
groupbyemail
);

--删除原表
droptablepeople;

--将中间表重命名为原表
altertablepeople_temprenametopeople;


在执行上面的语句之前，记得重新创建people表并生成测试数据。
这种方式需要注意的一个问题就是createtable…like语句不会复制原表上的外键约束，需要手动添加。
利用窗口函数删除重复数据
ROW_NUMBER()是MySQL8.0中新增的窗口函数，可以用于将数据进行分组，然后为每一条数据分配一个唯一的数字编号。例如：


selectid,name,email,
row_number()over(partitionbyemailorderbyid)asrow_num
frompeople;
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分组（partitionbyemail），同时按照id进行排序（orderbyid），然后为每个组内的数据分配一个编号；如果编号大于1就意味着存在重复的数据。

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

热门推荐

随机推荐