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. 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
16. mysql>loaddatainfile'c:\users_groups.txt'intotableusers_groupsfields
17. terminatedby','linesterminatedby'n';
18. QueryOK,14rowsaffected(0.05sec)
19. Records:14Deleted:0Skipped:0Warnings:0
21. mysql>select*fromusers_groups;
23. queryresult(14records)
25. iduidgid
26. 111502
27. 2107502
28. 3100503
29. 4110501
30. 5112501
31. 6104502
32. 7100502
33. 8100501
34. 9102501
35. 10104502
36. 11100502
37. 12100501
38. 13102501
39. 14110501
40. 14rowsinset(0.00sec)

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

1. mysql>createtemporarytabletmp_wrapselect*fromusers_groupsgroupbyuidhavingcount(1)>=1;
2. QueryOK,7rowsaffected(0.11sec)
3. Records:7Duplicates:0Warnings:0
5. mysql>truncatetableusers_groups;
6. QueryOK,14rowsaffected(0.03sec)
8. mysql>insertintousers_groupsselect*fromtmp_wrap;
9. QueryOK,7rowsaffected(0.03sec)
10. Records:7Duplicates:0Warnings:0
12. mysql>select*fromusers_groups;
13. --phpfensi.com
14. queryresult(7records)
15. iduidgid
16. 111502
17. 2107502
18. 3100503
19. 4110501
20. 5112501
21. 6104502
22. 9102501
24. mysql>droptabletmp_wrap;
25. 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)
9. queryresult(7records)
10. iduidgid
11. 111502
12. 2107502
13. 3100503
14. 4110501
15. 5112501
16. 6104502
17. 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)
16. queryresult(3records)
17. idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
18. 1PRIMARY<derived2>ALL(NULL)(NULL)(NULL)(NULL)4
19. 1PRIMARYarefPRIMARY,f_uidf_uid4b.uid1Usingwhere
20. 2DERIVEDusers_groupsindex(NULL)f_uid4(NULL)14

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

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.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. 5rowsinset(0.00sec)
26. mysql>select*fromdemo_neworderbyid;
27. +----+------------------------+
28. |id|site|
29. +----+------------------------+
30. |1|http://www.phpfensi.com |
31. |2|http://phpfensi.com |
32. |3|http://www.phpfensi.com |
33. +----+------------------------+
34. 3rowsinset(0.00sec)

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

1. mysql>renametabledemotodemo_old,demo_newtodemo;
2. QueryOK,0rowsaffected(0.04sec)
4. mysql>showtables;
5. +----------------+
6. |Tables_in_test|
7. +----------------+
8. |demo|
9. |demo_old|
10. +----------------+
11. 2rowsinset(0.00sec)
13. mysql>select*fromdemoorderbyid;
14. +----+------------------------+
15. |id|site|
16. +----+------------------------+
17. |1|http://www.phpfensi.com |
18. |2|http://phpfensi.com |
19. |3|http://www.phpfensi.com |
20. +----+------------------------+
21. 3rowsinset(0.00sec)