Eliminating Duplicates by Adding an Index

8.9.1 Problem

A table has duplicates and you'd like to get rid of them.

8.9.2 Solution

One way to do this is to create a unique index on the column or columns containing duplicates.

8.9.3 Discussion

If MySQL discovers duplicate key values when you try to create a PRIMARY KEY or a UNIQUE index, it aborts the ALTER TABLE operation. To ignore the duplicates and proceed anyway, use ALTER IGNORE TABLE rather than ALTER TABLE. The IGNORE keyword tells MySQL to retain the first row containing a duplicated key value and discard the others. This is, in fact, a useful way to eliminate duplicates in a column or set of columns: just create a unique-valued index and let MySQL throw away the duplicates. (If you need to identify which key values are duplicated, though, that's not a suitable technique. See Recipe 14.4 for information on duplicate identification.)

To see how IGNORE works to eliminate duplicates, use mytbl, which now has no indexes if you've issued the index-modification statements shown earlier. First, insert some duplicate values into the table:

mysql> INSERT INTO mytbl (i,c) VALUES(1,'a'),(1,'a'),(1,NULL),(1,NULL),
 -> (2,'a'),(2,'a'),(2,'b'),(2,'b');
mysql> SELECT * FROM mytbl;
+---+------+
| i | c |
+---+------+
| 1 | a |
| 1 | a |
| 1 | NULL |
| 1 | NULL |
| 2 | a |
| 2 | a |
| 2 | b |
| 2 | b |
+---+------+

Now suppose you want to create a unique-valued index comprising the i and c columns. A PRIMARY KEY cannot be used here, because c contains NULL values. You can create a UNIQUE index, but if you try to do so without using IGNORE, you'll get an error:

mysql> ALTER TABLE mytbl ADD UNIQUE (i,c);
ERROR 1062 at line 1: Duplicate entry '1-a' for key 1

Add IGNORE to the statement, then use SELECT to have a look at the table contents to see how the duplicates have been removed:

mysql> ALTER IGNORE TABLE mytbl ADD UNIQUE (i,c);
mysql> SELECT * FROM mytbl;
+---+------+
| i | c |
+---+------+
| 1 | NULL |
| 1 | NULL |
| 1 | a |
| 2 | a |
| 2 | b |
+---+------+

The output shows that duplicate records have been eliminatedexcept those that contain NULL in the key values. That's because UNIQUE indexes allow multiple NULL values. For another technique that removes even duplicates that contain NULLs, see Recipe 14.7.