说说MySQL ORDER BY

导读

在MySQL里，ORDER BY可以有几种玩法？

先看下手册里的说明：

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
....
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]

也就是，有三种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 |
+----+----+----+-----+

例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 |
+----+----+----+-----+

例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 |
+----+----+----+-----+

例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 |
+----+----+----+-----+

小结

1. 建议还是用常规的排序语法，别写太奇葩的子句，没准哪天就踩坑了；

2. MySQL 8.0之前，还不支持倒序索引，但可以支持基于索引的倒序排序（利用索引的有序性，倒序排序，性能也并不差）。当然了，如果有个多列索引，几个列排序顺序不一样的话，那么在8.0以前是不支持的；

延伸阅读

• MySQL 8.0.3 RC 版即将发布,看看有哪些变化

• GROUP BY另类优化

• 老叶观点:MySQL开发规范之我见

• 10分钟让你明白MySQL是如何利用索引的

知识无界限，不再加原创

喜欢就转走，铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com