萬盛學電腦網

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

MySQL NULL 值處理完全指南:更新、判斷、運算與 IFNULL/COALESCE 實戰

NULL 在關係數據庫中代表「值未知/不存在」,不等於 0、不等於空字符串、不等於空格。任何與 NULL 的比較運算(=、!=、>、<)結果均為 NULL(UNKNOWN),導致 WHERE 條件過濾失效。本文系統講解 NULL 更新、判斷、運算陷阱與標準處理函數。

核心誤區演示

-- 錯誤:用 = 比較 NULL
UPDATE client SET company = '無' WHERE company = NULL;  -- 0 行受影響

-- 錯誤:用 != 過濾非 NULL
SELECT * FROM client WHERE company != '無';  -- 不會返回 company 為 NULL 的行

-- 正確:IS NULL / IS NOT NULL
UPDATE client SET company = '無' WHERE company IS NULL;
SELECT * FROM client WHERE company IS NOT NULL;

NULL 參與運算的陷阱

-- 算術運算:結果全為 NULL
SELECT 10 + NULL;      -- NULL
SELECT NULL - 5;       -- NULL
SELECT NULL * 100;     -- NULL
SELECT 10 / NULL;      -- NULL

-- 比較運算:結果為 UNKNOWN(當作 FALSE 處理)
SELECT NULL = NULL;    -- NULL (不是 TRUE)
SELECT NULL != NULL;   -- NULL
SELECT NULL > 0;       -- NULL

-- 邏輯運算
SELECT TRUE AND NULL;  -- NULL
SELECT FALSE OR NULL;  -- NULL
SELECT NOT NULL;       -- NULL

標準處理函數

1. IFNULL(expr1, expr2) — MySQL 專用

-- expr1 不為 NULL 返回 expr1,否則返回 expr2
SELECT IFNULL(NULL, 0);        -- 0
SELECT IFNULL('abc', 'def');   -- 'abc'
SELECT IFNULL(score, 0) FROM exams;  -- 成績為 NULL 視作 0 分

-- 更新場景:NULL 加 10
UPDATE t SET col = IFNULL(col, 0) + 10;

2. COALESCE(val1, val2, ...) — 標準 SQL,多參數

-- 返回第一個非 NULL 參數
SELECT COALESCE(NULL, NULL, 'first', 'second');  -- 'first'
SELECT COALESCE(mobile, email, '未填寫') AS contact FROM users;

3. NULLIF(expr1, expr2) — 相等返回 NULL

-- 防除零錯誤
SELECT 100 / NULLIF(total, 0);  -- total=0 時返回 NULL 而非報錯

-- 空字符串視作 NULL
SELECT NULLIF(username, '');

4. ISNULL(expr) — 判斷是否為 NULL(返回 1/0)

SELECT ISNULL(NULL);   -- 1
SELECT ISNULL('abc');  -- 0

實戰場景對比

場景錯誤寫法正確寫法說明
更新 NULL 為默認值UPDATE t SET c='x' WHERE c=NULLUPDATE t SET c='x' WHERE c IS NULL=NULL 永遠為 UNKNOWN
查詢非空值WHERE c != 'x'WHERE c IS NOT NULL AND c != 'x'!= 不匹配 NULL
聚合忽略 NULLAVG(score) -- 自動忽略 NULLAVG(IFNULL(score,0)) -- 視作 0 參與平均業務語義決定
排序 NULL 靠前/靠後ORDER BY cORDER BY c IS NULL, c (NULL 靠後)
ORDER BY c IS NULL DESC, c (NULL 靠前)
MySQL 默認 NULL 靠前
分組統計GROUP BY c -- NULL 為一組GROUP BY IFNULL(c,'未知')合併 NULL 到默認組

DDL 定義最佳實踐

-- 明確指定 NOT NULL + DEFAULT,避免隱式 NULL
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status TINYINT NOT NULL DEFAULT 1 COMMENT '1待支付 2已支付 3取消',
  remark VARCHAR(255) DEFAULT ''  -- 字符串列建議 DEFAULT '' 而非 NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

應用層對接建議

  • ORM 框架:實體類基本類型包裝類對應 NULL(Integer vs int)
  • MyBatis:resultMap 中 nullValue 屬性、WHERE 動態標籤自動處理 NULL
  • JSON 序列化:@JsonInclude(Include.NON_NULL) 忽略 NULL 字段
  • 前端展示:統一轉換為 ''、0、'-' 或 null,避免顯示 "null" 字符串
copyright © 萬盛學電腦網 all rights reserved