为用户友谊设计数据库

Question

我希望在数据库中存储好友关系。我的想法是，当用户1和用户2成为好友时，我会将这个好友关系存储起来，以便在需要时可以获取任何一个用户的所有好友。起初，我想只需在一个表中存储它们的id，并进行一次插入操作，但后来我考虑到查询数据库时可能会出现一些复杂情况。

如果有两个用户的用户ID分别为10和20，那么他们成为好友时，我应该向数据库中插入两条记录吗？

ID USER1 USER2
1  10    20
2  20    10

或者有没有一种方法可以查询数据库，只获取特定用户的朋友，如果我只进行了一个插入操作，就像这样。

ID USER1 USER2
1  10    20

我知道第一种方法肯定能给我想要的结果，但我想知道这是否是良好的实践方式，以及是否有更好的替代方法。如果可以查询第二种方法以获取我想要的结果，比如用户10的所有朋友，则更好。

- gmustudent

---

user1 > user2 可以用作决定胜负的关键因素。或者 user1 < user2 ... - wildplasser

Answer 1

友谊是一种双向联系（在实际情况下）。不像其他单向的链接（比如消息），友谊应该只有一个条目。但是，你看到的是正确的；你需要查询两列才能获得某个用户的朋友，但这很简单：

-- The uses of `1` below is where you'd insert the ID of
-- the person you're looking up friends on
SELECT      u.id, u.name
FROM        friendship f
  LEFT JOIN user u
  ON        (u.id = f.user1 OR u.id = f.user2)
    AND     u.id <> 1
WHERE       (f.user1 = 1 OR f.user2 = 1)

example here

- Brad Christie

Answer 2

Brad Christie的建议是在两个方向上查询表格，这是很好的。 然而，考虑到MySQL在优化OR查询方面并不是很好，使用UNION ALL可能更有效：

( SELECT u.id, u.name
  FROM friendship f, user u
  WHERE f.user1 = 1 AND f.user2 = u.id )
UNION ALL
( SELECT u.id, u.name
  FROM friendship f, user u
  WHERE f.user2 = 1 AND f.user1 = u.id )

这里是一个基于Brad的示例的SQLFiddle。我修改了friendship表格，添加了双向索引以实现高效访问，并删除了无意义的id列。当然，由于这是一个非常小的示例，你无法真正测试现实世界的性能，但比较两个版本之间的执行计划可能会很有启发性。

- Ilmari Karonen

---

快速问题。我能不能只使用Union而不是Union All？因为这里不应该有任何重复的，对吧？ - gmustudent

你可以使用 UNION，但它需要 MySQL 实际检查并删除重复项（如果有的话）。如果你确定不会有重复项，通常使用 UNION ALL 更快。 - Ilmari Karonen

太棒了，谢谢你的建议。& 如果我只想要最多10个朋友，那么限制语句该怎么用？如果我在每个选择语句中添加Limit 0,10，那么总共会返回10个还是20个？有没有办法只获取10个朋友？ - gmustudent

您可以在整个联合中添加LIMIT子句。但是，请注意可能存在性能问题。（我不确定这些问题是否也适用于仅带有LIMIT而没有ORDER BY的情况，在任何情况下，MySQL版本之间可能存在差异。请尝试并查看。）当然，另一个（丑陋的）选项是只向各个SELECT添加LIMIT，并在客户端丢弃任何不需要的额外行。 - Ilmari Karonen

最后一个问题！我看到你的from子句中有两个表。我以前从未见过这种情况，通常使用带有on子句的内连接。使用任何一种方法是否有好处？因为我用内连接来做这件事，但现在我回来看了你的答案，发现了区别。谢谢。 - gmustudent

没有实际区别：SELECT * FROM foo JOIN bar ON foo.x = bar.y 和 SELECT * FROM foo, bar WHERE foo.x = bar.y 两者都会产生相同的输出和查询计划。我通常更喜欢使用 JOIN ... ON 的风格，但在这种情况下，我觉得使用另一种风格看起来更漂亮，并且使两个子查询之间的对称性更加明显。这里有一个非常好的解释不同风格的链接。 - Ilmari Karonen