MySQL Delete duplicate data

First, prepare the test table

CREATE TABLE ' ts ' (

' ID ' INT (one) not NULL auto_increment,

' Name ' VARCHAR (() DEFAULT NULL,

' Age ' INT (one) DEFAULT NULL,

' Sex ' VARCHAR () DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=1 DEFAULT Charset=utf8

II. Preparation of test data

INSERT into TS (name,age,sex) VALUES (' Zhang San ', 10, ' Male ');

INSERT into TS (name,age,sex) VALUES (' Zhang San ', 10, ' Male ');

INSERT into TS (name,age,sex) VALUES (' Zhang San ', 10, ' female ');

INSERT into TS (name,age,sex) VALUES (' John Doe ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' John Doe ', 20, ' female ');

INSERT into TS (name,age,sex) VALUES (' Harry ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' Harry ', 20, ' female ');

INSERT into TS (name,age,sex) VALUES (' Harry ', 11, ' female ');

INSERT into TS (name,age,sex) VALUES (' Harry ', 10, ' female ');

INSERT into TS (name,age,sex) VALUES (' Guo Jing ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' Guo Jing ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' Ouyang Fung ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' Huang Rong ', 20, ' female ');

INSERT into TS (name,age,sex) VALUES (' Aaron Kwok ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' Yellow pharmacist ', 20, ' male ');

INSERT into TS (name,age,sex) VALUES (' Mui Wind ', 20, ' female ');

SELECT * from TS;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/82/C9/wKioL1dhBNLzcIuTAABVKVM2-_g440.jpg "title=" 11.jpg "alt=" Wkiol1dhbnlzciutaabvkvm2-_g440.jpg "/>

Third, the need to delete duplicate data, the condition name and age, as repeating data, for duplicate data only need to keep one

Method One, through the Delete method

DELETE from TS WHERE ID in (the Select ID from (select MAX (ID) as Id,count (*) as COUNT from TS GROUP by Name,age have Coun T >1 ORDER by COUNT DESC) as tab);

Note: Because the maximum ID of duplicate data is deleted at a time, multiple deletes are required if there are multiple occurrences of the data

SELECT * from TS;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/82/CA/wKiom1dhBDfSyZT7AAA69jqfH68224.jpg "title=" 12.jpg "alt=" Wkiom1dhbdfsyzt7aaa69jqfh68224.jpg "/>

Way two, through the method of query, first build a table identical to the original table, including the primary key, constraints, indexes are the same table, and then through SQL to the results of the query to the new table, and finally rename table.

CREATE TABLE ts1 like ts;--The result of the query is non-repeating data, the Union duplicates the primary key of the data to take the smallest insert into TS1 select * FROM (select t.* from TS T,--duplicate data, take One of the minimum primary keys (select min (id) ID, name,age, COUNT (*) count from TS GROUP by Name,age have count >1 order by count Desc) b where T.id=b.id and T.name=b.name and T.age=b.age union SELECT t.* from TS T,--non-repeating data, of course, is also taken out (SELECT MAX (ID) as Id,co UNT (*) as COUNT from TS GROUP by Name,age have COUNT =1) b where T.id=b.id) TM ORDER by ID

SELECT * from Ts1;

The result is that the conditions are met exactly

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/82/C9/wKioL1dhBaiCI761AAA69jqfH68544.jpg "title=" 12.jpg "alt=" Wkiol1dhbaici761aaa69jqfh68544.jpg "/>

RENAME TABLE ts to ts_tmp;

RENAME TABLE ts1 to TS;

SELECT * from TS;

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/82/C9/wKioL1dhBaiCI761AAA69jqfH68544.jpg "title=" 12.jpg "alt=" Wkiol1dhbaici761aaa69jqfh68544.jpg "/>

This article from "Small Fish Blog" blog, declined reprint!

MySQL Delete duplicate data