MySQL: How to force a new unique index to drop duplicated rows

Posted August 19, 2010 * Comments(2)

If table is not empty, there may be duplicated records. If so a regular alter query to create a new unique index will return an error like below.

ERROR 1062 (23000): Duplicate entry ‘132653-47’ for key 1

However keyword IGNORE allows to force a new unique index to drop duplicated rows.

ALTER IGNORE TABLE `t` ADD UNIQUE INDEX `i` (`f1`, `f2`);

In this case the output will be something like this:

Query OK, 507 rows affected (0.02 sec)
Records: 507 Duplicates: 0 Warnings: 0

So simply using IGNORE helps to save time on writing a custom script to clean up duplicates.

2 Comments so far

  1. Josh on January 10th, 2014

    How do we do this with the CREATE UNIQUE INDEX operation? Where does the word IGNORE go?

  2. Alex on April 8th, 2014

    Hi Josh,

    Does not seem like CREATE UNIQUE INDEX supports IGNORE. Any specific reason you prefer CREATE INDEX over ALTER TABLE?

Leave a reply