导读
在MySQL里，ORDER BY可以有几种玩法？
先看下手册里的说明：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
....
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
AI 代码解读
也就是，有三种ORDER BY模式，下面分别简单演示下。
测试表：
[yejr]@[imysql.com]>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(10) unsigned NOT NULL DEFAULT '0',
`c2` int(10) unsigned NOT NULL DEFAULT '0',
`c3` int(10) unsigned NOT NULL DEFAULT '0',
`c4` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`c1`),
KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
[yejr]@[imysql.com]>select * from t1;
+----+----+----+-----+
| c1 | c2 | c3 | c4 |
+----+----+----+-----+
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 3 | 3 | 3 | 0 |
| 4 | 2 | 2 | 0 |
| 6 | 8 | 5 | 123 |
| 7 | 6 | 6 | 123 |
| 10 | 10 | 4 | 123 |
+----+----+----+-----+
AI 代码解读
例1. 按指定列名ORDER BY
[yejr]@[imysql.com]>select * from t1 order by c2;
+----+----+----+-----+
| c1 | c2 | c3 | c4 |
+----+----+----+-----+
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 4 | 2 | 2 | 0 |
| 3 | 3 | 3 | 0 |
| 7 | 6 | 6 | 123 |
| 6 | 8 | 5 | 123 |
| 10 | 10 | 4 | 123 |
+----+----+----+-----+
AI 代码解读
例2. 按指定序号的列排序
#按第二个列排序（同例1）
[yejr]@[imysql.com]>select * from t1 order by 2;
+----+----+----+-----+
| c1 | c2 | c3 | c4 |
+----+----+----+-----+
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 4 | 2 | 2 | 0 |
| 3 | 3 | 3 | 0 |
| 7 | 6 | 6 | 123 |
| 6 | 8 | 5 | 123 |
| 10 | 10 | 4 | 123 |
+----+----+----+-----+
#按第三个列排序
[yejr]@[imysql.com]>select * from t1 order by 3;
+----+----+----+-----+
| c1 | c2 | c3 | c4 |
+----+----+----+-----+
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 4 | 2 | 2 | 0 |
| 3 | 3 | 3 | 0 |
| 10 | 10 | 4 | 123 |
| 6 | 8 | 5 | 123 |
| 7 | 6 | 6 | 123 |
+----+----+----+-----+
AI 代码解读
例3. 根据表达式排序
#ORDER BY c3=3 DESC，也就是如果某条记录c3=3，则它排在第一位
#其他非c3=3的记录，则按照聚集索引的顺序显示
[yejr]@[imysql.com]>select * from t1 order by c3=3 desc;
+----+----+----+-----+
| c1 | c2 | c3 | c4 |
+----+----+----+-----+
| 3 | 3 | 3 | 0 |
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 4 | 2 | 2 | 0 |
| 6 | 8 | 5 | 123 |
| 7 | 6 | 6 | 123 |
| 10 | 10 | 4 | 123 |
+----+----+----+-----+
#甚至还可以用case when
#这个例子中，当c3=3时，会被重置成10，其余按照实际值倒序排
[yejr]@[imysql.com]>select * from t1 order by
case when c3=3 then 10 else c3 end desc;
+----+----+----+-----+
| c1 | c2 | c3 | c4 |
+----+----+----+-----+
| 3 | 3 | 3 | 0 |
| 7 | 6 | 6 | 123 |
| 6 | 8 | 5 | 123 |
| 10 | 10 | 4 | 123 |
| 4 | 2 | 2 | 0 |
| 1 | 1 | 1 | 0 |
| 0 | 0 | 0 | 0 |
+----+----+----+-----+
AI 代码解读
小结
-
建议还是用常规的排序语法，别写太奇葩的子句，没准哪天就踩坑了；
-
MySQL 8.0之前，还不支持倒序索引，但可以支持基于索引的倒序排序（利用索引的有序性，倒序排序，性能也并不差）。当然了，如果有个多列索引，几个列排序顺序不一样的话，那么在8.0以前是不支持的；
