Solidlystated.com

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. 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. 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