MySQL NULL 值完整處理指南：比較、索引、統計、特殊行為

MySQL NULL 值完整處理指南：比較、索引、統計、特殊行為

NULL 代表「未知」或「不存在」，不等於空字串 ''、不等於 0、不等於 FALSE。誤解 NULL 語義是 SQL 新手最常見 Bug 來源。本文系統整理 MySQL 中 NULL 的比較規則、索引限制、聚合行為、特殊處理場景。

1. NULL vs 空字串：語義截然不同

INSERT INTO contacts (phone) VALUES (NULL);   -- 電話號碼未知
INSERT INTO contacts (phone) VALUES ('');      -- 確定沒有電話

2. NULL 比較鐵律：永遠不為 TRUE

• NULL = NULL → NULL (非 TRUE)
• NULL <> NULL → NULL
• NULL > 5 → NULL
• 1 + NULL → NULL
• CONCAT('str', NULL) → NULL

正確查詢 NULL：

WHERE phone IS NULL      -- 找 NULL
WHERE phone IS NOT NULL  -- 找非 NULL
WHERE phone = ''         -- 找空字串 (非 NULL)

錯誤寫法：WHERE phone = NULL (永遠回傳 0 行)。

3. NULL 處理函數與運算子

4. 索引與 NULL 的關係

• MyISAM / InnoDB / MEMORY / BDB：允許 可為 NULL 的欄位建立索引。
• 其他儲存引擎：可能要求 NOT NULL 才能建索引。
• 最佳實務：除非業務強制需區分「未知」與「空值」，盡量定義 NOT NULL DEFAULT '' 或 DEFAULT 0，簡化查詢邏輯並縮小索引體積。

5. 載入資料時的 NULL (LOAD DATA INFILE)

• 預設：空值/缺失欄位 → 寫入空字串 ''。
• 需寫入 NULL：資料檔中使用 \N (反斜線 + 大寫 N)。
• 部分版本也接受字面字串 NULL。

6. 排序、分組、去重時的 NULL 行為

• ORDER BY col ASC：NULL 排最前；DESC 時 NULL 排最後。
• GROUP BY、DISTINCT：所有 NULL 視為同一群組/值。

7. 聚合函數自動忽略 NULL

SELECT COUNT(*), COUNT(age), SUM(age), AVG(age), MIN(age), MAX(age) FROM person;

• COUNT(*)：統計**行數**，包含 NULL 行。
• COUNT(col)、SUM、AVG、MIN、MAX：忽略 NULL 值。

8. 特殊欄位類型的 NULL 隱性轉換

9. 開發者檢查清單

1. [ ] 表設計階段：明確每欄位是否允許 NULL，能 NOT NULL 就 NOT NULL。
2. [ ] 查詢撰寫：永不使用 = NULL，一律 IS NULL / IS NOT NULL / <=>。
3. [ ] 顯示邏輯：IFNULL/COALESCE 統一處理前端顯示預設值。
4. [ ] 匯入資料：LOAD DATA 使用 \N 表示 NULL。
5. [ ] 統計報表：區分 COUNT(*) (總筆數) 與 COUNT(col) (非空筆數)。