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

来源：互联网发布：自学java知乎编辑：程序博客网时间：2024/05/28 05:14

博客分类：

(一)单个字段

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）字段来判断，只留有一个记录

伦理片 http://www.dotdy.com/

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

影音先锋电影 http://www.iskdy.com/

例,

数据库版本 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

0 0