mysql 數據庫超強刪除重復數據語句

在mysql中刪除重復數據的方法各千種，但我百度了N種刪除重復數據方法，個人測試小數據量還沒有問題，大數據量就卡死了，下面我來介紹超強刪除重復數據語句實例，各位朋友有興趣可參考。

月小升今天遇到的問題是students這個表有md這個字段重復。看看如何處理吧。

 代碼如下 復制代碼

select * from students
where md in (select md from students group by md having count(md) > 1) order by md

注明，這個被group的字段，請索引，否則很慢

 代碼如下 復制代碼 delete from students
where md in (select md from students group by md having count(md) > 1)
and id not in (select min(id) from students group by md having count(md )>1)

這個語句在mysql下會報錯

#1093 – You can’t specify target table ‘students’ for update in FROM clause

原因是好像mysql不准許我們進行聯合刪除內有條件語句指向自己的表。

策略是使用臨時表，存儲那些要刪除的ID

 代碼如下 復制代碼

create table tmp (id int);

insert into tmp (id) select id from students
where md in (select md from students group by md having count(md) > 1)
and id not in (select min(id) from students group by md having count(md )>1);

delete from students where id in (select id from tmp);

得出會被刪除的數據

 代碼如下 復制代碼

select * from students
where md in (select md from students group by md having count(md) > 1)
and id not in (select min(id) from students group by md having count(md )>1)

得出過濾後的數據，不刪除的數據。如果不用刪除，此sql語句可以用來顯示唯一數據

 代碼如下 復制代碼

select * from students
where
id in (select min(id) from students group by md having count(md )>1)