MySQL 整數運算遇 NULL 傳染陷阱：IFNULL 最佳實務與表設計建議

MySQL 整數運算遇 NULL 傳染陷阱：IFNULL 最佳實務與表設計建議

在撰寫報表或統計查詢時，整數欄位參與四則運算若遇 NULL，結果會直接變成 NULL（NULL 具有「傳染性」）。這是導致財務報表、庫存試算錯誤的常見隱形殺手。本文透過實測案例示範問題、提供標準解法，並給出表設計與查詢撰寫的雙重防禦建議。

1. 問題現場重現

假設表 test_table 有兩個可為 NULL 的整數欄位 total、used：

id | total | used
---|-------|-----
1  | 100   | 20
2  | NULL  | 10
3  | 50    | NULL
4  | NULL  | NULL

直接相減

SELECT id, total, used, (total - used) AS have FROM test_table;

結果

id | total | used | have
---|-------|------|-----
1  | 100   | 20   | 80
2  | NULL  | 10   | NULL   -- 期望 90，實得 NULL
3  | 50    | NULL | NULL   -- 期望 50，實得 NULL
4  | NULL  | NULL | NULL

結論：任一運算元為 NULL，整個運算式結果即為 NULL。

2. 標準解法：IFNULL / COALESCE

寫法一：IFNULL（MySQL 專用，兩參數）

SELECT
  id,
  IFNULL(total, 0) AS total,
  IFNULL(used, 0) AS used,
  (IFNULL(total, 0) - IFNULL(used, 0)) AS have
FROM test_table;

寫法二：COALESCE（ANSI 標準，支援多參數）

SELECT
  id,
  COALESCE(total, 0) AS total,
  COALESCE(used, 0) AS used,
  (COALESCE(total, 0) - COALESCE(used, 0)) AS have
FROM test_table;

修正後結果

id | total | used | have
---|-------|------|-----
1  | 100   | 20   | 80
2  | 0     | 10   | -10   -- 視業務邏輯決定是否合理
3  | 50    | 0    | 50
4  | 0     | 0    | 0

3. 雙重防禦：表設計 + 查詢習慣

層級一：表設計階段預防（根治）

• 凡涉及數值運算的欄位，預設值設為 0，並加 NOT NULL：

CREATE TABLE test_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  total INT NOT NULL DEFAULT 0,
  used  INT NOT NULL DEFAULT 0
);

優點：從源頭消除 NULL，查詢碼簡潔，效能最佳（無需函數呼叫）。

層級二：查詢撰寫階段防禦（補救）

• 無法修改表結構（如第三方系統、歷史資料）時，所有整數運算欄位一律包裹 IFNULL/COALESCE。
• 建立團隊 Code Review 清單項目：「數值運算是否處理 NULL？」

4. 擴展：其他聚合函數的 NULL 行為

只有純量運算子（+ - * /）會讓 NULL 傳染，聚合函數多為忽略 NULL，但最終結果仍可能為 NULL，需視業務再包裹 IFNULL(SUM(x),0)。

5. 實戰提醒

「一份財務報表若因 NULL 導致試算不平，後果遠比修補查詢成本高昂。」

• 新建表：數值欄位預設 NOT NULL DEFAULT 0。
• 舊表：加欄位 DEFAULT 0 並 UPDATE SET col=0 WHERE col IS NULL，再 ALTER ... NOT NULL。
• 查詢：養成 COALESCE(col, 0) 肌肉記憶。

將 NULL 視為「未知」而非「零」，並在邊界處理上建立統一規範，是資料品質管理的關鍵一環。