How to find and delete duplicate data in Mysql

(1) single Field

1. Search for redundant duplicate records in the table. The record is determined based on the (question_title) field.

The Code is as follows:
Select * from questions where question_title in (select question_title from people group by question_title having count (question_title)> 1)
 

2. Delete unnecessary duplicate records in the Table. Based on the (question_title) field, only one record is left.

The Code is as follows:
Delete from questions
Where peopleId in (select peopleId from people group by peopleId having count (question_title)> 1)
And min (id) not in (select question_id from questions group by question_title having count (question_title)> 1)

(2) Multiple Fields

Delete redundant record (multiple fields) in the table, with only the records with the smallest rowid

The Code is as follows:
Delete from questions WHERE (questions_title, questions_scope) IN (SELECT questions_title, questions_scope FROM questions group by region, having count (*)> 1) AND question_id not in (select min (question_id) FROM questions group by questions_scope, questions_title having count (*)> 1)
 

The preceding statement cannot be used to delete a temporary table.

The Code is as follows:
Create table tmp as select question_id FROM questions WHERE (questions_title, question_scope) IN (SELECT questions_title, questions_scope FROM questions group by questions_title, questions_scope having count (*)> 1) AND question_id not in (select min (question_id) FROM questions group by questions_scope, questions_title having count (*)> 1 );

Delete from questions WHERE question_id IN (SELECT question_id FROM tmp );

Drop table tmp;
 

(3) Stored Procedure

The Code is as follows:
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 primary field = @ id

Fetch cur_rows into @ id, @ max

End

Close cur_rows

Set rowcount 0
 

For example,

Database 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 of the numerical type.

Example 1 Test Data

The Code is as follows:
/* Table Structure */
Drop table if exists 't1 ';
Create table if not exists 't1 '(
'Id' INT (1) not null AUTO_INCREMENT,
'Name' VARCHAR (20) not null,
'Add' VARCHAR (20) 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 "),
('Wher', "546 "),
('Wher', "546 "),
('Server', "546 ");

SELECT * FROM 't1 ';
+ ---- + ------ + ----- +
| Id | name | add |
+ ---- + ------ + ----- +
| 1 | abc| 123 |
| 2 | abc| 123 |
| 3 | abc| 321 |
| 4 | abc| 123 |
| 5 | xzy | 123 |
| 7 | xzy | 456 |
| 6 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789
| 10 | xzy | 987 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147
| 14 | ijk | 852 |
| 15 | ops | 852 |
| 16 | ops | 963 |
| 17 | opq | 741 |
| 18 | tprimary | 741 |
| 19 | tprimary | 963 |
| 20 | tprimary | 963 |
| 21 | Lower | 546 |
| 22 | Lower | 546 |
| 23 | once | 546 |
+ ---- + ------ + ----- +
Rows in set (0.00 sec)
 

Find the duplicate data with the smallest id (only the id field is queried)

The Code is as follows:
/* Find the duplicate data with the smallest id (only the id field is queried )*/
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)
 

Search for all duplicate data

The Code is as follows:
/* Search for 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 | www. bKjia. c0m |
| 7 | xzy | 456 |
| 6 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147
| 19 | tprimary | 963 |
| 20 | tprimary | 963 |
| 21 | Lower | 546 |
| 22 | Lower | 546 |
+ ---- + ------ + ----- +
Rows in set (0.00 sec)

For more details, see http://www.bKjia. c0m/database/mysql/56725.htm