mysql有關null（IFNULL，COALESCE和NULLIF）的知識各位看官要不要了解下

在MySQL中，

NULL

值表示一個未知值，它不同于

或空字元串

''

，并且不等于它自身。

我們如果将

NULL

值與另一個

NULL

值或任何其他值進行比較，則結果為

NULL

，因為一個不知道是什麼的值(

NULL

值)與另一個不知道是什麼的值(

NULL

值)比較，其值當然也是一個不知道是什麼的值(

NULL

值)。

然而我們通常，使用

NULL

值來表示資料丢失，未知或不适用的情況。例如，潛在客戶的電話号碼可能為

NULL

，并且可以稍後添加。是以我們建立表時，可以通過使用

NOT NULL

限制來指定列是否接受

NULL

值。接下來，我們來建立一張

leads

表，并且以此為依據來具體了解下：

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','[email protected]',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');

完事如果我們要将列的值設定為

NULL

，可以使用指派運算符(

)。例如，要将

David William

的手機(

phone

)更新為

NULL

，請使用以下

UPDATE

語句：

UPDATE leads 
SET 
    phone = NULL
WHERE
    id = 3;

但是如果使用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   | [email protected] | 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   | [email protected] | 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 | [email protected] | 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 | [email protected] | NULL  |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

然而，即使

NULL

不等于

NULL

，

GROUP BY

子句中視兩個

NULL

值相等，來看下sql執行個體：

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

該查詢隻傳回兩行，因為其郵箱(

email

)列為

NULL

的行被分組為一行，結果如下所示：

+---------------------+----------+
| email               | count(*) |
+---------------------+----------+
| NULL                |        2 |
| [email protected] |        1 |
+---------------------+----------+
2 rows in set

我們要知道在列上使用唯一限制或

UNIQUE

索引時，可以在該列中插入多個

NULL

值，在這種情況下，MySQL認為

NULL

值是不同的。接下來我們通過為

phone

列建立一個

UNIQUE

索引來驗證這一點：

CREATE UNIQUE INDEX idx_phone ON leads(phone);

這裡我們要注意，如果使用BDB存儲引擎的話，mysql會認為

NULL

值相等，是以我們不能将多個

NULL

值插入到具有唯一限制的列中。

既然知道了null的好處和壞處，我們就來看下在mysql中應該如何處理它吧。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       | [email protected] |
|  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','[email protected]','');

因為

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       | [email protected] |
|  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       | [email protected]      |
|  2 | Lily       | Bush      | (408)-555-1234           |
|  3 | David      | William   | N/A                      |
|  4 | Thierry    | Henry     | [email protected] |
+----+------------+-----------+--------------------------+
4 rows in set

好啦，本次記錄就到這裡了。如果感覺不錯的話，請多多點贊支援哦。。。

mysql有關null（IFNULL，COALESCE和NULLIF）的知識各位看官要不要了解下

繼續閱讀

轉換字元串成ＩＮＴ６４

c寫檔案

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Android – ListView 中添加按鈕，動态删除添加ItemView的操作

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案