MySQL 如何查找刪除重復行

Posted on by

一、如何查找重復行

第一步是定義什麼樣的行才是重復行。多數情況下很簡單：它們某一列具有相同的值。本文采用這一定義，或許你對“重復”的定義比這復雜，你需要對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能用，我想會是最簡單不過的瞭。

到此這篇關於MySQL 如何查找刪除重復行的文章就介紹到這瞭,更多相關MySQL 查找刪除重復行內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：