【MySql】MySQL排序分页查询数据顺序错乱的原因和解决办
法
本⽂⽬录
⼀、问题现象
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 数据来源
遍历list表⾥的数据，随便填写即可。
2.3 问题复现
执⾏排序后分页查询的SQL语句如下：
SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num, id DESC LIMIT 0,10;
mysql语句顺序SELECT id,word,nature,weight,order_num FROM unlp_hot_dictionary ORDER BY order_num, id DESC LIMIT 10,10;
ffoutput是什么文件查询出的结果参考下图：
三、问题原因
以下内容摘录⾃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:
四、解决⽅案
c语言random函数头文件
以下有两种⽅式都可以完美解决这个问题：⼀个是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;scrollview滚动到指定位置
结果如下，完美解决
完结！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。