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和''是不同的。另外，包含一個或多個空格的空字元串在細節上有所不同，上面簡單實驗已經展示了這些細微區別。

參考資料