INSERT if not Exist Mysql [duplicate]

Question

I have this table

id     follow_id    follower_id
 1  |       2     |      3
 2  |       2     |      4
 3  |       2     |      5
 4  |       2     |      6
 5  |       3     |      7
 6  |       3     |      8

I'd like to skip If i try to insert the "couple" follow_id 2, follower_id 3 without do subquery for the performance

can I do INSERT... ON DUPLICATE KEY UPDATE or something of this?

UPDATE, for symfony user

after answers. i Use Symfony, and for do

UNIQUE INDEX followee_and_follower (follow_id,follower);

http://docs.doctrine-project.org/en/2.0.x/reference/annotations-reference.html#uniqueconstraint

Answer 1

The official syntax for this is

INSERT IGNORE ...

However, in your case it may actually be preferable to do

INSERT ... ON DUPLICATE KEY UPDATE id=id

See benchmarks. In my experience, this hack only improves performance if you've got a primary key--the optimizer won't recognize the no-op if you do follower_id = follower_id.

Answer 2

I believe the syntax you want is INSERT IGNORE.

From the manual:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

This requires a unique index on (follow_id,follower_id) so you will need to add that if it does not exist already:

ALTER TABLE mytable
  ADD UNIQUE INDEX followee_and_follower (follow_id,follower);