How to remove duplicate entries from a mysql db?

Question

I have a table with some ids + titles. I want to make the title column unique, but it has over 600k records already, some of which are duplicates (sometimes several dozen times over).

How do I remove all duplicates, except one, so I can add a UNIQUE key to the title column after?

question from:https://stackoverflow.com/questions/2867530/how-to-remove-duplicate-entries-from-a-mysql-db

Answer 1

This command adds a unique key, and drops all rows that generate errors (due to the unique key). This removes duplicates.

ALTER IGNORE TABLE table ADD UNIQUE KEY idx1(title); 

---

Edit: Note that this command may not work for InnoDB tables for some versions of MySQL. See this post for a workaround. (Thanks to "an anonymous user" for this information.)