mysql 去除重複資料

標籤：nbsp   重複資料   部分   group by   column   tab   group   values   2.4   

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 去除重複資料