萬盛學電腦網

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

MySQL NULL 值完全指南:誤區、操作符與最佳實踐

核心概念:NULL ≠ 空字串 ≠ 零

INSERT INTO t (phone) VALUES (NULL);    -- 電話號碼未知
INSERT INTO t (phone) VALUES ('');      -- 無電話 (空字串)
INSERT INTO t (phone) VALUES ('0');     -- 號碼為 0

三者語意迥異,查詢結果截然不同。

正確的 NULL 判斷

目的正確寫法錯誤寫法
判斷是 NULLWHERE col IS NULLWHERE col = NULL (永遠為 UNKNOWN)
判斷非 NULLWHERE col IS NOT NULLWHERE col != NULL
空值轉預設IFNULL(col, 'default') / COALESCE(col, 'default')CASE WHEN col = NULL THEN ...

NULL 參與運算與比較規則

  • 任何標量運算遇 NULL → 結果 NULL:1 + NULL = NULL, CONCAT('a', NULL) = NULL
  • 比較運算 (=, <, >, <=, >=, !=, <>) 與 NULL → UNKNOWN (非 TRUE/FALSE)
  • DISTINCT、GROUP BY、ORDER BY 視所有 NULL 為相等
  • ORDER BY col ASC NULL 排最前;DESC 排最後

聚合函數對 NULL 的處理

特殊欄位類型的 NULL 行為

  • TIMESTAMP:INSERT NULL → 當前時間戳 (若未顯式 DEFAULT CURRENT_TIMESTAMP)
  • AUTO_INCREMENT 整數欄:INSERT NULL/0 → 下一個序列值
  • JSON:NULL 與 JSON NULL 不同,後者是有效 JSON 值

索引與 NULL

  • MyISAM/InnoDB/BDB/MEMORY 允許索引含 NULL 欄位
  • 其他引擎需 NOT NULL 才能建索引
  • 複合索引中任一欄為 NULL,該索引條目仍會建立 (InnoDB)

資料匯入/匯出技巧

-- LOAD DATA INFILE
-- 檔案中用 \N 表示 NULL (預設)
-- 或指定 NULL 表示字串
LOAD DATA INFILE 'data.txt' INTO TABLE t
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY '\n'
  (col1, @var_col2)
  SET col2 = NULLIF(@var_col2, 'NULL_STRING');

-- SELECT ... INTO OUTFILE
-- NULL 輸出為 \N

開發規範建議

  1. 語意明確:業務上「不存在」用 NULL,「存在但為空」用空字串/零值
  2. DDL 顯式聲明:col VARCHAR(50) NOT NULL DEFAULT '' 或 col VARCHAR(50) NULL DEFAULT NULL
  3. 應用層防禦:ORM/DTO 層統一 NULL → 空字串/預設值,避免業務邏輯散落 IS NULL
  4. 避免 NOT IN 子查詢含 NULL:WHERE id NOT IN (SELECT nullable_col ...) 子查詢有 NULL 會導致結果為空,改用 NOT EXISTS 或 LEFT JOIN ... IS NULL
copyright © 萬盛學電腦網 all rights reserved
函數行為範例
COUNT(*)計算行數,含 NULL 行COUNT(*) = 10
COUNT(col)計算非 NULL 個數COUNT(phone) = 7 (3 個 NULL)
SUM/AVG/MIN/MAX忽略 NULLAVG(score) 僅算有分數者
GROUP_CONCAT忽略 NULL不會出現空項