In MySQL, how do I write a query to skip a duplicate row while inserting, when there's no unique field (primary key)?

Question

I am trying to insert rows into a table that has no unique field or primary key. How can I write a query that will simply ignore the insert if there already exists a row with the exact same values on all fields -- a duplicate row?

Thanks.

Answer 1

Uh, why not make a primary key?

Otherwise, you have to basically do SELECT COUNT(*) FROM table WHERE field1=value AND ... AND fieldN=value for before EVERY insert.

Answer 2

You must have a primary key or unique key defined on some column or columns in the table for uniqueness to have any meaning. Every mechanism for detecting duplicates automatically relies on this being true.

You can't do the SELECT COUNT(*)... solution because it's subject to race conditions. That is, someone could insert a duplicate row in the moment after you select and before you insert. The only way around this is to lock the table with SELECT ... FOR UPDATE or LOCK TABLES.