嗯啊快一点,小受性瘾放荡的np文

主頁 > 知識庫 > MySQL 如何查找并刪除重復記錄的實現

MySQL 如何查找并刪除重復記錄的實現

大家好，我是只談技術不剪發(fā)的 Tony 老師。由于一些歷史原因或者誤操作，可能會導致數據表中存在重復的記錄；今天我們就來談談如何查找 MySQL 表中的重復數據以及如何刪除這些重復的記錄。

創(chuàng)建示例表

首先創(chuàng)建一個示例表 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 創(chuàng)建一個唯一約束，將會返回錯誤：

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 字段中的重復記錄才能創(chuàng)建唯一約束。

查找單個字段中的重復數據

如果想要找出 email 重復的數據，可以基于該字段進行分組統(tǒng)計，并且返回行數大于 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 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|

另一種查找重復記錄的方法就是直接使用自連接查詢和 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，否則會某些數據（3、5、6）會返回多次。

查找多個字段中的重復數據

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

只有當 name 和 email 都相同時才是重復數據，所以 2 和 4 不是重復記錄。

刪除重復數據

找出重復數據之后，需要解決的就是如何刪除了，通常我們需要保留其中的一條記錄。

使用 DELETE FROM 刪除重復數據

假如我們想要刪除 email 重復的記錄，只保留其中一條，可以使用 DELETE FROM 語句實現：

delete p
from people p
join 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 最小的數據應該怎么實現呢？

利用子查詢刪除重復數據

通過子查詢可以找出需要保留的數據，然后刪除其他的數據：

delete
from people
where id not in (
  select max(id)
  from people
  group by email
  );

在執(zhí)行上面的語句之前，記得重新創(chuàng)建 people 表并生成測試數據。

通過中間表刪除重復數據

通過使用中間表也可以實現重復記錄的刪除，例如：

-- 創(chuàng)建中間表
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;

在執(zhí)行上面的語句之前，記得重新創(chuàng)建 people 表并生成測試數據。

這種方式需要注意的一個問題就是 create table … like 語句不會復制原表上的外鍵約束，需要手動添加。

利用窗口函數刪除重復數據

ROW_NUMBER() 是 MySQL 8.0 中新增的窗口函數，可以用于將數據進行分組，然后為每一條數據分配一個唯一的數字編號。例如：

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|

以上語句基于 email 分組（partition by email），同時按照 id 進行排序（order by id），然后為每個組內的數據分配一個編號；如果編號大于 1 就意味著存在重復的數據。

📝除了 ROW_NUMBER() 之外，RANK() 或者 DENSE_RANK() 函數也可以實現以上功能。關于窗口函數的介紹和使用案例，可以參考這篇文章。

基于該查詢結果可以刪除重復的記錄：

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í)行上面的語句之前，記得重新創(chuàng)建 people 表并生成測試數據。

基于多個字段的重復數據刪除方法和單個字段非常類似，大家可以自行嘗試，也歡迎留言討論！

總結

本文介紹了如何在 MySQL 中查找并刪除重復記錄，包括使用 GROUP BY 分組、子查詢或者連接查詢等方法查找單個字段或者多個字段中的重復數據，以及使用 DELETE FROM 語句、子查詢、中間表和窗口函數等方法實現重復數據的刪除。更多相關MySQL 查找并刪除重復記錄內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: