INSERT if not Exist Mysql

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

Ike Walker · Answer 1 · 2013-12-05T14:48:42.183

4

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);

edited Dec 05 '13 at 14:48

answered Dec 05 '13 at 14:41

Ike Walker

64,401
14
110
109

i try to INSERT IGNORE INTO mytable (follow_id, follower_id) VALUES (2,3), but add a row with new id and follow_id 2 follower_id 3 – Barno Dec 05 '13 at 14:46
1

You need a unique index on (follow_id,follower_id). Please see my updated answer above. – Ike Walker Dec 05 '13 at 14:49

score 4 · Accepted Answer · answered Dec 05 '13 at 14:49

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.

INSERT if not Exist Mysql

2 Answers2

Linked