Mysql:关联表

编程入门行业动态更新时间:2024-10-27 03:42:28

本文介绍了Mysql:关联表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

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