Handling Duplicates - MySQL - page 554

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 row was inserted or ignored. From
within a program, you can obtain this value by checking the rows-affected function
provided by your API (see Recipes 2.4 and 10.1 ).
• To replace the original row with the new one when a duplicate occurs, use RE
PLACE rather than INSERT . If the row is new, it's inserted just as with INSERT . If it's
a duplicate, the new row 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 row is deleted
and the new row is inserted in its place.
• To modify columns of an existing row when a duplicate occurs, use INSERT … ON
DUPLICATE KEY UPDATE . If the row is new, it's inserted. If it's a duplicate, the ON
DUPLICATE KEY UPDATE clause indicates how to modify the existing row in the table.
In other words, this statement can insert or update a row as necessary. The rows-
affected count indicates what happened: 1 for an insert, 2 for an update.
INSERT IGNORE is more efficient than REPLACE because it doesn't actually insert dupli‐
cates. Thus, it's most applicable when you just want to make sure a copy of a given row
is present in a table. REPLACE , on the other hand, is often more appropriate for tables in
which other nonkey columns need to be replaced. INSERT … ON DUPLICATE KEY UP
DATE is appropriate when you must insert a record if it doesn't exist, but just update
some of its columns if the new record is a duplicate in the indexed columns.
Suppose that you maintain a table named passtbl for a web application that contains
email addresses and password hash values, and that is indexed by email address:
CREATE TABLE passtbl
(
email VARCHAR ( 60 ) NOT NULL ,
password VARBINARY ( 60 ) NOT NULL ,
PRIMARY KEY ( email )
);
How do you create new rows for new users, but change passwords of existing rows for
existing users? Here's a typical algorithm for handling row maintenance: