Mysql中查找并删除重复数据的方法

(一)单个字段

1、查找表中多余的重复记录,根据question_title字段来判断,代码如下:

1. select * from questions where question_title in (select question_title from people group by question_title having count(question_title) > 1) 

2、删除表中多余的重复记录,根据question_title字段来判断,只留有一个记录,代码如下:

1. delete from questions 
2. where peopleId in (select peopleId from people group by peopleId having count(question_title) > 1) 
3. and min(id) not in (select question_id from questions group by question_title having count(question_title)>1)  

(二)多个字段

删除表中多余的重复记录,多个字段,只留有rowid最小的记录,代码如下:

1. DELETE FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1) 

用上述语句无法删除,创建了临时表才删的,求各位达人解释一下,代码如下:

1. CREATE TABLE tmp AS SELECT question_id FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1); 
2.  
3. DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp); 
4.  
5. DROP TABLE tmp; 

(三) 存储过程,代码如下:

1. declare @max integer,@id integer 
2.  
3. declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 
4.  
5. open cur_rows 
6.  
7. fetch cur_rows into @id,@max 
8.  
9. while @@fetch_status=0 
10.  
11. begin 
12.  
13. select @max = @max -1 
14.  
15. set rowcount @max 
16.  
17. delete from 表名 where 主字段 = @id 
18.  
19. fetch cur_rows into @id,@max 
20.  
21. end 
22.  
23. close cur_rows 
24.  
25. set rowcount 0 

例,数据库版本 Server version: 5.1.41-community-log MySQL Community Server (GPL).

例1,表中有主键(可唯一标识的字段),且该字段为数字类型,测试数据,代码如下:

1. /* 表结构 */ 
2. DROP TABLE IF EXISTS `t1`; 
3. CREATE TABLE IF NOT EXISTS `t1`( 
4.   `id` INT(1) NOT NULL AUTO_INCREMENT, 
5.   `name` VARCHAR(20) NOT NULL, 
6.   `add` VARCHAR(20) NOT NULL, 
7.   PRIMARY KEY(`id`) 
8. )Engine=InnoDB; 
9.  
10. /* 插入测试数据 */ 
11. INSERT INTO `t1`(`name`,`add`) VALUES 
12. ('abc',"123"), 
13. ('abc',"123"), 
14. ('abc',"321"), 
15. ('abc',"123"), 
16. ('xzy',"123"), 
17. ('xzy',"456"), 
18. ('xzy',"456"), 
19. ('xzy',"456"), 
20. ('xzy',"789"), 
21. ('xzy',"987"), 
22. ('xzy',"789"), 
23. ('ijk',"147"), 
24. ('ijk',"147"), 
25. ('ijk',"852"), 
26. ('opq',"852"), 
27. ('opq',"963"), 
28. ('opq',"741"), 
29. ('tpk',"741"), 
30. ('tpk',"963"), 
31. ('tpk',"963"), 
32. ('wer',"546"), 
33. ('wer',"546"), 
34. ('once',"546"); 
35.  
36. SELECT * FROM `t1`; 
37. +----+------+-----+ 
38. | id | name | add | 
39. +----+------+-----+ 
40. |  1 | abc  | 123 | 
41. |  2 | abc  | 123 | 
42. |  3 | abc  | 321 | 
43. |  4 | abc  | 123 | 
44. |  5 | xzy  | 123 | 
45. |  6 | xzy  | 456 | 
46. |  7 | xzy  | 456 | 
47. |  8 | xzy  | 456 | 
48. |  9 | xzy  | 789 | 
49. | 10 | xzy  | 987 | 
50. | 11 | xzy  | 789 | 
51. | 12 | ijk  | 147 | 
52. | 13 | ijk  | 147 | 
53. | 14 | ijk  | 852 | 
54. | 15 | opq  | 852 | 
55. | 16 | opq  | 963 | 
56. | 17 | opq  | 741 | 
57. | 18 | tpk  | 741 | 
58. | 19 | tpk  | 963 | 
59. | 20 | tpk  | 963 | 
60. | 21 | wer  | 546 | 
61. | 22 | wer  | 546 | 
62. | 23 | once | 546 | 
63. +----+------+-----+ 
64. rows in set (0.00 sec) 

查找id最小的重复数据(只查找id字段),代码如下:

1. /* 查找id最小的重复数据(只查找id字段) */ 
2. SELECT DISTINCT MIN(`id`) AS `id` 
3. FROM `t1` 
4. GROUP BY `name`,`add` 
5. HAVING COUNT(1) > 1; 
6. +------+ 
7. | id   | 
8. +------+ 
9. |    1 | 
10. |   12 | 
11. |   19 | 
12. |   21 | 
13. |    6 | 
14. |    9 | 
15. +------+ 
16. rows in set (0.00 sec) 

查找所有重复数据,代码如下:

1. /* 查找所有重复数据 */ 
2. SELECT `t1`.* 
3. FROM `t1`,( 
4.   SELECT `name`,`add` 
5.   FROM `t1` 
6.   GROUP BY `name`,`add` 
7.   HAVING COUNT(1) > 1 
8. ) AS `t2` 
9. WHERE `t1`.`name` = `t2`.`name` 
10.   AND `t1`.`add` = `t2`.`add`; 
11. +----+------+-----+ 
12.  
13.  
14. | id | name | add | 
15. +----+------+-----+ 
16. |  1 | abc  | 123 | 
17. |  2 | abc  | 123 | 
18. |  4 | abc  | www.Vevb.com| 
19. |  6 | xzy  | 456 | 
20. |  7 | xzy  | 456 | 
21. |  8 | xzy  | 456 | 
22. |  9 | xzy  | 789 | 
23. | 11 | xzy  | 789 | 
24. | 12 | ijk  | 147 | 
25. | 13 | ijk  | 147 | 
26. | 19 | tpk  | 963 | 
27. | 20 | tpk  | 963 | 
28. | 21 | wer  | 546 | 
29. | 22 | wer  | 546 | 
30. +----+------+-----+ 
31. rows in set (0.00 sec)