MySQL Insert If Not Exists

There are 3 possible solutions to insert data into MySql table (when it is possible that data might be duplicate): using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.

Method 1: using REPLACE

REPLACE INTO `table` SET `field1` = 'value1', `field2` = 12345, `field3` = 12678;

If the record exists, it will be overwritten, if it doesn't, it will be created. However, using this method isn’t efficient if you don't need to overwrite existing records, it’s fine just to skip them.

Method 2: using INSERT IGNORE

INSERT IGNORE INTO `table` (`field1`,`field2`,`field3`) values('value1',12345,12678);

Here, if the record doesn’t yet exist, it will be created, but if the index is duplicate in table, it will be silently skipped - ignored. To be more precise: If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.

This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.

Method 3: using INSERT … ON DUPLICATE KEY UPDATE

INSERT INTO `table` (`field1`,`field2`,`field3`) values('value1',12345,12678) ON DUPLICATE KEY UPDATE `field1` = 'value1', `field2` = 12345, `field3` = 12678;

This will create the record but if there is a duplicate in the indexed field, the record will be updated instead. Note that you have to actually write the update query - it is not automatic, and you can do nothing in the update part if you wish)