Mysql:关联表 [英] Mysql : Association table

问题描述

使用 mySQL，我有这些表:

Using mySQL, I have these tables:

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


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

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

I want each Person_Association to be unique: if (#1, #2) exists, then neither (#1, #2) nor (#2, #1) can be inserted.

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

To do that I've added a field and a trigger to Person_Association, like this:

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

它有效，但有没有更好的方法来做到这一点?

It works, but is there a better way to do that?

推荐答案

你最好扔掉触发器并在 (id_Person1, id_Person2) 上有一个唯一索引.
其实你也可以扔掉不必要的unique_id，让(id_Person1, id_Person2)为主键.

You better throw away the trigger and have an unique index on (id_Person1, id_Person2).
Actually you can also throw away the unnecessary unique_id and make (id_Person1, id_Person2) the primary key.

然后你像这样插入:

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)):

If you really insist on "SQL to assure that (not the application layer)", I'd write it like this (but still have your unique_id column removed and an unique index on (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 ;

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

I'd still prefer not to have a trigger on the table (that's from database administrators view, not only do they often cause confusion, but they tend also to get in the way when doing administrators tasks). So, to pursuit my first way of solving it, you can also create a stored procedure to do this for you:

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);

• 在此处阅读有关信号的更多信息.
• 这里原因我选择了 23000 作为 sqlstate(但其实无所谓)
• read more about signals here.
• here's why I chose 23000 as sqlstate (but in fact it doesn't matter)

这篇关于Mysql:关联表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！