MySQL NULL詳細和應用

在本教學中，您將學習如何使用MySQL NULL值。此外，您將學習一些有用的函數來有效地處理NULL值。

如果不能理解和使用資料庫中NULL值，那麼可以認為您的資料庫學習最多算剛入門水準。

MySQL NULL值簡介

在MySQL中，NULL值表示一個未知值。 NULL值不同於0或空字元串''。

NULL值不等於它自身。如果將NULL值與另一個NULL值或任何其他值進行比較，則結果為NULL，因為一個不知道是什麼的值(NULL值)與另一個不知道是什麼的值(NULL值)比較，其值當然也是一個不知道是什麼的值(NULL值)。

通常，使用NULL值來表示數據丟失，未知或不適用的情況。例如，潛在客戶的電話號碼可能為NULL，並且可以稍後添加。

創建表時，可以通過使用NOT NULL約束來指定列是否接受NULL值。

例如，以下語句用於創建一張leads表：

USE testdb;
CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    source VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(25)
);

因此，id是主鍵列，它不接受任何NULL值。

first_name，last_name和source列使用NOT NULL約束，因此，不能在這些列中插入任何NULL值，而email和phone列則可接受NULL值。

您可以在INSERT語句中使用NULL值來指定數據丟失。例如，以下語句將一行插入到線索表中。因為電話號碼丟失，所以使用NULL值。

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@xuhuhu.com',NULL);

因為email列的默認值為NULL，可以按照以下方式在INSERT語句中省略電子郵件：

INSERT INTO leads(first_name,last_name,source,phone)
VALUES('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');

UPDATE語句中的MySQL SET NULL值

要將列的值設置為NULL，可以使用賦值運算符(=)。例如，要將David William的手機(phone)更新為NULL，請使用以下UPDATE語句：

UPDATE leads
SET
    phone = NULL
WHERE
    id = 3;

MySQL ORDER BY為NULL

如果使用ORDER BY子句按昇冪對結果集進行排序，則MySQL認為NULL值低於其他值，因此，它會首先顯示NULL值。

以下查詢語句按照電話號碼(phone)昇冪排列。如下所示 -

SELECT
    *
FROM
    leads
ORDER BY phone;

執行上面查詢語句，結果如下 -

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source       | email               | phone          |
+----+------------+-----------+--------------+---------------------+----------------+
|  1 | John       | Doe       | Web Search   | john.doe@xuhuhu.com | NULL           |
|  3 | David      | William   | Web Search   | NULL                | NULL           |
|  2 | Lily       | Bush      | Cold Calling | NULL                | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

如果使用ORDER BY DESC，NULL值將顯示在結果集的最後。請參閱以下示例：

SELECT
    *
FROM
    leads
ORDER BY phone DESC;

執行上面查詢語句，結果如下 -

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source       | email               | phone          |
+----+------------+-----------+--------------+---------------------+----------------+
|  2 | Lily       | Bush      | Cold Calling | NULL                | (408)-555-1234 |
|  1 | John       | Doe       | Web Search   | john.doe@xuhuhu.com | NULL           |
|  3 | David      | William   | Web Search   | NULL                | NULL           |
+----+------------+-----------+--------------+---------------------+----------------+
3 rows in set

要在查詢中測試NULL，可以在WHERE子句中使用IS NULL或IS NOT NULL運算符。

例如，要獲得尚未提供電話號碼的潛在客戶，請使用IS NULL運算符，如下所示：

SELECT
    *
FROM
    leads
WHERE
    phone IS NULL;

執行上面查詢語句，結果如下 -

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source     | email               | phone |
+----+------------+-----------+------------+---------------------+-------+
|  1 | John       | Doe       | Web Search | john.doe@xuhuhu.com | NULL  |
|  3 | David      | William   | Web Search | NULL                | NULL  |
+----+------------+-----------+------------+---------------------+-------+
2 rows in set

可以使用IS NOT運算符來獲取所有提供電子郵件地址的潛在客戶。

SELECT
    *
FROM
    leads
WHERE
    email IS NOT NULL;

執行上面查詢語句，結果如下 -

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source     | email               | phone |
+----+------------+-----------+------------+---------------------+-------+
|  1 | John       | Doe       | Web Search | john.doe@xuhuhu.com | NULL  |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

即使NULL不等於NULL，GROUP BY子句中視兩個NULL值相等。

SELECT
    email, count(*)
FROM
    leads
GROUP BY email;

該查詢只返回兩行，因為其郵箱(email)列為NULL的行被分組為一行，結果如下所示 -

+---------------------+----------+
| email               | count(*) |
+---------------------+----------+
| NULL                |        2 |
| john.doe@xuhuhu.com |        1 |
+---------------------+----------+
2 rows in set

MySQL NULL和UNIQUE索引

在列上使用唯一約束或UNIQUE索引時，可以在該列中插入多個NULL值。這是非常好的，因為在這種情況下，MySQL認為NULL值是不同的。

我們通過為phone列創建一個UNIQUE索引來驗證這一點。

CREATE UNIQUE INDEX idx_phone ON leads(phone);

請注意，如果使用BDB存儲引擎，MySQL認為NULL值相等，因此您不能將多個NULL值插入到具有唯一約束的列中。

MySQL NULL函數

MySQL提供了幾個有用的功能，很好地處理空值：IFNULL，COALESCE和NULLIF。

IFNULL函數接受兩個參數。如果IFNULL函數不為NULL，則返回第一個參數，否則返回第二個參數。

例如，如果不是NULL，則以下語句返回電話號碼(phone)，否則返回N/A，而不是NULL。

SELECT
    id, first_name, last_name, IFNULL(phone, 'N/A') phone
FROM
    leads;

執行上面查詢語句，得到以下結果 -

+----+------------+-----------+----------------+
| id | first_name | last_name | phone          |
+----+------------+-----------+----------------+
|  1 | John       | Doe       | N/A            |
|  2 | Lily       | Bush      | (408)-555-1234 |
|  3 | David      | William   | N/A            |
+----+------------+-----------+----------------+
3 rows in set

COALESCE函數接受參數列表，並返回第一個非NULL參數。例如，可以使用COALESCE函數根據資訊的優先順序按照以下順序顯示線索的聯繫資訊：phone, email和N/A。

SELECT
    id,
    first_name,
    last_name,
    COALESCE(phone, email, 'N/A') contact
FROM
    leads;

執行上面查詢語句，得到以下代碼 -

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact             |
+----+------------+-----------+---------------------+
|  1 | John       | Doe       | john.doe@xuhuhu.com |
|  2 | Lily       | Bush      | (408)-555-1234      |
|  3 | David      | William   | N/A                 |
+----+------------+-----------+---------------------+
3 rows in set

NULLIF函數接受兩個參數。如果兩個參數相等，則NULLIF函數返回NULL。否則，它返回第一個參數。

在列中同時具有NULL和空字元串值時，NULLIF函數很有用。例如，錯誤地，您將以下行插入到leads表中：

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','thierry.henry@xuhuhu.com','');

phone是一個空字元串:''，而不是NULL。

如果您想獲得潛在客戶的聯繫資訊，則最終得到空phone，而不是電子郵件，如下所示：

SELECT
    id,
    first_name,
    last_name,
    COALESCE(phone, email, 'N/A') contact
FROM
    leads;

執行上面查詢語句，得到以下代碼 -

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact             |
+----+------------+-----------+---------------------+
|  1 | John       | Doe       | john.doe@xuhuhu.com |
|  2 | Lily       | Bush      | (408)-555-1234      |
|  3 | David      | William   | N/A                 |
|  4 | Thierry    | Henry     |                     |
+----+------------+-----------+---------------------+

要解決這個問題，您可以使用NULLIF函數將電話與空字元串('')進行比較，如果相等，則返回NULL，否則返回電話號碼。

SELECT
    id,
    first_name,
    last_name,
    COALESCE(NULLIF(phone, ''), email, 'N/A') contact
FROM
    leads;

執行上面查詢語句，得到以下代碼 -

+----+------------+-----------+--------------------------+
| id | first_name | last_name | contact                  |
+----+------------+-----------+--------------------------+
|  1 | John       | Doe       | john.doe@xuhuhu.com      |
|  2 | Lily       | Bush      | (408)-555-1234           |
|  3 | David      | William   | N/A                      |
|  4 | Thierry    | Henry     | thierry.henry@xuhuhu.com |
+----+------------+-----------+--------------------------+
4 rows in set

在本教學中，您已經學習了如何使用MySQL NULL值，以及如何使用一些方便的函數來處理查詢中的NULL。

上一篇： MySQL技巧下一篇： MySQL存儲過程

MySQL NULL值簡介

UPDATE語句中的MySQL SET NULL值

MySQL ORDER BY為NULL

MySQL NULL和UNIQUE索引

MySQL NULL函數

HTML / CSS

腳本語言

高級語言

Java技術

XML技術

大數據

開發工具

框架

軟體測試

前端技術

資料庫

其他技術