MySQL NULL 與 NOT NULL 深度解析：存儲差異、性能影響與查詢陷阱避坑指南

MySQL NULL 與 NOT NULL 深度解析：存儲差異、性能影響與查詢陷阱避坑指南

NULL 與空值的混淆是導致數據質量問題、查詢邏輯錯誤、索引失效的核心原因。本文從存儲原理、性能影響、查詢語義三個維度徹底釐清。

一、核心概念釐清

二、存儲引擎層面的差異

MyISAM

• 每列 NULL 額外佔用 1 bit，按字節對齊 (8 列 NULL = 1 字節)
• 文件結構：.MYD 數據文件中直接標記

InnoDB

• NULL 列不存儲在數據頁中，但需要 NULL 位圖 (每行固定開銷)
• 行格式 COMPACT/REDUNDANT：NULL 列不佔用數據區空間，但位圖固定開銷
• 行格式 DYNAMIC/COMPRESSED：長列溢出頁面不存 NULL
• 結論：InnoDB 中 NULL 列節省數據區空間，但增加元數據開銷

三、性能影響深度分析

1. 索引效能

• B+Tree 索引不存儲 NULL 值 (單列索引)，導致 WHERE col IS NULL 無法走索引 (全表掃描)
• 複合索引：若前導列為 NULL，該行不進入索引 (MyISAM/InnoDB 行為一致)
• 統計信息：ANALYZE TABLE 統計時 NULL 不計入基數，可能導致優化器誤判

2. 查詢優化器

• IS NULL / IS NOT NULL 可能無法使用範圍優化
• COUNT(col) 不計算 NULL，COUNT(*) 計算所有行
• ORDER BY col：NULL 排在最前 (ASC) 或最後 (DESC)

3. 存儲空間

• 定長類型 CHAR(10)：NULL 與非 NULL 佔用相同空間 (固定長度)
• 變長類型 VARCHAR(100)：NULL 只佔 NULL 位圖，非 NULL 佔用長度前綴 + 實際數據

四、NOT NULL 約束行為驗證

CREATE TABLE test_null (
    col1 VARCHAR(10) NOT NULL,
    col2 VARCHAR(10) NULL
) ENGINE=MyISAM;

-- 測試 1: 插入 NULL 到 NOT NULL 列
INSERT INTO test_null VALUES (NULL, 'abc');
-- ERROR 1048 (23000): Column 'col1' cannot be null

-- 測試 2: 插入空字符串到 NOT NULL 列
INSERT INTO test_null VALUES ('', 'abc');
-- Query OK, 1 row affected

-- 測試 3: 插入 NULL 到 NULL 列
INSERT INTO test_null VALUES ('xyz', NULL);
-- Query OK, 1 row affected

關鍵結論：NOT NULL 只禁止 NULL，不禁止空字符串。應用層需同時驗證業務「必填」邏輯。

五、查詢陷阱：IS NULL vs != ''

INSERT INTO test_null VALUES ('', NULL), ('hello', 'world'), (NULL, '');

-- 查詢 col1 不為空字符串
SELECT * FROM test_null WHERE col1 <> '';
-- 結果: ('hello', 'world')  -- 只有 1 行！NULL 和 '' 都被過濾

-- 查詢 col1 不為 NULL
SELECT * FROM test_null WHERE col1 IS NOT NULL;
-- 結果: ('', 'abc'), ('hello', 'world')  -- 2 行！空字符串保留

-- 查詢 col1 既非 NULL 也非空字符串
SELECT * FROM test_null WHERE col1 IS NOT NULL AND col1 <> '';
-- 結果: ('hello', 'world')  -- 業務通常真正想要的

六、最佳實踐指南

1. 設計原則

• 默認 NOT NULL：除非業務明確需要「未知」語義，否則所有列 NOT NULL DEFAULT '' 或 DEFAULT 0
• 業務語義區分：
  • 用戶生日：允許 NULL (用戶未填寫)
  • 用戶暱稱：NOT NULL DEFAULT '' (註冊必填，可改為空)
  • 訂單金額：NOT NULL DEFAULT 0 (業務邏輯不允許未知)
• 避免 NOT NULL + 無 DEFAULT：導致 INSERT 必須顯式提供值，增加應用層負擔

2. 索引優化

• 頻繁 IS NULL 查詢的列 → 考慮 DEFAULT 'NULL_STR' + 普通值索引
• 複合索引前導列 → 必須 NOT NULL
• 外鍵列 → 必須 NOT NULL (或配合 ON DELETE SET NULL 需允許 NULL)

3. 應用層處理

-- Java/JDBC 獲取 NULL 安全模式
String name = rs.getString("name");  // 返回 null
if (rs.wasNull()) { /* 處理 NULL */ }

-- MyBatis resultMap 映射


-- Spring Data JPA
@Column(nullable = false)  // DDL 生成 NOT NULL
private String name = "";   // Java 層默認空串

七、遷移與清洗腳本

-- 將現有 NULL 轉為空字符串 (謹慎執行)
UPDATE users SET nickname = '' WHERE nickname IS NULL;
ALTER TABLE users MODIFY nickname VARCHAR(50) NOT NULL DEFAULT '';

-- 統計 NULL 分布
SELECT 
    COUNT(*) AS total,
    COUNT(nickname) AS not_null_count,
    COUNT(*) - COUNT(nickname) AS null_count,
    SUM(CASE WHEN nickname = '' THEN 1 ELSE 0 END) AS empty_count
FROM users;

八、總結

NULL 是關係模型的理論基石，但在工程實踐中往往帶來更多複雜性而非價值。「默認 NOT NULL，業務需要才允許 NULL，應用層統一語義」 是降低認知負擔、提升查詢性能、保證數據質量的鐵律。