How to Remove Duplicates from a Table Using a Unique Index in MySQL?

Removing Duplicates with Unique Index

If you have a large table with duplicate records and you want to remove them by creating a unique index, you need to be aware of the potential consequences.

If you attempt to add a unique index to a table that already contains duplicates, the operation will fail due to a duplicate key error. This is because unique indexes enforce the uniqueness of the data they contain.

However, there is a workaround to this problem. You can use the IGNORE modifier when creating the unique index. This will instruct MySQL to ignore any duplicate values and only insert the unique ones.

<code class="sql">ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D) IGNORE;</code>

Using the IGNORE modifier will remove the duplicates, but it is important to note that it does not specify which row will be kept. MySQL will arbitrarily choose one row for each duplicate and discard the others.

If you are using MySQL version 5.7.4 or higher, the IGNORE modifier for ALTER TABLE has been removed and will cause an error. In this case, you can use the following workaround:

1. Copy the data into a temporary table.
2. Truncate the original table.
3. Create the UNIQUE index on the original table.
4. Insert the data back into the original table using INSERT IGNORE.

<code class="sql">CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;</code>

Using INSERT IGNORE will ignore any duplicate values and only insert the unique ones. Once the data has been inserted, the temporary table can be dropped.

The above is the detailed content of How to Remove Duplicates from a Table Using a Unique Index in MySQL?. For more information, please follow other related articles on the PHP Chinese website!