Add Unique Index in MySQL and Remove Duplicated Data Automatically using "IGNORE" Keyword

I have been trying to remove some duplicated data in MySQL and have been trying different approaches. Of course you can run a very complicated query but if a column or a few columns should only have unique values you really should build a unique index. The benefit of this is not only that you don't have to manually run a complicated query, but the data integrity will be ensured in the future.

You can run this script to build a unique index:

ALTER TABLE `table` ADD UNIQUE INDEX `name_of_index` (`unique_column` ASC);

The only problem is that if you already have duplicated data(in this case if you have multiple rows that have the same value in column unique_column), this script will chuck an error:

ERROR 1062: Duplicate entry 'some_value' for key 'name_of_index'

The trick we're missing here is actually quite simple - simply add "IGNORE" keyword in your script:

ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `name_of_index` (`unique_column` ASC);

All duplicated data will then be automatically removed to ensure data is unique in the column(s) on which the unique index is being built.

Job done:)

Thanks to the comment posted by Evan XX.