mysql里COALESCE_MySQL 技巧：COALESCE 函数的使用

COALESCE() 函数可以接收多个参数，并返回第一个非 NULL 的参数。如果所有参数都为 NULL，则 COALESCE() 函数返回 NULL。

例如：

COALESCE(NULL, 1, 2); # => 1

COALESCE(NULL, 'hi'); # => 'hi'

COALESCE(4, NULL, 2); # => 4

COALESCE(NULL, NULL); # => NULL

使用场景

1. 设置默认值

下面的表中，discount 为 NULL 意味着 discount 为 0。

mysql> desc for_sale;

+-----------+-----------+---------------+

| name | price | discount |

+-----------+-----------+---------------+

| orange | 200 | NULL |

| apple | 100 | 23 |

| lemon | 150 | NULL |

+-----------+-----------+---------------+

计算折扣后的价格，则我们需要使用下面的 SQL：

SELECT name, price - COALESCE(discount, 0) AS real_price FROM for_sale;

因为 price – NULL = NULL，所以我们需要使用 COALESCE() 函数以便获得正确的结果。

2. 使用来自多个列的值

使用 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 函数将返回 body 列中内容的前 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 则返回第一个参数，否则返回第二个参数；而 COALESCE 函数使用 n 个参数。如果参数的数量为 2，则两个函数都相同。