Removing duplicates with unique index

by

If you have duplicates in your table and you use

ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);

the query will fail with Error 1062 (duplicate key).

But if you use IGNORE

-- (only works before MySQL 5.7.4)
ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);

the duplicates will be removed. But the documentation doesn’t specify which row will be kept:

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

    As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and
    its use produces an error.

(ALTER TABLE Syntax)

If your version is 5.7.4 or greater – you can:

  • Copy the data into a temporary table (it doesn’t technically need to be temporary).
  • Truncate the original table.
  • Create the UNIQUE INDEX.
  • And copy the data back with INSERT IGNORE (which is still available).
CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;

If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors
generate warnings instead.

(INSERT Syntax)

Also see: INSERT … SELECT Syntax and Comparison of the IGNORE Keyword and Strict SQL Mode

Leave a Comment

tech