MySQL实现双向关注好友列表查询（排除当前登录用户）

基础信息 #

涉及表结构 #

• follows：用户关注关系表，核心字段为sender_username（关注发起方账号）、receiver_username（被关注方账号）
• users：用户信息表，核心字段为user_Name（用户唯一账号）、fullname（用户展示名称）

测试数据 #

-- follows表测试数据
mysql> select * from follows;
| id | sender_username | receiver_username |
|----|-----------------|-------------------|
| 1  | kate            | james             |
| 2  | john            | kate              |
| 3  | james           | kate              |
| 4  | niner           | manb              |
| 5  | james           | manb              |
| 6  | manb            | james             |
| 7  | bert            | oken              |
| 8  | kate            | alex              |

-- users表测试数据
mysql> select * from users;
| id | fullname   | user_Name |
|----|------------|-----------|
| 1  | Phill Ja   | kate      |
| 2  | Another H  | james     |
| 3  | Some Crazy | manb      |
| 4  | Nameless   | nede      |

需求规则 #

• 仅返回与当前登录用户双向互关的用户：需同时满足「当前用户关注对方」、「对方关注当前用户」两个条件
• 结果中排除当前登录用户自身
• 示例校验：当前登录用户为james时，预期返回互关好友为kate、manb

原有SQL缺陷 #

初始尝试的SQL仅关联了用户作为关注发起方的单向关注记录，未校验反向关注关系，不满足互关逻辑判断要求，无法返回正确结果，原SQL如下：

SELECT users.fullname, users.user_Name, 
follows.receiver_username,follows.sender_username 
from users 
inner join 
follows on users.user_Name=follows.sender_username 
where
users.user_Name ='name';

可直接运行的正确SQL #

核心逻辑为自关联关注表校验双向关注关系，再关联用户表拉取互关好友的基础信息，使用时将SQL中'james'替换为实际当前登录用户的账号即可：

SELECT 
  u.id,
  u.fullname,
  u.user_Name
FROM follows f1
-- 自关联查找反向关注记录，确认对方同时关注了当前用户
INNER JOIN follows f2 
  ON f1.receiver_username = f2.sender_username 
  AND f2.receiver_username = f1.sender_username
-- 关联用户表获取互关好友的展示信息
INNER JOIN users u 
  ON u.user_Name = f1.receiver_username
WHERE 
  -- 筛选当前用户主动发起关注的记录
  f1.sender_username = 'james'
  -- 兜底排除自身记录，避免异常数据导致结果出错
  AND u.user_Name != 'james';

逻辑说明 #

• 别名f1的记录集筛选当前用户主动关注的所有账号：f1.sender_username固定为当前登录用户，f1.receiver_username是当前用户关注的所有对象
• 自关联f2做互关校验：要求f1中的被关注对象，同时作为关注发起方关注了当前登录用户，匹配到记录即满足双向关注要求
• 最终关联users表仅返回互关好友的必要展示字段，无冗余数据
• 基于给出的测试数据运行该SQL，将正确返回kate、manb两条用户记录，完全符合预期结果

内容的提问来源于stack exchange，提问作者Barry Clinton