考虑两列，如何防止重复值插入mySQL？

问题描述：

Here is my table:

Table Name: UserLinks

Link_ID   User_1   User_2
1         234325   100982
2         116727   299011
3         399082   197983
4         664323   272351

Basically, in this table a duplicate value is:

Link_ID   User_1    User_2
1         232       109
2         109       232

I have looked around and found that I should use INSERT IGNORE to prevent duplicate entries, but I am not sure how to write a query that considers that the relationship between User_1 and User_2 is the same as between User_2 and User_1.

Any advice/help is really appreciated.

这是我的表格： p>

表名：UserLinks p>

  Link_ID User_1 User_2 
1 234325 100982 
2 116727 299011 
3 399082 197983 
4 664323 272351 
  code>  pre> 
 
 基本上，在此 表重复值为： p> 
 
 
  Link_ID User_1 User_2 
1 232 109 
2 109 232 
  code>  pre> 
 
 我有 环顾四周，发现我应该使用INSERT IGNORE来防止重复输入，但我不知道如何编写一个认为User_1和User_2之间的关系与User_2和User_1之间的关系相同的查询。 p> 
 \  n 
非常感谢任何建议/帮助。 p> 
  div>

答

Thats a bit nasty, a commutative relationship between the 2 fields, but a unique index will not help given the values can be either way around.

If you could alter the code / data to ensure that the lower value of the ids was always placed in the user_1 field, that would at least then let the unique index work - but its a bit nasty.

Alternatively if the insertion is set based (e.g. not a row at a time but a set of rows) you could join to the existing data and anti-join based on both ways round e.g. :

(existing.user_1 = new.user_1 and existing.user_2 = new user_2)
 OR (existing.user_1 = new.user_2 and existing.user_2 = new user_1)

and in the where clause check to ensure no match was made (the anti part of the join)

where existing.link_id is null

That wouldn't be efficient for row at a time insertion though.

答

How accurate do you need it. You could just create a unique index (or primary key) that is the hash of the 2 values xor'd together.

Something like primary key (md5(user_1) xor md5(user_2)).

Because "md5(232) xor md5(109)" will always equal to "md5(109) xor md5(232)". It does no matter on the order.

This will have a small chance of collision if you have a lot of records (like millions or billions) but otherwise, it should work.

You might need to check on the sql for this, as I did not test if SQL allows primary key to be generated like this.

This way, you do not need to add any additional check when inserting or updating as the unique constrant will do the checking for you.

考虑两列，如何防止重复值插入mySQL？

相关推荐