MySQL 如何查找刪除重復(fù)行？

來源：blog.csdn.net/zhengzhb/article/details/8590390

• 如何查找重復(fù)行
• 如何刪除重復(fù)行
• 如何查找多列上的重復(fù)行
• 錯誤的查詢語句
• 幾種正確的方法

如何查找重復(fù)行

第一步是定義什么樣的行才是重復(fù)行。多數(shù)情況下很簡單：它們某一列具有相同的值。本文采用這一定義，或許你對“重復(fù)”的定義比這復(fù)雜，你需要對sql做些修改。本文要用到的數(shù)據(jù)樣本：

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字段具有相同的值，因此如何我將他們當(dāng)做重復(fù)行，這里有一查詢語句可以查找。查詢語句使用GROUP BY子句把具有相同字段值的行歸為一組，然后計算組的大小。

select?day,?count(*)?from?test?GROUP?BY?day;
+------------+----------+
|?day????????|?count(*)?|
+------------+----------+
|?2006-10-08?|????????2?|
|?2006-10-09?|????????1?|
+------------+----------+

重復(fù)行的組大小大于1。如何希望只顯示重復(fù)行，必須使用HAVING子句，比如

select?day,?count(*)?from?test?group?by?day?HAVING?count(*)?>?1;
+------------+----------+
|?day????????|?count(*)?|
+------------+----------+
|?2006-10-08?|????????2?|
+------------+----------+

這是基本的技巧：根據(jù)具有相同值的字段分組，然后知顯示大小大于1的組。

為什么不能使用WHERE子句？因為WHERE子句過濾的是分組之前的行，HAVING子句過濾的是分組之后的行。

如何刪除重復(fù)行

一個相關(guān)的問題是如何刪除重復(fù)行。一個常見的任務(wù)是，重復(fù)行只保留一行，其他刪除，然后你可以創(chuàng)建適當(dāng)?shù)乃饕?，防止以后再有重?fù)的行寫入數(shù)據(jù)庫。

同樣，首先是弄清楚重復(fù)行的定義。你要保留的是哪一行呢？第一行，或者某個字段具有最大值的行？本文中，假設(shè)要保留的是第一行——id字段具有最小值的行，意味著你要刪除其他的行。

也許最簡單的方法是通過臨時表。尤其對于MYSQL，有些限制是不能在一個查詢語句中select的同時update一個表。簡單起見，這里只用到了臨時表的方法。

我們的任務(wù)是：刪除所有重復(fù)行，除了分組中id字段具有最小值的行。因此，需要找出大小大于1的分組，以及希望保留的行。你可以使用MIN()函數(shù)。這里的語句是創(chuàng)建臨時表，以及查找需要用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?|
+------------+--------+

有了這些數(shù)據(jù)，你可以開始刪除“臟數(shù)據(jù)”行了?？梢杂袔追N方法，各有優(yōu)劣（詳見我的文章many-to-one problems in SQL），但這里不做詳細比較，只是說明在支持查詢子句的關(guān)系數(shù)據(jù)庫中，使用的標準方法。

delete?from?test
??where?exists(
?????select?*?from?to_delete
?????where?to_delete.day?=?test.day?and?to_delete.min_id?<>?test.id
??)

如何查找多列上的重復(fù)行

有人最近問到這樣的問題：我的一個表上有兩個字段b和c，分別關(guān)聯(lián)到其他兩個表的b和c字段。我想要找出在b字段或者c字段上具有重復(fù)值的行。

咋看很難明白，通過對話后我理解了：他想要對b和c分別創(chuàng)建unique索引。如上所述，查找在某一字段上具有重復(fù)值的行很簡單，只要用group分組，然后計算組的大小。并且查找全部字段重復(fù)的行也很簡單，只要把所有字段放到group子句。但如果是判斷b字段重復(fù)或者c字段重復(fù)，問題困難得多。這里提問者用到的樣本數(shù)據(jù)

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);

現(xiàn)在，你可以輕易看到表里面有一些重復(fù)的行，但找不到兩行具有相同的二元組{b, c}。這就是為什么問題會變得困難了。

錯誤的查詢語句

如果把兩列放在一起分組，你會得到不同的結(jié)果，具體看如何分組和計算大小。提問者恰恰是困在了這里。有時候查詢語句找到一些重復(fù)行卻漏了其他的。這是他用到了查詢

select?b,?c,?count(*)?from?a_b_c
group?by?b,?c
having?count(distinct?b?>?1)
??or?count(distinct?c?>?1);

結(jié)果返回所有的行，因為CONT(*)總是1.為什么？因為 >1 寫在COUNT()里面。這個錯誤很容易被忽略，事實上等效于

select?b,?c,?count(*)?from?a_b_c
group?by?b,?c
having?count(1)
??or?count(1);

為什么？因為(b > 1)是一個布爾值，根本不是你想要的結(jié)果。你要的是

select?b,?c,?count(*)?from?a_b_c
group?by?b,?c
having?count(distinct?b)?>?1
??or?count(distinct?c)?>?1;

返回空結(jié)果。很顯然，因為沒有重復(fù)的{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?|
+------+----------+

沒有一個能夠找出全部的重復(fù)行。而且最令人沮喪的是，對于某些情況，這種語句是有效的，如果錯誤地以為就是這么寫法，然而對于另外的情況，很可能得到錯誤結(jié)果。

事實上，單純用GROUP BY 是不可行的。為什么？因為當(dāng)你對某一字段使用group by時，就會把另一字段的值分散到不同的分組里。對這些字段排序可以看到這些效果，正如分組做的那樣。首先，對b字段排序，看看它是如何分組的

當(dāng)你對b字段排序（分組），相同值的c被分到不同的組，因此不能用COUNT(DISTINCT c)來計算大小。COUNT()之類的內(nèi)部函數(shù)只作用于同一個分組，對于不同分組的行就無能為力了。類似，如果排序的是c字段，相同值的b也會分到不同的組，無論如何是不能達到我們的目的的。

幾種正確的方法

也許最簡單的方法是分別對某個字段查找重復(fù)行，然后用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字段為了提示重復(fù)的是哪個字段。另一個辦法是使用嵌套查詢：

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低許多，并且顯示每一重復(fù)的行，而不是重復(fù)的字段值。還有一種方法，將自己跟group的嵌套查詢結(jié)果聯(lián)表查詢。寫法比較復(fù)雜，但對于復(fù)雜的數(shù)據(jù)或者對效率有較高要求的情況，是很有必要的。

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能用，我想會是最簡單不過的了。