萬盛學電腦網

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

MySQL NULL 值處理完全指南:三值邏輯、IS NULL、COUNT 忽略、TIMESTAMP 特殊行為與避坑實戰

NULL 在 SQL 中代表「未知」,遵循三值邏輯(TRUE/FALSE/UNKNOWN)。誤解 NULL 行為是 Bug 溫床。本文系統整理 NULL 比較、函數處理、統計函數、特殊類型行為與最佳實踐。

核心:三值邏輯真值表

SELECT 
  NULL = 0,       -- NULL (UNKNOWN)
  NULL = 12345,   -- NULL
  NULL <> 12345,  -- NULL
  NULL + 12345,   -- NULL
  NULL || 'abc',  -- NULL
  NULL = NULL,    -- NULL(關鍵!)
  NULL <> NULL,   -- NULL
  NULL AND TRUE,  -- NULL
  NULL AND FALSE, -- FALSE(短路)
  NULL OR FALSE,  -- NULL
  NULL OR TRUE,   -- TRUE(短路)
  NOT NULL;       -- NULL

結論:任何與 NULL 的比較/運算結果均為 NULL(UNKNOWN),只有 IS NULL/IS NOT NULL 能正確判斷。

陷阱一:NULL ≠ 空字串


INSERT INTO `t` (`col`) VALUES (NULL), ('');
SELECT `col`, LENGTH(`col`), `col` IS NULL, `col` = '' FROM `t`;
+------+-------------+------------+----------+
| col  | LENGTH(col) | col IS NULL| col = '' |
+------+-------------+------------+----------+
| NULL | NULL        | 1          | NULL     |
|      | 0           | 0          | 1        |
+------+-------------+------------+----------+
  • NULL:佔 0 位元組,IS NULL 為真
  • '':佔 0 位元組但非 NULL,= '' 為真
  • 業務語義不同:NULL=「未知/未提供」,''=「明確為空」

陷阱二:TIMESTAMP / AUTO_INCREMENT 欄位插入 NULL 的特殊行為

欄位類型INSERT NULL 結果
TIMESTAMP自動填入當前時間
AUTO_INCREMENT自動填入下一個遞增值
其他存入 NULL

統計函數對 NULL 的處理


-- COUNT(*) 計算行數(含 NULL)
-- COUNT(col) 計算非 NULL 個數
-- SUM/AVG/MIN/MAX 忽略 NULL

CREATE TABLE `t` (`v` INT);
INSERT INTO `t` VALUES (1), (2), (NULL);
SELECT COUNT(*), COUNT(`v`), SUM(`v`), AVG(`v`) FROM `t`;
-- COUNT(*)=3, COUNT(v)=2, SUM=3, AVG=1.5

正確判斷 NULL 的寫法


-- 正確
WHERE `col` IS NULL
WHERE `col` IS NOT NULL

-- 錯誤
WHERE `col` = NULL      -- 永遠 UNKNOWN
WHERE `col` != NULL     -- 永遠 UNKNOWN
WHERE `col` <> NULL     -- 永遠 UNKNOWN

實務最佳實踐

  1. 欄位預設 NOT NULL,給予合理 DEFAULT 值
  2. 確需區分「未知」與「空」時,才允許 NULL
  3. 使用 COALESCE(col, 'default') 或 IFNULL(col, 'default') 提供預設顯示
  4. NOT IN 子查詢若含 NULL 會返回空集,改用 NOT EXISTS 或 LEFT JOIN ... IS NULL
  5. 唯一索引允許多個 NULL(除非主鍵),需注意業務邏輯

NULL 安全比較運算符(MySQL 特有)


-- <=> 空值安全等於:NULL <=> NULL 為 TRUE
SELECT NULL <=> NULL; -- 1
SELECT 1 <=> NULL;    -- 0

適用於 WHERE a <=> b 需將 NULL 視為相等的場景。

把 NULL 當作特殊值而非普通值處理,並養成 IS NULL 判斷習慣,能消除 90% 的 NULL 相關 Bug。

copyright © 萬盛學電腦網 all rights reserved