MySQL和UNIQUE列：如何避免重复？

I have a table with the following column:

我有一个包含以下列的表:

(id, col_1, col_2)

where "id" is the primary key (auto-inc).

其中“id”是主键(auto-inc)。

I would like to make col_1 and col_2 a "unique combo".

我想让col_1和col_2成为“独特的组合”。

For example, if I insert the data...

例如,如果我插入数据......

(.., col_1, col_2 ) = (.., 'A', 'B')

...Next, I shouldn't be allowed to insert the following:

...接下来,我不应该被允许插入以下内容:

(.., col_1, col_2 ) = (.., 'A', 'B')
(.., col_1, col_2 ) = (.., 'B', 'A') #because it already exists, in another order:('A','B')

I successfully blocked the first case with the use of UNIQUE(col_1, col_2)...

我使用UNIQUE(col_1,col_2)成功阻止了第一个案例......

But it seems that the "reverse combo" (.., 'B', 'A') is permitted.

但似乎允许“反向组合”(..,'B','A')。

How can I stop this?

我怎么能阻止这个?

2 个解决方案

#1

After trying it out it really isn't as easy as creating another unique index.
This seems to be an odd flaw in MySQL. A workaround though can be to always store the lesser value in col_1 and the greater value in col_2 (when you don't like triggers, that is):

在尝试之后,它实际上并不像创建另一个唯一索引那么容易。这似乎是MySQL中的一个奇怪的缺陷。解决方法虽然可以始终在col_1中存储较小的值,在col_2中存储较大的值(当您不喜欢触发器时):

INSERT INTO your_table (col_1, col_2) VALUES (LEAST('A', 'B'), GREATEST('A', 'B'));

You can find the documentation to these functions here.

您可以在此处找到这些功能的文档。

#2

As said by fancyPants, the solution is to ensure one column is always lower than the other. MySQL does not support CHECK constraints, but you can enforce this with a trigger, which will allow you to catch application bugs:

正如fancyPants所说,解决方案是确保一列始终低于另一列。 MySQL不支持CHECK约束,但您可以使用触发器强制执行此操作,这将允许您捕获应用程序错误:

Can a MySQL trigger simulate a CHECK constraint?

MySQL触发器可以模拟CHECK约束吗?

Beware: depending on MySQL version, raising an error with your trigger will use different methods.

注意:根据MySQL版本,使用触发器引发错误将使用不同的方法。

On newer MySQL versions, you can also create virtual columns and index them. Perhaps you could create a UNIQUE(least(a,b), greatest(a,b))... but it would be kinda clunky.

在较新的MySQL版本中,您还可以创建虚拟列并对其进行索引。也许你可以创造一个独特的(最少(a,b),最大(a,b))...但它会有点笨重。

Also, knowing that a<=b is a constraint will speed up search. If you need to search for either (x,y) or (y,x) then you know that the only one in your table will be the one with proper order, which simplifies your WHERE and makes index use much better.

此外,知道<= b是约束将加速搜索。如果你需要搜索(x,y)或(y,x),那么你知道表中唯一的一个是正确顺序的,这简化了你的WHERE并使索引使用得更好。

#1

INSERT INTO your_table (col_1, col_2) VALUES (LEAST('A', 'B'), GREATEST('A', 'B'));