order by后面跟一个逻辑表达式或者一个NOT DETERMINISTIC的rand函数应该没有工程意义,但是作为SQL一部分,在sql关系模型中是有意义的。既然题主问到为什么,那我们就来解释下sql关系模型中的意义。sql关系模型中,order by clause的BNF:ORDER BY expr,order by子句后面可以是任意表达式
数据库处理order by子句,就是先得到一张输出表,通常下面是table scan操作输出的,到order by子句,先对后面的expr表达式进行计算,投影得到一个tmp table,然后对这个tmp table按对应的列进行排序,也就是说,假如我有一张表:
a, b, c
2, 1, 1
1, 2, 2
3, 3, 3
如果我的order by子句是order by a+1
先进行表达式计算投影得到一个中间表:
a, b, c, a+1
2, 1, 1, 3
1, 2, 2, 2
3, 3, 3, 4
然后以a+1这一列作为排序列进行排序
这里先说最后一条语句,为什么order by 'xxx'得到的是一个顺序排序
按上面的模型,'xxx'是一个常量表达式,经过计算投影,就相当于在原来的输出追加了一个常量的列,常量的列排序当然顺序不会发生变化,所以以前的顺序是什么样,现在就是什么样子,order by的排序可能不是稳定排序,但是一般在优化器阶段,order by const_expr就会被优化掉,去掉order by属性,对于你这条select * from test.new_table order by 'xxx'会被优化成select * from test.new_table;
比较难解释的是上面的两条语句,先说一下rand这个函数,返回的是(0, 1)的随机数,你指定了随机数种子,说明,每次开始执行rand(1)得到的一串随机数序列总是固定的,但是在一张表中如果有多行数据的话,rand会被执行多次,多次执行的随机数序列是不一样的,例如:
mysql> select rand(1) from t;
+---------------------+
| rand(1) |
+---------------------+
| 0.40540353712197724 |
| 0.8716141803857071 |
| 0.1418603212962489 |
+---------------------+
3 rows in set (0.01 sec)
我这里有3行数据,每次得到的rand值不一样,但是无论我执行多少次这条SQL,这个序列是不会变化的,所以你的第一条select * from test.new_table order by rand(1);根据我们上面说的模型,是按你的随机数序列进行排序了,得到了一个无法人为预测但是稳定的结果序
第二条语句order by 1 and rand(1)的结果看上去有些奇怪,我猜测应该是你的序列中产生了0,然后1 and rand(1)的结果就有0, 1两种结果值,发生了实质的排序,排序有可能不是稳定排序,这个要取决于mysql的排序算法,不清楚是什么排序算法,但大多数是快排(不稳定排序),所以得到了这样的结果,你可以通过select *, 1 and rand(1) from test.new_table order by 1 and rand(1);来看你的排序键是否符合预期。