亲宝软件园·资讯

展开

union和子查询中order by一起使用导致排序失效问题及解决

DayDayUp丶 人气:0

一、前言

分页查询的需求如同家常便饭,多数情况下主要利用order bylimit即可实现,有些稍复杂一点的可能需要用到union操作去连接多个子查询结果集。

然而这三个操作是有一些需要留意的问题,下文将列举出3个可能碰到的情况。

MySQL版本:5.7.21

二、问题列举

2.1 子查询中不能使用order by

简单的union操作如下:

SELECT * FROM tb_artist WHERE id >= 10
UNION
SELECT * FROM tb_artist WHERE id < 10

其结果为:

若子查询中包含order by,写法假设如下:

SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC
UNION
SELECT * FROM tb_artist WHERE id < 10

则语法报错:1221 - Incorrect usage of UNION and ORDER BY

应当将子查询括起来,正确写法如下:

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC)
UNION
(SELECT * FROM tb_artist WHERE id < 10)

其结果为:

语法没问题了,但是注意到子查询的order by却没有效果,故引出下面第二种问题。

2.2 子查询order by无效

解决方式是在子查询的order by后添加limit操作即可,具体可以limit一个不小于子查询结果集大小的数值,如下:

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC LIMIT 99999)
UNION
(SELECT * FROM tb_artist WHERE id < 10)

其结果为:

2.3 排序条件不够严格导致分页数据重复

该问题与union无直接联系,属于order bylimit本身的注意点。

即,如果SQL中的order by条件比较宽松不够严格,或者说是结果集中的每行记录存在并列或不唯一的次序的话,MySQL可能会随机给并列记录行进行排序,特别是排序又分页查询配合了limit操作,可能会导致上一页有的记录行又出现在了下一页的结果集之中。

对一些后台系统的分页表格中可能感觉不明显,但对于同样SQL查询逻辑的C端下拉列表的效果一目了然,特别是对带有图片的,很容易看到数据的重复。

解决方案:

所以在做分页查询时,要尽量保证每行记录都有唯一确定的次序,具体做法可以在原有排序条件后添加id或编号等这类唯一值的字段(索引字段也可以)。

例如,在艺术家artist创建时间降序排序的基础上,再对唯一code进行排序:

SELECT * FROM tb_artist WHERE logic_delete = 0 
ORDER BY 
	create_time DESC, 
	code ASC 
LIMIT 0, 5

原因分析:

排序离不开算法,在关系型数据库中,往往会存在多种排序算法。通过MySQL的源码和官方文档介绍可以得知,它的排序规律可以总结如下:

所以解决排序分页数据重复问题有两种方式,第一种就是,在排序中加上唯一值,比如主键 id,这样由于 id 是唯一的,就能确保参与排序的 key 值不相同;第二种就是避免使用堆排序,让order by根据索引来排序。

说白了,就是order by后面的字段要有索引。

另外,使用JPA分页查询时,若order by的是非索引字段,通过查看JPA的sql发现,不会再去自动在order by后添加索引或id字段,需要注意。

MySQL5.7 相关文档:

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。

加载全部内容

相关教程
猜你喜欢
用户评论