There is a quick query for those of you who have a table full of duplicate values in a MySQL database. Sure, you can remove existing duplicate rows, attempt inserts only for unique values, and setup the table to block duplicate values from being inserted. However, using IGNORE with ALTER TABLE has drawbacks and you may want to tailor a DELETE statement instead.

[ad#Adsense-300×250-All-Text+GraphicsAd]

The Risk of IGNORE

  • If you use InnoDB, there is a known bug
  • Some solutions to InnoDB bug are bad advice
  • No control over which rows get deleted

Remove Existing Duplicate Entries with IGNORE

The following query will clean up your database and change the table so that no more duplicates will be allowed.

MYSQLview code
ALTER IGNORE TABLE table_name ADD UNIQUE INDEX field_name;

Watch Out! If your DB uses InnoDB, there is an old MySQL bug when using ALTER IGNORE TABLE with InnoDB engines.

The solution for InnoDB users is to run set session old_alter_table=1; before the ALTER statement, then set it back after.

Setup Insert Statements to Prevent Duplicate Attempts

This second query is

MYSQLview code
INSERT IGNORE INTO table_name (field_name);