mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識(shí)點(diǎn)總結(jié)

更新時(shí)間：2019年12月31日 10:59:29 作者：luyaran

這篇文章主要介紹了mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識(shí)點(diǎn),結(jié)合實(shí)例形式總結(jié)分析了mysql中關(guān)于null的判斷、使用相關(guān)操作技巧與注意事項(xiàng),需要的朋友可以參考下

本文實(shí)例講述了mysql中null（IFNULL，COALESCE和NULLIF）相關(guān)知識(shí)點(diǎn)。分享給大家供大家參考，具體如下：

在MySQL中，NULL值表示一個(gè)未知值，它不同于0或空字符串''，并且不等于它自身。

我們?nèi)绻麑ULL值與另一個(gè)NULL值或任何其他值進(jìn)行比較，則結(jié)果為NULL，因?yàn)橐粋€(gè)不知道是什么的值(NULL值)與另一個(gè)不知道是什么的值(NULL值)比較，其值當(dāng)然也是一個(gè)不知道是什么的值(NULL值)。

然而我們通常，使用NULL值來(lái)表示數(shù)據(jù)丟失，未知或不適用的情況。例如，潛在客戶的電話號(hào)碼可能為NULL，并且可以稍后添加。所以我們創(chuàng)建表時(shí)，可以通過(guò)使用NOT NULL約束來(lái)指定列是否接受NULL值。接下來(lái)，我們來(lái)創(chuàng)建一張leads表，并且以此為依據(jù)來(lái)具體了解下：

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)
);

我們可以看出來(lái)，id是主鍵列，它不接受任何NULL值，然后first_name，last_name和source列使用NOT NULL約束，因此，不能在這些列中插入任何NULL值，而email和phone列則可接受NULL值。

所以，我們可以在insert語(yǔ)句中使用NULL值來(lái)指定數(shù)據(jù)丟失。例如，以下語(yǔ)句將一行插入到線索表中。因?yàn)殡娫捥?hào)碼丟失，所以使用NULL值：

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

因?yàn)閑mail列的默認(rèn)值為NULL，可以按照以下方式在INSERT語(yǔ)句中省略電子郵件：

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

完事如果我們要將列的值設(shè)置為NULL，可以使用賦值運(yùn)算符(=)。例如，要將David William的手機(jī)(phone)更新為NULL，請(qǐng)使用以下UPDATE語(yǔ)句：

UPDATE leads 
SET 
  phone = NULL
WHERE
  id = 3;

但是如果使用order by子句按升序?qū)Y(jié)果集進(jìn)行排序，則MySQL認(rèn)為NULL值低于其他值，因此，它會(huì)首先顯示NULL值。以下查詢語(yǔ)句按照電話號(hào)碼(phone)升序排列：

SELECT 
  *
FROM
  leads
ORDER BY phone;

執(zhí)行上面查詢語(yǔ)句，結(jié)果如下：

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

如果使用ORDER BY DESC，NULL值將顯示在結(jié)果集的最后：

SELECT 
  *
FROM
  leads
ORDER BY phone DESC;

執(zhí)行上面查詢語(yǔ)句，結(jié)果如下：

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

我們?nèi)绻诓樵冎袦y(cè)試NULL，可以在where子句中使用IS NULL或IS NOT NULL運(yùn)算符。例如，要獲得尚未提供電話號(hào)碼的潛在客戶，請(qǐng)使用IS NULL運(yùn)算符，如下所示：

SELECT 
  *
FROM
  leads
WHERE
  phone IS NULL;

執(zhí)行上面查詢語(yǔ)句，結(jié)果如下：

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

我們還可以使用IS NOT運(yùn)算符來(lái)獲取所有提供電子郵件地址的潛在客戶：

SELECT 
  *
FROM
  leads
WHERE
  email IS NOT NULL;

執(zhí)行上面查詢語(yǔ)句，結(jié)果如下：

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

然而，即使NULL不等于NULL，GROUP BY子句中視兩個(gè)NULL值相等，來(lái)看下sql實(shí)例：

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

該查詢只返回兩行，因?yàn)槠溧]箱(email)列為NULL的行被分組為一行，結(jié)果如下所示：

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

我們要知道在列上使用唯一約束或UNIQUE索引時(shí)，可以在該列中插入多個(gè)NULL值，在這種情況下，MySQL認(rèn)為NULL值是不同的。接下來(lái)我們通過(guò)為phone列創(chuàng)建一個(gè)UNIQUE索引來(lái)驗(yàn)證這一點(diǎn)：

CREATE UNIQUE INDEX idx_phone ON leads(phone);

這里我們要注意，如果使用BDB存儲(chǔ)引擎的話，mysql會(huì)認(rèn)為NULL值相等，因此我們不能將多個(gè)NULL值插入到具有唯一約束的列中。

既然知道了null的好處和壞處，我們就來(lái)看下在mysql中應(yīng)該如何處理它吧。mysql一共提供了三個(gè)函數(shù)，分別是IFNULL，COALESCE和NULLIF。

我們來(lái)分別看下，首先，IFNULL函數(shù)接受兩個(gè)參數(shù)。如果IFNULL函數(shù)不為NULL，則返回第一個(gè)參數(shù)，否則返回第二個(gè)參數(shù)。例如，如果不是NULL，則以下語(yǔ)句返回電話號(hào)碼(phone)，否則返回N/A，而不是NULL。來(lái)看個(gè)實(shí)例：

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

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果：

+----+------------+-----------+----------------+
| 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函數(shù)，它接受參數(shù)列表，并返回第一個(gè)非NULL參數(shù)。例如，可以使用COALESCE函數(shù)根據(jù)信息的優(yōu)先級(jí)按照以下順序顯示線索的聯(lián)系信息：phone, email和N/A。以下是案例：

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

執(zhí)行上面查詢語(yǔ)句，得到以下代碼：

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

最后就是NULLIF函數(shù)了，它接受兩個(gè)參數(shù)。如果兩個(gè)參數(shù)相等，則NULLIF函數(shù)返回NULL。否則，它返回第一個(gè)參數(shù)。在列中同時(shí)具有NULL和空字符串值時(shí)，NULLIF函數(shù)很有用。例如，我們錯(cuò)誤地將以下行插入到leads表中：

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

因?yàn)閜hone是一個(gè)空字符串:''，而不是NULL。所以，如果我們想獲得潛在客戶的聯(lián)系信息，則最終得到空phone，而不是電子郵件，如下所示：

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

執(zhí)行上面查詢語(yǔ)句，得到以下代碼：

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

我們?nèi)绻鉀Q這個(gè)問(wèn)題，就要使用NULLIF函數(shù)將電話與空字符串('')進(jìn)行比較，如果相等，則返回NULL，否則返回電話號(hào)碼：

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

執(zhí)行上面查詢語(yǔ)句，得到以下代碼：

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

好啦，本次記錄就到這里了。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

您可能感興趣的文章: