mysql - 定义双向链接

Question

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

到目前为止，我为此使用了两种不同的方法:

1. friends 表包含user1 和user2。搜索用户涉及一个看起来像
   的查询... WHERE @userid IN (`user1`,`user2`)，效率不是很高
2. friends 表包含from 和to 字段。启动好友请求会在该方向创建一行，如果接受，则插入第二行，方向相反。还有一个 status 列表明这已经发生，使得搜索类似于:
   ... WHERE `user1`=@userid AND `status`=1

我对这两种解决方案都不是特别满意。第一个感觉 IN 用法很乱，第二个看起来很臃肿，有两行来定义一个链接。

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

Answer 1

一般有两种方法:

1. 每个 friend 对存储一次，首先存储 id 最小的 friend 。

   CREATE TABLE
           friend
           (
           l INT NOT NULL,
           g INT NOT NULL,
           PRIMARY KEY
                   (l, g),
           KEY (g)
           )
   

2. 将每个 friend 对存储两次，两种方式:

   CREATE TABLE
           (
           user INT NOT NULL,
           friend INT NOT NULL,
           PRIMARY KEY
                   (user, friend)
           )
   

要存储其他字段，如友谊状态、接受日期等，您通常会使用第二个表，原因我将在下面描述。

要检索每个用户的好友列表，您需要:

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

要检查两个用户是否是 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 上的索引中。

在性能方面，解决方案也几乎相同。不过，第一个需要两次索引查找，然后进行索引扫描(对于“所有 friend ”)，第二个只需要一次索引查找，因此对于 L/G 解决方案，I/O 数量可能会稍微多一些。一个单独的索引可能比两个独立的索引更深一层，这可能会稍微减轻一点，因此初始搜索可能需要一页阅读更多。与 L/G 相比，这可能会稍微减慢“他们是 friend 吗”对“双对”解决方案的查询速度。

---

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

它的布局还取决于您使用的查询类型。比如说，如果你想“显示我最近的十个友谊”，那么你可能想将时间戳存储在“两对”中，这样​​你就不必进行文件排序等操作。