oracle类似isempty,NULLs和empty strings在不同数据库的中特点

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=#

在pg中,NULLs和字符相连接后，NULL出现在任何一个值中都意味着结果是NULL作为输出值，而不管它连接的是什么。