How to find and delete duplicate data in MySQL

The examination system has done a user to import the question function, causes the user to import many duplicate questions, I need to inquire and deletes the duplicate record, therefore has this article.

(i) a single field

1, look up the redundant records in the table, according to (Question_title) field to judge

The code is as follows	Copy Code
SELECT * from questions where Question_title in (select Question_title to People GROUP by Question_title has count (qu Estion_title) > 1)

2, delete redundant records in the table, according to (Question_title) field to judge, only left a record

The code is as follows	Copy Code
Delete from questions where Peopleid in (select Peopleid from People GROUP by Peopleid has count (question_title) > 1) and min (id) not in (select question_id from questions GROUP by Question_title has count (question_title) >1)

(ii) Multiple fields

Delete extra duplicate records (multiple fields) in a table, leaving only the smallest ROWID records

The code is as follows	Copy Code
DELETE from Questions WHERE (Questions_title,questions_scope) in (SELECT Questions_title,questions_scope from questions GROUP by Questions_title,questions_scope has COUNT (*) > 1) and question_id not in (SELECT MIN. question_id) from ques tions GROUP by Questions_scope,questions_title has COUNT (*) >1)

Can not be deleted with the above statement, create a temporary table to delete, please everyone to explain.

The code is as follows

Copy Code

CREATE TABLE tmp as select question_id from questions WHERE (Questions_title,questions_scope) in (select Questions_title,q Uestions_scope from questions GROUP by Questions_title,questions_scope has COUNT (*) > 1) and question_id not in (SEL ECT MIN (question_id) from questions GROUP by Questions_scope,questions_title has COUNT (*) >1); DELETE from questions WHERE question_id in (SELECT question_id from TMP); DROP TABLE tmp;

Three

The code is as follows	Copy Code
Declare @max integer, @id integer Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) >; 1 Open Cur_rows Fetch cur_rows into @id, @max While @ @fetch_status =0 Begin Select @max = @max-1 SET ROWCOUNT @max Delete from table name where main field = @id Fetch cur_rows into @id, @max End Close Cur_rows SET ROWCOUNT 0

A lot of the above, let's look at the example to delete the duplicate record instance

Example 1, the table has a primary key (a field that can be uniquely identified), and the field is a numeric type

The code is as follows

Copy Code

/* Table Structure * * DROP TABLE IF EXISTS ' T1 '; CREATE TABLE IF not EXISTS ' T1 ' ( ' ID ' INT (1) Not NULL auto_increment, ' Name ' VARCHAR not NULL, ' Add ' VARCHAR not NULL, PRIMARY KEY (' id ') ) Engine=innodb; /* INSERT TEST data * * INSERT into ' t1 ' (' name ', ' Add ') VALUES (' abc ', ' 123 '), (' abc ', ' 123 '), (' abc ', ' 321 '), (' abc ', ' 123 '), (' Xzy ', "123"), (' Xzy ', "456"), (' Xzy ', "456"), (' Xzy ', "456"), (' Xzy ', "789"), (' Xzy ', "987"), (' Xzy ', "789"), (' Ijk ', "147"), (' Ijk ', "147"), (' Ijk ', "852"), (' Opq ', "852"), (' Opq ', "963"), (' OPQ ', "741"), (' TPK ', "741"), (' TPK ', "963"), (' TPK ', "963"), (' Wer ', "546"), (' Wer ', "546"), (' Once ', "546"); SELECT * from ' T1 '; +----+------+-----+ | ID | name | Add | +----+------+-----+ | 1 | ABC | 123 | | 2 | ABC | 123 | | 3 | ABC | 321 | | 4 | ABC | 123 | | 5 | Xzy | 123 | | 6 | Xzy | 456 | | 7 | Xzy | 456 | | 8 | Xzy | 456 | | 9 | Xzy | 789 | | 10 | Xzy | 987 | | 11 | Xzy | 789 | | 12 | Ijk | 147 | | 13 | Ijk | 147 | | 14 | Ijk | 852 | | 15 | OPQ | 852 | | 16 | OPQ | 963 | | 17 | OPQ | 741 | | 18 | TPK | 741 | | 19 | TPK | 963 | | 20 | TPK | 963 | | 21 | Wer | 546 | | 22 | Wer | 546 | | 23 | Once | 546 | +----+------+-----+ Rows in Set (0.00 sec)

Find duplicate data with least ID (Find ID field only)

The code is as follows	Copy Code
/* Find duplicate data with minimal ID (Find ID field only) * * SELECT DISTINCT MIN (' id ') as ' id ' From ' T1 ' GROUP by ' name ', ' Add ' Having COUNT (1) > 1; +------+ | ID | +------+ | 1 | | 12 | | 19 | | 21 | | 6 | | 9 | +------+ Rows in Set (0.00 sec)

Find all duplicate data

The code is as follows

Copy Code

/* Find all duplicate data * * SELECT ' T1 '. * From ' T1 ', ( SELECT ' name ', ' Add ' From ' T1 ' GROUP by ' name ', ' Add ' Having COUNT (1) > 1 ) as ' T2 ' WHERE ' t1 '. ' Name ' = ' T2 '. ' Name ' and ' T1 '. ' Add ' = ' t2 '. ' Add '; +----+------+-----+ | ID | name | Add | +----+------+-----+ | 1 | ABC | 123 | | 2 | ABC | 123 | | 4 | ABC | 123 | | 6 | Xzy | 456 | | 9 2 Xzy | 456 | | 8 | Xzy | 456 | | 9 | Xzy | 789 | | 11 | Xzy | 789 | | 12 | Ijk | 147 | | 13 | Ijk | 147 | | 19 | TPK | 963 | | 20 | TPK | 963 | | 21 | Wer | 546 | | 22 | Wer | 546 | +----+------+-----+ Rows in Set (0.00 sec)

Find duplicate data In addition to the least ID

code is as follows

copy code

/* Find duplicate data In addition to the least ID data * * SELECT ' T1 '. * From ' T1 ', ( SELECT DISTINCT MIN (' id ') as ' id ', ' name ', ' Add ' From ' T1 ' GROUP by ' name ', ' Add ' Having COUNT (1) > 1 ) as ' T2 ' WHERE ' t1 '. ' Name ' = ' T2 '. ' Name ' and ' T1 '. ' Add ' = ' t2 '. ' Add ' and ' T1 '. ' id ' <> ' t2 '. ' ID '; +----+------+-----+ | ID | name | Add | +----+------+-----+ | 2 | ABC | 123 | | 4 | ABC | 123 | | 9 2 Xzy | 456 | | 8 | Xzy | 456 | | 11 | Xzy | 789 | | 13 | Ijk | 147 | | 20 | TPK | 963 | | 22 | Wer | 546 | +----+------+-----+ Rows in Set (0.00 sec)

Example 2, the table does not have a primary key (a field that can be uniquely identified), or a primary key is not a numeric type (you can also delete duplicate data, but it is definitely slower in efficiency)

Example 2 test data

The code is as follows

Copy Code

/* Table Structure * * DROP TABLE IF EXISTS ' noid '; CREATE TABLE IF not EXISTS ' noid ' ( ' PK ' VARCHAR not NULL COMMENT ' string primary key ', ' Name ' VARCHAR not NULL, ' Add ' VARCHAR not NULL, PRIMARY KEY (' PK ') ) Engine=innodb; /* Test data, the same test data as the previous example, except that the primary key becomes a string form */ INSERT into ' noid ' (' PK ', ' name ', ' Add ') VALUES (' A ', ' abc ', ' 123 '), (' B ', ' abc ', ' 123 '), (' C ', ' abc ', "321"), (' d ', ' abc ', "123"), (' e ', ' xzy ', ' 123 '), (' f ', ' xzy ', ' 456 '), (' G ', ' Xzy ', ' 456 '), (' h ', ' xzy ', ' 456 '), (' I ', ' xzy ', ' 789 '), (' J ', ' Xzy ', ' 987 '), (' K ', ' xzy ', ' 789 '), (' l ', ' Ijk ', ' 147 '), (' m ', ' Ijk ', ' 147 '), (' n ', ' Ijk ', ' 852 '), (' O ', ' opq ', ' 852 '), (' P ', ' opq ', ' 963 '), (' Q ', ' Opq ', ' 741 '), (' R ', ' TPK ', ' 741 '), (' s ', ' tpk ', ' 963 '), (' t ', ' tpk ', ' 963 '), (' u ', ' wer ', ' 546 '), (' V ', ' Wer ', ' 546 '), (' W ', ' Once ', ' 546 '); SELECT * from ' noid '; +----+------+-----+ | PK | name | Add | +----+------+-----+ | A | ABC | 123 | | B | ABC | 123 | | C | ABC | 321 | | D | ABC | 123 | | e | Xzy | 123 | | f | Xzy | 456 | | G | Xzy | 456 | | H | Xzy | 456 | | I | Xzy | 789 | | J | Xzy | 987 | | K | Xzy | 789 | | l | Ijk | 147 | | m | Ijk | 147 | | n | Ijk | 852 | | o | OPQ | 852 | | P | OPQ | 963 | | Q | OPQ | 741 | | R | TPK | 741 | | s | TPK | 963 | | T | TPK | 963 | | u | Wer | 546 | | V | Wer | 546 | | W | Once | 546 | +----+------+-----+ Rows in Set (0.00 sec)

To add a self-growing ID field to a table

The code is as follows

Copy Code

/* Add the self-growing ID field to the table * * ALTER TABLE ' noid ' Add ' id ' INT (1) Not NULL auto_increment, ADD INDEX ' id ' (' id '); Query OK, rows affected (0.16 sec) Records:23 duplicates:0 warnings:0 SELECT * from ' noid '; +----+------+-----+----+ | PK | name | Add | ID | +----+------+-----+----+ | A | ABC |  123 | 1 | | B | ABC |  123 | 2 | | C | ABC |  321 | 3 | | D | ABC |  123 | 4 | | e | Xzy |  123 | 5 | | f | Xzy |  456 | 6 | | G | Xzy |  456 | 9 2 | H | Xzy |  456 | 8 | | I | Xzy |  789 | 9 | | J | Xzy | 987 | 10 | | K | Xzy | 789 | 11 | | l | Ijk | 147 | 12 | | m | Ijk | 147 | 13 | | n | Ijk | 852 | 14 | | o | OPQ | 852 | 15 | | P | OPQ | 963 | 16 | | Q | OPQ | 741 | 17 | | R | TPK | 741 | 18 | | s | TPK | 963 | 19 | | T | TPK | 963 | 20 | | u | Wer | 546 | 21 | | V | Wer | 546 | 22 | | W | Once | 546 | 23 | +----+------+-----+----+ Rows in Set (0.00 sec)

You must have indexed fields in MySQL to use the auto_increment

Delete duplicate data as in the previous example, remember to delete the ID field after deleting the data

Delete duplicate data, keep only one piece of data

The code is as follows

Copy Code

/* Delete duplicate data, keep only one piece of data * * DELETE from ' noid ' USING ' Noid ', ( SELECT DISTINCT MIN (' id ') as ' id ', ' name ', ' Add ' From ' Noid ' GROUP by ' name ', ' Add ' Having COUNT (1) > 1 ) as ' T2 ' WHERE ' noid '. ' Name ' = ' T2 '. ' Name ' and ' noid '. ' Add ' = ' t2 '. ' Add ' and ' noid '. ' id ' <> ' t2 '. ' ID '; Query OK, 8 rows affected (0.05 sec) /* Delete ID field * * ALTER TABLE ' noid ' DROP ' id '; Query OK, rows affected (0.16 sec) Records:15 duplicates:0 warnings:0 SELECT * from ' noid '; +----+------+-----+ | pk | name | add | +----+------+-----+ | a  | abc  | 123 | | c  | abc  | 321 | | e  | xzy  | 123 | | f  | xzy  | 456 | | i  | xzy  | 789 | | j  | xzy  | 987 | | l  | ijk  | 147 | | n  | ijk  | 852 | | o  | opq  | 852 | | p  | opq  | 963 | | q  | opq  | 741 | | r  | tpk  | 741 | | s  | tpk  | 963 | | u  | wer  | 546 | | w  | once | 546 | +----+------+-----+ Rows in Set (0.00 sec)