Fork me on GitHub

MySql5.7.15 Union中order by 排序不起作用

发表于 | 分类于 | 本文阅读 次
字数统计: 815 字 | 阅读时长 ≈ 4 分钟

mysql 5.7.15 union order by 子查询排序不生效

解决办法：添加limit，解析器不会优化order by字段

1
2
3
4
5
   (SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 6 ORDER BY vc_operdate DESC LIMIT 999999 ) 
UNION 
(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 1 ORDER BY vc_operdate DESC LIMIT 999999 ) 
UNION
(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 2 ORDER BY vc_operdate DESC LIMIT 999999 )

详细介绍：

理论上的查询语句正确写法

1
2
3
4
5
(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 6 ORDER BY vc_operdate DESC ) 
	UNION 
(select t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 1 ORDER BY vc_operdate DESC ) 
	UNION
(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 2 ORDER BY vc_operdate DESC )

主要完成的是TBatch表中按照c_state 显示6然后1最后2的先后分贝按照操作时间排序，返回分页结果集。
查询结果按照c_state 先后union在了一起，但是每个状态中并没有按照时间排序。排序失效

查看执行过程，SQL被解释器优化后的查询语句

查看执行计划：EXPLAIN …

1
2
3
4
5
EXPLAIN	(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 6 ORDER BY vc_operdate DESC ) 
	UNION 
	(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 1 ORDER BY vc_operdate DESC ) 
	UNION
	(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 2 ORDER BY vc_operdate DESC )

查看解释器优化后的SQL：SHOW WARNINGS

1
SHOW WARNINGS

复制解释器中message字段就是实际执行的SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
(
/* select#1 */
SELECT 
  `OperationDB`.`t`.`vc_batchno` AS `vc_batchno`,
  `OperationDB`.`t`.`vc_batchname` AS `vc_batchname`,
  `OperationDB`.`t`.`vc_guideno` AS `vc_guideno`,
  `OperationDB`.`t`.`vc_userid` AS `vc_userid`,
  `OperationDB`.`t`.`vc_operdate` AS `vc_operdate`,
  `OperationDB`.`t`.`c_state` AS `c_state`,
  `OperationDB`.`t`.`vc_message` AS `vc_message`,
  `OperationDB`.`t`.`vc_userid` AS `operateid` 
FROM
  `OperationDB`.`TBatch` `t` 
WHERE (`OperationDB`.`t`.`c_state` = 6)) 
UNION
(
/* select#2 */
SELECT 
  `OperationDB`.`t`.`vc_batchno` AS `vc_batchno`,
  `OperationDB`.`t`.`vc_batchname` AS `vc_batchname`,
  `OperationDB`.`t`.`vc_guideno` AS `vc_guideno`,
  `OperationDB`.`t`.`vc_userid` AS `vc_userid`,
  `OperationDB`.`t`.`vc_operdate` AS `vc_operdate`,
  `OperationDB`.`t`.`c_state` AS `c_state`,
  `OperationDB`.`t`.`vc_message` AS `vc_message`,
  `OperationDB`.`t`.`vc_userid` AS `operateid` 
FROM
  `OperationDB`.`TBatch` `t` 
WHERE (`OperationDB`.`t`.`c_state` = 1)) 
UNION
(
/* select#3 */
SELECT 
  `OperationDB`.`t`.`vc_batchno` AS `vc_batchno`,
  `OperationDB`.`t`.`vc_batchname` AS `vc_batchname`,
  `OperationDB`.`t`.`vc_guideno` AS `vc_guideno`,
  `OperationDB`.`t`.`vc_userid` AS `vc_userid`,
  `OperationDB`.`t`.`vc_operdate` AS `vc_operdate`,
  `OperationDB`.`t`.`c_state` AS `c_state`,
  `OperationDB`.`t`.`vc_message` AS `vc_message`,
  `OperationDB`.`t`.`vc_userid` AS `operateid` 
FROM
  `OperationDB`.`TBatch` `t` 
WHERE (`OperationDB`.`t`.`c_state` = 2))

通过查看sql的执行计划和解析器优化后的执行SQL发现，子查询中根本不带 order by 查询，很苦恼。

经过查询解决办法，发现 如果order by 不带limit，会被优化器干掉order by 字段。

解决方案：

1
2
3
4
5
6
EXPLAIN	
(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 6 ORDER BY vc_operdate DESC LIMIT 999999 ) 
	UNION 
	(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 1 ORDER BY vc_operdate DESC LIMIT 999999 ) 
	UNION
	(SELECT t.*,t.vc_userid AS operateid FROM TBatch t WHERE c_state = 2 ORDER BY vc_operdate DESC LIMIT 999999 )
1
SHOW WARNINGS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
(
/* select#1 */
SELECT 
  `OperationDB`.`t`.`vc_batchno` AS `vc_batchno`,
  `OperationDB`.`t`.`vc_batchname` AS `vc_batchname`,
  `OperationDB`.`t`.`vc_guideno` AS `vc_guideno`,
  `OperationDB`.`t`.`vc_userid` AS `vc_userid`,
  `OperationDB`.`t`.`vc_operdate` AS `vc_operdate`,
  `OperationDB`.`t`.`c_state` AS `c_state`,
  `OperationDB`.`t`.`vc_message` AS `vc_message`,
  `OperationDB`.`t`.`vc_userid` AS `operateid` 
FROM
  `OperationDB`.`TBatch` `t` 
WHERE (`OperationDB`.`t`.`c_state` = 6) 
ORDER BY `OperationDB`.`t`.`vc_operdate` DESC 
LIMIT 999999) 
UNION
(
/* select#2 */
SELECT 
  `OperationDB`.`t`.`vc_batchno` AS `vc_batchno`,
  `OperationDB`.`t`.`vc_batchname` AS `vc_batchname`,
  `OperationDB`.`t`.`vc_guideno` AS `vc_guideno`,
  `OperationDB`.`t`.`vc_userid` AS `vc_userid`,
  `OperationDB`.`t`.`vc_operdate` AS `vc_operdate`,
  `OperationDB`.`t`.`c_state` AS `c_state`,
  `OperationDB`.`t`.`vc_message` AS `vc_message`,
  `OperationDB`.`t`.`vc_userid` AS `operateid` 
FROM
  `OperationDB`.`TBatch` `t` 
WHERE (`OperationDB`.`t`.`c_state` = 1) 
ORDER BY `OperationDB`.`t`.`vc_operdate` DESC 
LIMIT 999999) 
UNION
(
/* select#3 */
SELECT 
  `OperationDB`.`t`.`vc_batchno` AS `vc_batchno`,
  `OperationDB`.`t`.`vc_batchname` AS `vc_batchname`,
  `OperationDB`.`t`.`vc_guideno` AS `vc_guideno`,
  `OperationDB`.`t`.`vc_userid` AS `vc_userid`,
  `OperationDB`.`t`.`vc_operdate` AS `vc_operdate`,
  `OperationDB`.`t`.`c_state` AS `c_state`,
  `OperationDB`.`t`.`vc_message` AS `vc_message`,
  `OperationDB`.`t`.`vc_userid` AS `operateid` 
FROM
  `OperationDB`.`TBatch` `t` 
WHERE (`OperationDB`.`t`.`c_state` = 2) 
ORDER BY `OperationDB`.`t`.`vc_operdate` DESC 
LIMIT 999999)

参考资料：https://blog.csdn.net/xihuanqiqi/article/details/73550783

------本文结束 感谢阅读------
鲁顺德 wechat
欢迎您扫一扫上面的微信公众号，订阅我的分享资源！
坚持原创技术分享，您的支持将鼓励我继续创作！
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多