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!

mysql 去除重复数据