MySQL中的唯一值组合

Question

我正在构建一个简单的朋友关系表，包含3列：id、user1和user2。

当用户与另一个用户成为朋友时，它们的ID将被添加到表格中，如下所示：

+----+-------+-------+
| id | user1 | user2 |
+----+-------+-------+
|  1 |   15  |   9   |
+----+-------+-------+
|  2 |   9   |   32  |
+----+-------+-------+

上面的表格很好，但在某些情况下，用户可能想要与已经是朋友的用户成为朋友，导致以下表格：

+----+-------+-------+
| id | user1 | user2 |
+----+-------+-------+
|  1 |   15  |   9   |
+----+-------+-------+
|  2 |   9   |   32  |
+----+-------+-------+
|  3 |   9   |   15  |
+----+-------+-------+

在这个表格中，索引1和3彼此冲突（2和3不是），因此我希望在插入时返回一个错误（重复条目）。有办法做到这一点吗？

- Felix ZY

Answer 1

在向此表中插入数据之前，您应始终确保user1具有两个用户ID中较小的那一个。这样，您所描述的情况就不会发生。

您可以在应用程序代码中实现此功能，也可以将其作为存储过程执行以插入两个用户之间的新关系。

- Dmytro Shevchenko

---

你所指的存储过程是“插入之前”触发器，更加具体地说？ - Bulat

@Bulat 不是触发器，而是存储过程。我的意思是，可以通过存储过程来执行查询，而不是在应用程序代码中形成查询。 - Dmytro Shevchenko

好的。在插入之前，还有另一种选择。 - Bulat

@Bulat 是的，那是另一种可能性。虽然我认为将其实现为触发器会在代码维护的角度上使逻辑有些模糊。 - Dmytro Shevchenko

我猜触发器与应用程序代码的决定是团队口味的问题。我不明白为什么它会比存储过程更加模糊。 - Bulat

Answer 2

让我提供另一个视角。你可能希望将friends表保持为互惠关系。因此，两个方向的关系都应存储在该表中。正确的表格如下：

----+-------+-------+
| id | user1 | user2 |
+----+-------+-------+
|  1 |   15  |   9   |
+----+-------+-------+
|  2 |   9   |   15  |
+----+-------+-------+
|  3 |   9   |   32  |
+----+-------+-------+
|  4 |   32  |   9   |
+----+-------+-------+

为什么要做这样一个荒谬的事情，将数据的大小增加一倍？这样的数据通常用于查询某个用户的所有好友或好友的朋友是谁。这些查询需要遍历这个数据作为图形结构，并且你需要两个链接。如果每个好友关系只有一行数据，这些查询不仅变得更加复杂，而且由于子查询（派生表）经常涉及，查询失去了使用索引的能力。

在这种结构中，插入数据时需要注意插入关系的两个方向。然后，在这两列上简单地使用唯一性约束条件就可以确保没有重复插入。

- Gordon Linoff

---

这是一个很好的观点。我能想到另一个角度，即用户1将用户2添加为好友并不意味着他们成为朋友，直到用户2也添加了用户1。而且很可能应用程序在逻辑上应该区分这两个操作/关系。 - Dmytro Shevchenko

Answer 3

您可以创建一个触发器来自动修复此问题，类似于Dmytro的回答：

CREATE TRIGGER trgr_uid_check BEFORE INSERT ON Relationships 
    FOR EACH ROW 
    BEGIN 
      IF NEW.user1 > NEW.user2 THEN 
        SET @user1 = NEW.user1;
        SET NEW.user1 = NEW.user2;
        SET NEW.user2 = @user1;
      END IF; 
    END

- Bulat

Answer 4

你可以执行一个简单的查询来检查是否已经存在友谊关系：

SELECT id
FROM your_table
WHERE (user1 = numToInsert1 AND user2 = numToInsert2) 
OR (user1 = numToInsert2 AND user2 = numToInsert1)

如果这个语句返回了任何内容，那么意味着这两个人之间已经有了友谊关系。如果这个语句没有返回任何内容，请插入您的新朋友关系。

- MikeVe