萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL NULL 值處理完整指南:IS NULL、IS NOT NULL、<=> 操作符

MySQL NULL 值處理全解析:查詢、比較與子查詢陷阱

NULL 在 MySQL 中代表「值未知」而非空字符串或零,無法使用 = != 進行比較。本文系統梳理 NULL 處理的三大操作符、函數與子查詢陷阱。

三大核心操作符

基本查詢示例

-- 錯誤:永遠返回空結果
SELECT * FROM users WHERE phone = NULL;
SELECT * FROM users WHERE phone != NULL;

-- 正確:查詢 NULL 值
SELECT * FROM users WHERE phone IS NULL;

-- 正確:查詢非 NULL 值
SELECT * FROM users WHERE phone IS NOT NULL;

-- 正確:查詢空字符串(非 NULL)
SELECT * FROM users WHERE phone = '';

-- NULL-safe 比較
SELECT * FROM t1 JOIN t2 ON t1.col <=> t2.col;

IFNULL / COALESCE / NULLIF 函數

操作符語法結果說明
IS NULLcol IS NULLTRUE/FALSE值為 NULL 返回 TRUE
IS NOT NULLcol IS NOT NULLTRUE/FALSE值不為 NULL 返回 TRUE
<=> (NULL-safe equal)col1 <=> col2TRUE/FALSE兩邊均為 NULL 返回 TRUE,等同於 col1 = col2 OR (col1 IS NULL AND col2 IS NULL)
函數語法行為示例
IFNULLIFNULL(expr1, expr2)expr1 非 NULL 返回 expr1,否則 expr2IFNULL(NULL, 'N/A') → 'N/A'
COALESCECOALESCE(v1, v2, ...)返回第一個非 NULL 參數COALESCE(NULL, NULL, 'a', 'b') → 'a'
NULLIFNULLIF(expr1, expr2)expr1 = expr2 返回 NULL,否則 expr1NULLIF(5, 5) → NULL

子查詢陷阱:NOT IN vs NOT EXISTS

問題場景

CREATE TABLE t2 (col1 INT, col2 INT);
CREATE TABLE t3 (col1 INT, col2 INT);

INSERT INTO t2 VALUES (1,2), (1,3);
INSERT INTO t3 VALUES (1,2), (1,NULL);

-- NOT IN 子查詢
SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);
-- 結果:Empty set ❌

-- NOT EXISTS 子查詢
SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);
-- 結果:返回 (1,3) ✅

根因分析

MySQL 三值邏輯:TRUE、FALSE、UNKNOWN(NULL 參與比較結果為 UNKNOWN)

col2 NOT IN (2, NULL)
≡ col2 <> 2 AND col2 <> NULL
≡ TRUE AND UNKNOWN
≡ UNKNOWN → WHERE 視為 FALSE

NOT EXISTS 逐行評估,子查詢無結果返回 TRUE,不受 NULL 影響。

最佳實踐

  • 優先使用 NOT EXISTS / LEFT JOIN ... IS NULL 替代 NOT IN
  • 若必須用 NOT IN,確保子查詢結果集不含 NULL:
    WHERE col NOT IN (SELECT col FROM t WHERE col IS NOT NULL)
  • 建模階段關鍵業務列設置 NOT NULL DEFAULT '' 或 DEFAULT 0

聚合函數與 NULL

  • COUNT(*):統計行數,包含 NULL
  • COUNT(col):統計非 NULL 值個數,忽略 NULL
  • SUM/AVG/MIN/MAX:忽略 NULL 值計算
  • GROUP BY:將所有 NULL 視為同一組

實戰建議

  1. 業務語義明確的列盡量定義 NOT NULL
  2. 查詢條件習慣用 IS NULL / IS NOT NULL
  3. 子查詢避免 NOT IN,改用 NOT EXISTS
  4. 顯示層使用 COALESCE(col, '默認值') 避免前端報錯
  5. 唯一索引允許多個 NULL(除非使用 UNIQUE NOT NULL 組合)
copyright © 萬盛學電腦網 all rights reserved