mysql中删除重复记录sql语句

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