個(gè)人中心
退出
MySQL 如何查找并刪除重復(fù)記錄
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
創(chuàng)建示例表
查找單個(gè)字段中的重復(fù)數(shù)據(jù)
查找多個(gè)字段中的重復(fù)數(shù)據(jù)
刪除重復(fù)數(shù)據(jù)
使用 DELETE FROM 刪除重復(fù)數(shù)據(jù)
利用子查詢刪除重復(fù)數(shù)據(jù)
通過(guò)中間表刪除重復(fù)數(shù)據(jù)
利用窗口函數(shù)刪除重復(fù)數(shù)據(jù)
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。由于一些歷史原因或者誤操作，可能會(huì)導(dǎo)致數(shù)據(jù)表中存在重復(fù)的記錄；今天我們就來(lái)談?wù)勅绾尾檎?MySQL 表中的重復(fù)數(shù)據(jù)以及如何刪除這些重復(fù)的記錄。
創(chuàng)建示例表
首先創(chuàng)建一個(gè)示例表 people 并生成一些數(shù)據(jù)：
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 字段存在重復(fù)數(shù)據(jù)；3、5 和 6 的 name 和 email 字段存在重復(fù)數(shù)據(jù)。
此時(shí)，如果我們想要為 email 創(chuàng)建一個(gè)唯一約束，將會(huì)返回錯(cuò)誤：
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 字段中的重復(fù)記錄才能創(chuàng)建唯一約束。
查找單個(gè)字段中的重復(fù)數(shù)據(jù)
如果想要找出 email 重復(fù)的數(shù)據(jù)，可以基于該字段進(jìn)行分組統(tǒng)計(jì)，并且返回行數(shù)大于 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|
查詢結(jié)果顯示有兩個(gè)郵箱地址存在重復(fù)情況。如果想要查看完整的重復(fù)數(shù)據(jù)，可以使用子查詢或者連接查詢：
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 on p.email = d.email
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|
另一種查找重復(fù)記錄的方法就是直接使用自連接查詢和 distinct 操作符，例如：
select distinct p.*
from people p
join people d on p.email = d.email
where p.id <> d.id
order 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，否則會(huì)某些數(shù)據(jù)（3、5、6）會(huì)返回多次。
查找多個(gè)字段中的重復(fù)數(shù)據(jù)
如果我們想要找出 name 和 email 字段都重復(fù)的數(shù)據(jù)，實(shí)現(xiàn)方式也類似：
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 on p.name = d.name and p.email = d.email
where p.id <> d.id
order by email;
id|name |email |
--|------|---------------|
6|王五 |wangwu@test.com|
5|王五 |wangwu@test.com|
3|王五 |wangwu@test.com|
只有當(dāng) name 和 email 都相同時(shí)才是重復(fù)數(shù)據(jù)，所以 2 和 4 不是重復(fù)記錄。
刪除重復(fù)數(shù)據(jù)
找出重復(fù)數(shù)據(jù)之后，需要解決的就是如何刪除了，通常我們需要保留其中的一條記錄。
使用 DELETE FROM 刪除重復(fù)數(shù)據(jù)
假如我們想要?jiǎng)h除 email 重復(fù)的記錄，只保留其中一條，可以使用 DELETE FROM 語(yǔ)句實(shí)現(xiàn)：
delete p
from people p
join people d on p.email = d.email and p.id < d.id;
delete 語(yǔ)句通過(guò)連接找出需要?jiǎng)h除的記錄，以上示例保留了重復(fù)數(shù)據(jù)中的最大 id 對(duì)應(yīng)的數(shù)據(jù)行。再次查詢 people 表：
select * from people;
id|name |email |
--|------|-----------------|
1|張三 |zhangsan@test.com|
4|李斯 |lisi@test.com |
6|王五 |wangwu@test.com |
想一想，如果想要保留重復(fù)數(shù)據(jù)中 id 最小的數(shù)據(jù)應(yīng)該怎么實(shí)現(xiàn)呢？
利用子查詢刪除重復(fù)數(shù)據(jù)
通過(guò)子查詢可以找出需要保留的數(shù)據(jù)，然后刪除其他的數(shù)據(jù)：
delete
from people
where id not in (
select max(id)
from people
group by email
);
在執(zhí)行上面的語(yǔ)句之前，記得重新創(chuàng)建 people 表并生成測(cè)試數(shù)據(jù)。
通過(guò)中間表刪除重復(fù)數(shù)據(jù)
通過(guò)使用中間表也可以實(shí)現(xiàn)重復(fù)記錄的刪除，例如：
-- 創(chuàng)建中間表
create table people_temp like people;
-- 復(fù)制需要保留的數(shù)據(jù)行
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;
在執(zhí)行上面的語(yǔ)句之前，記得重新創(chuàng)建 people 表并生成測(cè)試數(shù)據(jù)。
??這種方式需要注意的一個(gè)問(wèn)題就是 create table … like 語(yǔ)句不會(huì)復(fù)制原表上的外鍵約束，需要手動(dòng)添加。
利用窗口函數(shù)刪除重復(fù)數(shù)據(jù)
ROW_NUMBER() 是 MySQL 8.0 中新增的窗口函數(shù)，可以用于將數(shù)據(jù)進(jìn)行分組，然后為每一條數(shù)據(jù)分配一個(gè)唯一的數(shù)字編號(hào)。例如：
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|
以上語(yǔ)句基于 email 分組（partition by email），同時(shí)按照 id 進(jìn)行排序（order by id），然后為每個(gè)組內(nèi)的數(shù)據(jù)分配一個(gè)編號(hào)；如果編號(hào)大于 1 就意味著存在重復(fù)的數(shù)據(jù)。
??除了 ROW_NUMBER() 之外，RANK() 或者 DENSE_RANK() 函數(shù)也可以實(shí)現(xiàn)以上功能。關(guān)于窗口函數(shù)的介紹和使用案例，可以參考這篇文章。
基于該查詢結(jié)果可以刪除重復(fù)的記錄：
delete
from people
where 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);
在執(zhí)行上面的語(yǔ)句之前，記得重新創(chuàng)建 people 表并生成測(cè)試數(shù)據(jù)。
基于多個(gè)字段的重復(fù)數(shù)據(jù)刪除方法和單個(gè)字段非常類似，大家可以自行嘗試，也歡迎留言討論！
總結(jié)
本文介紹了如何在 MySQL 中查找并刪除重復(fù)記錄，包括使用 GROUP BY 分組、子查詢或者連接查詢等方法查找單個(gè)字段或者多個(gè)字段中的重復(fù)數(shù)據(jù)，以及使用 DELETE FROM 語(yǔ)句、子查詢、中間表和窗口函數(shù)等方法實(shí)現(xiàn)重復(fù)數(shù)據(jù)的刪除。
如果覺(jué)得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??！
分類導(dǎo)航