Remove repeat rows from MySQL table

Is t开发者_运维百科here a way to remove all repeat rows from a MySQL database?

A couple of years ago, someone requested a way to delete duplicates. Subselects make it possible with a query like this in MySQL 4.1:

DELETE FROM some_table WHERE primaryKey NOT IN
(SELECT MIN(primaryKey) FROM some_table GROUP BY some_column)

Of course, you can use MAX(primaryKey) as well if you want to keep the newest record with the duplicate value instead of the oldest record with the duplicate value.

To understand how this works, look at the output of this query:

SELECT some_column, MIN(primaryKey) FROM some_table GROUP BY some_column

As you can see, this query returns the primary key for the first record containing each value of some_column. Logically, then, any key value NOT found in this result set must be a duplicate, and therefore it should be deleted.

These questions / answers might interest you :

• How to delete duplicate records in mysql database?
• How to delete Duplicates in MySQL table.

And idea that's often used when you are working with a big table is to :

• Create a new table
• Insert into that table the unique records (i.e. only one version of the duplicates in the original table, generally using a select distinct)
• and use that new table in your application ; or drop the old table and rename the new one to the old name.

Good thing with this principle is you have the possibility to verify what's in the new table before dropping the old one -- always nice to check that sort of thing ^^