avoid duplicate in mysql insert -

avoid duplicate in mysql insert -

i have friends table has 'id', 'friend (integer)' , 'user (integer)' fields.

a friend relationship exists between user , friend. i.e.

id user friend 6 22 45 7 45 22

is same friend relationship , should considered duplicate record.

i want input whole lot of records @ once, like:

insert friends (user, friend) values(22, 34), (22, 76), (22, 567)...;

in situation, can utilize ignore avoid entering duplicate (22, 34) entry(if (22, 34) exists), there way can avoid entering (22, 34) if (34, 22) exists, same relationship.

sort each pair; insert ignore avoid error messages.

you can sort insert doing

insert ignore tbl (a,b) values (least($a, $b), greatest($a, $b));

however, in order batch insert, should sort in client language.

another issue: insert ignore create id before checks dup. therefore, lots of auto_increment values 'burned'.

rather explaining how avoid burning, there seems no reason id. instead have

primary key(user_id, friend_id)

another issue sorting... select may need union:

( select ... user_id = $x ) union ( select ... friend_id = $x )

that implies need this, too:

index(friend_id, user_id)

mysql