请教 order by 后面跟表达式或子查询的困惑

mysql root@localhost:security> select id from users order by 1+1;
+------+
|   id |
|------|
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set
Time: 0.003s
mysql root@localhost:security> select id from users order by 2;
(1054, "Unknown column '2' in 'order clause'")

order by (子查询)

请教二：order by (select 0)不等价于 order by 0 的原因？

mysql root@localhost:security> select * from users order by 0;
(1054, "Unknown column '0' in 'order clause'")
mysql root@localhost:security> select 0;
+-----+
|   0 |
|-----|
|   0 |
+-----+
1 row in set
Time: 0.002s
mysql root@localhost:security> select * from users order by (select 0);
+------+------------+------------+
|   id | username   | password   |
|------+------------+------------|
|    1 | Dumb       | Dumb       |
|    2 | Angelina   | I-kill-you |
|    3 | Dummy      | p@ssword   |
|    4 | secure     | crappy     |
|    5 | stupid     | stupidity  |
|    6 | superman   | genious    |
|    7 | batman     | mob!le     |
|    8 | admin      | admin      |
+------+------------+------------+
8 rows in set
Time: 0.005s

另外，这种问题的解决思路是怎么样呢，只有调试源码这一条困难之路么？

13 条回复 • 2020-07-13 14:55:31 +08:00

JasonLaw

2020-07-12 20:46:02 +08:00

请教一：order by 1+1 不等价于 order by 2 的原因？
这是因为你的表只有一列，所以会报错，你可以使用`order by 1`试试。

请教二：order by (select 0)不等价于 order by 0 的原因？
同样的道理，`order by ordinal`中的 ordinal 要大于 0，小于等于表的列数

详细请看 https://stackoverflow.com/questions/11353688/what-is-this-order-by-1

注意：官方文档有这么一句话
Use of column positions is deprecated because the syntax has been removed from the SQL standard.

JasonLaw

2020-07-12 20:54:19 +08:00

@JasonLaw #1 不好意思，看错了

JasonLaw

2020-07-12 21:04:39 +08:00

@JasonLaw #1 第一条回复加上 https://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first/14104090 应该能够解答你的疑问。

hanssx

2020-07-12 21:32:37 +08:00

@JasonLaw 其实我主要是想问 1+1 不是等于 2 吗，为什么 2 报错，1+1 不报错。
select 0 返回的也是 0，而 order by 0 会报错，order by (select 0)却不会报错。

不过我现在好像有点明白了，1+1 和 select 0 都是 expr （表达式），这种情况每行数据运行到 order by expr 的时候，仅仅是执行这个 expr，然后 select，然后就是下一行数据了。而 order by 0 和 order by 2 中的 0 和 2 应该是 position，虽然 1+1 和 2 是相等的，但是含义不同，此“2”非彼“2”。

hanssx

2020-07-12 21:34:56 +08:00

具体佐证还需要调试源码或者详细分析 select 的执行流程，我记得 select 和 order by 哪个先执行，网上很多人都是说 select 先执行，我最近总觉得应该是 order by 先执行，具体我也不是十分肯定。

JasonLaw

2020-07-12 21:50:45 +08:00

@hanssx #4 第一条和第三条回复加起来就能够解释为什么两者不一样了，你的理解是对的。

JasonLaw

2020-07-12 22:29:40 +08:00

@hanssx #5 谁先谁后“只能”通过最后的执行计划确定（“只能”加上双引号是因为有些还是可以直接看出来的）。如果 select 先执行，那么`select column1 from t order by column2`怎么能够成功执行呢？说 order by 先执行，`select column1 from t order by column1`，如果 t 有很多列，那么先执行 select，然后再执行 order by，肯定性能会更好。

hanssx

2020-07-12 22:46:32 +08:00

@JasonLaw 谁先谁后“只能”通过最后的执行计划确定，这个怎么看呀。

JasonLaw

2020-07-12 23:56:33 +08:00

@hanssx #8 https://stackoverflow.com/a/47451071/5232255，这个回答或许对你有帮助。我暂时没有找到更好的方法。

realpg

2020-07-13 00:32:45 +08:00

把写出这种查询的人都开除了就无此烦恼了

不是开玩笑

zhangysh1995

2020-07-13 14:16:41 +08:00

这个问题刚见过。

order by 后边直接跟一串数字，这些数字表达的是 select xxx 这里的列的编号；如果后边接表达式，是按照表达式值和行的对应处理的，举个例子如下：

假设表 t1 只有一个列 a，存了三行，值是 0 0 1

````
SELECT a
FROM t1
ORDER BY a DESC
````
=> 1, 0, 0

````
SELECT a
FROM t1
ORDER BY a in (0,0,0) DESC
````
=> 0, 0 ,1

完整例子看这里 -> https://www.db-fiddle.com/f/rx52Zy9qYeTANTAjgAQdcK/0

zhangysh1995

2020-07-13 14:18:50 +08:00

@hanssx 上面这个例子是从某个 MySQL bug 改出来的

hanssx

2020-07-13 14:55:31 +08:00

@zhangysh1995 嗯，应该是扫描每一行然后算表达式的值，结果按照 DESC 或 ASC 排序。比如
0 in (0,0,0)为 true，为 1
1 in (0,0,0)为 false，为 0