How to find and delete duplicate data in MySQL

Source: Internet
Author: User
Tags numeric rowcount

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.