As of version 5.7 MySQL no longer support the keyword IGNORE for ALTER TABLE command, as it was an extension which was badly defined (more information here). Therefore it’s now impossible to use the old query that allowed to delete duplicates by creating a new UNIQUE INDEX and altering the table.
In such case the IGNORE keyword informed the database, that any duplicate entry must be ignored, and allowed to remove them by simply running:
IGNORE TABLE `example` ADD UNIQUE INDEX (`id`, `name`);
Creating new table
With altering not being an option we’re left with creating a new table and inserting the data into it. INSERT accepts IGNORE keyword, so it will filter out duplicates. The general syntax is:
CREATE TABLE `example_new` LIKE `example`; ALTER TABLE `example_new` ADD UNIQUE INDEX (`id`, `name`); INSERT IGNORE INTO `example_new` SELECT * FROM `example`; DROP TABLE `example`; RENAME TABLE `example_new` TO `example`;