Oracle/MySQL/PG/SQL Server关系数据库中NULL与空字符串的区别

Oracle数据库

在Oracle数据库中，''（空字符串）与null是什么关系呢？ ''（空字符串）是否会等同(或者说等价于)于null值呢？''跟' '（长度为零的空字符串或包含一个或多个空格的空字符串）是否又等价？下面我们测试一下

SQL> create table test(id number not null, name varchar2(16));

Table created.

SQL> insert into test(id, name) values(1, null);

1 row created.

SQL> insert into test(id, name) values(2, '');

1 row created.

SQL> insert into test(id, name) values(3, ' '); --符号中' '有一个空格

1 row created.

SQL> insert into test(id, name) values(4, '  '); --符号中'  '有两个空格


1 row created.

SQL> commit;

Commit complete.

SQL> select * from test where name is null;

        ID NAME
---------- ----------------
         1
         2

2 rows selected.

##三值逻辑，因为''等价与null，null跟任何非null的值比较都等于false
SQL> select * from test where name='';

no rows selected

SQL> select * from test where name=' '; --符号中' '有一个空格

        ID NAME
---------- ----------------
         3

1 row selected.

SQL> select * from test where name='  ';--符号中'  '有两个空格

        ID NAME
---------- ----------------
         4

1 row selected.

SQL> select id ,dump(name) from test;

        ID DUMP(NAME)
---------- --------------------
         1 NULL
         2 NULL
         3 Typ=1 Len=1: 32
         4 Typ=1 Len=2: 32,32

4 rows selected.

SQL>

如上所示，插入''时，Oracle数据库确实将其等同与null，但是，像' '这种长度不为零的空字符串，Oracle会存储其值，从上面实验就可以看出。另外官方文档^[1]中关于''与Null的解释如下：

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

翻译如下：

Oracle 数据库当前将长度为零的空字符值视为Null。但是，在将来的版本中，这种情况可能不会继续存在，Oracle 建议您不要将空字符串视为null值。

另外，还有一个有意思的现象，如果在查询条件中，包含一个空格与包含两个空格的对比，它们似乎又是等价的。如下所示：

SQL> select * from test where ' ' = '  '; 

        ID NAME
---------- ----------------
         1
         2
         3
         4

4 rows selected.

SQL>

SQL Server数据库

这个实验，我们在SQL Server数据库测试一下看看,你会看到不一样的现象。

create table test(id int ,name varchar(16) );

insert into test(id, name) values(1, null);
insert into test(id, name) values(2, '');
insert into test(id, name) values(3, ' ');--包含一个空格
insert into test(id, name) values(4, '  ');--包含两个空格

SELECT * FROM TEST WHERE NAME IS NULL;

SELECT * FROM TEST WHERE NAME ='';
SELECT * FROM TEST WHERE NAME =' ';--包含一个空格
SELECT * FROM TEST WHERE NAME ='  ';--包含两个空格

SELECT ID, DATALENGTH(NAME) AS COLUMN_LEN FROM TEST;

SELECT * FROM TEST WHERE '' = ' ';

如上测试，SQL Server跟Oracle不一样，在SQL Server中，’’跟null是不一样的，空字符串是空字符串，null是null，SQL Server数据库中不会将长度为零的空字符串视为null。但是SQL Server在查询的时候比较空字符串时，长度为零的空字符串与长度为1的字符串，长度为N的字符串，它们似乎是等价的。跟Oracle的行为完全不一样。这个的解释如下,详情参考官方文档^[2]：

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

SQL Server 遵循 ANSI/ISO SQL-92 规范（第8.2、<比较谓语>、常规规则 #3），说明如何比较带有空格的字符串。 ANSI 标准要求在比较中使用空字符填充字符串，以便它们的长度与比较它们的长度相匹配。 填充直接影响 WHERE 和 HAVING 子句谓词以及其他 Transact-SQL 字符串比较的语义。 例如，Transact-SQL 会将字符串 "abc" 和 "abc " 视为对大多数比较操作等效。此规则的唯一例外是类似谓语。 当 LIKE 谓词表达式的右侧具有尾随空格的值时，SQL Server 不会在比较发生之前将这两个值填充到同一长度。 根据定义，LIKE 谓语的用途是促进模式搜索，而不是简单的字符串相等测试，这不违反前面提到的 ANSI SQL-92 规范的部分。

MySQL数据库

接下来，我们来看看MySQL数据库的测试情况。

mysql> use test
Database changed
mysql> create table test(id int not null, name varchar(16));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(id , name) values(1, null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(id , name) values(2, '');
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into test(id , name) values(4, '  ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where name is null;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test where name='';
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql>
mysql> select * from test where name=' ';
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql>

MySQL的对于null和空字符串的处理方式跟SQL Server是一致的。但是你会发现还是有不一样的地方，下面这个SQL语句， 在SQL Server和MySQL中有不一样的结果。

mysql> select * from test where '' = ' ';
Empty set (0.00 sec)

PostgreSQL数据库

postgres=# \c kerry
You are now connected to database "kerry" as user "postgres".
kerry=# create table test(id int not null, name varchar(16));
CREATE TABLE
kerry=# insert into test(id , name) values(1, null);
INSERT 0 1
kerry=# insert into test(id,  name) values(2, '');
INSERT 0 1
kerry=# insert into test(id,  name) values(3, ' ');
INSERT 0 1
kerry=# insert into test(id,  name) values(4, '  ');
INSERT 0 1
kerry=# select * from test where name is null;
 id | name 
----+------
  1 | 
(1 row)

kerry=# select * from test where name='';
 id | name 
----+------
  2 | 
(1 row)

kerry=# select * from test where name=' ';
 id | name 
----+------
  3 |  
(1 row)

kerry=# select * from test where name='  ';
 id | name 
----+------
  4 |   
(1 row)

kerry=#

kerry=# select * from test where '' = ' ';
 id | name 
----+------
(0 rows)

PostgreSQL的对于null和空字符串的处理方式跟MySQL是一致的.

总结：

几大关系数据库中，只有Oracle数据库会将''视为null，其它关系数据库中，null和''是不同的。另外，包含一个或多个空格的空字符串在细节上有所不同，上面简单实验已经展示了这些细微区别。

参考资料