代码之家 › 专栏 › 技术社区 › stone

如何跨多个列实现双向唯一索引

mysql sql

stone · 技术社区 · 15 年前

好的,我知道我可以在应用层做这件事,这可能是最容易做的事情,但只是为了确保没有错误向DB注水,我有一个严肃的问题。

我有两列x和y,每列存储两个整数(在任意列中为a或b)。是否可以有一个唯一的索引约束,这样,在任何情况下,我们都不应该

带A的X列和带B的Y列
带B的X列和带A的Y列

我会给出一个场景

我有两个用户,用户A的ID是678498,用户B的ID是679879。两个用户都将要玩一个2人游戏,该游戏要求将此会话的新记录存储在表中(tbl_chalenger)。为此,我有一个包含“host”和“challenger”列的表。

我有一个独特的约束添加到tbl_挑战者

UNIQUE KEY `UNIQUE_PARTICIPANTS` (`host`,`challenger`)

给用户打上主机或挑战者的烙印基本上取决于游戏的发起人。所以如果usera启动游戏,我们有一个查询如下

INSERT INTO `tbl_challenger` VALUES(678498 , 679879);

但不幸的是,如果同时用户b试图启动与用户a的游戏,我们会得到

INSERT INTO `tbl_challenger` VALUES(679879, 678498 );

这将创建一个新的不需要的行,属于相同的参与者。这与唯一键约束无关。

所以我的问题是如何有一个双向的约束?,使得“主机挑战者”和“挑战者主机”不能具有相同的数据对

2 回复 | 直到 15 年前

Michael Krelin - hacker 15 年前

在MySQL中,我唯一能想到的就是添加几个实用列。

CREATE TABLE tbl_challenger (
  host int,
  challenger int,
  u0 int, u1 int
);

并添加两个触发器 u0 和 u1 最起码也最重要的是:

CREATE TRIGGER uinsert BEFORE INSERT ON tbl_challenger
 FOR EACH ROW SET NEW.u0 = LEAST(NEW.host,NEW.challenger),
  NEW.u1 = GREATEST(NEW.host,NEW.challenger);
CREATE TRIGGER uupdate BEFORE UPDATE ON tbl_challenger
 FOR EACH ROW SET NEW.u0 = LEAST(NEW.host,NEW.challenger),
  NEW.u1 = GREATEST(NEW.host,NEW.challenger);

然后在 (u0,u1)

CREATE UNIQUE INDEX uniqueness ON tbl_challenger(u0,u1);

现在,无论顺序如何,尝试插入重复对时都会出现错误。

在一个像样的关系数据库上 PostgreSQL 可以对表达式使用索引:

CREATE UNIQUE INDEX uniqueness ON tbl_challenger
    ( LEAST(host,challenger), GREATEST( host,challenger) );

所以,在太迟之前切换;-)

Pascal MARTIN 15 年前

我不认为你能做到这一点,就像使用一个独特的索引或诸如此类的东西。

不过,您可能仍然可以使用触发器实现这种检查,该触发器在插入和更新之前触发。

我已经有一段时间没有使用触发器了,而且它也不在mysql上,所以我不能给你举任何例子,但是这里是手册的相关页面: 12.1.19. CREATE TRIGGER Syntax

附带说明:正如您所说,这种约束通常在应用程序端实现。