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

(一)单个字段

 

1、查找表中多余的重复记录，根据（question_title）字段来判断

 

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

 

 

2、删除表中多余的重复记录，根据（question_title）字段来判断，只留有一个记录

 

delete from questions

where peopleId in (select peopleId from people group by peopleId having count(question_title) > 1)

and min(id) not in (select question_id from questions group by question_title having count(question_title)>1)

 

(二)多个字段

 

删除表中多余的重复记录（多个字段），只留有rowid最小的记录

 

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)

 

 

用上述语句无法删除，创建了临时表才删的，求各位达人解释一下。

 

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);

 

DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp);

 

DROP TABLE tmp;

 

 

(三) 存储过程

 

declare @max integer,@id integer

 

declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1

 

open cur_rows

 

fetch cur_rows into @id,@max

 

while @@fetch_status=0

 

begin

 

select @max = @max -1

 

set rowcount @max

 

delete from 表名 where 主字段 = @id

 

fetch cur_rows into @id,@max

 

end

 

close cur_rows

 

set rowcount 0

 

 

例,

 

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

 

例1，表中有主键(可唯一标识的字段)，且该字段为数字类型

 

例1测试数据

 

/* 表结构 */

DROP TABLE IF EXISTS `t1`;

CREATE TABLE IF NOT EXISTS `t1`(

  `id` INT(1) NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(20) NOT NULL,

  `add` VARCHAR(20) NOT NULL,

  PRIMARY KEY(`id`)

)Engine=InnoDB;

 

/* 插入测试数据 */

INSERT INTO `t1`(`name`,`add`) VALUES

('abc',"123"),

('abc',"123"),

('abc',"321"),

('abc',"123"),

('xzy',"123"),

('xzy',"456"),

('xzy',"456"),

('xzy',"456"),

('xzy',"789"),

('xzy',"987"),

('xzy',"789"),

('ijk',"147"),

('ijk',"147"),

('ijk',"852"),

('opq',"852"),

('opq',"963"),

('opq',"741"),

('tpk',"741"),

('tpk',"963"),

('tpk',"963"),

('wer',"546"),

('wer',"546"),

('once',"546");

 

SELECT * FROM `t1`;

+----+------+-----+

| id | name | add |

+----+------+-----+

|  1 | abc  | 123 |

|  2 | abc  | 123 |

|  3 | abc  | 321 |

|  4 | abc  | 123 |

|  5 | xzy  | 123 |

|  6 | xzy  | 456 |

|  7 | xzy  | 456 |

|  8 | xzy  | 456 |

|  9 | xzy  | 789 |

| 10 | xzy  | 987 |

| 11 | xzy  | 789 |

| 12 | ijk  | 147 |

| 13 | ijk  | 147 |

| 14 | ijk  | 852 |

| 15 | opq  | 852 |

| 16 | opq  | 963 |

| 17 | opq  | 741 |

| 18 | tpk  | 741 |

| 19 | tpk  | 963 |

| 20 | tpk  | 963 |

| 21 | wer  | 546 |

| 22 | wer  | 546 |

| 23 | once | 546 |

+----+------+-----+

rows in set (0.00 sec)

 

 

查找id最小的重复数据(只查找id字段)

 

 

/* 查找id最小的重复数据(只查找id字段) */

SELECT DISTINCT MIN(`id`) AS `id`

FROM `t1`

GROUP BY `name`,`add`

HAVING COUNT(1) > 1;

+------+

| id   |

+------+

|    1 |

|   12 |

|   19 |

|   21 |

|    6 |

|    9 |

+------+

rows in set (0.00 sec)

 

 

 

查找所有重复数据

 

/* 查找所有重复数据 */

SELECT `t1`.*

FROM `t1`,(

  SELECT `name`,`add`

  FROM `t1`

  GROUP BY `name`,`add`

  HAVING COUNT(1) > 1

) AS `t2`

WHERE `t1`.`name` = `t2`.`name`

  AND `t1`.`add` = `t2`.`add`;

+----+------+-----+

 

 

| id | name | add |

+----+------+-----+

|  1 | abc  | 123 |

|  2 | abc  | 123 |

|  4 | abc  | 123

|  6 | xzy  | 456 |

|  7 | xzy  | 456 |

|  8 | xzy  | 456 |

|  9 | xzy  | 789 |

| 11 | xzy  | 789 |

| 12 | ijk  | 147 |

| 13 | ijk  | 147 |

| 19 | tpk  | 963 |

| 20 | tpk  | 963 |

| 21 | wer  | 546 |

| 22 | wer  | 546 |

+----+------+-----+

rows in set (0.00 sec)