mysql中删除重复记录sql语句

小潘seo 重庆小潘seo博客和你一起学习SEO知识,共同分享SEO优化~

在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. TableCreateTable
  2. --------------------------------------------------------------------
  3. users_groupsCREATETABLE`users_groups`(
  4. `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
  5. `uid`int(11)NOTNULL,
  6. `gid`int(11)NOTNULL,
  7. PRIMARYKEY(`id`)
  8. )ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=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. mysql>loaddatainfile'c:\users_groups.txt'intotableusers_groupsfields
  16. terminatedby','linesterminatedby'n';
  17. QueryOK,14rowsaffected(0.05sec)
  18. Records:14Deleted:0Skipped:0Warnings:0
  19. mysql>select*fromusers_groups;
  20. queryresult(14records)
  21. iduidgid
  22. 111502
  23. 2107502
  24. 3100503
  25. 4110501
  26. 5112501
  27. 6104502
  28. 7100502
  29. 8100501
  30. 9102501
  31. 10104502
  32. 11100502
  33. 12100501
  34. 13102501
  35. 14110501
  36. 14rowsinset(0.00sec)

根据一位兄弟的建议修改,代码如下:

  1. mysql>createtemporarytabletmp_wrapselect*fromusers_groupsgroupbyuidhavingcount(1)>=1;
  2. QueryOK,7rowsaffected(0.11sec)
  3. Records:7Duplicates:0Warnings:0
  4. mysql>truncatetableusers_groups;
  5. QueryOK,14rowsaffected(0.03sec)
  6. mysql>insertintousers_groupsselect*fromtmp_wrap;
  7. QueryOK,7rowsaffected(0.03sec)
  8. Records:7Duplicates:0Warnings:0
  9. mysql>select*fromusers_groups;
  10. --phpfensi.com
  11. queryresult(7records)
  12. iduidgid
  13. 111502
  14. 2107502
  15. 3100503
  16. 4110501
  17. 5112501
  18. 6104502
  19. 9102501
  20. mysql>droptabletmp_wrap;
  21. QueryOK,0rowsaffected(0.05sec)

2、还有一个很精简的办法.

查找重复的,并且除掉最小的那个,代码如下:

  1. deleteusers_groupsasafromusers_groupsasa,
  2. (
  3. select*,min(id)fromusers_groupsgroupbyuidhavingcount(1)>1
  4. )asb
  5. wherea.uid=b.uidanda.id>b.id;
  6. (7row(s)affected)
  7. (0mstaken)
  8. queryresult(7records)
  9. iduidgid
  10. 111502
  11. 2107502
  12. 3100503
  13. 4110501
  14. 5112501
  15. 6104502
  16. 9102501

3、现在来看一下这两个办法的效率,运行一下以下SQL 语句,代码如下:

  1. createindexf_uidonusers_groups(uid);
  2. explainselect*fromusers_groupsgroupbyuidhavingcount(1)>1unionall
  3. select*fromusers_groupsgroupbyuidhavingcount(1)=1;
  4. explainselect*fromusers_groupsasa,
  5. (
  6. select*,min(id)fromusers_groupsgroupbyuidhavingcount(1)>1
  7. )asb
  8. wherea.uid=b.uidanda.id>b.id;
  9. queryresult(3records)
  10. idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
  11. 1PRIMARYusers_groupsindex(NULL)f_uid4(NULL)14
  12. 2UNIONusers_groupsindex(NULL)f_uid4(NULL)14
  13. (NULL)UNIONRESULT<union1,2>ALL(NULL)(NULL)(NULL)(NULL)(NULL)
  14. queryresult(3records)
  15. idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
  16. 1PRIMARY<derived2>ALL(NULL)(NULL)(NULL)(NULL)4
  17. 1PRIMARYarefPRIMARY,f_uidf_uid4b.uid1Usingwhere
  18. 2DERIVEDusers_groupsindex(NULL)f_uid4(NULL)14

很明显的第二个比第一个扫描的函数要少,当没有创建表或创建索引权限的时候,创建一个新表,然后将原表中不重复的数据插入新表,代码如下:

  1. mysql>createtabledemo_newasselect*fromdemogroupbysite;
  2. QueryOK,3rowsaffected(0.19sec)
  3. Records:3Duplicates:0Warnings:0
  4. mysql>showtables;
  5. +----------------+
  6. |Tables_in_test|
  7. +----------------+
  8. |demo|
  9. |demo_new|
  10. +----------------+
  11. 2rowsinset(0.00sec)
  12. mysql>select*fromdemoorderbyid;
  13. +----+------------------------+
  14. |id|site|
  15. +----+------------------------+
  16. |1|http://www.phpfensi.com |
  17. |2|http://phpfensi.com |
  18. |3|http://www.phpfensi.com |
  19. |4|http://www.phpfensi.com |
  20. |5|http://www.phpfensi.com |
  21. +----+------------------------+
  22. 5rowsinset(0.00sec)
  23. mysql>select*fromdemo_neworderbyid;
  24. +----+------------------------+
  25. |id|site|
  26. +----+------------------------+
  27. |1|http://www.phpfensi.com |
  28. |2|http://phpfensi.com |
  29. |3|http://www.phpfensi.com |
  30. +----+------------------------+
  31. 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)
  11. mysql>select*fromdemoorderbyid;
  12. +----+------------------------+
  13. |id|site|
  14. +----+------------------------+
  15. |1|http://www.phpfensi.com |
  16. |2|http://phpfensi.com |
  17. |3|http://www.phpfensi.com |
  18. +----+------------------------+
  19. 3rowsinset(0.00sec)

相关广告
  • mysql中删除重复记录sql语句 mysql中删除重复记录sql语句 mysql中删除重复记录sql语句
相关阅读

mysql中删除重复记录sql语句

2019/10/10 17:34:16 | 谷歌SEO算法 | SEO优化

mysql删除数据库重复记录实现方法

2019/10/10 17:33:05 | 谷歌SEO算法 | 挖矿