如何實現(xiàn) MySQL 刪除重復(fù)記錄并且只保留一條
作者：千g
blog.csdn.net/n950814abc/article/details/82284838
最近在做題庫系統(tǒng)，由于在題庫中添加了重復(fù)的試題，所以需要查詢出重復(fù)的試題，并且刪除掉重復(fù)的試題只保留其中1條，以保證考試的時候抽不到重復(fù)的題。
首先寫了一個小的例子：
一、單個字段的操作
這是數(shù)據(jù)庫中的表：
分組介紹：
Select?重復(fù)字段?From?表?Group?By?重復(fù)字段?Having?Count(*)>1
查看是否有重復(fù)的數(shù)據(jù)：
GROUP BY <列名序列>
HAVING <組條件表達(dá)式>
查詢出：根據(jù)dname分組，同時滿足having字句中組條件表達(dá)式（重復(fù)次數(shù)大于1）的那些組
count(*)與count(1)?其實沒有什么差別，用哪個都可以
count(*)與count（列名）的區(qū)別：
count(*)將返回表格中所有存在的行的總數(shù)包括值為null的行，然而count(列名)將返回表格中除去null以外的所有行的總數(shù)(有默認(rèn)值的列也會被計入）
1. 查詢?nèi)恐貜?fù)的數(shù)據(jù)：
Select?*?From?表?Where?重復(fù)字段?In?(Select?重復(fù)字段?From?表?Group?By?重復(fù)字段?Having?Count(*)>1)
2. 刪除全部重復(fù)試題：
將上面的查詢select改為delete（這樣會出錯的）
DELETE
FROM
?dept
WHERE
?dname?IN?(
??SELECT
???dname
??FROM
???dept
??GROUP?BY
???dname
??HAVING
???count(1)?>?1
?)
會出現(xiàn)如下錯誤：[Err] 1093 - You can't specify target table 'dept' for update in FROM clause
原因是：更新這個表的同時又查詢了這個表，查詢這個表的同時又去更新了這個表，可以理解為死鎖。mysql不支持這種更新查詢同一張表的操作
解決辦法：把要更新的幾列數(shù)據(jù)查詢出來做為一個第三方表，然后篩選更新。
3. 查詢表中多余重復(fù)試題（根據(jù)depno來判斷，除了rowid最小的一個）
a. 第一種方法：
SELECT
?*
FROM
?dept
WHERE
?dname?IN?(
??SELECT
???dname
??FROM
???dept
??GROUP?BY
???dname
??HAVING
???COUNT(1)?>?1
?)
AND?deptno?NOT?IN?(
?SELECT
??MIN(deptno)
?FROM
??dept
?GROUP?BY
??dname
?HAVING
??COUNT(1)?>?1
)
上面這種寫法正確，但是查詢的速度太慢，可以試一下下面這種方法：
b. 第二種方法：
☆根據(jù)dname分組，查找出deptno最小的。然后再查找deptno不包含剛才查出來的。這樣就查詢出了所有的重復(fù)數(shù)據(jù)（除了deptno最小的那行）。搜索Java知音公眾號，回復(fù)“后端面試”，送你一份Java面試題寶典.pdf
SELECT?*
FROM
?dept
WHERE
?deptno?NOT?IN?(
??SELECT
???dt.minno
??FROM
???(
????SELECT
?????MIN(deptno)?AS?minno
????FROM
?????dept
????GROUP?BY
?????dname
???)?dt
?)
c. 補(bǔ)充第三種方法：
SELECT
?*?
FROM
?table_name?AS?ta?
WHERE
?ta.唯一鍵?<>?(?SELECT?max(?tb.唯一鍵?)?FROM?table_name?AS?tb?WHERE?ta.判斷重復(fù)的列?=?tb.判斷重復(fù)的列?);
4. 刪除表中多余重復(fù)試題并且只留1條：
a. 第一種方法：
DELETE
FROM
?dept
WHERE
?dname?IN?(
??SELECT
???t.dname
??FROM
???(
????SELECT
?????dname
????FROM
?????dept
????GROUP?BY
?????dname
????HAVING
?????count(1)?>?1
???)?t
?)
AND?deptno?NOT?IN?(
SELECT
?dt.mindeptno
FROM
?(
??SELECT
???min(deptno)?AS?mindeptno
??FROM
???dept
??GROUP?BY
???dname
??HAVING
???count(1)?>?1
?)?dt
)
b. ☆第二種方法（與上面查詢的第二種方法對應(yīng)，只是將select改為delete）：
DELETE
FROM
?dept
WHERE
?deptno?NOT?IN?(
??SELECT
???dt.minno
??FROM
???(
????SELECT
?????MIN(deptno)?AS?minno
????FROM
?????dept
????GROUP?BY
?????dname
???)?dt
?)
c. 補(bǔ)充第三種方法（評論區(qū)推薦的一種方法）：
DELETE?
FROM
?table_name?AS?ta?
WHERE
?ta.唯一鍵?<>?(
SELECT
?t.maxid?
FROM
?(?SELECT?max(?tb.唯一鍵?)?AS?maxid?FROM?table_name?AS?tb?WHERE?ta.判斷重復(fù)的列?=?tb.判斷重復(fù)的列?)?t?
?);
二、多個字段的操作：
單個字段的如果會了，多個字段也非常簡單。就是將group by 的字段增加為你想要的即可。搜索Java知音公眾號，回復(fù)“后端面試”，送你一份Java面試題寶典.pdf
此處只寫一個，其他方法請仿照一個字段的寫即可。
DELETE
FROM
?dept
WHERE
?(dname,?db_source)?IN?(
??SELECT
???t.dname,
???t.db_source
??FROM
???(
????SELECT
?????dname,
?????db_source
????FROM
?????dept
????GROUP?BY
?????dname,
?????db_source
????HAVING
?????count(1)?>?1
???)?t
?)
AND?deptno?NOT?IN?(
?SELECT
??dt.mindeptno
?FROM
??(
???SELECT
????min(deptno)?AS?mindeptno
???FROM
????dept
???GROUP?BY
????dname,
????db_source
???HAVING
????count(1)?>?1
??)?dt
)
總結(jié)：
其實上面的方法還有很多需要優(yōu)化的地方，如果數(shù)據(jù)量太大的話，執(zhí)行起來很慢，可以考慮加優(yōu)化一下：
在經(jīng)常查詢的字段上加上索引
將*改為你需要查詢出來的字段，不要全部查詢出來
小表驅(qū)動大表用IN，大表驅(qū)動小表用EXISTS。IN適合的情況是外表數(shù)據(jù)量小的情況，而不是外表數(shù)據(jù)大的情況，因為IN會遍歷外表的全部數(shù)據(jù)，假設(shè)a表100條，b表10000條那么遍歷次數(shù)就是100*10000次，而exists則是執(zhí)行100次去判斷a表中的數(shù)據(jù)是否在b表中存在，它只執(zhí)行了a.length次數(shù)。至于哪一個效率高是要看情況的，因為in是在內(nèi)存中比較的，而exists則是進(jìn)行數(shù)據(jù)庫查詢操作的
推薦閱讀
分享我常用的5個免費(fèi)的在線 SQL 數(shù)據(jù)庫環(huán)境，簡直太方便了！
Spring Boot 三招組合拳，手把手教你打出優(yōu)雅的后端接口
MySQL 5.7 vs 8.0，你選那個？網(wǎng)友：我繼續(xù)原地踏步~
最后，推薦給大家一個有趣有料的公眾號：寫代碼的渣渣鵬，7年老程序員教你寫bug,回復(fù)”面試“有驚喜哦
掃碼關(guān)注
