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.
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.
Say you have a table named
tbl with two columns,
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.