MySQL: Dealing with Duplicates When Inserting Rows

In MySQL, there are basically three ways where you can insert data into a table where a duplicate might happen.

1. INSERT IGNORE INTO TABLENAME(col1, col2) VALUES (‘xxx’, ‘yyy’)
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 row is present in a table.

2. REPLACE INTO TABLENAME(col1, col2) VALUES (‘xxx’, ‘yyy’)
REPLACE is often more appropriate for tables in which other nonkey columns need to be replaced.

3. INSERT INTO TABLENAME(col1, col2) VALUES (‘xxx’, ‘yyy’) ON DUPLICATE KEY UPDATE col1 = ‘zzz’
INSERT … ON DUPLICATE KEY UPDATE 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.