在sql中我們經常會碰到有重復的一些數據,下面我來介紹在mysql中刪除重復記錄的多種方法,有需要的朋友可參考參考.

刪除重復記錄方法一:

1.新建一個臨時表,代碼如下:

create table tmp as select * from youtable group by  name（name為不希望有重復的列）

2.刪除原來的表,代碼如下:drop table youtable

3.重命名表,代碼如下:

alter table tmp rename youtable

但是這個方法有個問題，由臨時表轉變過來的最終表，其表結構會和原來的不一致，需要手工更改。這個問題，待解決。

刪除重復記錄方法二:

1.新建一個臨時表,代碼如下:

CREATE TABLE tmp AS SELECT * FROM youtable GROUP BY name（name為不希望有重復的列）

2.清空原來的表,代碼如下:

TRUNCATE TABLE youtable

3.把臨時表插入到youtable,代碼如下:

INSERT INTO tablename SELECT  * FROM temp

4.刪除臨時表,代碼如下:

DROP TABLE  temp

刪除重復記錄方法三:代碼如下:

delete table where ID not in(select min(ID) from table group by name(name:重復的字段))

刪除重復記錄方法四:

具體實現如下:

1. Table         Create Table                                             
2. ------------  -------------------------------------------------------- 
3. users_groups  CREATE TABLE `users_groups` (                            
4.                 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,         
5.                 `uid` int(11) NOT NULL,                                
6.                 `gid` int(11) NOT NULL,                                
7.                 PRIMARY KEY (`id`)                                     
8.               ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8  

users_groups.txt內容:

1. 1,11,502 
2. 2,107,502 
3. 3,100,503 
4. 4,110,501 
5. 5,112,501 
6. 6,104,502 
7. 7,100,502 
8. 8,100,501 
9. 9,102,501 
10. 10,104,502 
11. 11,100,502 
12. 12,100,501 
13. 13,102,501 
14. 14,110,501 
15.  
16. mysql> load data infile 'c:/users_groups.txt' into table users_groups fields 
17. terminated by ',' lines terminated by 'n'; 
18. Query OK, 14 rows affected (0.05 sec) 
19. Records: 14  Deleted: 0  Skipped: 0  Warnings: 0 
20.  
21. mysql> select * from users_groups; 
22.  
23. query result(14 records) 
24.  
25. id uid gid  
26. 1 11 502  
27. 2 107 502  
28. 3 100 503  
29. 4 110 501  
30. 5 112 501  
31. 6 104 502  
32. 7 100 502  
33. 8 100 501  
34. 9 102 501  
35. 10 104 502  
36. 11 100 502  
37. 12 100 501  
38. 13 102 501  
39. 14 110 501  
40. 14 rows in set (0.00 sec) 

根據一位兄弟的建議修改,代碼如下:

1. mysql> create temporary table tmp_wrap select * from users_groups group by uid having count(1) >= 1; 
2. Query OK, 7 rows affected (0.11 sec) 
3. Records: 7  Duplicates: 0  Warnings: 0 
4.  
5. mysql> truncate table users_groups; 
6. Query OK, 14 rows affected (0.03 sec) 
7.  
8. mysql> insert into users_groups select * from tmp_wrap; 
9. Query OK, 7 rows affected (0.03 sec) 
10. Records: 7  Duplicates: 0  Warnings: 0 
11.  
12. mysql> select * from users_groups; 
13. --Vevb.com 
14. query result(7 records) 
15. id uid gid  
16. 1 11 502  
17. 2 107 502  
18. 3 100 503  
19. 4 110 501  
20. 5 112 501  
21. 6 104 502  
22. 9 102 501 
23.  
24. mysql> drop table tmp_wrap; 
25. Query OK, 0 rows affected (0.05 sec) 

2、還有一個很精簡的辦法.

查找重復的,并且除掉最小的那個,代碼如下:

1. delete users_groups as a from users_groups as a, 
2. ( 
3. select *,min(id) from users_groups group by uid having count(1) > 1 
4. ) as b 
5.  where a.uid = b.uid and a.id > b.id; 
6. (7 row(s)affected) 
7. (0 ms taken) 
8.  
9. query result(7 records) 
10. id uid gid  
11. 1 11 502  
12. 2 107 502  
13. 3 100 503  
14. 4 110 501  
15. 5 112 501  
16. 6 104 502  
17. 9 102 501 

3、現在來看一下這兩個辦法的效率,運行一下以下SQL 語句,代碼如下:

1. create index f_uid on users_groups(uid); 
2. explain select * from users_groups group by uid having count(1) > 1 union all 
3. select * from users_groups group by uid having count(1) = 1; 
4. explain select * from  users_groups as a, 
5. ( 
6. select *,min(id) from users_groups group by uid having count(1) > 1 
7. ) as b 
8.  where a.uid = b.uid and a.id > b.id; 
9. query result(3 records) 
10. id select_type table type possible_keys key key_len ref rows Extra  
11. 1 PRIMARY users_groups index (NULL) f_uid 4 (NULL) 14    
12. 2 UNION users_groups index (NULL) f_uid 4 (NULL) 14    
13. (NULL) UNION RESULT <union1,2> ALL (NULL) (NULL) (NULL) (NULL) (NULL)   
14.  
15.  
16. query result(3 records) 
17. id select_type table type possible_keys key key_len ref rows Extra  
18. 1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 4    
19. 1 PRIMARY a ref PRIMARY,f_uid f_uid 4 b.uid 1 Using where  
20. 2 DERIVED users_groups index (NULL) f_uid 4 (NULL) 14   

很明顯的第二個比第一個掃描的函數要少,當沒有創建表或創建索引權限的時候,創建一個新表,然后將原表中不重復的數據插入新表,代碼如下:

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.com  |  
19. |  2 | http://Vevb.com        |  
20. |  3 | http://m.survivalescaperooms.com |  
21. |  4 | http://m.survivalescaperooms.com  |  
22. |  5 | http://m.survivalescaperooms.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.com  |  
31. |  2 | http://Vevb.com        |  
32. |  3 | http://m.survivalescaperooms.com |  
33. +----+------------------------+  
34. 3 rows in set (0.00 sec) 

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

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