嘿，这个统计好友对共同好友数的需求很常见，我来给你一个能直接跑通的MySQL解决方案，还会帮你拆解清楚逻辑~

首先得明确：原表里的好友关系是单向存储的，但实际是双向的（比如user1=1,user2=2意味着1和2互为好友），所以我们得先把所有双向的好友关系都梳理出来，再去统计每一对的共同好友数。

直接查询结果的SQL #

如果你只是想查询出带common_friends的结果（不需要修改原表），用这个语句：

SELECT 
    f.id,
    f.user1,
    f.user2,
    COUNT(DISTINCT fr.friend_id) AS common_friends
FROM 
    friends f
-- 先获取每个用户的所有好友（双向）
JOIN (
    SELECT user1 AS user_id, user2 AS friend_id FROM friends
    UNION ALL
    SELECT user2 AS user_id, user1 AS friend_id FROM friends
) fr ON fr.user_id = f.user1
-- 再匹配user2的好友，找出和user1好友的交集
JOIN (
    SELECT user1 AS user_id, user2 AS friend_id FROM friends
    UNION ALL
    SELECT user2 AS user_id, user1 AS friend_id FROM friends
) fr2 ON fr2.user_id = f.user2 AND fr.friend_id = fr2.friend_id
-- 排除把对方自己算成好友的情况（虽然原表没这数据，但防一手更稳）
WHERE fr.friend_id != f.user2
GROUP BY f.id, f.user1, f.user2
ORDER BY f.id;

给原表新增列并填充数据的SQL #

如果需要永久给friends表添加common_friends列并填充数据，分两步走：

1. 先添加列：

ALTER TABLE friends ADD COLUMN common_friends INT DEFAULT 0;

2. 再用更新语句填充数据：

UPDATE friends f
JOIN (
    -- 这里就是上面的查询逻辑，把结果作为临时表关联更新
    SELECT 
        f_inner.id,
        COUNT(DISTINCT fr.friend_id) AS cnt
    FROM 
        friends f_inner
    JOIN (
        SELECT user1 AS user_id, user2 AS friend_id FROM friends
        UNION ALL
        SELECT user2 AS user_id, user1 AS friend_id FROM friends
    ) fr ON fr.user_id = f_inner.user1
    JOIN (
        SELECT user1 AS user_id, user2 AS friend_id FROM friends
        UNION ALL
        SELECT user2 AS user_id, user1 AS friend_id FROM friends
    ) fr2 ON fr2.user_id = f_inner.user2 AND fr.friend_id = fr2.friend_id
    WHERE fr.friend_id != f_inner.user2
    GROUP BY f_inner.id
) t ON f.id = t.id
SET f.common_friends = t.cnt;

逻辑拆解 #

我给你掰扯清楚每一步为啥这么写：

• 双向好友关系子查询：用UNION ALL把原表的user1→user2和user2→user1合并，这样每个用户的所有好友都能被完整列出来（比如用户1的好友是2、3、4、5，用户2的好友是1、3、4）。
• 关联匹配共同好友：第一个JOIN拉取当前行user1的所有好友，第二个JOIN把这些好友和user2的好友做匹配，匹配上的就是两人的共同好友。
• 去重统计：用COUNT(DISTINCT)是怕出现重复的好友记录（比如如果原表不小心存了双向数据），确保计数准确；WHERE条件是避免把user2自己算成user1的好友（虽然原表没这情况，但严谨点总没错）。

验证结果 #

不管用哪种方式，最终都会得到你想要的结果：

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