Mysql 子过滤

Question

假设我有一个用户表 U 和一个表示用户之间关系(友谊)的表 R。用户可以创建保存在另一个表 P 中的帖子(使用发送帖子的用户的 user_id)。

我想检索 friend 的 friend 的 friend ....特定人的 friend 发布的所有帖子。这意味着当例如我有以下设置:

users (U)
  id - name
  1 - Mark
  2 - Eve
  3 - Peter

relations (R)
  id_user1 - id_user2
  1 - 2
  2 - 3

posts (P)
  id - user_id - text
  1 - 1 - "Hello from Mark"
  2 - 2 - "Hello from Eve"
  3 - 3 - "Hello from Peter"

我想检索 Mark 发出的所有消息，我只需选择带有 Mark 的 user_id 的所有帖子。但是当我想要检索 Mark 的 friend 发布的所有帖子时，它变得很棘手。马克在这个例子中只有一个 friend ，所以很明显我们会在这里看到夏娃和马克的帖子。但我们假设他有 500。我会先执行一个 SQL 命令来检索他所有的 friend ，然后做类似的事情

SELECT * from posts WHERE user_id in (2,4,5,6,7,8,9,10,11,12.....,1442);

好吧，这看起来效率很低，但当我想获取他 friend 的 friend (或更多子 friend 层 n)发出的消息时，情况会变得更糟:我会像上面的例子一样发出 n 个请求(每一层一个)来检索他 friend 的 friend 的 id。然后，当我得到他的 n-sub-friends 的 Id 结果时，我会像上面那样做。例如，当我们想要检索 Marks friend 的 friend 的所有帖子时，我们将获取所有帖子，因为 Mark 的 friend Eve 有 Peter 作为 friend 。

当用户只有 10 个 friend 并且每个 friend 也有 10 个 friend 时，我会得到 10^n 个 ID。当我想创建一个包含“以某种方式连接”的 friend 的所有帖子的网络时，只获取所有用户的 ID 会花费很长时间。我想当我传递 100'000 个 ID 时，使用“in (....)”进行过滤会花费更长的时间。

有没有有效的方法来解决这个问题？

我考虑过保存一些冗余数据，例如将每个子 friend 与每个用户直接关联到另一个表中，但我怀疑这是否有效，因为这将成为一个非常巨大的表..

Answer 1

这差不多了(见下面的temporary table问题):

DELIMITER // -- not to be used on sqlfiddle.com, use //-setting under window
CREATE TABLE allfriends (fid int)// -- this should be inside myProc as
                                    -- CREATE TEMPORARY TABLE ...
CREATE PROCEDURE myProc(id int)
BEGIN
 DECLARE ncnt int;
 DECLARE cnt int;
 INSERT INTO allfriends VALUES (id);
 SET ncnt=1;
 REPEAT
  SET cnt = ncnt;
  INSERT INTO allfriends 
  SELECT id_user2 FROM relations 
    INNER JOIN allfriends ON id_user1=fid 
          AND NOT EXISTS (SELECT 1 FROM allfriends WHERE fid=id_user2);
  SET ncnt:= (SELECT COUNT(*) FROM allfriends);
 UNTIL ncnt=cnt END REPEAT;
 SELECT p.* from posts p INNER JOIN allfriends ON user_id=fid AND fid<>id;
 DELETE FROM allfriends;
END//

打电话

call myProc(1)

然后将为您获取马克的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的 friend 的所有消息。

理想情况下，您应该为 allfriends 使用临时表因为多个同时调用 myProc()现在会相互干扰，但由于某种原因我无法让它在 SQLfidde 上工作。自己看看:http://sqlfiddle.com/#!2/28944f/1

编辑

关系可以双向，所以你真的应该做这样的事情

SELECT id2 FROM 
    (      SELECT id_user1 id1,id_user2 id2 FROM relations 
     UNION SELECT id_user2    ,id_user1     FROM relations) rel
    INNER JOIN allfriends ON id1=fid 
          AND NOT EXISTS (SELECT 1 FROM allfriends WHERE fid=id2);

当您在 allfriends 中收集 ID 时，请在此处查看更新的 fiddle :http://sqlfiddle.com/#!2/f9a6b3/1

第二次编辑

终于明白了!看这里:http://sqlfiddle.com/#!2/b16b7a/1

-- DELIMITER //
CREATE PROCEDURE myProc(id int)
BEGIN
 DECLARE ncnt int;
 DECLARE cnt int;
 CREATE TEMPORARY TABLE allfriends (fid int);
 CREATE TEMPORARY TABLE dbl (di int);
 INSERT INTO allfriends VALUES (id);
 SET ncnt=1;
 REPEAT
  SET cnt = ncnt;
  INSERT INTO dbl 
  SELECT id2 FROM 
    (      SELECT id_user1 id1,id_user2 id2 FROM relations 
     UNION SELECT id_user2,    id_user1     FROM relations) rel
    INNER JOIN allfriends ON id1=fid;
  DELETE FROM allfriends;
  INSERT INTO allfriends SELECT DISTINCT di FROM dbl;
  SET ncnt:= (SELECT COUNT(*) FROM allfriends);
 UNTIL ncnt=cnt END REPEAT;
 SELECT p.* from posts p INNER JOIN allfriends ON user_id=fid AND fid<>id;
 DELETE FROM dbl;
END// 

因为临时表只能在每个 SELECT 中打开一次-statement 我现在正在使用两个临时表:allfriends有不同的记录和dbl可能重复。通过复制 dbl 的内容消除重复项进入allfriends使用 SELECT DISTINCT ... REPEAT ... UNTIL 内的声明环形。整个事情看起来很复杂 - 但它工作可靠!