Oracle 中 NULL 與空字符串的本質區別與 CHAR/VARCHAR2 特殊行為深度解析

核心概念

• NULL：未知、不存在、無值。長度為 0，不可用 = 比較，需用 IS NULL / IS NOT NULL。
• 空字符串 ''：Oracle 中等同於 NULL（非 ANSI 標準）。
• 任何與 NULL 運算（+ - * /）結果均為 NULL。
• 排序時 NULL 視為最大值，默認降序排最後。
• 聚合函數 COUNT(*) 統計行數，COUNT(col) 忽略 NULL，需 NVL(col,0) 處理。
• NULL 無法被索引，查詢條件含 NULL 列可能漏數據。

經典驗證腳本

SELECT 1 FROM DUAL WHERE NULL = NULL;        -- 無記錄
SELECT 1 FROM DUAL WHERE NULL = '';          -- 無記錄
SELECT 1 FROM DUAL WHERE '' = '';            -- 無記錄
SELECT 1 FROM DUAL WHERE NULL IS NULL;       -- 返回 1
SELECT 1 FROM DUAL WHERE NVL(NULL,0)=NVL(NULL,0); -- 返回 1
SELECT 1+NULL FROM DUAL;                     -- 結果 NULL

實戰案例：銷售表含 NULL 月份

CREATE TABLE sale (month CHAR(6), sell NUMBER);
INSERT INTO sale VALUES ('200001',1000);
...
INSERT INTO sale (month) VALUES ('200009');  -- sell 為 NULL
COMMIT;

SELECT * FROM sale WHERE sell LIKE '%';      -- 只有 11 行，漏掉 NULL 行
SELECT * FROM sale WHERE sell LIKE '%' OR sell IS NULL; -- 12 行全出
SELECT * FROM sale WHERE NVL(sell,0) LIKE '%'; -- 12 行全出

CHAR/VARCHAR2 的特殊陷阱

CREATE TABLE test (a CHAR(5), b CHAR(5));
INSERT INTO test(a,b) VALUES ('1','1');
INSERT INTO test(a,b) VALUES ('2','2');
INSERT INTO test(a,b) VALUES ('3','');  -- 空串
INSERT INTO test(a) VALUES ('4');       -- 隱式 NULL

SELECT * FROM test;
-- A  B
-- 1  1
-- 2  2
-- 3     <-- 空串顯示為空
-- 4     <-- NULL 顯示為空

SELECT * FROM test WHERE b = '';   -- 0 行！空串在 WHERE 中不等於 NULL
SELECT * FROM test WHERE b IS NULL; -- 3 行 (3,4)！空串與 NULL 存儲同義

UPDATE test SET b = '' WHERE a = '2';
SELECT * FROM test WHERE b = '';   -- 仍 0 行
SELECT * FROM test WHERE b IS NULL; -- 3 行 (2,3,4)

結論

1. 存儲層面：CHAR/VARCHAR2 列插入 '' 實際存為 NULL。
2. 查詢層面：WHERE 條件中 '' 不等於 NULL，需用 IS NULL。
3. 默認值同理：DEFAULT '' 實際存 NULL。

最佳實踐：代碼中統一使用 IS NULL / NVL / COALESCE 處理空值，永不直接比較 =''。