MySQL - removing data that violates a uniqueness constraint

Question

How can data that violates a uniqueness constraint be removed?

I have a table that I would like to add a uniqueness constraint for. Inside the table is data that violates that constraint, and I would like to remove it. I don't care at this point which data I lose, only that the final data is good.

My first attempts at this have been to use 'create table like' to copy the table, use alter table to add the unique key, and the copy the data from the old table to the new table.

Unfortunately the last step copies all the data and doesn't complain about the uniqueness.

Answer 1

This will add the unique index and drop duplicates:

ALTER IGNORE TABLE your_table ADD UNIQUE INDEX your_table_index (...);

From the docs:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.