无限卷轴的mybatis偏移位置设置问题

iszxjhcz  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(445)

我正在构建一个类似quora的应用程序。后端使用springboot,mybatis连接到mysql数据库。当用户打开网站时,后端会返回前10个问题。如果用户单击“获取更多”按钮,后端应该返回接下来的10个问题。
mybatis密码是

<mapper namespace="com.quora.dao.QuestionDAO">
    <sql id="table">question</sql>
    <sql id="selectFields">id, title, content, comment_count,created_date,user_id
    </sql>
    <select id="selectLatestQuestions" resultType="com.quora.model.Question">
        SELECT
        <include refid="selectFields"/>
        FROM
        <include refid="table"/>

        <if test="userId != 0">
            WHERE user_id = #{userId}
        </if>
        ORDER BY id DESC
        LIMIT #{offset},#{limit}
    </select>
</mapper>

目前,我的逻辑是第一次{offset}是0,第二次{offset}是10。但我发现,当表频繁更新时,这种逻辑是不正确的。如果表插入了新行,用户可能会得到重复的数据。如何根据前端显示的最后一个问题id设置#{offset}?例如,前端的最后一个问题id是10,那么#{offset}应该是问题id 10的行号。
谁能给我一些建议吗?
谢谢,彼得

eqzww0vc

eqzww0vc1#

总的想法是不要使用 OFFSET 而不是做过滤。如果您可以定义消息的顺序,使其在插入新消息时不会更改(例如,您以增量方式生成ID并按消息排序) id ASC )那么很简单:

SELECT id, some_other_field, yet_another_field
FROM question
<if test="last_seen_question_id != null">
    WHERE id > #{last_seen_question_id}
</if>
ORDER BY id ASC
LIMIT #{limit}

然后客户机应该使用最后一次看到的问题id,并在需要获取下一页时传递它。
从你的询问中( ORDER BY id DESC )看起来你想在最上面看到最新的问题。这是一个问题,因为新插入的问题往往会到达顶部。
如果您可以先在下一页上提出新问题,然后再提出旧问题,您可以这样做:

<!-- This condition is needed to avoid duplication when the first page is fetched
     and we haven't seen any question yet.
     In this case we just get up to limit of the last questions.
-->
<if test="newest_seen_question_id != null">
SELECT * FROM (
  -- select all questions that were created _after_
  -- the last seen max question id
  -- client should maintain this value as in get the 
  -- largest question id for every page and compare it
  -- with the current known max id. And client should
  -- do it for the whole duration of the paging
  -- and pass it to the follow up queries
  SELECT id, some_other_field, yet_another_field
  FROM question
  WHERE id > #{newest_seen_question_id}
  -- note that here we do sorting in the reverse order
  -- basically expanding the set of records that was returned
  -- in the direction of the future
  ORDER BY id ASC
  LIMIT #{limit}
  UNION ALL
</if>
  -- select all questions that were created _before_
  -- the last seen min question id.
  -- client should maintain this value for the whole
  -- duration of the paging
  -- and pass it to the follow up queries      
  SELECT id, some_other_field, yet_another_field
  FROM question
  <if test="oldest_seen_question_id != null">
    WHERE id < #{oldest_seen_question_id}
  </if>
  ORDER BY id DESC
  LIMIT #{limit}
<if test="newest_seen_question_id != null">
) AS q
ORDER BY id DESC
LIMIT #{limit}
</if>

另一个好处是这种分页方法不使用 OFFSET 从性能的Angular 来看要更好。

相关问题