coalesce() mysql_MySQL coalesce()函数

本教程将向您介绍使用可以替换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函数示例

请参见示例数据库(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个字符。

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值。

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。