How do I Remove Duplicate Entries in a MySQL Database While Preserving One Instance?

Effective Removal of Duplicate Entries in a MySQL Database

Problem:

Maintaining data integrity requires the identification and elimination of duplicate entries. In this specific scenario, a database table with unique ID and title columns contains a significant number of duplicate titles. The goal is to remove all duplicates while preserving one representative entry to subsequently add a UNIQUE constraint.

Solution:

MySQL provides a powerful command that efficiently addresses this challenge:

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

This command serves two primary functions:

1. Adding a Unique Key: It adds a UNIQUE key to the "title" column, ensuring that no duplicate values can exist within that column.
2. Removing Duplicates: The IGNORE flag instructs MySQL to drop all rows that conflict with the new unique key. Consequently, all duplicate rows except one are eliminated.

Important Note:

For InnoDB tables in certain MySQL versions, this command may encounter issues. To resolve this, refer to the specified workaround outlined in the post linked in the answer.

By implementing this solution, you can effectively remove duplicate titles from your database, enabling you to add a UNIQUE key to enforce data integrity and prevent future duplication.

The above is the detailed content of How do I Remove Duplicate Entries in a MySQL Database While Preserving One Instance?. For more information, please follow other related articles on the PHP Chinese website!