MySQL 如何查找刪除重複行?
如何查找重複行
第一步是定義什麼樣的行纔是重複行。多數情況下很簡單:它們某一列具有相同的值。本文采用這一定義,或許你對 “重複” 的定義比這複雜,你需要對 sql 做些修改。本文要用到的數據樣本:
create table test(id int not null primary key, day date not null);
insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');
select * from test;
+----+------------+
| id | day |
+----+------------+
| 1 | 2006-10-08 |
| 2 | 2006-10-08 |
| 3 | 2006-10-09 |
+----+------------+
前面兩行在 day 字段具有相同的值,因此如何我將他們當做重複行,這裏有一查詢語句可以查找。查詢語句使用 GROUP BY 子句把具有相同字段值的行歸爲一組,然後計算組的大小。
select day, count(*) from test GROUP BY day;
+------------+----------+
| day | count(*) |
+------------+----------+
| 2006-10-08 | 2 |
| 2006-10-09 | 1 |
+------------+----------+
重複行的組大小大於 1。如何希望只顯示重複行,必須使用 HAVING 子句,比如
select day, count(*) from test group by day HAVING count(*) > 1;
+------------+----------+
| day | count(*) |
+------------+----------+
| 2006-10-08 | 2 |
+------------+----------+
這是基本的技巧:根據具有相同值的字段分組,然後知顯示大小大於 1 的組。
爲什麼不能使用 WHERE 子句?因爲 WHERE 子句過濾的是分組之前的行,HAVING 子句過濾的是分組之後的行。
如何刪除重複行
一個相關的問題是如何刪除重複行。一個常見的任務是,重複行只保留一行,其他刪除,然後你可以創建適當的索引,防止以後再有重複的行寫入數據庫。
同樣,首先是弄清楚重複行的定義。你要保留的是哪一行呢?第一行,或者某個字段具有最大值的行?本文中,假設要保留的是第一行——id 字段具有最小值的行,意味着你要刪除其他的行。
也許最簡單的方法是通過臨時表。尤其對於 MYSQL,有些限制是不能在一個查詢語句中 select 的同時 update 一個表。簡單起見,這裏只用到了臨時表的方法。
我們的任務是:刪除所有重複行,除了分組中 id 字段具有最小值的行。因此,需要找出大小大於 1 的分組,以及希望保留的行。你可以使用 MIN() 函數。這裏的語句是創建臨時表,以及查找需要用 DELETE 刪除的行。
create temporary table to_delete (day date not null, min_id int not null);
insert into to_delete(day, min_id)
select day, MIN(id) from test group by day having count(*) > 1;
select * from to_delete;
+------------+--------+
| day | min_id |
+------------+--------+
| 2006-10-08 | 1 |
+------------+--------+
有了這些數據,你可以開始刪除 “髒數據” 行了。可以有幾種方法,各有優劣,只是說明在支持查詢子句的關係數據庫中,使用的標準方法。
delete from test
where exists(
select * from to_delete
where to_delete.day = test.day and to_delete.min_id <> test.id
)
如何查找多列上的重複行
有人最近問到這樣的問題:我的一個表上有兩個字段 b 和 c,分別關聯到其他兩個表的 b 和 c 字段。我想要找出在 b 字段或者 c 字段上具有重複值的行。
咋看很難明白,通過對話後我理解了:他想要對 b 和 c 分別創建 unique 索引。如上所述,查找在某一字段上具有重複值的行很簡單,只要用 group 分組,然後計算組的大小。並且查找全部字段重複的行也很簡單,只要把所有字段放到 group 子句。但如果是判斷 b 字段重複或者 c 字段重複,問題困難得多。這裏提問者用到的樣本數據
create table a_b_c(
a int not null primary key auto_increment,
b int,
c int
);
insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);
現在,你可以輕易看到表裏面有一些重複的行,但找不到兩行具有相同的二元組 {b, c}。這就是爲什麼問題會變得困難了。
錯誤的查詢語句
如果把兩列放在一起分組,你會得到不同的結果,具體看如何分組和計算大小。提問者恰恰是困在了這裏。有時候查詢語句找到一些重複行卻漏了其他的。這是他用到了查詢
select b, c, count(*) from a_b_c
group by b, c
having count(distinct b > 1)
or count(distinct c > 1);
結果返回所有的行,因爲 CONT(*) 總是 1. 爲什麼?因爲 >1 寫在 COUNT() 裏面。這個錯誤很容易被忽略,事實上等效於
select b, c, count(*) from a_b_c
group by b, c
having count(1)
or count(1);
爲什麼?因爲 (b> 1) 是一個布爾值,根本不是你想要的結果。你要的是
select b, c, count(*) from a_b_c
group by b, c
having count(distinct b) > 1
or count(distinct c) > 1;
返回空結果。很顯然,因爲沒有重複的 {b,c}。這人試了很多其他的 OR 和 AND 的組合,用來分組的是一個字段,計算大小的是另一個字段,像這樣
select b, count(*) from a_b_c group by b having count(distinct c) > 1;
+------+----------+
| b | count(*) |
+------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+------+----------+
沒有一個能夠找出全部的重複行。而且最令人沮喪的是,對於某些情況,這種語句是有效的,如果錯誤地以爲就是這麼寫法,然而對於另外的情況,很可能得到錯誤結果。
事實上,單純用 GROUP BY 是不可行的。爲什麼?因爲當你對某一字段使用 group by 時,就會把另一字段的值分散到不同的分組裏。對這些字段排序可以看到這些效果,正如分組做的那樣。首先,對 b 字段排序,看看它是如何分組的
當你對 b 字段排序(分組),相同值的 c 被分到不同的組,因此不能用 COUNT(DISTINCT c) 來計算大小。COUNT() 之類的內部函數只作用於同一個分組,對於不同分組的行就無能爲力了。類似,如果排序的是 c 字段,相同值的 b 也會分到不同的組,無論如何是不能達到我們的目的的。
幾種正確的方法
也許最簡單的方法是分別對某個字段查找重複行,然後用 UNION 拼在一起,像這樣:
select b as value, count(*) as cnt, 'b' as what_col
from a_b_c group by b having count(*) > 1
union
select c as value, count(*) as cnt, 'c' as what_col
from a_b_c group by c having count(*) > 1;
+-------+-----+----------+
| value | cnt | what_col |
+-------+-----+----------+
| 1 | 3 | b |
| 2 | 3 | b |
| 3 | 3 | b |
| 1 | 3 | c |
| 2 | 3 | c |
| 3 | 3 | c |
+-------+-----+----------+
輸出 what_col 字段爲了提示重複的是哪個字段。另一個辦法是使用嵌套查詢:
select a, b, c from a_b_c
where b in (select b from a_b_c group by b having count(*) > 1)
or c in (select c from a_b_c group by c having count(*) > 1);
+----+------+------+
| a | b | c |
+----+------+------+
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
+----+------+------+
這種方法的效率要比使用 UNION 低許多,並且顯示每一重複的行,而不是重複的字段值。還有一種方法,將自己跟 group 的嵌套查詢結果聯表查詢。寫法比較複雜,但對於複雜的數據或者對效率有較高要求的情況,是很有必要的。
select a, a_b_c.b, a_b_c.c
from a_b_c
left outer join (
select b from a_b_c group by b having count(*) > 1
) as b on a_b_c.b = b.b
left outer join (
select c from a_b_c group by c having count(*) > 1
) as c on a_b_c.c = c.c
where b.b is not null or c.c is not null
以上方法可行,我敢肯定還有其他的方法。如果 UNION 能用,我想會是最簡單不過的了。
本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源:https://mp.weixin.qq.com/s/UuUeClVHBk2yrEVgAzSqiw