mysql的order by如何处理null值

官方结论：

When doing an `ORDER BY`, `NULL` values are presented first if you do `ORDER BY ... ASC` and last if you do `ORDER BY ... DESC`.

可见mysql把null值当做最小值处理，下面看看示例：

原始数据：

mysql> select id,name,age from user;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 |          |    0 |
|  2 | zhangsan |   18 |
|  3 | NULL     |   20 |
|  4 | lisi     |   20 |
|  5 | NULL     |   20 |
|  6 |          |   20 |
+----+----------+------+
6 rows in set (0.00 sec)

1,6的name是空字符串，3,5的name是NULL

按照name递增排序：

mysql> select id,name,age from user order by name asc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | NULL     |   20 |
|  5 | NULL     |   20 |
|  1 |          |    0 |
|  6 |          |   20 |
|  4 | lisi     |   20 |
|  2 | zhangsan |   18 |
+----+----------+------+
6 rows in set (0.01 sec)

发现3,5排到了最前面

按照name递减排序：

mysql> select id,name,age from user order by name desc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | zhangsan |   18 |
|  4 | lisi     |   20 |
|  1 |          |    0 |
|  6 |          |   20 |
|  3 | NULL     |   20 |
|  5 | NULL     |   20 |
+----+----------+------+
6 rows in set (0.00 sec)

发现3,5排到了最后面。

另外，发现一个有趣的现象，相同的值，无论是递增还是递减，排序的相对位置不会发生变化，即(1,6),(3,5)这两对数据是相同的，无论递增还是递减，1总是在6前面，3总是在5前面。说明mysql的order by是稳定排序。

postgresql的order by如何处理null值

官方结论：

if `NULLS LAST` is specified, null values sort after all non-null values; if `NULLS FIRST` is specified, null values sort before all non-null values. If neither is specified, the default behavior is `NULLS LAST` when `ASC` is specified or implied, and `NULLS FIRST` when `DESC` is specified (thus, the default is to act as though nulls are larger than non-nulls). When `USING` is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.

意思就是null值会被当做最大值处理，当order by asc的时候null值在最后面；当order by desc的时候null值在最前面。还可以可以通过nulls last或者nulls first指定null值在前面或者后面。

下面来看看具体的示例：

没有排序的情况：

# select * from "user";
 id |   name   | email | age 
----+----------+-------+-----
  2 | zhangsan |       |  18
  4 | lisi     |       |  20
  3 |          |       |  20
  5 |          |       |  20
  1 |          |       |   0
  6 |          |       |  20
(6 rows)

按name递增排序：

# select * from "user" order by name asc;
 id |   name   | email | age 
----+----------+-------+-----
  1 |          |       |   0
  6 |          |       |  20
  4 | lisi     |       |  20
  2 | zhangsan |       |  18
  3 |          |       |  20
  5 |          |       |  20

1,6的name是空字符串，3,5的name是null

按name递减排序：

# select * from "user" order by name desc;
 id |   name   | email | age 
----+----------+-------+-----
  3 |          |       |  20
  5 |          |       |  20
  2 | zhangsan |       |  18
  4 | lisi     |       |  20
  1 |          |       |   0
  6 |          |       |  20
(6 rows)

可见，postgresql确实把null值当做最大值处理。

当然，也可以在sql后面加上nulls last/first来指定null值位置。例如：

# select * from "user" order by name desc nulls last;
 id |   name   | email | age 
----+----------+-------+-----
  2 | zhangsan |       |  18
  4 | lisi     |       |  20
  1 |          |       |   0
  6 |          |       |  20
  3 |          |       |  20
  5 |          |       |  20
(6 rows)