使用MySQL LIMIT分页时部分记录丢失的问题求助

问题现象 #

• 执行LIMIT 0,10时，id=34的记录位于第5位，id=48的记录位于第6位
• 改为LIMIT 0,5时，id=34的记录丢失，id=48的记录变为第5位
• 改为LIMIT 5,5时，id=48的记录重复出现，id=34的记录仍丢失
• 已确认id=34的记录符合WHERE p.userId=1的查询条件

问题原因 #

核心问题是排序字段total_views存在重复值：当多个记录的total_views数值相同时，MySQL没有稳定的次级排序规则，会以不确定的顺序返回这些同值记录。不同分页场景下，这些记录的相对位置可能被打乱，进而导致数据丢失或重复出现。

解决方案 #

修改ORDER BY子句，添加一个唯一字段（如p.id）作为次级排序条件，确保排序结果的稳定性。即使total_views相同，也能通过唯一字段固定记录顺序，避免分页混乱。

修改后的SQL：

SELECT 
        p.*,
        s.name AS series_name,
        COUNT(DISTINCT pv.id) as total_views,
        COUNT(DISTINCT pl.id) as total_likes,
        COUNT(DISTINCT pc.id) as total_comments
      FROM 
        table_one p
      LEFT JOIN
        table_two s
      ON
        s.id = p.seriesId
      LEFT JOIN
        table_three pv
      ON
        p.id = pv.postId
      LEFT JOIN
        table_four pl
      ON
        p.id = pl.postId
      LEFT JOIN
        table_five pc
      ON
        p.id = pc.postId
      WHERE 
        p.userId = 1 
      GROUP BY
        p.id
      ORDER BY
        total_views DESC,
        p.id DESC -- 可根据需求改为ASC，关键是保证排序唯一稳定
      LIMIT
        0, 10;

补充说明 #

• 除了p.id，也可以选择其他唯一字段（如记录创建时间create_time），只要能让同total_views的记录拥有固定排序顺序即可。
• 稳定的排序规则是分页查询正确性的基础，只要排序字段存在重复，就必须补充次级唯一排序条件。

内容的提问来源于stack exchange，提问作者Spurgeon Gnan Prakasham Tara