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

MySQL 数据库中删除重复记录方法总结

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

本文章总结了关于在mysql数据库中各种删除重复记录的sql语句,下面我们用实例介绍了操作方法,大家可参考一下.

MYSQL数据库中，经常会遇到重复记录的情况,那么就需要SQL删除重复记录,下面为您列举了四种删除重复记录的方式,用于不同的情况,希望对您有所帮助.

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,代码如下:

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

2、SQL删除重复记录,重复记录是根据单个字段(peopleId)来判断,只留有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、查找表中多余的重复记录(多个字段),代码如下:

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

4、删除表中多余的重复记录(多个字段),只留有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) 

演示数据,表结构,代码如下:

  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) 

数据,代码如下:

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

当没有创建表或创建索引权限的时候,可以用下面的方法,如果你要删除较旧的重复记录,可以使用下面的语句:

  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://www.phpfensi.comn | 
  12. | 2 | http://安卓主题_www.phpfensi.com | 
  13. | 3 | http://www.zhutiy.com | 
  14. +----+------------------------+ 
  15. 3 rows in set (0.00 sec) 

如果你要删除较新的重复记录,可以使用下面的语句:

  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://安卓主题_www.phpfensi.com | 
  12. | 4 | http://www.phpfensi.comn | 
  13. | 5 | http://www.zhutiy.com | 
  14. +----+------------------------+ 
  15. 3 rows in set (0.00 sec) 

你可以用下面的语句先确认将被删除的重复记录,代码如下:

  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://www.phpfensi.comn | 
  9. | 3 | http://www.zhutiy.com | 
  10. +----+------------------------+ 
  11. 2 rows in set (0.00 sec) 

如果有创建索引的权限,可以用下面的方法,在表上创建唯一键索引,代码如下:

  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://www.phpfensi.comn | 
  10. | 2 | http://安卓主题_www.phpfensi.com | 
  11. | 3 | http://www.zhutiy.com | 
  12. +----+------------------------+ 
  13. 3 rows in set (0.00 sec) 

重复记录被删除后,如果需要,可以删除索引,代码如下:

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

如果有创建表的权限,可以用下面的方法,创建一个新表,然后将原表中不重复的数据插入新表,代码如下:

  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.comn | 
  19. | 2 | http://安卓主题_www.phpfensi.com | 
  20. | 3 | http://www.zhutiy.com | 
  21. | 4 | http://www.phpfensi.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://www.phpfensi.comn | 
  31. | 2 | http://安卓主题_www.phpfensi.com | 
  32. | 3 | http://www.zhutiy.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. 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://www.phpfensi.comn | 
  17. | 2 | http://安卓主题_www.phpfensi.com | 
  18. | 3 | http://www.zhutiy.com | 
  19. +----+------------------------+ 
  20. 3 rows in set (0.00 sec) 

注意:使用这种方式创建的表会丢失原表的索引信息,代码如下:

  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; 来查看原表的创建语句,然后使用原表的创建语句创建新表,接着使用 insert … select 语句插入数据,再重命名表即可.

实例:今天无意导入几个测试数据,发现测试数据中,有很多数据记录是相同的,我现在就想删除掉这些数据,如何查询数据相同记录呢？这个好说,以下语句就可以查看相同记录的了:

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

其中 HAVING c >1 代表相同记录数就有相同的了。

查询相同的是比较容易实现的了,但是想要删除这些重复的,估计就比较麻烦的了,因为你是要删除自身表里的记录,有些朋友可能就会使用到临时表,把相同需要删除的记录，导到时这个临时表,然后再通过临时表来删除主表,或者写一个临时程序,删除掉其中的一条重复记录.

以上二个方法删除重复记录,最大的麻烦就是操作繁琐,而我们今天介绍的就是利用MYSQL自身的语句,不创建临时表,不写程序来删除掉自身的重复记录,请看以下SQL语句:

  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 

这条语句就跟我们第一条语句是一样的功能,但是这条语句好处就是MIN(id),可以控制是删除大的id重复记录(MAX),还是删除小的id重复记录(MIN).

OK,经过改良的语句实现了查询,现在就可以利用DELETE FROM语句来删除了,代码如下:

  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)  

执行时,请多执行几次,因为每次删除时,只删除掉重复的一次记录,如果你一条记录重复五次,那你就要执行五次的了,所以多执行几次,直到没有可删除的记录了,这样你直接一条语句删除掉重复的mysql记录功能就实现了.

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

分享到：

相关文章

推荐文章

热门文章

最新评论文章