萬盛學電腦網

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

SQL ISNULL/COALESCE/IFNULL/NVL 跨資料庫空值處理函數完整對比指南

空值(NULL)處理是 SQL 查詢中的高頻需求。各主流資料庫對空值處理函數的實現存在顯著差異。本文系統對比 SQL Server、Oracle、MySQL、PostgreSQL、Access 五大資料庫的函數語義、語法與最佳實踐。

核心函數語義對比表

資料庫雙參數函數多參數函數標準 SQL 函數語義
SQL ServerISNULL(expr, replacement)❌COALESCE(expr1, expr2, ...)expr 為 NULL 返回 replacement
MySQLIFNULL(expr, replacement)❌COALESCE(expr1, expr2, ...)expr 為 NULL 返回 replacement
OracleNVL(expr, replacement)❌COALESCE(expr1, expr2, ...)expr 為 NULL 返回 replacement
PostgreSQL❌❌COALESCE(expr1, expr2, ...)返回第一個非 NULL 參數
AccessISNULL(expr, replacement)❌❌expr 為 NULL 返回 replacement
SQLite❌❌COALESCE(expr1, expr2, ...)返回第一個非 NULL 參數

關鍵差異深度解析

1. ISNULL 的雙重含義陷阱

  • SQL Server/Access:ISNULL(expr, val) = 空值替換函數
  • MySQL/PostgreSQL/標準 SQL:ISNULL(expr) = 判斷函數,返回 1/0 (TRUE/FALSE)
  • MySQL 陷阱:SELECT ISNULL(NULL); -- 返回 1,非預期的替換值

2. COALESCE:ANSI SQL 標準通用解法

-- 所有主流資料庫均支援
COALESCE(expr1, expr2, expr3, ..., default_val)
-- 返回第一個非 NULL 的表達式值,全 NULL 返回 default_val
  • ✅ 可變參數,支援鏈式備選
  • ✅ 標準 SQL,可移植性最強
  • ⚠️ 類型推斷規則:返回類型為所有參數的「最高優先級類型」

3. 類型推斷與隱性轉換陷阱

-- MySQL: 參數類型不一致時的隱性轉換
SELECT COALESCE(NULL, 1, 'abc');  -- 返回 '1' (字串,因 'abc' 優先級高)
SELECT COALESCE(NULL, 1, 2.5);    -- 返回 2.5 (DECIMAL)
SELECT COALESCE(NULL, 'abc', 1);  -- 返回 'abc' (字串)

-- SQL Server ISNULL: 返回類型強制跟隨第一個參數
SELECT ISNULL(NULL, 'abc');       -- 返回 'abc' (VARCHAR)
SELECT ISNULL(NULL, 1);           -- 返回 1 (INT)
-- 若第一參數為 INT,第二參數為 VARCHAR 會報錯或截斷

實戰應用模式

模式一:數值運算中的 NULL 安全

-- 需求:單價 * (庫存 + 訂單中),訂單中可能為 NULL
-- SQL Server / Access
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products;

-- Oracle
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products;

-- MySQL / PostgreSQL / SQLite / 標準 SQL
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;
-- 或 MySQL 專用
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

模式二:字串拼接 NULL 安全

-- 標準 SQL (|| 操作符遇 NULL 則全 NULL)
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM users;

-- MySQL (CONCAT 忽略 NULL)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;  -- NULL 參數會導致整體 NULL
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name FROM users;

-- SQL Server (+ 操作符遇 NULL 則全 NULL)
SELECT ISNULL(first_name, '') + ' ' + ISNULL(last_name, '') AS full_name FROM users;

模式三:動態預設值鏈

-- 依優先級取值:用戶設定 → 部門預設 → 系統預設 → 硬編碼預設
SELECT COALESCE(
    user_pref.theme,
    dept_pref.theme,
    sys_pref.theme,
    'light'
) AS effective_theme
FROM users u
LEFT JOIN prefs user_pref ON u.id = user_pref.user_id
LEFT JOIN prefs dept_pref ON u.dept_id = dept_pref.dept_id
CROSS JOIN (SELECT theme FROM prefs WHERE scope='system') sys_pref;

模式四:條件聚合中的 NULL 處理

SELECT 
    category,
    AVG(COALESCE(price, 0)) AS avg_price_with_zero,      -- NULL 視為 0
    AVG(NULLIF(price, 0)) AS avg_price_exclude_zero,     -- 0 視為 NULL
    SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS null_price_count
FROM products
GROUP BY category;

效能與最佳實踐

  1. 優先使用 COALESCE:標準化、可移植、多參數靈活
  2. 避免在 WHERE/JOIN 條件中使用函數包裹欄位:WHERE ISNULL(col, '') = '' 導致索引失效,改寫為 WHERE col IS NULL OR col = ''
  3. 數值運算預設值用 0,字串用空串,日期用 '1970-01-01' 或極值
  4. 關鍵業務欄位定義 NOT NULL DEFAULT 'default',從 schema 層面消除 NULL
  5. 區分「未知」(NULL) 與「無/空」(0/空串) 的業務語義,勿濫用 COALESCE 掩蓋資料品質問題

MySQL 專屬:NULL 安全比較運算符

<=>  -- NULL-safe equal: NULL <=> NULL 為 TRUE, NULL <=> 1 為 FALSE
SELECT * FROM t1 WHERE col <=> 'value';  -- 等同於 (col = 'value' OR (col IS NULL AND 'value' IS NULL))

跨資料庫開發時,建立「空值處理標準庫」:封裝 nvl()、coalesce() 等統一介面,底層根據方言路由至對應函數,實現 SQL 可移植性與業務邏輯分離。

copyright © 萬盛學電腦網 all rights reserved