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>
select distinct p.*
from people p
join people d>查找多个字段中的重复数据

如果我们想要找出 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>删除重复数据

找出重复数据之后，需要解决的就是如何删除了，通常我们需要保留其中的一条记录。
使用 DELETE FROM 删除重复数据
假如我们想要删除 email 重复的记录，只保留其中一条，可以使用 DELETE FROM 语句实现：



delete p
from people p
join people d>
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 语句不会复制原表上的外键约束，需要手动添加。
MySQL 如何查找并删除重复记录的实现