MySQL千万级 重复数据查找及删除方案

创建表: 其中部门号(dept),用户名(name)

create table userinfo (  id varchar(32) ,  dept int  name varchar(32),  primary key (id));

向表中插入测试数据:

insert into userinfo (id,dept,name) value('a001',1,'aa');insert into userinfo (id,dept,name) value('b002',1,'aa');insert into userinfo (id,dept,name) value('cc03',1,'bb');insert into userinfo (id,dept,name) value('aa04',2,'bb');insert into userinfo (id,dept,name) value('ac05',2,'bv');insert into userinfo (id,dept,name) value('ab06',3,'bc');insert into userinfo (id,dept,name) value('ad07',4,'bd');insert into userinfo (id,dept,name) value('ae08',5,'bb');insert into userinfo (id,dept,name) value('aa09',2,'bb');

查出重复名字和部门:

select dept,name from userinfo group by dept,name having count(*)>1;

查询出名字和部门重复的数据:

方法一:

select u.* from userinfo u,     (select dept,name from userinfo group by dept,name      having count(*)>1) owhere u.dept = o.dept and u.name = o.name

方法二:

select u1.* from userinfo u1,userinfo u2where u1.id <> u2.id and u1.dept = u2.dept and u1.name = u2.name;

如果表中数据很少可用如下方法删除i字典排序中id最小的数据:

delete a from userinfo a,(select min(id) as id from userinfo  group by dept,name having count(*)>1) o where o.id = a.id

方法三:受此启发可以使用临时表进行数据操作，先查出要删除的数据id放入临时表中，然后操作删除原表中对应临时表中存在的id。

方法四:使用索引去重法，该方法需要使用索引，只要能建起索引，速度会相当快，适合千万级别的数据去重操作,整个操作下来不会超过半个小时。

//创建临时操作表CREATE TABLE tmp SELECT * FROM userinfo; //为临时操作表添加索引CREATE INDEX ind_tmp ON tmp(NAME,dept); //排重后最终结果CREATE TABLE tmp_data SELECT NAME,dept,MAX(id) FROM tmp FORCE INDEX (ind_tmp) GROUP BY NAME,dept;