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

文章详细的利用了一个实例来告诉我们如何在mysql删除数据库重复记录的实现方法与原理,有需要的同学可以参考一下.

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

1. mysql>descdemo;
3. +-------+------------------+------+-----+---------+----------------+
5. |Field|Type|Null|Key|Default|Extra|
7. +-------+------------------+------+-----+---------+----------------+
9. |id|int(11)unsigned|NO|PRI|NULL|auto_increment|
11. |site|varchar(100)|NO|MUL|||
13. +-------+------------------+------+-----+---------+----------------+
15. 2rowsinset(0.00sec)
18. --数据：
20. mysql>select*fromdemoorderbyid;
22. +----+------------------------+
24. |id|site|
26. +----+------------------------+
28. |1|http://www.phpfensi.com |
30. |2||
32. |3|http://www.phpfensi.com |
34. |4|http://www.phpfensi.com |
36. |5|http://www.phpfensi.com |
38. +----+------------------------+
40. 5rowsinset(0.00sec)

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

1. mysql>deletefroma
3. ->usingdemoasa,demoasb
5. ->where(a.id>b.id)
7. ->and(a.site=b.site);
9. QueryOK,2rowsaffected(0.12sec)
13. mysql>select*fromdemoorderbyid;
15. +----+------------------------+
17. |id|site|
19. +----+------------------------+
21. |1|http://www.phpfensi.com |
23. |2||
25. |3|http://www.phpfensi.com |
27. +----+------------------------+
29. 3rowsinset(0.00sec)

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

1. mysql>deletefroma
3. ->usingdemoasa,demoasb
5. ->where(a.id<b.id)
7. ->and(a.site=b.site);
9. QueryOK,2rowsaffected(0.12sec)
13. mysql>select*fromdemoorderbyid;
15. +----+------------------------+
17. |id|site|
19. +----+------------------------+
21. |2||
23. |4|http://www.phpfensi.com |
25. |5|http://www.phpfensi.com |
27. +----+------------------------+
30. 3rowsinset(0.00sec)

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

1. mysql>SELECTa.*
3. ->FROMdemoa,demob
5. ->WHEREa.id>b.id
7. ->AND(a.site=b.site);
9. +----+------------------------+
11. |id|site|
13. +----+------------------------+
15. |1|http://www.phpfensi.com |
17. |3|http://www.phpfensi.com |
19. +----+------------------------+
21. 2rowsinset(0.00sec)

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

1. mysql>alterignoretabledemoadduniqueindexukey(site);
3. QueryOK,5rowsaffected(0.46sec)
5. Records:5Duplicates:2Warnings:0
9. mysql>select*fromdemoorderbyid;
11. +----+------------------------+
13. |id|site|
15. +----+------------------------+
17. |1|http://www.phpfensi.com |
19. |2||
21. |3|http://www.phpfensi.com |
23. +----+------------------------+
25. 3rowsinset(0.00sec)

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

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

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

1. mysql>createtabledemo_newasselect*fromdemogroupbysite;
3. QueryOK,3rowsaffected(0.19sec)
5. Records:3Duplicates:0Warnings:0
9. mysql>showtables;
11. +----------------+
13. |Tables_in_test|
15. +----------------+
17. |demo|
19. |demo_new|
21. +----------------+
23. 2rowsinset(0.00sec)
27. mysql>select*fromdemoorderbyid;
29. +----+------------------------+
31. |id|site|
33. +----+------------------------+
35. |1|http://www.phpfensi.com |
37. |2||
39. |3|http://www.phpfensi.com |
41. |4|http://www.phpfensi.com |
43. |5|http://www.phpfensi.com |
45. +----+------------------------+
47. 5rowsinset(0.00sec)
51. mysql>select*fromdemo_neworderbyid;
53. +----+------------------------+
55. |id|site|
57. +----+------------------------+
59. |1|http://www.phpfensi.com |
61. |2||
63. |3|http://www.phpfensi.com |
65. +----+------------------------+
67. 3rowsinset(0.00sec)

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

1. mysql>renametabledemotodemo_old,demo_newtodemo;
3. QueryOK,0rowsaffected(0.04sec)
7. mysql>showtables;
9. +----------------+
11. |Tables_in_test|
13. +----------------+
15. |demo|
17. |demo_old|
19. +----------------+
21. 2rowsinset(0.00sec)
25. mysql>select*fromdemoorderbyid;
27. +----+------------------------+
29. |id|site|
31. +----+------------------------+
33. |1|http://www.phpfensi.com |
35. |2||
37. |3|http://www.phpfensi.com |
39. +----+------------------------+
41. 3rowsinset(0.00sec)

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

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

如果要保持和原表信息一致,你可以使用 show create table demo;来查看原表的创建语句,然后使用原表的创建语句创建新表,接着使用 insert … select 语句插入数据,再重命名表即可.