本文章總結(jié)了關(guān)于在mysql數(shù)據(jù)庫(kù)中各種刪除重復(fù)記錄的sql語(yǔ)句,下面我們用實(shí)例介紹了操作方法,大家可參考一下.

MYSQL數(shù)據(jù)庫(kù)中，經(jīng)常會(huì)遇到重復(fù)記錄的情況,那么就需要SQL刪除重復(fù)記錄,下面為您列舉了四種刪除重復(fù)記錄的方式,用于不同的情況,希望對(duì)您有所幫助.

1、查找表中多余的重復(fù)記錄,重復(fù)記錄是根據(jù)單個(gè)字段(peopleId)來(lái)判斷,代碼如下:

1. select * from people  where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)     

2、SQL刪除重復(fù)記錄,重復(fù)記錄是根據(jù)單個(gè)字段(peopleId)來(lái)判斷,只留有rowid最小的記錄,代碼如下:

1. delete from people  where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)  and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)   

3、查找表中多余的重復(fù)記錄(多個(gè)字段),代碼如下:

1. select * from vitae a  where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)    

4、刪除表中多余的重復(fù)記錄(多個(gè)字段),只留有rowid最小的記錄,代碼如下:

1. delete from vitae a  where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)  and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 

演示數(shù)據(jù),表結(jié)構(gòu),代碼如下:

1. mysql> desc demo; 
2. +-------+------------------+------+-----+---------+----------------+ 
3. | Field | Type | Null | Key | Default | Extra | 
4. +-------+------------------+------+-----+---------+----------------+ 
5. | id | int(11) unsigned | NO | PRI | NULL | auto_increment | 
6. | site | varchar(100) | NO | MUL | | | 
7. +-------+------------------+------+-----+---------+----------------+ 
8. 2 rows in set (0.00 sec) 

數(shù)據(jù),代碼如下:

1. mysql> select * from demo order by id; 
2. +----+------------------------+ 
3. | id | site | 
4. +----+------------------------+ 
5. | 1 | http://m.survivalescaperooms.comn | 
6. | 2 | http://安卓主題_m.survivalescaperooms.com | 
7. | 3 | http://www.zhutiy.com | 
8. | 4 | http://m.survivalescaperooms.comn | 
9. | 5 | http://www.zhutiy.com | 
10. +----+------------------------+ 
11. 5 rows in set (0.00 sec) 

當(dāng)沒有創(chuàng)建表或創(chuàng)建索引權(quán)限的時(shí)候,可以用下面的方法,如果你要?jiǎng)h除較舊的重復(fù)記錄,可以使用下面的語(yǔ)句:

1. mysql> delete from a  
2. -> using demo as a, demo as b 
3. -> where (a.id > b.id) 
4. -> and (a.site = b.site); 
5. Query OK, 2 rows affected (0.12 sec) 
6.  
7. mysql> select * from demo order by id; 
8. +----+------------------------+ 
9. | id | site | 
10. +----+------------------------+ 
11. | 1 | http://m.survivalescaperooms.comn | 
12. | 2 | http://安卓主題_m.survivalescaperooms.com | 
13. | 3 | http://www.zhutiy.com | 
14. +----+------------------------+ 
15. 3 rows in set (0.00 sec) 

如果你要?jiǎng)h除較新的重復(fù)記錄,可以使用下面的語(yǔ)句:

1. mysql> delete from a  
2. -> using demo as a, demo as b 
3. -> where (a.id < b.id) 
4. -> and (a.site = b.site); 
5. Query OK, 2 rows affected (0.12 sec) 
6.  
7. mysql> select * from demo order by id; 
8. +----+------------------------+ 
9. | id | site | 
10. +----+------------------------+ 
11. | 2 | http://安卓主題_m.survivalescaperooms.com | 
12. | 4 | http://m.survivalescaperooms.comn | 
13. | 5 | http://www.zhutiy.com | 
14. +----+------------------------+ 
15. 3 rows in set (0.00 sec) 

你可以用下面的語(yǔ)句先確認(rèn)將被刪除的重復(fù)記錄,代碼如下:

1. mysql> SELECT a.*  
2. -> FROM demo a, demo b 
3. -> WHERE a.id > b.id 
4. -> AND (a.site = b.site); 
5. +----+------------------------+ 
6. | id | site | 
7. +----+------------------------+ 
8. | 1 | http://m.survivalescaperooms.comn | 
9. | 3 | http://www.zhutiy.com | 
10. +----+------------------------+ 
11. 2 rows in set (0.00 sec) 

如果有創(chuàng)建索引的權(quán)限,可以用下面的方法,在表上創(chuàng)建唯一鍵索引,代碼如下:

1. mysql> alter ignore table demo add unique index ukey (site); 
2. Query OK, 5 rows affected (0.46 sec) 
3. Records: 5 Duplicates: 2 Warnings: 0 
4.  
5. mysql> select * from demo order by id; 
6. +----+------------------------+ 
7. | id | site | 
8. +----+------------------------+ 
9. | 1 | http://m.survivalescaperooms.comn | 
10. | 2 | http://安卓主題_m.survivalescaperooms.com | 
11. | 3 | http://www.zhutiy.com | 
12. +----+------------------------+ 
13. 3 rows in set (0.00 sec) 

重復(fù)記錄被刪除后,如果需要,可以刪除索引,代碼如下:

1. mysql> alter table demo drop index ukey; 
2. Query OK, 3 rows affected (0.37 sec) 
3. Records: 3 Duplicates: 0 Warnings: 0 

如果有創(chuàng)建表的權(quán)限,可以用下面的方法,創(chuàng)建一個(gè)新表,然后將原表中不重復(fù)的數(shù)據(jù)插入新表,代碼如下:

1. mysql> create table demo_new as select * from demo group by site; 
2. Query OK, 3 rows affected (0.19 sec) 
3. Records: 3 Duplicates: 0 Warnings: 0 
4.  
5. mysql> show tables; 
6. +----------------+ 
7. | Tables_in_test | 
8. +----------------+ 
9. | demo | 
10. | demo_new | 
11. +----------------+ 
12. 2 rows in set (0.00 sec) 
13.  
14. mysql> select * from demo order by id; 
15. +----+------------------------+ 
16. | id | site | 
17. +----+------------------------+ 
18. | 1 | http://m.survivalescaperooms.comn | 
19. | 2 | http://安卓主題_m.survivalescaperooms.com | 
20. | 3 | http://www.zhutiy.com | 
21. | 4 | http://m.survivalescaperooms.comn | 
22. | 5 | http://www.zhutiy.com | 
23. +----+------------------------+ 
24. 5 rows in set (0.00 sec) 
25.  
26. mysql> select * from demo_new order by id; 
27. +----+------------------------+ 
28. | id | site | 
29. +----+------------------------+ 
30. | 1 | http://m.survivalescaperooms.comn | 
31. | 2 | http://安卓主題_m.survivalescaperooms.com | 
32. | 3 | http://www.zhutiy.com | 
33. +----+------------------------+ 
34. 3 rows in set (0.00 sec) 

然后將原表備份,將新表重命名為當(dāng)前表,代碼如下:

1. mysql> rename table demo to demo_old, demo_new to demo; 
2. Query OK, 0 rows affected (0.04 sec) 
3. mysql> show tables; 
4. +----------------+ 
5. | Tables_in_test | 
6. +----------------+ 
7. | demo | 
8. | demo_old | 
9. +----------------+ 
10. 2 rows in set (0.00 sec) 
11.  
12. mysql> select * from demo order by id; 
13. +----+------------------------+ 
14. | id | site | 
15. +----+------------------------+ 
16. | 1 | http://m.survivalescaperooms.comn | 
17. | 2 | http://安卓主題_m.survivalescaperooms.com | 
18. | 3 | http://www.zhutiy.com | 
19. +----+------------------------+ 
20. 3 rows in set (0.00 sec) 

注意:使用這種方式創(chuàng)建的表會(huì)丟失原表的索引信息,代碼如下:

1. mysql> desc demo; 
2. +-------+------------------+------+-----+---------+-------+ 
3. | Field | Type | Null | Key | Default | Extra | 
4. +-------+------------------+------+-----+---------+-------+ 
5. | id | int(11) unsigned | NO | | 0 | | 
6. | site | varchar(100) | NO | | | | 
7. +-------+------------------+------+-----+---------+-------+ 
8. 2 rows in set (0.00 sec) 

如果要保持和原表信息一致,你可以使用 show create table demo; 來(lái)查看原表的創(chuàng)建語(yǔ)句,然后使用原表的創(chuàng)建語(yǔ)句創(chuàng)建新表,接著使用 insert … select 語(yǔ)句插入數(shù)據(jù),再重命名表即可.

實(shí)例:今天無(wú)意導(dǎo)入幾個(gè)測(cè)試數(shù)據(jù),發(fā)現(xiàn)測(cè)試數(shù)據(jù)中,有很多數(shù)據(jù)記錄是相同的,我現(xiàn)在就想刪除掉這些數(shù)據(jù),如何查詢數(shù)據(jù)相同記錄呢？這個(gè)好說(shuō),以下語(yǔ)句就可以查看相同記錄的了:

SELECT COUNT(*) AS c, key_word FROM search_keywrod GROUP BY key_word HAVING c > 1  

其中 HAVING c >1 代表相同記錄數(shù)就有相同的了。

查詢相同的是比較容易實(shí)現(xiàn)的了,但是想要?jiǎng)h除這些重復(fù)的,估計(jì)就比較麻煩的了,因?yàn)槟闶且獎(jiǎng)h除自身表里的記錄,有些朋友可能就會(huì)使用到臨時(shí)表,把相同需要?jiǎng)h除的記錄，導(dǎo)到時(shí)這個(gè)臨時(shí)表,然后再通過臨時(shí)表來(lái)刪除主表,或者寫一個(gè)臨時(shí)程序,刪除掉其中的一條重復(fù)記錄.

以上二個(gè)方法刪除重復(fù)記錄,最大的麻煩就是操作繁瑣,而我們今天介紹的就是利用MYSQL自身的語(yǔ)句,不創(chuàng)建臨時(shí)表,不寫程序來(lái)刪除掉自身的重復(fù)記錄,請(qǐng)看以下SQL語(yǔ)句:

1. SELECT t1.id, t1.key_word    
2. FROM search_keywrod t1, (   
3. SELECT key_word, MIN(id) AS minid    
4. FROM search_keywrod    
5. GROUP BY key_word HAVING COUNT(key_word) > 1   
6. )t2    
7. WHERE t1.key_word = t2.key_word AND t1.id = t2.minid 

這條語(yǔ)句就跟我們第一條語(yǔ)句是一樣的功能,但是這條語(yǔ)句好處就是MIN(id),可以控制是刪除大的id重復(fù)記錄(MAX),還是刪除小的id重復(fù)記錄(MIN).

OK,經(jīng)過改良的語(yǔ)句實(shí)現(xiàn)了查詢,現(xiàn)在就可以利用DELETE FROM語(yǔ)句來(lái)刪除了,代碼如下:

1. DELETE FROM search_keywrod WHERE id IN (SELECT id FROM (   
2.    
3. SELECT t1.id   
4. FROM search_keywrod t1, (   
5. SELECT key_word, MIN(id) AS minid FROM search_keywrod    
6. GROUP BY key_word HAVING COUNT(key_word) > 1   
7. )t2    
8. WHERE t1.key_word = t2.key_word AND t1.id = t2.minid   
9.    
10. )t3)  

執(zhí)行時(shí),請(qǐng)多執(zhí)行幾次,因?yàn)槊看蝿h除時(shí),只刪除掉重復(fù)的一次記錄,如果你一條記錄重復(fù)五次,那你就要執(zhí)行五次的了,所以多執(zhí)行幾次,直到?jīng)]有可刪除的記錄了,這樣你直接一條語(yǔ)句刪除掉重復(fù)的mysql記錄功能就實(shí)現(xiàn)了.