MySQL & MariaDB的世界

MySQL 的 NULL 判斷方法完全指南：SQL 中正確處理 NULL 的技巧與實務範例

SQL基礎
目次

1. 簡介：MySQL 中 NULL 判定的重要性

什麼是 NULL？

MySQL 中的 NULL 表示資料「不存在」。NULL 不同於「空白」或「零」，它用來表示資料庫中不確定的值。NULL 表示尚未輸入值或資料缺失的狀態，因此在資料庫設計和查詢操作中特別需要注意。

例如，在處理客戶資訊的資料庫中，如果「電話號碼」的欄位為 NULL，則表示該客戶尚未提供電話號碼或尚未輸入。NULL 常被誤解為「空」，但實際上它與空白字元或零有不同的特殊含義。

NULL 判定的重要性

如果處理 NULL 時出錯，資料庫的查詢可能無法如預期般運作。例如，在 SQL 中設定條件時，如果未適當判斷 NULL 就使用運算子，可能會返回錯誤的結果。這可能導致預期外的錯誤或問題，因此正確理解並適當使用 NULL 判定非常重要。

例如，讓我們考慮以下的 SQL 語句。

SELECT * FROM customers WHERE phone_number = NULL;

此查詢不會返回預期的結果。因為 NULL 無法使用「等於」運算子進行比較。判斷 NULL 的方法需要使用特殊的運算子。

如果判斷 NULL 時出錯，不僅會影響資料擷取，還會涉及資料的完整性和可靠性。因此，理解如何在 SQL 中正確處理 NULL 是資料庫操作中不可或缺的。

2. NULL 判定的基礎：MySQL 中應使用的運算子

IS NULL 和 IS NOT NULL 的基礎

MySQL 中，為了判斷 NULL，不能使用 =（等於）或 <>（不等）等運算子。相反地，使用 IS NULL 和 IS NOT NULL 這些運算子。

  • IS NULL：判斷欄位的值是否為 NULL。
  • IS NOT NULL：判斷欄位的值不是 NULL。

例如，在客戶資料庫中搜尋「電話號碼」為 NULL 的客戶時，可以這樣描述。

SELECT * FROM customers WHERE phone_number IS NULL;

此查詢會返回所有 phone_number 為 NULL 的客戶。相反地，要搜尋不是 NULL 的情況，可以這樣使用 IS NOT NULL。

SELECT * FROM customers WHERE phone_number IS NOT NULL;

因此，處理 NULL 時，基本上必須使用 IS NULL 或 IS NOT NULL。

NULL 與其他值（空字串、零）的差異

NULL 和空字串（''）或零（0）雖然外觀相似，但在資料庫中具有不同的意義。

  • NULL：表示值不存在，或未知的值。
  • 空字串（''）：長度為 0 的字串，表示資料存在但為空。
  • 零（0）：表示數值的零，意指值為「零」。

例如，有以下這樣的查詢。

SELECT * FROM products WHERE price = 0;

此查詢會搜尋「價格為零的產品」，但不會搜尋 NULL 的價格。要搜尋包含 NULL 價格的產品，需要使用 IS NULL。

SELECT * FROM products WHERE price IS NULL;

理解此差異是正確進行 NULL 判定的第一步。

3. NULL 與其他資料類型的比較: 容易忽略的要點

NULL 與空字串、零的差異

MySQL 中處理 NULL 時，容易將空字串或零與 NULL 混淆，但實際上它們是不同的概念。NULL 表示「值不存在」，空字串表示「空的字串」，零表示「數值的零」。

  • NULL: 表示資料不存在，或不明狀態。
  • 空字串（''）: 表示存在長度為 0 的字串。
  • 零（0）: 表示數值為零。

例如，考慮以下將 NULL 與空字串比較的情況。

SELECT * FROM users WHERE name = '';

此查詢會返回名稱為空字串的使用者。然而，若要取得名稱為 NULL 的使用者，則需如此撰寫。

SELECT * FROM users WHERE name IS NULL;

因此，NULL 與空字串需視為不同的東西來處理。

NULL 與 FALSE 的差異

NULL 與 FALSE 也常被混淆，但它們也不同。FALSE 表示「邏輯上的偽」，NULL 表示「沒有值」。

例如，在以下條件中處理 NULL 與 FALSE 時，需注意結果會不同。

SELECT * FROM users WHERE is_active = FALSE;

此查詢會返回「不活躍」的使用者，但 is_active 為 NULL 的情況不會包含在搜尋結果中。若要包含 NULL 進行搜尋，則需新增以下條件。

SELECT * FROM users WHERE is_active IS NULL OR is_active = FALSE;

由於 NULL 與 FALSE 的意義不同，因此在 SQL 查詢中需適當區分使用。

4. 實務中實用的 NULL 判斷：活用於查詢的技巧

在多個欄位中判斷 NULL

在實務中，經常會有包含多個欄位為 NULL 的情況。例如，在管理客戶資訊的表格中，「電話號碼」或「電子郵件地址」可能為 NULL 的情況，需要對多個欄位進行 NULL 判斷。

例如，想要搜尋客戶的電話號碼或電子郵件地址為 NULL 的客戶時，可以這樣撰寫。

SELECT * FROM customers
WHERE phone_number IS NULL OR email IS NULL;

此查詢會擷取電話號碼或電子郵件地址其中一者為 NULL 的客戶。相反地，若要搜尋電話號碼及電子郵件地址皆非 NULL 的客戶，則使用AND運算子。

SELECT * FROM customers
WHERE phone_number IS NOT NULL AND email IS NOT NULL;

對多個欄位進行 NULL 判斷，是能夠靈活撰寫 SQL 查詢的重要技術。

使用考慮 NULL 的彙總函數

在彙總包含 NULL 的資料時，通常的彙總函數（如COUNT或SUM等）會忽略 NULL 值，因此需要特別處理。例如，COUNT(*)會包含 NULL 值進行計數，但COUNT(column_name)會排除 NULL 值進行計數。

例如，在商品表格中，想要排除庫存數量為 NULL 的商品來彙總銷售金額時，可以這樣撰寫。

SELECT SUM(sales_amount) 
FROM products 
WHERE stock_quantity IS NOT NULL;

另外，若要將 NULL 值包含在彙總結果中，可以使用COALESCE函數將 NULL 取代為特定值。例如，若想將 NULL 視為 0 來處理時，可以這樣描述。

SELECT COALESCE(SUM(sales_amount), 0) 
FROM products;

活用 NULL 的條件分歧

透過使用 SQL 的CASE文，可以對包含 NULL 的資料進行條件分歧。例如，針對商品的庫存為 NULL 的情況顯示「不明」，若有庫存數量則顯示該數值的查詢，讓我們來撰寫看看。

SELECT product_name,
       CASE
           WHEN stock_quantity IS NULL THEN '不明'
           ELSE stock_quantity
       END AS stock_status
FROM products;

此查詢中，庫存數量為 NULL 的情況會顯示「不明」，其他情況則會直接顯示庫存數量。透過CASE文，可以對包含 NULL 的資料進行靈活的顯示。

5. 處理 NULL 時的最佳實務

資料設計時將 NULL 使用量最小化

處理 NULL 值時最重要的點是，在資料設計時將使用 NULL 的情況最小化。在資料庫設計階段，盡可能避免使用 NULL，並對必須值的欄位附加NOT NULL限制，這是推薦的做法。

例如，在客戶資料表的「姓名」或「地址」等，必須資訊不會變成 NULL 的設計非常重要。對必要的欄位附加NOT NULL限制，對可能輸入 NULL 的欄位明確設計為允許 NULL。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15),
    email VARCHAR(100)
);

像這樣，透過在name欄位附加NOT NULL限制，即可保證客戶資料的「姓名」一定會被輸入。

維持資料的完整性

即使是允許 NULL 的欄位，設定適當的預設值也很重要。為了維持資料的完整性，請考慮設定有意義的值，如「未設定」或「0」，而非 NULL。

例如，即使商品的「發售日期」欄位允許 NULL，在未設定的情況下，設定預設值如「1900-01-01」等，即可防止因 NULL 導致的整合問題。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    release_date DATE DEFAULT '1900-01-01'
);

像這樣，透過設定有意義的預設值取代 NULL，即可維持資料的完整性，並使後續的 NULL 判斷更容易。

效能的最佳化

使用大量 NULL 的查詢可能會影響效能。特別是，對包含大量 NULL 的欄位頻繁使用IS NULL或IS NOT NULL進行判斷時，需要最佳化索引。在包含大量 NULL 值的欄位上建立索引，可能導致搜尋效能降低，因此最佳化索引設計非常重要。

6. FAQ：解答常見的 NULL 相關疑問

Q1: 使用=運算子比較 NULL 也不會發生錯誤嗎？

A1: 否，雖然不會發生錯誤，但使用=或<>運算子比較 NULL 是不正確的。NULL 表示「未知的值」，因此一般的比較運算子不會如預期般運作。要判斷 NULL，請使用IS NULL或IS NOT NULL。

Q2: 如何彙總包含 NULL 的資料？

A2: 彙總包含 NULL 的資料時，可以使用COALESCE函數將 NULL 替換為預設值（例如 0），或在條件中加入IS NULL。這樣即使包含 NULL 也能精確彙總。

Q3: 在資料庫中儲存 NULL 值時有什麼注意事項？

A3: NULL 值表示「資料不存在」，因此使用時必須清楚理解其意義，並僅在必要時使用。如果不必要地大量使用 NULL，可能會使資料解釋變得困難。

Q4: 可以對包含 NULL 的欄位使用索引嗎？

A4: 是的，可以對包含 NULL 的欄位使用索引，但如果 NULL 值很多，索引的效率可能會降低。特別是當經常進行IS NULL或IS NOT NULL的搜尋時，需要適當設計索引。

7. 總結：正確使用 NULL 判定的要點

在 MySQL 中適當處理 NULL 是準確且高效運作資料庫不可或缺的技能。NULL 表示「不存在的資料」，具有與其他值不同的特殊意義。要正確進行 NULL 判定，必須使用 IS NULL 或 IS NOT NULL，並從資料設計階段開始意識到 NULL 的處理方式，這一點很重要。

在實務中，需要運用技巧來有效使用包含 NULL 的查詢或彙總，以維持資料的完整性和效能。例如，將 NULL 值替換為特定值的 COALESCE，或結合 NULL 判定的彈性查詢設計，非常有用。

透過正確判定 NULL 並適當活用，可以大幅提升 SQL 查詢的精準度和效率。請活用本文介紹的技術，減少資料庫操作的問題，目標是實現更高可靠性的資料運作。