关注列表的数据库设计 ｜ 青训营笔记

这是我参与「第五届青训营 」伴学笔记创作活动的第 8 天。

在大作业需要实现的功能中有一个关于用户关注和粉丝数量的功能，在此对该功能进行分析。

1. 逻辑分析

用户关注粉丝是一个多对多的数据模型，我们可以单独设计一个表来对用户的关注状态进行记录，这个表需要考虑的功能有：

• 保存被关注者和粉丝对应的用户id
• 若用户进行取消关注的动作，需要对关注状态进行更改
• 在搜索时考虑查询时最常见的约束条件：被关注者和粉丝同时作为条件

2. 数据库设计

CREATE TABLE `user_follows` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint DEFAULT '0' COMMENT 'user id',
    `followed_id` bigint DEFAULT '0' COMMENT 'followed user ID',
    `status` tinyint(1) DEFAULT '0' COMMENT 'follow status: 0 for cancel, 1 for follow',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'User information create time',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'User information update time',
    `deleted_at` timestamp NULL DEFAULT NULL COMMENT 'User information delete time',
    PRIMARY KEY (`id`),
    UNIQUE KEY `vip_followed_indx` (`user_id`, `followed_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'User follow table';

用户每关注一个人，都会在表中添加一条数据，但是取消关注时，并不会擦除先前关注的记录，而是修改status的值。

3. 使用

我们先不考虑status字段，该字段可以在应用层进行处理。

• 查看自己的粉丝或者关注列表：
  以followed_vip_id字段查询，可以获取粉丝列表，以vip_id字段查询，可以获取关注者列表。

  如查找用户1的粉丝：

  select user_id from user_follows where user_id = 1
  

• 查看别人的粉丝或者关注列表：
  查看别人的粉丝或者关注时就会复杂一些了，需要用到子查询或者连表

  • 用内连接查找所有互粉（可以根据自身需求在后面把查询范围补上，如用户1用户2的关注/粉丝列表）：

  select a.* from user_follows as a inner join user_follows as b 
  on a.followed_id = b.user_id and a.user_id = b.followed_id
  

  • 用子查询查找用户1和用户2的共同关注

  select followed_id from user_follows 
  where followed_id in (select followed_id from user_follows where user_id = 1) 
  and user_id = 2
  

  • 用子查询查找用户2和用户3的共同粉丝

  select user_id from user_follows 
  where user_id in (select user_id from user_follows where followed_id = 3) 
  and followed_id = 2