mysql实现微博关注粉丝

首先从原型上说存在四种关系状态

+关注（0 表示无关系 1表示粉丝）
已关注（2表示已关注）
互相关注（3表示相互关注）
表设计

1.select * from s_follower where u1=?
查询用户id = ？的粉丝列表
2.select * from s_follower where u2=?
查询用户id = ？的关注列表

然后按原型实现关注列表/粉丝列表/关注（取消关注）
1.关注列表

 /**
     * 关注列表
     * @param params
     * @return
     */
    @RequestMapping("getFollowList")
    @ResponseBody
    public ResponseVO getFollowList(@RequestBody FollowerParams params){
        try{
            Integer userId=params.getUserId();
            //通过userId查询关注列表
            PageHelper.startPage(params.getPageNum(),params.getPageSize());
            List<FollowerVO> follows =  followerService.getFollowList(userId);

            List<FollowerVO> followers =  followerService.getFollowerList(userId);


            List<Integer> FollowUS= new ArrayList<>();
            List<Integer> FollowerUS=new ArrayList<>();

            List<Integer> TempUs=new ArrayList<>();

            for (int i = 0; i < follows.size(); i++) {
                Integer u1 = follows.get(i).getU1();
                //关注列表为userId=u2
                FollowUS.add(u1);
            }
            for (int i = 0; i < followers.size(); i++) {
                Integer u2 = followers.get(i).getU2();
                //关注列表为userId=u2
                FollowerUS.add(u2);
            }
            //交集TempUs 为相互关注
            TempUs.addAll(FollowUS);
            TempUs.retainAll(FollowerUS);

            /**
             * 封装view对象返回
             */
            for (int i = 0; i <follows.size() ; i++) {

                //如果U为交集，肯定相互关注，否则已关注
                if(TempUs.contains(follows.get(i).getU1())){
                    follows.get(i).setStatus(3);
                }else{
                    follows.get(i).setStatus(2);
                }
            }
            PageInfo<Follower> pageInfo=new PageInfo(follows);
            return  ResponseVO.createBySuccess(pageInfo);
        } catch (Exception e) {
            logger.info("我的关注列表出现异常{}",e.getMessage());
            return ResponseVO.createByError(null);
        }
    }

/**
     * 粉丝列表
     * @param params
     * @return
     */
    @RequestMapping("getFollowerList")
    @ResponseBody
    public ResponseVO getFollowerList(@RequestBody FollowerParams params){
        try{
            Integer userId=params.getUserId();


            //关注列表
            List<FollowerVO> follows =  followerService.getFollowList(userId);
            //通过userId查询关注列表
            PageHelper.startPage(params.getPageNum(),params.getPageSize());
            //粉丝列表
            List<FollowerVO> followers =  followerService.getFollowerList(userId);


            List<Integer> FollowUS= new ArrayList<>();
            List<Integer> FollowerUS=new ArrayList<>();

            List<Integer> TempUs=new ArrayList<>();

            for (int i = 0; i < follows.size(); i++) {
                Integer u1 = follows.get(i).getU1();
                //关注列表为userId=u2
                FollowUS.add(u1);
            }
            for (int i = 0; i < followers.size(); i++) {
                Integer u2 = followers.get(i).getU2();
                //关注列表为userId=u2
                FollowerUS.add(u2);
            }
            //交集TempUs 为相互关注
            TempUs.addAll(FollowerUS);
            TempUs.retainAll(FollowUS);

            /**
             * 封装view对象返回
             */
            for (int i = 0; i <followers.size() ; i++) {

                //如果U为交集，肯定相互关注，否则已关注
                if(TempUs.contains(followers.get(i).getU2())){
                    followers.get(i).setStatus(3);
                }else{
                    followers.get(i).setStatus(1);
                }
            }
            PageInfo<Follower> pageInfo=new PageInfo(followers);

            /**
             * 查看有无新粉丝，有则更新数据
             */
            List<Follower> newfollowers = followerService.getNewfollowers(userId);
            if(newfollowers.size()>0){
                followerService.editFollowers(newfollowers);
            }
            return  ResponseVO.createBySuccess(pageInfo);
        } catch (Exception e) {
            logger.info("我的关注列表出现异常{}",e.getMessage());
            return ResponseVO.createByError(null);
        }
    }
//添加关注
    @RequestMapping("addFollowing")
    @ResponseBody
    @Version(value = "1.0.0",desc = "userId添加关注",type = "all")
    public ResponseVO AddFollowing(@RequestBody FollowerParams params){
        try{
            Integer userId=params.getUserId();
            Integer u=params.getU();
            Integer status=params.getStatus();




            if(status==0){
                /**
                 * 添加关注
                 */
                Follower follower=new Follower();
                follower.setU1(u);
                follower.setU2(userId);
                follower.setCreateTime(DateUtils.getNowDate());

                Follower exist = followerService.getIsFollower(u, userId);
                if(exist==null){
                    int i=followerService.addFollower(follower);
                    //粉丝数  关注数
                    if(i>0){
                        publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化",userId,u,status));
                    }
                }

                return  ResponseVO.createBySuccess(2);
            }else if(status==1){
                /**
                 * 添加关注
                 */
                Follower follower=new Follower();
                follower.setU1(u);
                follower.setU2(userId);
                follower.setCreateTime(DateUtils.getNowDate());
                Follower exist = followerService.getIsFollower(u, userId);
                if(exist==null) {
                    int i = followerService.addFollower(follower);
                    if (i > 0) {
                        //粉丝数  关注数
                        publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化", userId, u, status));
                    }
                }

                return  ResponseVO.createBySuccess(3);
            }else if(status==2){
                /**
                 * 删除关注
                 */

                int i= followerService.deleteFollower(userId,u);
                if(i>0){
                    //粉丝数  关注数
                    publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化",userId,u,status));
                }

                return  ResponseVO.createBySuccess(0);
            }else{
                /**
                 * 删除关注
                 */
                int i=followerService.deleteFollower(userId,u);
                if(i>0){
                    //粉丝数  关注数
                    publisher.publishEvent(new UserFollowEvent("粉丝数和关注数变化",userId,u,status));
                }

                return  ResponseVO.createBySuccess(1);
            }

        } catch (Exception e) {
            logger.error("userId添加关注{}",e.getMessage());
            return ResponseVO.createByError(null);
        }
    }
@Data
public class FollowerParams {

    private Integer pageNum;

    private Integer pageSize;

    /**
     * 当前用户
     */
    private Integer userId;

    /**
     * u 其他用户
     */
    private Integer u;

    // 0 默认无关系，1 粉丝未关注 2 已关注  3 相互关注
    private Integer status;

}

最后对表的优化时建立索引
alter table s_follower add unique index idx_follow_u1_u2(u1,u2);
这里对添加操作使用了更新

insert into s_follower(u1,u2)
VALUES
(47,15)ON DUPLICATE KEY UPDATE u1=VALUES(u1),u2=VALUES(u2)

最后说一下关注数，粉丝数。使用了监听

@Service
public class UserFollowListener implements ApplicationListener<UserFollowEvent> {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private UserService userService;

    @Override
    public void onApplicationEvent(UserFollowEvent userFollowEvent) {

        logger.info("用户添加关注信息{}", JSON.toJSONString(userFollowEvent));
        User user = new User();

        if (userFollowEvent.getStatus().equals(0) || userFollowEvent.getStatus().equals(1)) {
            user.setId(userFollowEvent.getUserId());
            userService.updateUserFollowerNumById(user, 1);
            user.setId(userFollowEvent.getU());
            userService.updateUserFansNumByUserId(user, 1);

        }
        //
        if (userFollowEvent.getStatus().equals(2) || userFollowEvent.getStatus().equals(3)) {
            //粉丝减一 关注减一
            user.setId(userFollowEvent.getUserId());
            userService.updateUserFollowerNumById(user, -1);
            user.setId(userFollowEvent.getU());
            userService.updateUserFansNumByUserId(user, -1);
        }

    }
}



public class UserFollowEvent extends ApplicationEvent {

    public UserFollowEvent(Object source, Integer userId, Integer u, Integer status) {
        super(source);
        this.userId = userId;
        this.u = u;
        this.status = status;
    }

    /**
     * 当前用户
     */
    private Integer userId;

    /**
     * 被关注用户信息
     */
    private Integer u;

    /**
     * 加好友状态信息
     */
    private Integer status;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Integer getU() {
        return u;
    }

    public void setU(Integer u) {
        this.u = u;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }
}

由于测试过程出现许多脏数据，使用存储过程更新关注粉丝数

循环更新关注数，粉丝数
delimiter //                            #定义标识符为双斜杠 
drop procedure if exists test; #如果存在test存储过程则删除
create procedure test()          #创建无参存储过程,名称为test
begin
    declare i int; 	#申明变量
		set i = 0; #变量赋值 
		lp : loop #lp为循环体名,可随意 loop为关键字 
		UPDATE s_users set fans_num=(SELECT count(*) from s_follower where u1=i),follow_num=(SELECT count(*) from s_follower where u2=i)
			where id=i; #往test表添加数据 
		set i = i + 1; #循环一次,i加一 
			if i > 133 then #结束循环的条件: 当i大于10时跳出loop循环 
				leave lp; 
			end if; 
	end loop; 
	select * from test; #查看test表数据 
end //                                      #结束定义语句 
	call test();