MySQL removes duplicate data

1. Description of the problem

Sometimes load or insert operations cause table data to be duplicated

2. Solution

By temporary table, primary key ID, Daoteng

Example

2.1 CREATE TABLE Student (

Name varchar (+) NOT null default ' ',

Age smallint (3) is not NULL default 0,

Love varchar (+) NOT null default ' '

)

Insert some data ... (Contains duplicates)

INSERT into student (Name,age,love) VALUES (' Zhangsan ', ' basketball ');

INSERT into student (Name,age,love) VALUES (' Zhangsan ', ' basketball ');

INSERT into student (Name,age,love) VALUES (' Zhangsan ', ' basketball ');

2.2 Backup (full data or partial data)

CREATE table student_backup like student;

INSERT INTO Student_backup select * from student where name= ' Zhangsan ';

2.3 Creating a temporary table
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 Creating a temporary Table II

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 Creating a temporary Table III

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 Deleting duplicate data

Delete from student where name= ' Zhangsan ';

2.7 Inserting de-duplication data

INSERT INTO student select * from Tmp3;

done!

MySQL removes duplicate data