How to add to a MySQL table only if the record doesn't already exist?

Question

I'm getting an array of strings from a JSON object. I want to save these strings in a DB, but only if they're not already in there. What's the best way to compare what's already in the DB with what I'm about to enter, and exclude matches?

I'm using PHP and MySQL. Thanks!

Answer 1

REPLACE may do what you want - it inserts, but if the primary key already exists that row gets deleted first. Unless you need to retain other columns in the table, it should work.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Answer 2

INSERT ... ON DUPLICATE KEY UPDATE

See also: "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

Answer 3

Since what you describe is wanting to leave the record alone if the strings already exist, what you probably want is the IGNORE modifier to INSERT (i.e. you do INSERT IGNORE ...). That will make your query do nothing if a duplicate key is found. This requires, of course, that the string fields you're inserting into have unique keys on them.

Answer 4

In MySql you can use an insert statement with the "ignore" option, which will not insert a row if its unique fields match those of an existing row. The table must have a uniqueness constraint with the relevant fields included.

Answer 5

Very simple example:

Your table : users Your fields : users_id, user_name, zip_code, created Unique Index : user_name

INSERT INTO users (user_name, zip_code, created) VALUES ('joe',75034,'2009-06-11') ON DUPLICATE KEY UPDATE users_id=LAST_INSERT_ID(id)

The user_name has a unique index; so, you can't add it more than once. When you use the SQL above, it will add just fine the first time. The second time, it won't actually do the insert. The way the on duplicate key is setup, you can now fetch the last insert id from mysql with PHP and know what the users_id was whether it was a true insert or a duplicate.