mysql删除重复记录sql语句

我们经常会碰到需要删除数据表中重复记录,下面我来总结了几种能删除重复记录并助相对来讲效率是非常不错的,有需要了解的朋友可进入参考.

创建一个表用来存放,要删除的记录的id信息,代码如下:

1. CREATETABLE`tmp_ids`(
2. `id`int(11),
3. `name`char(20)
4. )ENGINE=MyISAM;

如果要删除的记录不多的话,可以把这个表创建成内存表形式,代码如下:

1. CREATETABLE`tmp_ids`(
2. `id`int(11),
3. `name`char(20)
4. )ENGINE=HEAP;

然后在test表中删除重复记录,代码如下:

1. insertintotmp_idsselectmin(id),namefromtestgroupbynamehavingcount(*)>1orderbynull;
2. deletea.*fromtesta,tmp_idsbwhereb.name=a.nameanda.id>b.id;
3. truncatetabletmp_ids;

方法二,复制无重复记录到新表格,删除旧表格,然后重命名新表格为旧表名称,代码如下 :

1. mysql>select*fromduplicatewhereidin(selectmin(id)fromduplicategroupbyname);
2. +----+-------+
3. |id|name|
4. +----+-------+
5. |1|wang|
6. |3|wdang|
7. |5|wdand|
8. |6|wddda|
9. +----+-------+
10. 4rowsinset(0.01sec)
11. mysql>createtableduplicaselect*fromduplicatewhereidin(selectmin(id)fromduplicategroupbyname);
12. QueryOK,4rowsaffected(0.02sec)
13. Records:4Duplicates:0Warnings:0
14. mysql>droptableduplicate;
15. QueryOK,0rowsaffected(0.01sec)
16. mysql>altertableduplicarenametoduplicate;
17. QueryOK,0rowsaffected(0.00sec)
18. mysql>select*fromduplicate;
19. +----+-------+
20. |id|name|
21. +----+-------+
22. |1|wang|
23. |3|wdang|
24. |5|wdand|
25. |6|wddda|
26. +----+-------+
27. 4rowsinset(0.00sec)
28. --phpfensi.com
29. mysql>altertableduplicatemodifyidint(2)notnullprimarykeyauto_increment;
30. QueryOK,4rowsaffected(0.00sec)
31. Records:4Duplicates:0Warnings:0

后来想了一个语句搞定了,代码如下:

1. mysql>usemysql
2. Databasechanged
3. mysql>select*fromduplicate;
4. +----+-------+
5. |id|name|
6. +----+-------+
7. |1|wang|
8. |3|wdang|
9. |5|wdand|
10. |6|wddda|
11. |2|wang|
12. |4|wdang|
13. +----+-------+
14. 6rowsinset(0.00sec)
15. mysql>deleteduplicateasafromduplicateasa,
16. ->(
17. ->select*fromduplicategroupbynamehavingcount(1)>1)asb
18. ->wherea.name=b.nameanda.id>b.id;
19. QueryOK,2rowsaffected(0.00sec)
20. mysql>select*fromduplicate;
21. +----+-------+
22. |id|name|
23. +----+-------+
24. |1|wang|
25. |3|wdang|
26. |5|wdand|
27. |6|wddda|
28. +----+-------+
29. 4rowsinset(0.00sec)

保留ID最小的记录.

第1种,代码如下:

1. deletefrom%swheregoodsurlin(selectgoodsrulasgurl1from%s
2. #groupbygrul1havingcount(gurl1)>1)rs1andidnotin(selectmin(id)asid2from%s
3. #groupbygoodsurlhavingcount(goodsurl)>1)rs2"%(a,a,a)

第2种,这种方法不使用子集,但是我不知道怎么把rs1,rs2里面的元组嵌入到SQL语句中,代码如下:

1. exeSql="selectmin(id)from%sgroupbygoodsurlhavingcount(goodsurl)>1)"%(a,)--phpfensi.com
2. cur.execute(exeSql)
3. rs1=cur.fetchall()
4. exeSql="selectgoodsurlfrom%sgroupbygoodsurlhavingcount(goodsurl)>1"%(a,)
5. cur.execute(exeSql)
6. rs2=cur.fetchall()
7. exeSql="deletefrom%swheregoodsurlin%sandidnotin%"%(a,rs2,rs1)
8. cur.execute(exeSql)