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

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

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

1. mysql> desc demo; 
2.  
3. +-------+------------------+------+-----+---------+----------------+ 
4.  
5. | Field | Type | Null | Key | Default | Extra | 
6.  
7. +-------+------------------+------+-----+---------+----------------+ 
8.  
9. | id | int(11) unsigned | NO | PRI | NULL | auto_increment | 
10.  
11. | site | varchar(100) | NO | MUL | | | 
12.  
13. +-------+------------------+------+-----+---------+----------------+ 
14.  
15. 2 rows in set (0.00 sec) 
16.  
17.  
18. --数据： 
19.  
20. mysql> select * from demo order by id; 
21.  
22. +----+------------------------+ 
23.  
24. | id | site | 
25.  
26. +----+------------------------+ 
27.  
28. | 1 | http://www.phpfensi.com | 
29.  
30. | 2 |  | 
31.  
32. | 3 | http://www.phpfensi.com | 
33.  
34. | 4 | http://www.phpfensi.com | 
35.  
36. | 5 | http://www.phpfensi.com | 
37.  
38. +----+------------------------+ 
39.  
40. 5 rows in set (0.00 sec) 

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

1. mysql> delete from a 
2.  
3. -> using demo as a, demo as b 
4.  
5. -> where (a.id > b.id) 
6.  
7. -> and (a.site = b.site); 
8.  
9. Query OK, 2 rows affected (0.12 sec) 
10.  
11.  
12.  
13. mysql> select * from demo order by id; 
14.  
15. +----+------------------------+ 
16.  
17. | id | site | 
18.  
19. +----+------------------------+ 
20.  
21. | 1 | http://www.phpfensi.com | 
22.  
23. | 2 |  | 
24.  
25. | 3 | http://www.phpfensi.com | 
26.  
27. +----+------------------------+ 
28.  
29. 3 rows in set (0.00 sec) 

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

1. mysql> delete from a 
2.  
3. -> using demo as a, demo as b 
4.  
5. -> where (a.id < b.id) 
6.  
7. -> and (a.site = b.site); 
8.  
9. Query OK, 2 rows affected (0.12 sec) 
10.  
11.  
12.  
13. mysql> select * from demo order by id; 
14.  
15. +----+------------------------+ 
16.  
17. | id | site | 
18.  
19. +----+------------------------+ 
20.  
21. | 2 |  | 
22.  
23. | 4 | http://www.phpfensi.com | 
24.  
25. | 5 | http://www.phpfensi.com | 
26.  
27. +----+------------------------+ 
28.  
29.  
30. 3 rows in set (0.00 sec) 

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

1. mysql> SELECT a.* 
2.  
3. -> FROM demo a, demo b 
4.  
5. -> WHERE a.id > b.id 
6.  
7. -> AND (a.site = b.site); 
8.  
9. +----+------------------------+ 
10.  
11. | id | site | 
12.  
13. +----+------------------------+ 
14.  
15. | 1 | http://www.phpfensi.com | 
16.  
17. | 3 | http://www.phpfensi.com | 
18.  
19. +----+------------------------+ 
20.  
21. 2 rows in set (0.00 sec) 

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

1. mysql> alter ignore table demo add unique index ukey (site); 
2.  
3. Query OK, 5 rows affected (0.46 sec) 
4.  
5. Records: 5 Duplicates: 2 Warnings: 0 
6.  
7.  
8.  
9. mysql> select * from demo order by id; 
10.  
11. +----+------------------------+ 
12.  
13. | id | site | 
14.  
15. +----+------------------------+ 
16.  
17. | 1 | http://www.phpfensi.com | 
18.  
19. | 2 |  | 
20.  
21. | 3 | http://www.phpfensi.com | 
22.  
23. +----+------------------------+ 
24.  
25. 3 rows in set (0.00 sec) 

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

1. mysql> alter table demo drop index ukey; 
2.  
3. Query OK, 3 rows affected (0.37 sec) 
4.  
5. Records: 3 Duplicates: 0 Warnings: 0 

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

1. mysql> create table demo_new as select * from demo group by site; 
2.  
3. Query OK, 3 rows affected (0.19 sec) 
4.  
5. Records: 3 Duplicates: 0 Warnings: 0 
6.  
7.  
8.  
9. mysql> show tables; 
10.  
11. +----------------+ 
12.  
13. | Tables_in_test | 
14.  
15. +----------------+ 
16.  
17. | demo | 
18.  
19. | demo_new | 
20.  
21. +----------------+ 
22.  
23. 2 rows in set (0.00 sec) 
24.  
25.  
26.  
27. mysql> select * from demo order by id; 
28.  
29. +----+------------------------+ 
30.  
31. | id | site | 
32.  
33. +----+------------------------+ 
34.  
35. | 1 | http://www.phpfensi.com | 
36.  
37. | 2 |  | 
38.  
39. | 3 | http://www.phpfensi.com | 
40.  
41. | 4 | http://www.phpfensi.com | 
42.  
43. | 5 | http://www.phpfensi.com | 
44.  
45. +----+------------------------+ 
46.  
47. 5 rows in set (0.00 sec) 
48.  
49.  
50.  
51. mysql> select * from demo_new order by id; 
52.  
53. +----+------------------------+ 
54.  
55. | id | site | 
56.  
57. +----+------------------------+ 
58.  
59. | 1 | http://www.phpfensi.com | 
60.  
61. | 2 |  | 
62.  
63. | 3 | http://www.phpfensi.com | 
64.  
65. +----+------------------------+ 
66.  
67. 3 rows in set (0.00 sec) 

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

1. mysql> rename table demo to demo_old, demo_new to demo; 
2.  
3. Query OK, 0 rows affected (0.04 sec) 
4.  
5.  
6.  
7. mysql> show tables; 
8.  
9. +----------------+ 
10.  
11. | Tables_in_test | 
12.  
13. +----------------+ 
14.  
15. | demo | 
16.  
17. | demo_old | 
18.  
19. +----------------+ 
20.  
21. 2 rows in set (0.00 sec) 
22.  
23.  
24.  
25. mysql> select * from demo order by id; 
26.  
27. +----+------------------------+ 
28.  
29. | id | site | 
30.  
31. +----+------------------------+ 
32.  
33. | 1 | http://www.phpfensi.com | 
34.  
35. | 2 |  | 
36.  
37. | 3 | http://www.phpfensi.com | 
38.  
39. +----+------------------------+ 
40.  
41. 3 rows in set (0.00 sec) 

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

1. mysql> desc demo; 
2.  
3. +-------+------------------+------+-----+---------+-------+ 
4.  
5. | Field | Type | Null | Key | Default | Extra | 
6.  
7. +-------+------------------+------+-----+---------+-------+ 
8.  
9. | id | int(11) unsigned | NO | | 0 | | 
10.  
11. | site | varchar(100) | NO | | | | 
12.  
13. +-------+------------------+------+-----+---------+-------+ 
14.  
15. 2 rows in set (0.00 sec) 

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