How Can You Fix Duplicate Records After Creating a Non-Unique Index in MySQL?

Preserving Data Integrity with Unique Indices

In your database, you aimed to prevent duplicate records by creating a unique index on fields A, B, C, and D. However, you mistakenly created a normal index instead, leading to duplicate insertions. With 20 million records at stake, you're naturally hesitant to make changes.

Handling Duplicate Records with Unique Indices

When you change an existing index to a unique index or add a new one, MySQL's behavior depends on whether there are duplicates:

• Normal ALTER TABLE: If duplicates exist, the operation fails with Error 1062 (duplicate key).
• ALTER TABLE with IGNORE (only before MySQL 5.7.4): Removes duplicates but does not specify which row is retained.
• MySQL 5.7.4 or later: The IGNORE clause is removed, and using it results in an error.

Step-by-Step Resolution

To address this issue in MySQL 5.7.4 or later, follow these steps:

1. Copy the data into a temporary table.
2. Truncate the original table.
3. Create the UNIQUE INDEX on the desired columns.
4. Copy the data back into the original table using INSERT IGNORE.
5. Drop the temporary table.

This method effectively removes duplicates while preserving the remaining data.

Alternate Solution for MySQL Before 5.7.4

For MySQL versions prior to 5.7.4, you can use the IGNORE clause in your ALTER TABLE statement to remove duplicates without specifying which row is retained. However, you may want to ensure you have a backup of your data before attempting this operation.

The above is the detailed content of How Can You Fix Duplicate Records After Creating a Non-Unique Index in MySQL?. For more information, please follow other related articles on the PHP Chinese website!