MySQL delete duplicate lines (MyISAM and InnoDB)

Sometimes this task can be a real pain in the ass.. Heres a quick & dirty way to remove.

Let’s say you have the following table:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| col_a | varchar(10)      | YES  |     | NULL    |                |
| col_b | varchar(10)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

With the following data:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  3 | YY    | ZZ    |
|  4 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Note: On this example I’ll assume a unique row is identified by the pair “col_a” + “col_b”.

If you want to delete duplicate rows and you don’t care for the PRIMARY KEY or which of the rows gets selected, you can add a UNIQUE INDEX.

For MyISAM engine tables

1
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);

Doing this will automatically purge the duplicate rows instead of shouting an error because we’re using IGNORE.

Et voila:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Important Note on InnoDB engine tables

If your under MySQL 5.5/InnoDB this will not work due to a known “InnoDB index limitation“.

Luckily, someone at stackoverflow.com came up with a simple solution:

1
2
SET SESSION old_alter_table=1;
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>