☰

MySQL 复合键 - 唯一组合

tag-icon • tag-icon mysql
MySQL 复合键 - 唯一组合

假设有一张表，其列为a和b，且具有来自a,bpair 的复合唯一键。还有 行a=1, b=2。

所以我不能插入另一行a=1, b=2，没关系。

但在这种情况下我怎么能拒绝插入行呢a=2, b=1？

例如：

CREATE TABLE `t` (`a` INT, `b` INT); 
ALTER TABLE `t` ADD UNIQUE(`a`, `b`); 
INSERT INTO `t` (`a`, `b`) VALUES ('1', '2'); 
INSERT INTO `t` (`a`, `b`) VALUES ('2', '1');

第二次插入成功了，尽管我预计它会失败。

答案1

自 MySQL 7.5.3 起

你可以定义生成的列并对其设置唯一索引。例如，您可以说concat(least(a, b), ',', greatest(a, b))必须是唯一的。然后您可以定义一个虚拟的具有相同表达式的列并在其上设置唯一索引。

create table t (
  a int,
  b int,
  virtual_unique varchar(50) as (concat(least(a, b), ',', greatest(a, b))),
  primary key (a, b),
  unique index (virtual_unique)
);

当您尝试插入(1, 2)和时(2, 1)，您会收到错误：

ER_DUP_ENTRY：键“virtual_unique”的重复条目“1,2”

db-fiddle

请注意，在这种情况下，列不应包含使用的分隔符（,在本例中），对于整数来说总是如此。

您还可以在虚拟列上定义复合唯一键：

create table t (
  a int,
  b int,
  virtual_min int as (least(a, b)),
  virtual_max int as (greatest(a, b)),
  primary key (a, b),
  unique index (virtual_min, virtual_max)
);

您的插入语句将引发：

ER_DUP_ENTRY：键“virtual_min”的重复条目“1-2”

db-fiddle

此处不存在分隔符问题。

请注意，SELECT *查询将显示这些虚拟列。

自 MySQL 8.0.13 起

你也可以索引表达式，无需未使用的虚拟列：

create table t (
  a int,
  b int,
  primary key (a, b),
  unique index ((least(a,b)), (greatest(a,b)))
);

您的插入将引发以下错误：

键“ functional_index”的重复条目“ 1-2”

数据库<>文件

答案2

您不能使用键/索引定义。从数据库角度来看，(1,2) 和 (2,1) 的值始终不同。

解决方案是确保 eg 的a值始终高于b或反之亦然。您可以在添加记录的应用程序中或作为 DB 触发器来确保这一点。

也可以看看：MySQL 唯一组合键其中有一个关于如何使用触发器执行此操作的示例。

相关内容