Oracle數(shù)據(jù)庫
在Oracle數(shù)據(jù)庫中，''（空字符串）與null是什么關(guān)系呢？ ''（空字符串）是否會等同(或者說等價于)于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數(shù)據(jù)庫確實將其等同與null，但是，像' '這種長度不為零的空字符串，Oracle會存儲其值，從上面實驗就可以看出。另外官方文檔[1]中關(guān)于''與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 數(shù)據(jù)庫當前將長度為零的空字符值視為Null。但是，在將來的版本中，這種情況可能不會繼續(xù)存在，Oracle 建議您不要將空字符串視為null值。
另外，還有一個有意思的現(xiàn)象，如果在查詢條件中，包含一個空格與包含兩個空格的對比，它們似乎又是等價的。如下所示：
SQL>?select?*?from?test?where?'?'?=?'??';?
????????ID?NAME
----------?----------------
?????????1
?????????2
?????????3
?????????4
4?rows?selected.
SQL>
SQL Server數(shù)據(jù)庫
這個實驗，我們在SQL Server數(shù)據(jù)庫測試一下看看,你會看到不一樣的現(xiàn)象。
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數(shù)據(jù)庫中不會將長度為零的空字符串視為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 規(guī)范（第8.2、<比較謂語>、常規(guī)規(guī)則 #3），說明如何比較帶有空格的字符串。 ANSI 標準要求在比較中使用空字符填充字符串，以便它們的長度與比較它們的長度相匹配。 填充直接影響 WHERE 和 HAVING 子句謂詞以及其他 Transact-SQL 字符串比較的語義。 例如，Transact-SQL 會將字符串 "abc" 和 "abc " 視為對大多數(shù)比較操作等效。此規(guī)則的唯一例外是類似謂語。 當 LIKE 謂詞表達式的右側(cè)具有尾隨空格的值時，SQL Server 不會在比較發(fā)生之前將這兩個值填充到同一長度。 根據(jù)定義，LIKE 謂語的用途是促進模式搜索，而不是簡單的字符串相等測試，這不違反前面提到的 ANSI SQL-92 規(guī)范的部分。
MySQL數(shù)據(jù)庫
接下來，我們來看看MySQL數(shù)據(jù)庫的測試情況。
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是一致的。但是你會發(fā)現(xiàn)還是有不一樣的地方，下面這個SQL語句， 在SQL Server和MySQL中有不一樣的結(jié)果。
mysql>?select?*?from?test?where?''?=?'?';
Empty?set?(0.00?sec)
PostgreSQL數(shù)據(jù)庫
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是一致的.
總結(jié)：
幾大關(guān)系數(shù)據(jù)庫中，只有Oracle數(shù)據(jù)庫會將''視為null，其它關(guān)系數(shù)據(jù)庫中，null和''是不同的。另外，包含一個或多個空格的空字符串在細節(jié)上有所不同，上面簡單實驗已經(jīng)展示了這些細微區(qū)別。
參考資料
1: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html#:~:text=Oracle%20Database%20currently%20treats%20a%20character%20value%20with,expression%20containing%20a%20null%20always%20evaluates%20to%20null文章來源：http://www.zghlxwxcb.cn/news/detail-573472.html
[2]2: https://support.microsoft.com/en-us/topic/inf-how-sql-server-compares-strings-with-trailing-spaces-b62b1a2d-27d3-4260-216d-a605719003b0文章來源地址http://www.zghlxwxcb.cn/news/detail-573472.html
到了這里，關(guān)于Oracle/MySQL/PG/SQL Server關(guān)系數(shù)據(jù)庫中NULL與空字符串的區(qū)別的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
