在ORDER 排序中NULL是最大还是最小|如何处理数据库中NULL的排序问题

skylines 2023-05-17

181

上一期《Oracle、MySQL和PostgreSQL三种数据库如何处理NULL值》，我主要写了Oracle、MySQL和PostgreSQL这三种数据库各种数据类型，如何进行处理并存储空值。这一期，主要是写空值null在这三种数据库进行order排序的时候是如何处理的。一些MySQL用户，用时候在实际业务中，使用order排序进行查询的时候，总会遇到不合预期的查询结果出来，后面通过修改业务表中的数据质量，才能满足业务查询需求。

在之前，我在处理一个政府部门使用的扫黑除恶系统的迁移问题的时候，就碰到同样的业务SQL语句，还有order排序，跑在MySQL系的数据库和跑在Oracle系的数据库，跑出来，结果的表现不一样，导致了业务在前端展示就不一样，这当中主要涉及了空值的排序问题。当时开发用比较强硬的语气跟我说，因为时间紧急，还有两天要迁移上线了，如果改SQL，我们会改动工作量非常大，可能影响整个架构，时间已经来不及了，在不改动SQL的情况下，要保证迁移前后，前端页面展示的效果要一致。我从后台拿到的SQL看了之后，说可以，说真的，我也不想他们去修改SQL，解决问题的方法并不难。然后我通过，前端页面操作，后台查看，结合SQL，定位到了问题所在。

入下图所示，就是在扫黑除恶系统中，处理查询政策解读的业务，涉及到知否置顶与发布时间两个字段，都进行降序排序，MySQL系数据库系统中，未置顶的政策发布信息会在置顶政策的下面，而Oracle系数据库系统中，新增的未发布未置顶的政策居然排在置顶的政策的上面，这样的展示，确实也有问题，这问题一定要解决。但是在数据库中的表发现，是否置顶字段未设置任何约束，该字段是一个int数据类型，当中有1与0，还有空值。原来，新增政策时候，操作人员不确定该新增正确是否要置顶，就不给是否置顶字段操作任何内容，留空，数据库就是写入null空值，当操作置顶，该字段就修改成1，取消置顶，就修改成0。然后我跟开发说，你们要评估一下你们给业务这样设计得逻辑对不对，是否置顶，无非就1或0，怎么还有空值null呢。然后我跟那开发说，我来改，我就只需要修改表结构，给这个字段添加一个默认值“0”，这样确保所有不确定是否置顶的政策，都按照默认不置顶进行处理。后面的两天里，开发都不再找我折腾这件事，已经默认同意了，我的修改方案，当天，我自己也已经在后台修改了表结构。这个扫黑除恶系统政策发布的业务逻辑问题，业务开发商也去重新评估了这个业务逻辑，最后就按照我的修改建议去落实了。

通过这个事情，我们要知道，要善用null空值，不要滥用空值。对于业务跑在MySQL和PostgreSQL数据库的系统，在字符数据类型的字段设计上，也要善用''空字符，不要滥用。很多在MySQL上开发的业务，一些开发人员，比较喜欢在字符类型的字段使用 not null非空约束，然后给该字段很多记录写入了''空字符，这样的设计有意思吗？既然设计了非空约束，为什么还要写入空字符。这样的设计，有没有考虑到后期，跨平台迁移问题的。你写个0、1和阿猫阿狗之类的进来都好啊。

针对null空值在Oracle、MySQL和PostgreSQL三种数据库中排序的处理问题，我进行了以下的一系列测试实验，详细内容，可以往下继续阅览。

对此，我也根据数据库，稍微进行总结了一些内容，如下：

Oracle数据库，数字类型和字符类型中，null空值在order排序中，在不指定情况下，都是无穷大，看作最大；在order by字句之后，可以使用nulls last或者nulls first指定在order排序中排最前还是最后；字符类型字段进行order排序，根据ascii码进行排序（如下图ASCII码对照表）。
PostgreSQL数据库，数字类型和字符类型中，null空值在order排序中，在不指定情况下，都是无穷大，看作最大；在order by字句之后，可以使用nulls last或者nulls first指定在order排序中排最前还是最后;在字符类型的字段中进行order排序，''空字符小于非空字符；数字型字符，跟数字排序一致，其他字符排序，会跟字符集有关，不一定根据根据ascii码进行排序，这个跟MySQL数据库的order排序表现一致。
MySQL数据库，数字类型和字符类型中，null空值在order排序中，在不指定情况下，都是无穷小，看作最小；在order by字句之后，不可以使用nulls last或者nulls first指定在order排序中排最前还是最后;在字符类型的字段中进行order排序，''空字符小于非空字符；数字型字符，跟数字排序一致，其他字符排序，会跟字符集有关，比如GBK、UTF8和UTF8MB4，utf8不是标准的的utf-8字符集编码，不一定根据ascii码进行排序，这个跟PostgreSQL数据库的order排序表现一致。
根据之前做过的测试，达梦数据库，数字类型和字符类型中，null空值在order排序中，在不指定情况下，都是无穷小，看作最小，与MySQL数据库的order排序表现一致；在order by字句之后，可以使用nulls last或者nulls first指定在order排序中排最前还是最后。

1、NULL在数字数据类型中排序

--table of number type for mysql/postgresql/oracle

create table order_test(whether_top int);
insert into order_test values(1);
insert into order_test values(3);
insert into order_test values(10);
insert into order_test values(null);
insert into order_test values(2);
insert into order_test values(0);
commit;

--执行的查看SQL语句

##表按原写入顺序查询
select * from order_test;
##表按数据库默认排序顺序(ASC升序)查询
select * from order_test order by 1;
##表按数据库ASC升序排序查询
select * from order_test order by 1 asc;
##表按数据库DESC降序排序查询
select * from order_test order by 1 desc;

--mysql

mysql> desc order_test;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| whether_top | int(11) | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql> select * from order_test;
+-------------+
| whether_top |
+-------------+
|           1 |
|           3 |
|          10 |
|        NULL |
|           2 |
|           0 |
|          -2 |
+-------------+
7 rows in set (0.00 sec)
##初始时候，只插入6条记录。
mysql> select * from order_test order by 1;
+-------------+
| whether_top |
+-------------+
|        NULL |
|          -2 |
|           0 |
|           1 |
|           2 |
|           3 |
|          10 |
+-------------+
7 rows in set (0.00 sec)


mysql> 
mysql> select * from order_test order by 1 asc;
+-------------+
| whether_top |
+-------------+
|        NULL |
|          -2 |
|           0 |
|           1 |
|           2 |
|           3 |
|          10 |
+-------------+
7 rows in set (0.00 sec)


mysql> 
mysql> select * from order_test order by 1 desc;
+-------------+
| whether_top |
+-------------+
|          10 |
|           3 |
|           2 |
|           1 |
|           0 |
|          -2 |
|        NULL |
+-------------+
7 rows in set (0.00 sec)

--postgresql

postgres=# \d order_test;
               Table "public.order_test"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 whether_top | integer |           |          | 


postgres=# select * from order_test;
 whether_top 
-------------
           1
           3
          10
            
           2
           0
(6 rows)
postgres=# select * from order_test order by 1;
 whether_top 
-------------
           0
           1
           2
           3
          10
            
(6 rows)
postgres=# select * from order_test order by 1 asc;
 whether_top 
-------------
           0
           1
           2
           3
          10
            
(6 rows)


postgres=# select * from order_test order by 1 desc;
 whether_top 
-------------
            
          10
           3
           2
           1
           0
(6 rows)
postgres=# select * from order_test order by 1 asc nulls first;
 whether_top 
-------------
            
           0
           1
           2
           3
          10
(6 rows)
postgres=# select * from order_test order by 1 desc nulls last;
 whether_top 
-------------
          10
           3
           2
           1
           0
            
(6 rows)

--oracle

sys@MYSOURCE > desc order_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WHETHER_TOP                                        NUMBER(38)


sys@MYSOURCE > select * from order_test;


         WHETHER_TOP
--------------------
                   1
                   3
                  10




                   2
                   0


6 rows selected.


sys@MYSOURCE > select * from order_test order by 1;


         WHETHER_TOP
--------------------
                   0
                   1
                   2
                   3
                  10




6 rows selected.




sys@MYSOURCE > select * from order_test order by 1 asc;


         WHETHER_TOP
--------------------
                   0
                   1
                   2
                   3
                  10




6 rows selected.


sys@MYSOURCE > select * from order_test order by 1 desc;




         WHETHER_TOP
--------------------


                  10
                   3
                   2
                   1
                   0
                   
6 rows selected.




sys@MYSOURCE > 
sys@MYSOURCE > select * from order_test order by 1 asc nulls first;


         WHETHER_TOP
--------------------


                   0
                   1
                   2
                   3
                  10


6 rows selected.




sys@MYSOURCE > select * from order_test order by 1 desc nulls last;


         WHETHER_TOP
--------------------
                  10
                   3
                   2
                   1
                   0

2、NULL与''空字符在字符数据类型中排序

--table of char type for mysql/postgresql/oracle

create table nullable_test05(id int,name varchar(10));
insert into nullable_test05 values(1,'');
insert into nullable_test05 values(2,null);
insert into nullable_test05 values(3,'');
insert into nullable_test05 values(4,null);
insert into nullable_test05 values(5,'0');
insert into nullable_test05 values(6,'1');
insert into nullable_test05 values(7,'a');
insert into nullable_test05 values(8,'b');
insert into nullable_test05 values(9,'A');

--mysql

mysql> create table nullable_test05(id int,name varchar(10));
Query OK, 0 rows affected (0.00 sec)


mysql> insert into nullable_test05 values(1,'');
Query OK, 1 row affected (0.00 sec)


mysql> insert into nullable_test05 values(2,null);
Query OK, 1 row affected (0.00 sec)


mysql> insert into nullable_test05 values(3,'');
Query OK, 1 row affected (0.00 sec)


mysql> insert into nullable_test05 values(4,null);
Query OK, 1 row affected (0.00 sec)


mysql> insert into nullable_test05 values(5,'0');
Query OK, 1 row affected (0.00 sec)


mysql> insert into nullable_test05 values(6,'1');
Query OK, 1 row affected (0.01 sec)


mysql> insert into nullable_test05 values(7,'a');
Query OK, 1 row affected (0.00 sec)


mysql> insert into nullable_test05 values(8,'b');
Query OK, 1 row affected (0.01 sec)


mysql> insert into nullable_test05 values(9,'A');
Query OK, 1 row affected (0.00 sec)


mysql> 
mysql> desc nullable_test05;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


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


mysql> select * from nullable_test05 order by 2;
+------+------+
| id   | name |
+------+------+
|    2 | NULL |
|    4 | NULL |
|    1 |      |
|    3 |      |
|    5 | 0    |
|    6 | 1    |
|    7 | a    |
|    9 | A    |
|    8 | b    |
+------+------+
9 rows in set (0.00 sec)


mysql> select * from nullable_test05 order by 2 asc;
+------+------+
| id   | name |
+------+------+
|    2 | NULL |
|    4 | NULL |
|    1 |      |
|    3 |      |
|    5 | 0    |
|    6 | 1    |
|    7 | a    |
|    9 | A    |
|    8 | b    |
+------+------+
9 rows in set (0.00 sec)


mysql> select * from nullable_test05 order by 2 desc;
+------+------+
| id   | name |
+------+------+
|    8 | b    |
|    7 | a    |
|    9 | A    |
|    6 | 1    |
|    5 | 0    |
|    1 |      |
|    3 |      |
|    2 | NULL |
|    4 | NULL |
+------+------+
9 rows in set (0.00 sec)


--该表采用的字符集与编码规则（排序规则）
mysql> select c.TABLE_NAME,c.COLUMN_NAME,c.CHARACTER_SET_NAME,c.COLLATION_NAME 
    -> from information_schema.`COLUMNS` c 
    -> where c.TABLE_SCHEMA ='mytest' and c.TABLE_NAME ='nullable_test05';
+-----------------+-------------+--------------------+-----------------+
| TABLE_NAME      | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME  |
+-----------------+-------------+--------------------+-----------------+
| nullable_test05 | id          | NULL               | NULL            |
| nullable_test05 | name        | utf8               | utf8_general_ci |
+-----------------+-------------+--------------------+-----------------+
2 rows in set (0.00 sec)

--postgresql

postgres=# create table nullable_test05(id int,name varchar(10));
CREATE TABLE
postgres=# 
postgres=# insert into nullable_test05 values(1,'');
INSERT 0 1
postgres=# insert into nullable_test05 values(2,null);
INSERT 0 1
postgres=# insert into nullable_test05 values(3,'');
INSERT 0 1
postgres=# insert into nullable_test05 values(4,null);
INSERT 0 1
postgres=# insert into nullable_test05 values(5,'0');
INSERT 0 1
postgres=# insert into nullable_test05 values(6,'1');
INSERT 0 1
postgres=# insert into nullable_test05 values(7,'a');
INSERT 0 1
postgres=# insert into nullable_test05 values(8,'b');
INSERT 0 1
postgres=# insert into nullable_test05 values(9,'A');
INSERT 0 1
postgres=# \d nullable_test05;
                 Table "public.nullable_test05"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          | 
 name   | character varying(10) |           |          | 


postgres=# 
postgres=# select * from nullable_test05;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
  4 | 
  5 | 0
  6 | 1
  7 | a
  8 | b
  9 | A
(9 rows)


postgres=# 
postgres=# select * from nullable_test05 order by 2;
 id | name 
----+------
  3 | 
  1 | 
  5 | 0
  6 | 1
  7 | a
  9 | A
  8 | b
  4 | 
  2 | 
(9 rows)


postgres=# select * from nullable_test05 order by 2 asc;
 id | name 
----+------
  3 | 
  1 | 
  5 | 0
  6 | 1
  7 | a
  9 | A
  8 | b
  4 | 
  2 | 
(9 rows)


postgres=# select * from nullable_test05 order by 2 desc;
 id | name 
----+------
  2 | 
  4 | 
  8 | b
  9 | A
  7 | a
  6 | 1
  5 | 0
  1 | 
  3 | 
(9 rows)


--postgres数据库采用的字符集信息
postgres=# select pg_encoding_to_char(encoding),datcollate,datctype from pg_database where datname = 'postgres';
 pg_encoding_to_char | datcollate  |  datctype   
---------------------+-------------+-------------
 UTF8                | en_US.UTF-8 | en_US.UTF-8
(1 row)

--Oracle

00:55:30 sys@MYSOURCE > create table nullable_test05(id int,name varchar(10));


Table created.


Elapsed: 00:00:00.11
18:52:23 sys@MYSOURCE > desc nullable_test05;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(10)


00:55:37 sys@MYSOURCE > insert into nullable_test05 values(1,'');
insert into nullable_test05 values(2,null);


1 row created.


Elapsed: 00:00:00.04
00:55:46 sys@MYSOURCE > 
1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(3,'');


1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(4,null);


1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(5,'0');


1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(6,'1');


1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(7,'a');


1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(8,'b');


1 row created.


Elapsed: 00:00:00.00
00:55:46 sys@MYSOURCE > insert into nullable_test05 values(9,'A');


1 row created.


Elapsed: 00:00:00.00
00:55:47 sys@MYSOURCE > commit;


Commit complete.


Elapsed: 00:00:00.00
00:55:50 sys@MYSOURCE > 
00:55:54 sys@MYSOURCE > select * from nullable_test05;


                  ID NAME
-------------------- ------------------------------------------------------------
                   1
                   2
                   3
                   4
                   5 0
                   6 1
                   7 a
                   8 b
                   9 A




9 rows selected.
00:55:59 sys@MYSOURCE > select * from nullable_test05 order by 2;


                  ID NAME
-------------------- ------------------------------------------------------------
                   5 0
                   6 1
                   9 A
                   7 a
                   8 b
                   2
                   3
                   4
                   1


9 rows selected.


Elapsed: 00:00:00.00
00:56:20 sys@MYSOURCE > select * from nullable_test05 order by 2 asc;


                  ID NAME
-------------------- ------------------------------------------------------------
                   5 0
                   6 1
                   9 A
                   7 a
                   8 b
                   2
                   3
                   4
                   1


9 rows selected.


Elapsed: 00:00:00.00
00:57:05 sys@MYSOURCE > select * from nullable_test05 order by 2 desc;


                  ID NAME
-------------------- ------------------------------------------------------------
                   1
                   2
                   3
                   4
                   8 b
                   7 a
                   9 A
                   6 1
                   5 0


9 rows selected.

（文毕）

文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

在ORDER 排序中NULL是最大还是最小|如何处理数据库中NULL的排序问题

评论