MySQL NULL 值深度解析：概念、陷阱、索引影響與處理函數

MySQL NULL 值深度解析：概念、陷阱、索引影響與處理函數

NULL ≠ 空字符串

INSERT INTO t(phone) VALUES (NULL);    -- 電話號碼未知
INSERT INTO t(phone) VALUES ('');       -- 沒有電話 (明確為空)

語義完全不同，切勿混淆。

NULL 參與比較運算永遠為 FALSE

SELECT NULL = NULL;        -- 0 (FALSE)
SELECT NULL <> NULL;       -- 0 (FALSE)
SELECT NULL > 0;           -- 0 (FALSE)
SELECT 1 + NULL;           -- NULL
SELECT CONCAT('a', NULL);  -- NULL

結論：任何與 NULL 的運算結果均為 NULL，除非函數文檔明確說明 (如 IFNULL)。

查詢 NULL 必須用 IS NULL / IS NOT NULL

SELECT * FROM t WHERE phone = NULL;      -- 永遠返回空集 (錯誤)
SELECT * FROM t WHERE phone IS NULL;     -- 正確
SELECT * FROM t WHERE phone = '';        -- 查空字符串

索引與 NULL 的關係

• MySQL 允許 索引包含 NULL 的列 (早期版本/某些引擎有限制，現代 InnoDB 無此限制)。
• 但定義為 NOT NULL 的列 不能 插入 NULL。
• 主鍵列隱含 NOT NULL。

排序與分組中的 NULL

• ORDER BY col ASC：NULL 排在最前；DESC 排在最後。
• GROUP BY col：所有 NULL 視為同一組。

常用處理函數

跨數據庫對應：MySQL IFNULL ≈ Oracle NVL ≈ SQL Server ISNULL。

NOT NULL 列與空字符串的微妙關係

CREATE TABLE t(
  col1 VARCHAR(10) NOT NULL,
  col2 VARCHAR(10) NULL
);
INSERT INTO t VALUES ('', 'a');   -- OK
INSERT INTO t VALUES (NULL, 'b'); -- ERROR: col1 cannot be NULL
SELECT * FROM t WHERE col1 IS NOT NULL; -- 返回所有行 (空串不為 NULL)
SELECT * FROM t WHERE col1 <> '';       -- 只返回非空串行
SELECT * FROM t WHERE col2 <> '';       -- 同時過濾掉 NULL 和空串

總結：

1. NULL 是特殊標記，非空字符串。
2. col <> '' 會同時過濾掉 NULL 和空串。
3. NOT NULL 只禁止 NULL，允許空串。
4. 設計時優先考慮 NOT NULL DEFAULT '' 或 DEFAULT 0，減少 NULL 判斷邏輯。