本教程将向您介绍使用可以替换NULL值的MySQL COALESCE函数。
MySQL 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
MySQL COALESCE函数示例
请参见示例数据库(zaixian)中的以下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个字符。
MySQL COALESCE和CASE表达式
除了使用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函数实现代码更长。
MySQL COALESCE与IFNULL对比
IFNULL函数接受两个参数，如果不为NULL则返回第一个参数，否则返回第二个参数。
IFNULL函数有两个参数，而COALESCE函数使用n个参数。如果参数的数量为2，则两个函数都相同。
在本教程中，您已经学习了如何使用MySQL COALESCE函数来替换NULL值。
上一篇：
MySQL函数
下一篇：
MySQL+Node.js连接和操作