当前位置：首页 > Mysql教程 > 列表

mysql中删除重复记录sql语句

发布:smiling　来源: PHP粉丝网 　添加日期：2014-10-09 15:19:19　浏览: 评论：0　

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

Tags: mysql删除重复记录 mysql删除

分享到：

相关文章

推荐文章

热门文章

最新评论文章