大家好,我是只谈技术不剪发的 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 如何查找并删除重复记录的实现
扫一扫手机访问
