MySQL - ignore insert error: duplicate entry

Question

I am working in PHP.

Please what's the proper way of inserting new records into the DB, which has unique field. I am inserting lot of records in a batch and I just want the new ones to be inserted and I don't want any error for the duplicate entry.

Is there only way to first make a SELECT and to see if the entry is already there before the INSERT - and to INSERT only when SELECT returns no records? I hope not.

I would like to somehow tell MySQL to ignore these inserts without any error.

Thank you

Answer 1

You can use INSERT... IGNORE syntax if you want to take no action when there's a duplicate record.

You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.

Or, you can use INSERT... ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate.

Edit: Thought I'd add some examples.

Examples

Say you have a table named tbl with two columns, id and value. There is one entry, id=1 and value=1. If you run the following statements:

REPLACE INTO tbl VALUES(1,50); 

You still have one record, with id=1 value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:

INSERT IGNORE INTO tbl VALUES (1,10); 

The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:

INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200; 

You now have a single record with id=1 and value=200.