MySQL在两列之间强制执行唯一性

本文介绍了MySQL在两列之间强制执行唯一性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在MySQL中实现我以前从未听说过的东西(也许是，研究并没有太大帮助).

I'm trying to achieve something in MySQL that I have not heard is possible before (maybe it is, researching didn't help me much).

我想做的是在MySQL的两列中强制执行唯一性.我的意思不是设置UNIQUE(column1，column2)而是以下两个条件:

What I'm trying to do is enforce uniqueness in MySQL across two columns. What I mean by this is not setting UNIQUE(column1,column2) but the following two conditions:

如果列1中存在一个值，则不能在列1中重复该值(与设置UNIQUE(column1)相同).

如果一个值存在于任一列中，则该值不能存在于另一列中.

因此，对于数据集{column1，column2}，如果已经存在{1,2}，{3,4}，{5,6}数据，那么这两列都不能具有以上任何一项新数据的数据项，即新数据项{x，y}，其中x = NOT {column1} AND y = NOT {column2} AND x！= y

Hence, for the data set {column1,column2}, if {1,2}, {3,4}, {5,6} are data already present, then neither of the two columns can have any of the above data items for new data,i.e. new data item {x,y} where x=NOT{column1} AND y=NOT{column2} AND x!=y

这可能吗?请在这里帮助我.谢谢.

Is this possible? Please help me out here. Thank you.

推荐答案

这可能是一个矫kill过正的方法，但是您可以将column1和column2存储在单独的表中.

This might be an overkill, but you can store column1 and column2 in a separate table.

假设您的桌子是

create table items ( id int primary key, column1 int, column2 int );

带有数据:

id | column1 | column2 ---|---------|-------- 1 | 1 | 2 1 | 3 | 4 1 | 5 | 6

您可以将架构更改为

create table items ( id int primary key ); create table item_columns ( item_id int, position int, val int, primary key (item_id, position), unique key (val), foreign key (item_id) references items(id) );

带有数据:

item_id | position | val --------|----------|---- 1 | 1 | 1 1 | 2 | 2 2 | 1 | 3 2 | 2 | 4 3 | 1 | 5 3 | 2 | 6

您可以使用来模拟旧模式

You can simulate the old schema with

select i.id, c1.val as column1, c2.val as column2 from items i left join item_columns c1 on c1.item_id = i.id and c1.position = 1 left join item_columns c2 on c2.item_id = i.id and c2.position = 2

如果愿意，可以在视图中使用它.

You can use it in a view if you like.

演示: rextester/PPBT42478

要确保position列的完整性，可以将其设为positions表的外键，该表仅包含值1和2.您也可以使用ENUM('1', '2')，但是ENUM始终允许使用空字符串作为值.

To guaranty the integrity for the position column, you can make it a foreign key to a positions table, which will only contain the values 1 and 2. You could also use ENUM('1', '2'), but ENUM always allow an empty string as a value.