MySQL对包含NULL字段排序

2018-03-07

mysql
order

CREATE TABLE `sorted` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`idx` INT(11) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)

INSERT INTO `sorted` (`id`, `idx`) VALUES (1, 2);
INSERT INTO `sorted` (`id`, `idx`) VALUES (2, NULL);
INSERT INTO `sorted` (`id`, `idx`) VALUES (3, 3);
INSERT INTO `sorted` (`id`, `idx`) VALUES (4, 1);
INSERT INTO `sorted` (`id`, `idx`) VALUES (5, 1);
INSERT INTO `sorted` (`id`, `idx`) VALUES (6, 4);
INSERT INTO `sorted` (`id`, `idx`) VALUES (7, 3);
INSERT INTO `sorted` (`id`, `idx`) VALUES (8, NULL);
INSERT INTO `sorted` (`id`, `idx`) VALUES (9, 0);

NULL被视为低于任何非NULL值。

mysql> select * from sorted;
+----+------+
| id | idx  |
+----+------+
|  1 |    2 |
|  2 | NULL |
|  3 |    3 |
|  4 |    1 |
|  5 |    1 |
|  6 |    4 |
|  7 |    3 |
|  8 | NULL |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

方案一：

mysql> select id, idx from sorted order by -idx desc;
+----+------+
| id | idx  |
+----+------+
|  9 |    0 |
|  4 |    1 |
|  5 |    1 |
|  1 |    2 |
|  3 |    3 |
|  7 |    3 |
|  6 |    4 |
|  2 | NULL |
|  8 | NULL |
+----+------+
9 rows in set (0.00 sec)

方案二：

mysql> select id,idx from sorted order by isnull(idx), idx;
+----+------+
| id | idx  |
+----+------+
|  9 |    0 |
|  4 |    1 |
|  5 |    1 |
|  1 |    2 |
|  3 |    3 |
|  7 |    3 |
|  6 |    4 |
|  2 | NULL |
|  8 | NULL |
+----+------+
9 rows in set (0.00 sec)

方案三：

mysql> select id,idx from sorted order by if(isnull(idx), 0, 1) desc, idx;
+----+------+
| id | idx  |
+----+------+
|  9 |    0 |
|  4 |    1 |
|  5 |    1 |
|  1 |    2 |
|  3 |    3 |
|  7 |    3 |
|  6 |    4 |
|  2 | NULL |
|  8 | NULL |
+----+------+
9 rows in set (0.00 sec)