1. 问题描述
有时load或者insert操作导致 表数据有重复
2. 解决方案
通过临时表、主键id、倒腾去重
示例
2.1 create table student(
name varchar(30) not null default ‘‘,
age smallint(3) not null default 0,
love varchar(50) not null default ‘‘
)
插入一些数据......(包含重复)
insert into student(name,age,love) values(‘zhangsan‘,15,‘basketball‘);
insert into student(name,age,love) values(‘zhangsan‘,15,‘basketball‘);
insert into student(name,age,love) values(‘zhangsan‘,15,‘basketball‘);
2.2 备份(全部数据或部分数据)
create table student_backup like student;
insert into student_backup select * from student where name=‘zhangsan‘;
2.3 创建临时表一
create table student_tmp like student;
insert into student_tmp select * from student where name=‘zhangsan‘;
alter table student_tmp add id int primary key auto_increment not null ;
2.4 创建临时表二
create table tmp2 (
id int auto_increment not null,
primary key(id)
);
insert into tmp2 select min(id) as id from student_tmp group by name,age,love;
2.5 创建临时表三
create table tmp3 like student_tmp;
insert into tmp3 select student_tmp.* from student_tmp,tmp2 where student_tmp.id=tmp2.id;
alter table tmp3 drop column id;
2.6 删除重复数据
delete from student where name=‘zhangsan‘;
2.7 插入去重后数据
insert into student select * from tmp3;
Done!
原文：http://www.cnblogs.com/rocky-fang/p/7264996.html