mysql - 联系表的问题

Question

我有一些类似于 friend 表的东西。它称为 联系人。它有:

• user_id 是用户的 ID，类似于该联系人的所有者，
• contact_id 是与 user_id 为好友的用户的 ID；

还有第二个表叫做events。它有:

• user_id 是该事件创建者的用户 ID。

我需要选择好友创建的事件。因此，如果我的联系人列表中有 John 和 Anna...我需要显示他们的事件。

解决方法:

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN `bio_contacts` ON (`bio_contacts`.`contact_id` = `bio_community_events`.`user_id`)
 WHERE `bio_contacts`.`user_id` = '33'

即，我的 ID 是 33。它给我 friend 的事件。问题来了……

在某些情况下，我不是与 friend 联系的人。反之亦然，安娜做到了。此查询将简单地忽略它并且不显示 Anna 的结果。

Answer 1

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN `bio_contacts`
    ON (`bio_contacts`.`contact_id` = `bio_community_events`.`user_id`)
 WHERE `bio_contacts`.`user_id` = '33'

UNION ALL                                 --- or UNION if this gives you
                                          --- duplicate row
SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN `bio_contacts` 
    ON (`bio_contacts`.`user_id` = `bio_community_events`.`user_id`)
 WHERE `bio_contacts`.`contact_id` = '33'

或者像这样:

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN 
    ( SELECT `bio_contacts`.`contact_id` AS id
        FROM `bio_contacts`
       WHERE `bio_contacts`.`user_id` = '33'
    UNION ALL                               
      SELECT `bio_contacts`.`user_id` AS id 
        FROM `bio_contacts`                                  
       WHERE `bio_contacts`.`contact_id` = '33'
    ) AS un
    ON ( un.id = `bio_community_events`.`user_id`)

---

要对示例 #1 中的所有返回行设置限制，请使用:

( SELECT ... )
UNION ALL
( SELECT ... )

ORDER BY ?        --- optional
LIMIT x ;

使用 ORDER BY 在这样的查询中可能会非常昂贵。你也可以有这个(不同的)查询，它可以使用索引:

( SELECT ... 
  ORDER BY ?
  LIMIT a
)
UNION ALL
( SELECT ...
  ORDER BY ?
  LIMIT b
)

LIMIT x ;         --- with or without this LIMIT

---

解决原始问题的另一种方法是使用 EXISTS :

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  WHERE EXISTS  
      ( SELECT *
          FROM `bio_contacts`
         WHERE `bio_contacts`.`user_id` = '33'
           AND `bio_contacts`.`contact_id` = `bio_community_events`.`user_id`
      )
     OR EXISTS
      ( SELECT *
          FROM `bio_contacts`
         WHERE `bio_contacts`.`contact_id` = '33'
           AND `bio_contacts`.`user_id` = `bio_community_events`.`user_id`
      )

或:

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  WHERE EXISTS  
      ( SELECT *
          FROM `bio_contacts`
         WHERE ( `bio_contacts`.`user_id` = '33'
             AND `bio_contacts`.`contact_id` = `bio_community_events`.`user_id` )
           OR  ( `bio_contacts`.`contact_id` = '33'
             AND `bio_contacts`.`user_id` = `bio_community_events`.`user_id` )
      )

---

如果您的计划是找到最有效的查询，请尝试所有正确的方法(使用 IN、使用 UNION、使用 EXISTS)- 添加您想要的 ORDER BY 当然-并检查它们的速度和执行计划。

我至少会:

• 在表 bio_community_events 中
  • user_id 的索引
  • 用于ORDER BY的字段索引

和

• 在表bio_contacts中，两个复合索引
  • (contact_id, user_id) 和
  • 在 (user_id, contact_id)

如果你不能让它在 X 毫秒内运行(X 由你的老板决定 :)，请发布另一个问题