定义双向链接

Question

我有一个users表，我想定义两个任意用户之间的“朋友”关系。

到目前为止，我已经使用了两种不同的方法：

1. friends表包含user1和user2。搜索用户需要一个类似于 ... WHERE @userid IN (`user1`,`user2`)，效率不高
2. friends表包含from和to字段。发起一个朋友请求会在这个方向上创建一个行，如果它被接受了，那么第二个行将被插入到相反的方向。另外还有一个status列，该列指示发生了这种情况，使得搜索类似于： ... WHERE `user1`=@userid AND `status`=1

我对这两种解决方案都不太满意。第一个问题让人觉得IN的使用很混乱，第二个问题似乎有点臃肿，因为有两行来定义一个链接。

这就是我来这里的原因。你对这种联系有什么建议？请注意，我不需要保存更多的信息，我只需要两个彼此关联的用户it，最好是某种状态，比如ENUM('pending','accepted','blocked')，但这是可选的，这取决于它的最佳设计。

Answer 1

一般而言，有两种办法：

1. 将每个朋友对存储一次，首先用最少的id存储朋友。 创建表友(l INT NULL，g INT NULL，主键(l，g)，KEY (g) )
2. 两次存储每对朋友，两种方式： 创建表(用户INT不为空，朋友INT不为空，主键(用户，朋友))

为了存储其他字段，如友谊状态、接收日期等，您通常使用第二个表，原因如下。

若要检索每个用户的朋友列表，请执行以下操作：

SELECT  CASE @myuserid WHEN l THEN g ELSE l END
FROM    friend
WHERE   l = @myuserid 
        OR
        g = @myuserid

或

SELECT  g
FROM    friend
WHERE   l = @myuserid
UNION
SELECT  l
FROM    friend
WHERE   g = @myuserid

第一个解决办法；以及

SELECT  friend
FROM    friend
WHERE   user = @friend

若要检查两个用户是否是朋友，请发出以下命令：

SELECT  NULL
FROM    friend
WHERE   (l, g) =
        (
        CASE WHEN @user1 < @user2 THEN @user1 ELSE @user2 END,
        CASE WHEN @user1 > @user2 THEN @user1 ELSE @user2 END
        )

或

SELECT  NULL
FROM    friend
WHERE   (user, friend) = (@user1, @user2)

就存储而言，这两种解决方案几乎是相同的。第一个(最少/最大)解决方案存储的行数是原来的两倍，但是，为了快速工作，您应该在g上有一个辅助索引，它实际上必须存储g加上表的主键中没有在辅助索引(即l)中的部分。因此，每个记录有效地存储了两次:一次在表本身中，一次在g上的索引中。

就性能而言，解决方案也几乎是一样的。但是，第一种方法需要两个索引查找，然后是索引扫描(对于“所有的朋友”)，第二个索引搜索只需要一个索引查找，因此对于L/G解决方案，I/O量可能要轻一些。一个索引可能比两个独立索引更深，这一点可能会稍微减轻一些，因此初始搜索可能需要多读一页。与L/G相比，这可能会减缓“他们是朋友”对“两对”解决方案的查询速度。

至于额外数据的附加表，您很可能想要它，因为它通常比我前面描述的两个查询少得多(通常只用于历史目的)。

它的布局也取决于您使用的查询类型。比方说，如果你想“展示我最后的十种友谊”，那么你可能想把时间戳存储在“两对”中，这样你就不用做文件操作等等了。

Answer 2

考虑以下模式：

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  PRIMARY KEY (`uid`)
);

INSERT INTO `users` (`uid`, `username`) VALUES
(1, 'h2ooooooo'),
(2, 'water'),
(3, 'liquid'),
(4, 'wet');


CREATE TABLE `friends` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid_from` int(10) unsigned NOT NULL,
  `uid_to` int(10) unsigned NOT NULL,
  `status` enum('pending','accepted','blocked') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid_from` (`uid_from`),
  KEY `uid_to` (`uid_to`)
);

INSERT INTO `friends` (`id`, `uid_from`, `uid_to`, `status`) VALUES
(1, 1, 3, 'accepted'), -- h2ooooooo sent a friend request to liquid - accepted
(2, 1, 2, 'pending'), -- h2ooooooo sent a friend request to water - pending
(3, 4, 1, 'pending'), -- wet sent a friend request to h2ooooooo - pending
(4, 4, 2, 'pending'), -- wet sent a friend request to water - pending
(5, 3, 4, 'accepted'); -- liquid sent a friend request to wet - accepted

我会用下面这样的方法：

SELECT
    fu.username as `friend_username`,
    fu.uid as `friend_uid`
FROM
    `users` as `us`
LEFT JOIN
    `friends` as `fr`
ON
    (fr.uid_from = us.uid OR fr.uid_to = us.uid)
LEFT JOIN
    `users` as `fu`
ON
    (fu.uid = fr.uid_from OR fu.uid = fr.uid_to)
WHERE
    fu.uid != us.uid
AND
    fr.status = 'accepted'
AND
    us.username = 'liquid'

结果：

friend_username | friend_uid
----------------|-----------
h2ooooooo       | 1
wet             | 4

在这里，us将是您要查询的朋友的用户，而fu将是用户的朋友。您可以轻松地更改WHERE语句以选择您想要的用户。如果您希望找到用户尚未答复的好友请求，则可以将状态更改为挂起(只应加入uid_to)。

SQLFIDDLE演示

EXPLAIN如果我们使用us.uid来匹配用户(因为它是索引的)：

​

​

Answer 3

撇开性能考虑因素不谈，另一个选项可能是一个“朋友”表，其中一行代表朋友(不管是哪种方式)，还有一个视图，它为任何朋友行生成两个结果行(每个方向一个)。在使用中，它将简化查询，因为它可以与“两行”解决方案相同的方式使用，而每个“友谊”只需要一个数据行。

唯一的缺点是表演..。取决于查询优化器的工作方式。