mysql null值排序

MySQL中order by 排序遇到NULL值的问题 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。

SELECT handle_person, create_date,handle_person IS NULL AS handlePersonNull FROM tb_event_collect WHERE 1 = 1 ORDER BY handlePersonNull DESC, create_date DESC