如何在SQL数据库中设计好友列表并关联现有用户表？

现有Friends表设计不合理，完全不建议上线使用 #

核心问题如下 #

• 可扩展性为0：你当前只有4个用户所以设了4列，后续如果新增用户难道每次都要修改表结构加列？如果有10万用户，单表列数直接爆炸，根本无法维护
• 查询/修改效率极低：查询单个用户的好友需要遍历对应列的所有行，无法添加索引优化，加删好友操作也要写定制化的SQL，复杂度极高
• 存储空间浪费：大量空白单元格占用无效存储，数据冗余严重

合理的设计方案：使用多对多关联中间表 #

好友关系是典型的用户对用户的多对多关联，你只需要新增一张friend_relations关联表即可，不需要随用户新增修改表结构：

表结构示例 #

CREATE TABLE friend_relations (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
  user_id INT NOT NULL COMMENT '关联users表的用户ID',
  friend_id INT NOT NULL COMMENT '关联users表的好友ID',
  relation_status TINYINT DEFAULT 0 COMMENT '好友状态：0=待同意，1=已好友，2=已拉黑',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '关系创建时间',
  -- 联合唯一索引，避免重复添加同一个好友
  UNIQUE KEY `uk_user_friend` (`user_id`, `friend_id`),
  -- 外键约束，保证关联的用户ID一定存在（可选，根据业务需求选择）
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

关联查询示例 #

如果要查询tag=#1111的X-user的所有已同意好友的信息，SQL写法如下：

SELECT u.id, u.tag, u.name, u.email
FROM friend_relations fr
-- 关联用户表拿到好友的详细信息
INNER JOIN users u ON fr.friend_id = u.id
-- 筛选X-user的已生效好友关系
WHERE fr.user_id = (SELECT id FROM users WHERE tag = '#1111') 
  AND fr.relation_status = 1;

双向好友关系处理说明 #

如果你的业务中好友是双向关系，有两种实现方案：

• 方案一：添加好友时插入两条对称记录（比如A加B为好友，插入user_id=A, friend_id=B和user_id=B, friend_id=A两条），优点是查询逻辑非常简单，直接按user_id筛选即可，适合用户量不大的场景
• 方案二：只插入一条记录，查询时同时匹配user_id和friend_id两个字段，优点是节省一半存储空间，适合用户量很大的场景

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