How to delete duplicate records in MySQL

MySQL deduplication is a frequently used operation. The following describes five methods for deleting duplicate records in MySQL. If you are interested, take a look.

1. Search for redundant duplicate records in the Table. duplicate records are determined based on the peopleId of a single field.
Select * from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. Delete unnecessary duplicate records in the Table. duplicate records are determined based on the peopleId of a single field. Only the records with the smallest rowid are retained.
Delete from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

3. Search for redundant fields in the table)
Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. Delete multiple fields with the least rowid.
Delete from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

5. Search for multiple fields of redundant record records in the table), excluding records with the smallest rowid
Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)
 

Use MySQL temporary tables to accelerate queries

How to properly use MySQL Indexes

Five common MySQL Log types

Automatic Restoration of MySQL Log Files

How to use the new MySQL Update log