CODEWITHSUNDEEP
mysql
grooble
grooble

Reputation: 657

avoid duplicate in mysql insert

I have a friends table that has 'id', 'friend (INTEGER)' and 'user (INTEGER)' fields.

A friend relationship exists between user and friend. i.e. 

id     user     friend

6       22        45

7       45        22

is the same friend relationship and should be considered a duplicate record.

I want to input a whole lot of records at once, so something like:

INSERT INTO friends (user, friend) VALUES(22, 34), (22, 76), (22, 567)...;

In this situation, I can easily use IGNORE to avoid entering a duplicate (22, 34) entry(if (22, 34) already exists), but is there a way I can also avoid entering (22, 34) if (34, 22) already exists, as this is the same relationship.

Upvotes: 1

Views: 85

Answers (1)

Rick James
Rick James

Reputation: 142208

Sort each pair; then do INSERT IGNORE to avoid error messages.

You can sort as you insert by doing

INSERT IGNORE INTO tbl (a,b) VALUES (LEAST($a, $b), GREATEST($a, $b));

However, in order to do a batch insert, you should probably sort in the client language.

Another issue: INSERT IGNORE will create an id before it checks for dup. Therefore, lots of AUTO_INCREMENT values will be 'burned'.

Rather than explaining how to avoid the burning, I will say that there seems to be no good reason for id. Instead have

PRIMARY KEY(user_id, friend_id)

Another issue with the sorting... SELECT may need to do a UNION:

( SELECT ... WHERE user_id = $x )
UNION ALL
( SELECT ... WHERE friend_id = $x )

That implies that you need this, too:

INDEX(friend_id, user_id)

Upvotes: 1

Related Questions