MySQLcoalesce()函数

转载自  MySQL coalesce()函数

MySQL COALESCE函数介绍

下面说明了COALESCE函数语法&＃xff1a;

COALESCE(value1,value2,...);


COALESCE函数需要许多参数&＃xff0c;并返回第一个非NULL参数。如果所有参数都为NULL&＃xff0c;则COALESCE函数返回NULL。

以下显示了使用COALESCE函数的一些简单示例&＃xff1a;

mysql> SELECT COALESCE(NULL, 0); -- 0
SELECT COALESCE(NULL, NULL); -- NULL
&＃43;-------------------&＃43;
| COALESCE(NULL, 0) |
&＃43;-------------------&＃43;
| 0 |
&＃43;-------------------&＃43;
1 row in set&＃43;----------------------&＃43;
| COALESCE(NULL, NULL) |
&＃43;----------------------&＃43;
| NULL |
&＃43;----------------------&＃43;
1 row in set


MySQL COALESCE函数示例

请参见示例数据库(yiibai)中的以下customers表。

mysql> desc customers;
&＃43;------------------------&＃43;---------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;
| Field | Type | Null | Key | Default | Extra |
&＃43;------------------------&＃43;---------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;
| 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 | |
&＃43;------------------------&＃43;---------------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;
13 rows in set


以下查询返回orders表中所有客户的客户名称&＃xff0c;城市&＃xff0c;州和国家。

SELECT customerName, city, state, country
FROMcustomers;


执行上面查询语句&＃xff0c;得到以下结果 -

&＃43;------------------------------------&＃43;-------------------&＃43;---------------&＃43;--------------&＃43;
| customerName | city | state | country |
&＃43;------------------------------------&＃43;-------------------&＃43;---------------&＃43;--------------&＃43;
| 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&＃39;s Gift Shop | Auckland | NULL | New Zealand |
&＃43;------------------------------------&＃43;-------------------&＃43;---------------&＃43;--------------&＃43;
122 rows in set


如您所见&＃xff0c;state列具有NULL值&＃xff0c;因为某些此类信息不适用于某些客户的国家/地区。

要替换结果集中的NULL值&＃xff0c;可以使用COALESCE函数&＃xff0c;如下查询所示&＃xff1a;

SELECT customerName, city, COALESCE(state, &＃39;N/A&＃39;), country
FROMcustomers;


执行上面查询语句&＃xff0c;得到以下结果 -

&＃43;------------------------------------&＃43;-------------------&＃43;------------------------&＃43;--------------&＃43;
| customerName | city | COALESCE(state, &＃39;N/A&＃39;) | country |
&＃43;------------------------------------&＃43;-------------------&＃43;------------------------&＃43;--------------&＃43;
| 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&＃39;s Gift Shop | Auckland | N/A | New Zealand |
&＃43;------------------------------------&＃43;-------------------&＃43;------------------------&＃43;--------------&＃43;
122 rows in set


在这个例子中&＃xff0c;如果state列中的值为NULL&＃xff0c;则COALESCE函数将用N/A字符串代替。 否则&＃xff0c;它返回state列的值。

使用COALESCE函数的另一个典型例子是当指定的一列为NULL时&＃xff0c;将其中的值使用另一列来替换。

假设有一个具有以下结构的articles表&＃xff1a;

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(&＃39;MySQL COALESCE Tutorial&＃39;,&＃39;This tutorial is about MySQL COALESCE function&＃39;, &＃39;all about COALESCE function&＃39;),(&＃39;MySQL 8.0 New Features&＃39;,null, &＃39;The following is a list of new features in MySQL 8.0&＃39;);


想象一下&＃xff0c;假设必须在概述页面上显示文章&＃xff0c;其中每篇文章包含标题&＃xff0c;摘录和发布日期(以及链接的文章页面)。需要做的第一个任务是从文章表查询此数据&＃xff1a;

mysql> SELECT id, title, excerpt, published_at
FROMarticles;
&＃43;----&＃43;-------------------------&＃43;------------------------------------------------&＃43;---------------------&＃43;
| id | title | excerpt | published_at |
&＃43;----&＃43;-------------------------&＃43;------------------------------------------------&＃43;---------------------&＃43;
| 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 |
&＃43;----&＃43;-------------------------&＃43;------------------------------------------------&＃43;---------------------&＃43;
2 rows in set


可以看到id&＃61;2的文章没有摘要&＃xff0c;显示文章时可能没有导读内容了。

一个典型的解决方案是获取文章正文中指定长度内容&＃xff0c;用来代替显示摘录。这时就可以使用COALESCE函数来实现了。

SELECT id, title, COALESCE(excerpt, LEFT(body, 150)), published_at
FROMarticles;


执行上面查询语句&＃xff0c;得到以下结果 -

&＃43;----&＃43;-------------------------&＃43;------------------------------------------------------&＃43;---------------------&＃43;
| id | title | COALESCE(excerpt, LEFT(body, 150)) | published_at |
&＃43;----&＃43;-------------------------&＃43;------------------------------------------------------&＃43;---------------------&＃43;
| 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 |
&＃43;----&＃43;-------------------------&＃43;------------------------------------------------------&＃43;---------------------&＃43;
2 rows in set


在此示例中&＃xff0c;如果excerpt列中的值为NULL&＃xff0c;则COALESCE函数将返回oody列中内容的前150个字符。

MySQL COALESCE和CASE表达式

除了使用COALESCE函数&＃xff0c;可以使用CASE表达式实现相同的效果。

以下查询使用CASE表达式实现与上述示例相同的结果&＃xff1a;

SELECT id,title,(CASEWHEN excerpt IS NULL THEN LEFT(body, 150)ELSE excerptEND) AS excerpt,published_at
FROMarticles;


在这个例子中&＃xff0c;CASE表达式比使用COALESCE函数实现代码更长。

MySQL COALESCE与IFNULL对比

IFNULL函数接受两个参数&＃xff0c;如果不为NULL则返回第一个参数&＃xff0c;否则返回第二个参数。

IFNULL函数有两个参数&＃xff0c;而COALESCE函数使用n个参数。如果参数的数量为2&＃xff0c;则两个函数都相同。

在本教程中&＃xff0c;您已经学习了如何使用MySQL COALESCE函数来替换NULL值。