mysqlduplicates

How to remove duplicate entries from a mysql db?

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?

Solution

  • 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.)