How to 'insert if not exists' in php MySQL [duplicate]

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.

You should turn your logic around. MySQL has a functionality to update if a record exist. dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html You can use that to avoid insert duplicate records. Yes you do need a unique field in the records. — Daniel
– Daniel, Commented Jan 24, 2018 at 16:50

Blue · Accepted Answer · 2018-01-24 16:39:03Z

6

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

The unique constraints are enforced when inserting new data. If you try and insert data into a table that already has data, you should get an error like below:

Error Code: 1062. Duplicate entry 'john-doe-(408)-999-9765' for key 'idx_name_phone'

You can use INSERT IGNORE to simply ignore the insert on the duplicate rows (The error is essentially turned to a warning), or use ON DUPLICATE KEY UPDATE to update the row instead of inserting a new one. See this post for more information.

answered Jan 24, 2018 at 16:39

Blue

23k7 gold badges69 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to 'insert if not exists' in php MySQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related