Dealing with Duplicates at Record-Creation Time

14.3.1 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 record, and you want to avoid having to deal with such errors.

14.3.2 Solution

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

14.3.3 Discussion

By default, MySQL generates an error when you insert a record that duplicates an existing unique key. For example, you'll see the following result if the person table contains a unique index on the last_name and first_name columns:

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 at line 1: 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 records, an error may terminate the program. One way to avoid this is to modify the program's error-handling behavior to trap the error and then ignore it. See Recipe 2.3 for information about error-handling techniques.

If you want to prevent the error from occurring in the first place, you might consider using a two-query method to solve the duplicate-record problem: issue a SELECT to see if the record is already present, followed by an INSERT if it's not. But that doesn't really work. Another client might insert the same record after the SELECT and before the INSERT, in which case the error would still occur. To make sure that doesn't happen, you could use a transaction or lock the tables, but then you're up from two statements to four. MySQL provides two single-query solutions to the problem of handling duplicate records:

• Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error:

  mysql> INSERT IGNORE INTO person (last_name, first_name)
   -> VALUES('X2','Y2');
  Query OK, 1 row affected (0.00 sec)
  mysql> INSERT IGNORE INTO person (last_name, first_name)
   -> VALUES('X2','Y2');
  Query OK, 0 rows affected (0.00 sec)

  The row count value indicates whether the record was inserted or ignored. From within a program, you can obtain this value by checking the rows-affected function provided by your API. (See Recipe 2.5 and Recipe 9.2.)

• Use REPLACE rather than INSERT. If the record is new, it's inserted just as with INSERT. If it's a duplicate, the new record replaces the old one:

  mysql> REPLACE INTO person (last_name, first_name)
   -> VALUES('X3','Y3');
  Query OK, 1 row affected (0.00 sec)
  mysql> REPLACE INTO person (last_name, first_name)
   -> VALUES('X3','Y3');
  Query OK, 2 rows affected (0.00 sec)

  The rows-affected value in the second case is 2 because the original record is deleted and the new record is inserted in its place.

INSERT IGNORE and REPLACE should be chosen according to the duplicate-handling behavior you want to effect. INSERT IGNORE keeps the first of a set of duplicated records and discards the rest. REPLACE keeps the last of a set of duplicates and kicks out any earlier ones. INSERT IGNORE is more efficient than REPLACE because it doesn't actually insert duplicates. Thus, it's most applicable when you just want to make sure a copy of a given record is present in a table. REPLACE, on the other hand, is often more appropriate for tables in which other non-key columns may need updating. Suppose you're maintaining a table named passtbl for a web application that contains email addresses and passwords and that is keyed by email address:

CREATE TABLE passtbl
(
 email CHAR(60) NOT NULL,
 password CHAR(20) BINARY NOT NULL,
 PRIMARY KEY (email)
);

How do you create records for new users, and change passwords for existing users? Without REPLACE, creating a new user and changing an existing user's password must be handled differently. A typical algorithm for handling record maintenance might look like this:

• Issue a SELECT to see if a record already exists with a given email value.
• If no such record exists, add a new one with INSERT.
• If the record does exist, update it with UPDATE.

All of that must be performed within a transaction or with the tables locked to prevent other users from changing the tables while you're using them. With REPLACE, you can simplify both cases to the same single-statement operation:

REPLACE INTO passtbl (email,password) VALUES(address,passval);

If no record with the given email address exists, MySQL creates a new one. If a record does exist, MySQL replaces it; in effect, this updates the password column of the record associated with the address.

INSERT IGNORE and REPLACE have the benefit of eliminating overhead that might otherwise be required for a transaction. But this benefit comes at the price of portability, because both are MySQL-specific statements. If portability is a high priority, you might prefer to stick with a transactional approach.

For bulk-load operations in which you use the LOAD DATA statement to load a set of records from a file into a table, duplicate-record handling can be controlled using the statement's IGNORE and REPLACE modifiers. These produce behavior analogous to that of the INSERT IGNORE and REPLACE statements. See Recipe 10.8 for more information.