本教學將向您介紹使用可以替換NULL值的MySQL COALESCE函式。
COALESCE函式介紹下面說明了COALESCE函式語法：
COALESCE(value1,value2,...);
COALESCE函式需要許多引數，並返回第一個非NULL引數。如果所有引數都為NULL，則COALESCE函式返回NULL。
以下顯示了使用COALESCE函式的一些簡單範例：
mysql> SELECT COALESCE(NULL, 0); -- 0
SELECT COALESCE(NULL, NULL); -- NULL
+-------------------+
| COALESCE(NULL, 0) |
+-------------------+
| 0 |
+-------------------+
1 row in set
+----------------------+
| COALESCE(NULL, NULL) |
+----------------------+
| NULL |
+----------------------+
1 row in set
請參見範例資料庫(yiibai)中的以下customers表。
mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int(11) | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set
以下查詢返回orders表中所有客戶的客戶名稱，城市，州和國家。
SELECT
customerName, city, state, country
FROM
customers;
執行上面查詢語句，得到以下結果 -
+------------------------------------+-------------------+---------------+--------------+
| customerName | city | state | country |
+------------------------------------+-------------------+---------------+--------------+
| Atelier graphique | Nantes | NULL | France |
| Signal Gift Stores | Las Vegas | NV | USA |
| Australian Collectors, Co. | Melbourne | Victoria | Australia |
| La Rochelle Gifts | Nantes | NULL | France |
| Baane Mini Imports | Stavern | NULL | Norway |
************** 此處省略了一大波資料 ******************************************************
| Motor Mint Distributors Inc. | Philadelphia | PA | USA |
| Signal Collectibles Ltd. | Brisbane | CA | USA |
| Double Decker Gift Stores, Ltd | London | NULL | UK |
| Diecast Collectables | Boston | MA | USA |
| Kelly's Gift Shop | Auckland | NULL | New Zealand |
+------------------------------------+-------------------+---------------+--------------+
122 rows in set
如您所見，state列具有NULL值，因為某些此類資訊不適用於某些客戶的國家/地區。
要替換結果集中的NULL值，可以使用COALESCE函式，如下查詢所示：
SELECT
customerName, city, COALESCE(state, 'N/A'), country
FROM
customers;
執行上面查詢語句，得到以下結果 -
+------------------------------------+-------------------+------------------------+--------------+
| customerName | city | COALESCE(state, 'N/A') | country |
+------------------------------------+-------------------+------------------------+--------------+
| Atelier graphique | Nantes | N/A | France |
| Signal Gift Stores | Las Vegas | NV | USA |
| Australian Collectors, Co. | Melbourne | Victoria | Australia |
| La Rochelle Gifts | Nantes | N/A | France |
| Baane Mini Imports | Stavern | N/A | Norway |
| Mini Gifts Distributors Ltd. | San Rafael | CA | USA |
| Havel & Zbyszek Co | Warszawa | N/A | Poland |
| Blauer See Auto, Co. | Frankfurt | N/A | Germany |
************** 此處省略了一大波資料 ******************************************************
| Kremlin Collectables, Co. | Saint Petersburg | N/A | Russia |
| Raanan Stores, Inc | Herzlia | N/A | Israel |
| Iberia Gift Imports, Corp. | Sevilla | N/A | Spain |
| Motor Mint Distributors Inc. | Philadelphia | PA | USA |
| Signal Collectibles Ltd. | Brisbane | CA | USA |
| Double Decker Gift Stores, Ltd | London | N/A | UK |
| Diecast Collectables | Boston | MA | USA |
| Kelly's Gift Shop | Auckland | N/A | New Zealand |
+------------------------------------+-------------------+------------------------+--------------+
122 rows in set
在這個例子中，如果state列中的值為NULL，則COALESCE函式將用N/A字串代替。 否則，它返回state列的值。
使用COALESCE函式的另一個典型例子是當指定的一列為NULL時，將其中的值使用另一列來替換。
假設有一個具有以下結構的articles表：
USE testdb;
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
excerpt TEXT,
body TEXT NOT NULL,
published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
我們向articles表中插入一些資料。
INSERT INTO articles(title,excerpt,body)
VALUES('MySQL COALESCE Tutorial','This tutorial is about MySQL COALESCE function', 'all about COALESCE function'),
('MySQL 8.0 New Features',null, 'The following is a list of new features in MySQL 8.0');
想象一下，假設必須在概述頁面上顯示文章，其中每篇文章包含標題，摘錄和發佈日期(以及閱讀更多連結的文章頁面)。需要做的第一個任務是從文章表查詢此資料：
mysql> SELECT
id, title, excerpt, published_at
FROM
articles;
+----+-------------------------+------------------------------------------------+---------------------+
| id | title | excerpt | published_at |
+----+-------------------------+------------------------------------------------+---------------------+
| 1 | MySQL COALESCE Tutorial | This tutorial is about MySQL COALESCE function | 2017-08-10 23:46:35 |
| 2 | MySQL 8.0 New Features | NULL | 2017-08-10 23:46:35 |
+----+-------------------------+------------------------------------------------+---------------------+
2 rows in set
可以看到id=2的文章沒有摘要，顯示文章時可能沒有導讀內容了。
一個典型的解決方案是獲取文章正文中指定長度內容，用來代替顯示摘錄。這時就可以使用COALESCE函式來實現了。
SELECT
id, title, COALESCE(excerpt, LEFT(body, 150)), published_at
FROM
articles;
執行上面查詢語句，得到以下結果 -
+----+-------------------------+------------------------------------------------------+---------------------+
| id | title | COALESCE(excerpt, LEFT(body, 150)) | published_at |
+----+-------------------------+------------------------------------------------------+---------------------+
| 1 | MySQL COALESCE Tutorial | This tutorial is about MySQL COALESCE function | 2017-08-10 23:46:35 |
| 2 | MySQL 8.0 New Features | The following is a list of new features in MySQL 8.0 | 2017-08-10 23:46:35 |
+----+-------------------------+------------------------------------------------------+---------------------+
2 rows in set
在此範例中，如果excerpt列中的值為NULL，則COALESCE函式將返回oody列中內容的前150個字元。
除了使用COALESCE函式，可以使用CASE表示式實現相同的效果。
以下查詢使用CASE表示式實現與上述範例相同的結果：
SELECT
id,
title,
(CASE
WHEN excerpt IS NULL THEN LEFT(body, 150)
ELSE excerpt
END) AS excerpt,
published_at
FROM
articles;
在這個例子中，CASE表示式比使用COALESCE函式實現程式碼更長。
IFNULL函式接受兩個引數，如果不為NULL則返回第一個引數，否則返回第二個引數。
IFNULL函式有兩個引數，而COALESCE函式使用n個引數。如果引數的數量為2，則兩個函式都相同。
在本教學中，您已經學習了如何使用MySQL COALESCE函式來替換NULL值。