mysql : Association table

Question

使用 mySQL，我有这些表:

Person
----------
id (PK)
name
...


Person_Association
----------
id_Person1  (FK Person.id)
id_Person2  (FK Person.id)

我希望每个 Person_Association 都是唯一的:如果 (#1, #2) 存在，则 (#1, #2) 和 (#2, #1) 都不能插入。

为此，我向 Person_Association 添加了一个字段和一个触发器，如下所示:

Person_Association
----------
id_Person1  (FK Person.id)
id_Person2  (FK Person.id)
unique_id   (PK)


CREATE TRIGGER `Person_Association_BINS` BEFORE INSERT ON `Person_Association` FOR EACH ROW
BEGIN
  IF (new.id_Person1 < new.id_Person2) THEN
    SET new.unique_id = CONCAT(new.id_Person1, '-', new.id_Person2);
  ELSE
    SET new.unique_id = CONCAT(new.id_Person2, '-', new.id_Person1);
  END IF;
END

它确实有效，但是有更好的方法吗？

Answer 1

您最好放弃触发器并在(id_Person1, id_Person2)上拥有唯一索引。
实际上，您也可以扔掉不必要的 unique_id 并将 (id_Person1, id_Person2) 作为主键。

然后你像这样插入:

INSERT INTO your_table VALUES (LEAST($value1, $value2), GREATEST($value1, $value2));

或

INSERT INTO your_table SELECT LEAST($value1, $value2), GREATEST($value1, $value2);

编辑:如果你真的坚持“SQL来确保这一点(而不是应用程序层)”，我会这样写(但仍然删除你的 unique_id 列并在 (id_Person1, id_Person2 )):

DELIMITER $$
CREATE TRIGGER `Person_Association_BINS` BEFORE INSERT ON `Person_Association` FOR EACH ROW
BEGIN
  IF EXISTS(SELECT 1 FROM `Person_Association` WHERE id_Person1 = LEAST(NEW.id_Person1, NEW.id_Person2) AND id_Person2 = GREATEST(NEW.id_Person1, NEW.id_Person2)) THEN
  SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT='dup key error';
  END IF;
END $$
DELIMITER ;

我仍然不希望在表上有触发器(从数据库管理员的角度来看，它们不仅经常引起困惑，而且在执行管理员任务时也往往会妨碍)。因此，为了采用我的第一种解决方法，您还可以创建一个存储过程来为您执行此操作:

CREATE PROCEDURE insert_p_a(IN p_id1 INT, IN p_id2 INT)
INSERT INTO your_table SELECT LEAST(p_id1, p_id2), GREATEST(p_id1, p_id2);

在您的应用程序层中您只需这样做

CALL insert_p_a($value1, $value2);

• 了解有关信号的更多信息 here .
• here这就是为什么我选择23000作为sqlstate(但实际上这并不重要)