Database Reference
In-Depth Information
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 'PRIMARY'
If you issue 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 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.2
for information about error-handling techniques.
To prevent the error from occurring in the first place, you might consider using a two-
query method to solve the duplicate-row problem:
• Issue a
SELECT
to check whether the row is already present.
• Issue an
INSERT
if the row is not present.
But that doesn't really work: another client might insert the same row after the
SELECT
and before the
INSERT
, in which case the error would still occur for your
INSERT
. To
make sure that doesn't happen, you could use a transaction or lock the tables, but then
you've gone from two statements to four. MySQL provides three single-query solutions
to the problem of handling duplicate rows. Choose from among them depending on
the duplicate-handling behavior you want:
• To keep the original row when a duplicate occurs, use
INSERT
IGNORE
rather than
INSERT
. If the row duplicates no existing row, MySQL inserts it as usual. If the row
is a duplicate, the
IGNORE
keyword tells MySQL to discard it silently without gen‐
erating an error:
