MySQL How to delete duplicate records and rows of data
The fastest and easiest way to delete duplicate records is my issuing a very simple command.
alter ignore table [tablename] add unique index `unique_index` ([fieldname])
What this does is create a unique index on the field that you do not want to have any duplicates. The ignore syntax instructs MySQL to not stop and display an error when it hits a duplicate. This is much easier than dumping and reloading a table.
This also will work, but is not as elegant:
delete from [tablename] where fieldname in (select a.[fieldname] from
(select [fieldname] from [tablename] group by [fieldname] having count(*) > 1 ) a )
- MySQL – Restoring from a Dump Syntax
- MySQL Master and Slave have different Query Execution Plans