MySQL中order by 排序遇到NULL值的问题

2014-03-08 12:28:42
MySQL数据库,在order by排序的时候,如果存在NULL值,那么NULL是最小的,ASC正序排序的话,NULL值是在最前面的。 如果我们想让NULL排在后面,让非NULL的行排在前面该怎么做呢?

MySQL数据库在设计的时候,如果字段允许NULL值,那么对该字段进行排序的时候需要注意那些值为NULL的行。

我们知道NULL的意思表示什么都不是,或者理解成“未知”也可以,它与任何值比较的结果都是false,

默认情况下,MySQL会认为NULL值比其他类型的数据小,

也就是说,在order by排序的时候,NULL是最小的,ASC正序排序的话,NULL值是在最前面的。
如果我们想让NULL排在后面,让非NULL的行排在前面该怎么做呢?

下面我们通过一个小例子,来说明这个情况。

首先,创建一个测试数据表 test_user,
mysql> create table test_user(
    -> id int unsigned not null auto_increment,
    -> username varchar(10) not null,
    -> age int,
    -> primary key (id))
    -> engine=myisam
    -> default charset=utf8
    -> ;
Query OK, 0 rows affected (0.14 sec)

填充6条数据,其中3条设置了age值,另外3条age值为NULL
mysql> insert into test_user values(1,'hutuseng1',28),(2,'hutuseng2',30);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test_user(username) values('hutuseng3'),('hutuseng4'),('hutuseng5');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test_user values(6,'hutuseng6',23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_user;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  1 | hutuseng1 |   28 |
|  2 | hutuseng2 |   30 |
|  3 | hutuseng3 | NULL |
|  4 | hutuseng4 | NULL |
|  5 | hutuseng5 | NULL |
|  6 | hutuseng6 |   23 |
+----+-----------+------+
6 rows in set (0.00 sec)

我们按照age字段从小到大排序,我们看到NULL值是最小的,排在了最前面
mysql> select * from test_user order by age;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  3 | hutuseng3 | NULL |
|  4 | hutuseng4 | NULL |
|  5 | hutuseng5 | NULL |
|  6 | hutuseng6 |   23 |
|  1 | hutuseng1 |   28 |
|  2 | hutuseng2 |   30 |
+----+-----------+------+
6 rows in set (0.00 sec)

按照age字段从大到小排序,我们看到NULL值确实是最小的,排在了最后面

mysql> select * from test_user order by age desc;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  2 | hutuseng2 |   30 |
|  1 | hutuseng1 |   28 |
|  6 | hutuseng6 |   23 |
|  3 | hutuseng3 | NULL |
|  4 | hutuseng4 | NULL |
|  5 | hutuseng5 | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)

这里有个需要注意的事项,就是NULL值本身是无法排序的,也就是说一个NULL是无法和另外一个NULL比较的。
你可能已经发现了,那三个NULL值的行,不管正序还是倒序,顺序都是一致的,当然也可能不一致。

那如果在对age进行正序排序的时候,我们希望NULL值的记录在最后面显示,那该如何做呢?

1、重新生成一列,比如agenull,利用is null操作符,把NULL值的行变成1,非NULL值的行变成0,先对该字段排序,再对age排序
mysql> select *,age is null as agenull from test_user order by agenull,age;
+----+-----------+------+---------+
| id | username  | age  | agenull |
+----+-----------+------+---------+
|  6 | hutuseng6 |   23 |       0 |
|  1 | hutuseng1 |   28 |       0 |
|  2 | hutuseng2 |   30 |       0 |
|  3 | hutuseng3 | NULL |       1 |
|  4 | hutuseng4 | NULL |       1 |
|  5 | hutuseng5 | NULL |       1 |
+----+-----------+------+---------+
6 rows in set (0.01 sec)

2、直接利用isnull函数对age列求值,跟第一种方法的道理是一样的
mysql> select * from test_user order by isnull(age),age;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  6 | hutuseng6 |   23 |
|  1 | hutuseng1 |   28 |
|  2 | hutuseng2 |   30 |
|  3 | hutuseng3 | NULL |
|  4 | hutuseng4 | NULL |
|  5 | hutuseng5 | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)

3、还可以利用MySQL中的一个小技巧,在字段前面加上一个负号,也就是减号,ASC改成DESC ,DESC改成ASC

mysql> select * from test_user order by -age desc;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  6 | hutuseng6 |   23 |
|  1 | hutuseng1 |   28 |
|  2 | hutuseng2 |   30 |
|  3 | hutuseng3 | NULL |
|  4 | hutuseng4 | NULL |
|  5 | hutuseng5 | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)

所以,在设计数据库的时候,如果某个字段要进行排序的话,最好不要为NULL。