【MySql】MySQL排序分页查询数据顺序错乱的原因和解决办法

本文目录

一、问题现象

二、问题复现

2.1 表结构

2.2 数据来源

2.3 问题复现

三、问题原因

四、解决方案

4.1 官网推荐的 order by 索引列

4.2 order by 后多添加一个id字段排序

---

一、问题现象

mysql对无索引字段进行排序后limit ，当被排序字段有相同值时并且在limit范围内，取的值并不是正常排序后的值，有可能第一页查询的记录，重复出现在第二页的查询记录中，而且第二页的查询结果乱序，导致分页结果查询错乱问题。

二、问题复现

2.1 表结构

以下是这次问题出现的创建的表结构SQL语句，可以直接执行

DROP TABLE IF EXISTS `unlp_hot_dictionary`;
CREATE TABLE `unlp_hot_dictionary`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '词',
  `nature` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '词性',
  `weight` int(10) NOT NULL DEFAULT 1 COMMENT '权重',
  `order_num` int(10) NOT NULL DEFAULT 0 COMMENT '排序码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 72 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = COMPACT;

2.2 数据来源

表里的数据，随便填写即可。

2.3 问题复现

执行排序后分页查询的SQL语句如下：

SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num DESC LIMIT 0,10;

SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num DESC LIMIT 10,10;

查询出的结果参考下图：

三、问题原因

mysql官网对limit的详细说明及优化建议：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

以下内容摘录自mysql官网：

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

......

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

四、解决方案

以下有两种方式都可以完美解决这个问题：一个是Mysql官网推荐的，另外一个比官网推荐的更加简单。

4.1 官网推荐的 order by 索引列

官网推荐的解决方案是 order by 的列中包含一个索引列（如果没有，则需要把这个列改为索引列）

创建索引方法官网上有写，或者使用数据库可视化工具（如Navicat、SqlYog等）创建

4.2 order by 后多添加一个id字段排序

SQL语句为：

SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num, id DESC LIMIT 0,10;

SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num, id DESC LIMIT 10,10;

结果如下，完美解决

完结！