Ways to find and delete duplicate data in MySQL

(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;

(iii) Stored procedures

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

Cases

Database version Server Version:5.1.41-community-log MySQL Community Server (GPL)

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

Example 1 Test data

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

code is as follows	copy code
/* Find all duplicate data */ Select ' T1 '. * from ' T1 ', (   SELECT ' name ', ' Add '   to ' T1 '   GROUP by ' name ', ' Add ' &N Bsp 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  | w Ww.111cn.net|
|  6 | xzy  | 456 |
|  7 | xzy  | 456 |
|  8 | xzy  | 456 |
|  9 | xzy  | 789 |
| xzy  | 789 |
| ijk  | 147 |
| ijk  | 147 |
| tpk  | 963 |
| tpk  | 963 |
| wer  | 546 |
| wer  | 546 |
+----+------+-----+
Rows in Set (0.00 sec)