MySQL Cookbook, 2nd Edition

Dealing with Duplicates When Loading Rows into a Table

Problem

You’ve created a table with a unique index to prevent duplicate values in the indexed column or columns. But this results in an error if you attempt to insert a duplicate row, and you want to avoid having to deal with such errors.

Solution

One approach is to just ignore the error. Another is to use an INSERT IGNORE, REPLACE, or INSERT ... ON DUPLICATE KEY UPDATE statement, each of which modifies MySQL’s duplicate-handling behavior. For bulk-loading operations, LOAD DATA has modifiers that enable you to specify how to handle duplicates.

Discussion

By default, MySQL generates an error when you insert a row that duplicates an existing unique key value. Suppose that the person table has the following structure, with a unique index on the last_name and first_name columns:

CREATE TABLE person
(
  last_name   CHAR(20) NOT NULL,
  first_name  CHAR(20) NOT NULL,
  address     CHAR(40),
  PRIMARY KEY (last_name, first_name)
);

An attempt to insert a row with duplicate values in the indexed columns results in an error:

mysql>INSERT INTO person (last_name, first_name)
    -> VALUES('X1','Y1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO person (last_name, first_name)
    -> VALUES('X1','Y1');
ERROR 1062 (23000): Duplicate entry 'X1-Y1' for key 1

If you’re issuing the statements from the mysql program interactively, you can simply say, “Okay, that didn’t work,” ignore the error, and continue. But if you write a program to insert the rows, an error ...