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

木木老贼 互联网营销达人~

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

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

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

  1. select*frompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)

2、SQL删除重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录,代码如下:

  1. deletefrompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)androwidnotin(selectmin(rowid)frompeoplegroupbypeopleIdhavingcount(peopleId)>1)

3、查找表中多余的重复记录(多个字段),代码如下:

  1. select*fromvitaeawhere(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录,代码如下:

  1. deletefromvitaeawhere(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)

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

  1. mysql>descdemo;
  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. 2rowsinset(0.00sec)

数据,代码如下:

  1. mysql>select*fromdemoorderbyid;
  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. 5rowsinset(0.00sec)

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

  1. mysql>deletefroma
  2. ->usingdemoasa,demoasb
  3. ->where(a.id>b.id)
  4. ->and(a.site=b.site);
  5. QueryOK,2rowsaffected(0.12sec)
  7. mysql>select*fromdemoorderbyid;
  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. 3rowsinset(0.00sec)

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

  1. mysql>deletefroma
  2. ->usingdemoasa,demoasb
  3. ->where(a.id<b.id)
  4. ->and(a.site=b.site);
  5. QueryOK,2rowsaffected(0.12sec)
  7. mysql>select*fromdemoorderbyid;
  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. 3rowsinset(0.00sec)

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

  1. mysql>SELECTa.*
  2. ->FROMdemoa,demob
  3. ->WHEREa.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. 2rowsinset(0.00sec)

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

  1. mysql>alterignoretabledemoadduniqueindexukey(site);
  2. QueryOK,5rowsaffected(0.46sec)
  3. Records:5Duplicates:2Warnings:0
  5. mysql>select*fromdemoorderbyid;
  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. 3rowsinset(0.00sec)

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

  1. mysql>altertabledemodropindexukey;
  2. QueryOK,3rowsaffected(0.37sec)
  3. Records:3Duplicates:0Warnings:0

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

  1. mysql>createtabledemo_newasselect*fromdemogroupbysite;
  2. QueryOK,3rowsaffected(0.19sec)
  3. Records:3Duplicates:0Warnings:0
  5. mysql>showtables;
  6. +----------------+
  7. |Tables_in_test|
  8. +----------------+
  9. |demo|
  10. |demo_new|
  11. +----------------+
  12. 2rowsinset(0.00sec)
  14. mysql>select*fromdemoorderbyid;
  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. 5rowsinset(0.00sec)
  26. mysql>select*fromdemo_neworderbyid;
  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. 3rowsinset(0.00sec)

然后将原表备份,将新表重命名为当前表,代码如下:

  1. mysql>renametabledemotodemo_old,demo_newtodemo;
  2. QueryOK,0rowsaffected(0.04sec)
  3. mysql>showtables;
  4. +----------------+
  5. |Tables_in_test|
  6. +----------------+
  7. |demo|
  8. |demo_old|
  9. +----------------+
  10. 2rowsinset(0.00sec)
  12. mysql>select*fromdemoorderbyid;
  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. 3rowsinset(0.00sec)

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

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

如果要保持和原表信息一致,你可以使用 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. SELECTt1.id,t1.key_word
  2. FROMsearch_keywrodt1,(
  3. SELECTkey_word,MIN(id)ASminid
  4. FROMsearch_keywrod
  5. GROUPBYkey_wordHAVINGCOUNT(key_word)>1
  6. )t2
  7. WHEREt1.key_word=t2.key_wordANDt1.id=t2.minid

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

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

  1. DELETEFROMsearch_keywrodWHEREidIN(SELECTidFROM(
  3. SELECTt1.id
  4. FROMsearch_keywrodt1,(
  5. SELECTkey_word,MIN(id)ASminidFROMsearch_keywrod
  6. GROUPBYkey_wordHAVINGCOUNT(key_word)>1
  7. )t2
  8. WHEREt1.key_word=t2.key_wordANDt1.id=t2.minid
  10. )t3)

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

相关广告
  • MySQL 数据库中删除重复记录方法总结 MySQL 数据库中删除重复记录方法总结 MySQL 数据库中删除重复记录方法总结
相关阅读

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

2019/10/10 17:33:45 | 谷歌SEO算法 | SEO技术