說說MySQLORDERBY

導讀

在MySQL裡，ORDER BY可以有幾種玩法？

先看下手冊裡的說明：

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

也就是，有三種ORDER BY模式，下面分別簡單演示下。

測試表：

[yejr]@[imysql.com]>show create table t1G
*************************** 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以前是不支援的；