NULLs和empty strings在不同数据库的中特点

2020-09-07 21:52 abce 阅读(284) 评论(0) 编辑收藏举报

1.以oracle为例

SQL> create table test(id int primary key,content varchar(20));  
SQL> INSERT INTO test (id, content) VALUES (1, NULL);
SQL> INSERT INTO test (id, content) VALUES (2, '');
SQL> INSERT INTO test (id, content) VALUES (3, ' ');
SQL> INSERT INTO test (id, content) VALUES (4, 'x');
SQL> select * from test;
 
        ID CONTENT
---------- --------------------
         1
         2
         3
         4 x
 
SQL> SELECT ID,CONTENT,
     case when content is null then 1 else 0 end as isnull,
     case when content = '' then 1 else 0 end as isempty,
     case when content = ' ' then 1 else 0 end as blank 
     from
        test;
 
        ID CONTENT            ISNULL    ISEMPTY      BLANK
---------- -------------- ---------- ---------- ----------
         1                         1          0          0
         2                         1          0          0
         3                         0          0          1
         4 x                       0          0          0
 
SQL> select id,content,length(content) from test;
 
        ID CONTENT              LENGTH(CONTENT)
---------- -------------------- ---------------
         1
         2
         3                                    1
         4 x                                  1
 
SQL> 

从结果可以看到，empry string被插入表中时，被当做NULL对待。因此，empty strings不会在数据库中存储。单个空格是不会被转换的，因为不是一个empty string。

2.以mysql为例

>create table test(id int primary key,content varchar(20)); 
>INSERT INTO test (id, content) VALUES (1, NULL);
>INSERT INTO test (id, content) VALUES (2, '');
>INSERT INTO test (id, content) VALUES (3, ' ');
>INSERT INTO test (id, content) VALUES (4, 'x');
>select * from test;
+----+---------+
| id | content |
+----+---------+
|  1 | NULL    |
|  2 |         |
|  3 |         |
|  4 | x       |
+----+---------+
4 rows in set (0.00 sec)
 
>SELECT ID,CONTENT,
          case when content is null then 1 else 0 end as isnull,
          case when content = '' then 1 else 0 end as isempty,
          case when content = ' ' then 1 else 0 end as blank 
          from
             test;
+----+---------+--------+---------+-------+
| ID | CONTENT | isnull | isempty | blank |
+----+---------+--------+---------+-------+
|  1 | NULL    |      1 |       0 |     0 |
|  2 |         |      0 |       1 |     1 |
|  3 |         |      0 |       1 |     1 |
|  4 | x       |      0 |       0 |     0 |
+----+---------+--------+---------+-------+
4 rows in set (0.00 sec)
 
>select id,content,length(content) from test;
+----+---------+-----------------+
| id | content | length(content) |
+----+---------+-----------------+
|  1 | NULL    |            NULL |
|  2 |         |               0 |
|  3 |         |               1 |
|  4 | x       |               1 |
+----+---------+-----------------+

可以看到NULL和empty string是不同的。而empty string和空格string被认为是相同的，但是在计算长度的时候却又不同了。

3.以pg为例

postgres=# create table test(id int primary key,content varchar(20)); 
postgres=# INSERT INTO test (id, content) VALUES (1, NULL);
postgres=# INSERT INTO test (id, content) VALUES (2, '');
postgres=# INSERT INTO test (id, content) VALUES (3, ' ');
postgres=# INSERT INTO test (id, content) VALUES (4, 'x');
postgres=# select * from test;
 id | content 
----+---------
  1 | 
  2 | 
  3 |  
  4 | x
(4 rows)
postgres=# SELECT ID,CONTENT,
           case when content is null then 1 else 0 end as isnull,
           case when content = '' then 1 else 0 end as isempty,
           case when content = ' ' then 1 else 0 end as blank 
           from test;
            
 id | content | isnull | isempty | blank 
----+---------+--------+---------+-------
  1 |         |      1 |       0 |     0
  2 |         |      0 |       1 |     0
  3 |         |      0 |       0 |     1
  4 | x       |      0 |       0 |     0
(4 rows)
 
 
postgres=# select id,content,length(content) from test;
 id | content | length 
----+---------+--------
  1 |         |       
  2 |         |      0
  3 |         |      1
  4 | x       |      1
(4 rows)
 
postgres=# 

看前两行，NULL被插入后仍被当做NULL，不能当做empty string。从第二行可以看到，插入的empty string没有被当做NULL，仍然是一个empty string。

NULLs和non-NULLs

(1)oracle数据库

SQL> SELECT id, content,
      content || NULL AS concatnull,
      content || 'x' AS concatchar
    FROM test;
 
        ID CONTENT              CONCATNULL           CONCATCHAR
---------- -------------------- -------------------- ---------------------
         1                                           x
         2                                           x
         3                                            x
         4 x                    x                    xx
 
SQL> 

在oracle中,NULLs和字符相连接后，输出结果是字符。

(2)mysql数据库

>SELECT id, content,
       content || NULL AS concatnull,
       content || 'x' AS concatchar
     FROM test;
+----+---------+------------+------------+
| id | content | concatnull | concatchar |
+----+---------+------------+------------+
|  1 | NULL    |       NULL |       NULL |
|  2 |         |       NULL |          0 |
|  3 |         |       NULL |          0 |
|  4 | x       |       NULL |          0 |
+----+---------+------------+------------+

mysql中可以用concat拼接多个，但用||无法拼接字符串，会显示零。

>SELECT id, content,
            concat(content,NULL) AS concatnull,
            concat(content,'x') AS concatchar
          FROM test;
+----+---------+------------+------------+
| id | content | concatnull | concatchar |
+----+---------+------------+------------+
|  1 | NULL    | NULL       | NULL       |
|  2 |         | NULL       | x          |
|  3 |         | NULL       |  x         |
|  4 | x       | NULL       | xx         |
+----+---------+------------+------------+

NULL和non-NULLS拼接结果是NULL

(3)pg数据库

postgres=# SELECT id, content,
postgres-#   content || NULL AS concatnull,
postgres-#   content || 'x' AS concatchar
postgres-# FROM test;
 id | content | concatnull | concatchar 
----+---------+------------+------------
  1 |         |            | 
  2 |         |            | x
  3 |         |            |  x
  4 | x       |            | xx
(4 rows)
 
postgres=# 