关注/粉丝 表结构设计及查询(附带SQL)

表主要字段：

1. focus_user_id 设置索引 用于查询 我的粉丝

在查询时：

select focus_user_id from user_focus where user_id = A.userId order by create_time desc

2. 查询 A 的粉丝列表：

select user_id from user_focus where focus_user_id = A.userId order by create_time desc

( SELECT 1 from user_focus where user_id = A.userId and focus_user_id = B.userId )
union all
( SELECT 2 from user_focus where user_id = B.userId and focus_user_id = A.userId )

这里会返回一个List<Integer> 数组，有四种情况

5. 查询 A，B 共同粉丝

致数据库大量数据排序

---------------------------------------------------------------分割线-------------------------------------------------------------------------------------

<select id="getXXX" resultType="java.lang.String">
         select distinct B.focus_user_id from user_fans A
         INNER JOIN user_fans B on A.focus_user_id = B.user_id
         LEFT JOIN user_fans C on B.focus_user_id = C.focus_user_id AND C.user_id = #{userId,jdbcType=VARCHAR}
         where A.user_id = #{userId,jdbcType=VARCHAR} AND B.focus_user_id != #{userId,jdbcType=VARCHAR}
         AND C.focus_user_id is NULL limit 10
    </select>

<select id="getXXX" resultType="java.lang.String">
        SELECT
          A.focus_user_id
        from user_fans A
        INNER JOIN user_fans B on A.focus_user_id = B.user_id
        where A.user_id = #{myUserId,jdbcType=VARCHAR}
        AND B.focus_user_id = #{userId,jdbcType=VARCHAR} limit 3
    </select>