How to 'insert if not exists' in MySQL?

Question

I started by googling, and found this article which talks about mutex tables.

I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (ie, one query to check and one to insert is the result set is empty)?

Does a unique constraint on a field guarantee the insert will fail if it's already there?

It seems that with merely a constraint, when I issue the insert via php, the script croaks.

Answer 1

Any simple constraint should do the job, if an exception is acceptable. Examples :

• primary key if not surrogate
• unique constraint on a column
• multi-column unique constraint

Sorry is this seems deceptively simple. I know it looks bad confronted to the link you share with us. ;-(

But I neverleless give this answer, because it seem to fill your need. (If not, it may trigger your updating your requirements, which would be "a Good Thing"(TM) also).

Edited: If an insert would break the database unique constraint, an exception is throw at the database level, relayed by the driver. It will certainly stop your script, with a failure. It must be possible in PHP to adress that case ...

Answer 2

use INSERT IGNORE INTO table

see http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

there's also INSERT … ON DUPLICATE KEY UPDATE syntax, you can find explanations on dev.mysql.com

Answer 3

Replace might work for you.

Answer 4

on duplicate key update, or insert ignore can be viable solutions with MySQL.

Answer 5

Also i'm using

INSERT INTO table (value1,value2) SELECT 'stuff for value1','stuff for value2' WHERE NOT EXISTS (SELECT * FROM table WHERE value1='stuff for value1' AND value2='stuff for value2') LIMIT 1