Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Eliminating Duplicates by Adding an Index

Problem

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

Solution

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

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata