如何使用 MySQL COALESCE 函数替换 NULL 值

摘要：本教程向您介绍 MySQL COALESCE 函数，该函数允许您替换 NULL 值。

MySQL COALESCE函数简介

下面说明了COALESCE函数语法：

COALESCE(value1,value2,...);
Code language: SQL (Structured Query Language) (sql)

COALESCE函数接受多个参数并返回第一个非 NULL 参数。如果所有参数均为 NULL， COALESCE函数将返回 NULL。

下面显示了使用COALESCE函数的一些简单示例：

SELECT COALESCE(NULL, 0);  -- 0
SELECT COALESCE(NULL, NULL); -- NULL;
Code language: SQL (Structured Query Language) (sql)

MySQL COALESCE 函数示例

请参阅示例数据库中的以下customers表。

以下查询返回customers表中所有客户的客户名称、城市、州和国家/地区。

SELECT 
    customerName, city, state, country
FROM
    customers;
Code language: SQL (Structured Query Language) (sql)

如您所见，州列具有 NULL 值，因为某些信息不适用于某些客户所在的国家/地区。

要替换结果集中的 NULL 值，可以使用COALESCE函数，如下所示：

SELECT 
    customerName, city, COALESCE(state, 'N/A'), country
FROM
    customers;
Code language: SQL (Structured Query Language) (sql)

在此示例中，如果 state 列中的值为 NULL， COALESCE函数将用N/A字符串替换它。否则，它返回状态列的值。

使用COALESCE函数的另一个典型示例是，当第一列为 NULL 时，将一列中的值替换为另一列中的值。

假设您有一个具有以下结构的articles表：

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
);
Code language: SQL (Structured Query Language) (sql)

让我们向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');
Code language: SQL (Structured Query Language) (sql)

想象一下，您必须在概述页面上显示文章，其中每篇文章都包含标题、专家和发布日期（以及文章页面的阅读更多链接）。您需要做的第一个任务是从articles表中查询此数据：

SELECT 
    id, title, excerpt, published_at
FROM
    articles;   
Code language: SQL (Structured Query Language) (sql)

正如您所看到的，id 为 2 的文章没有摘录，这不利于显示。

典型的解决方案是获取文章body中的前几个字符以显示为摘录。这就是COALESCE函数发挥作用的原因。

SELECT 
    id, title, COALESCE(excerpt, LEFT(body, 150)), published_at
FROM
    articles;
Code language: SQL (Structured Query Language) (sql)

在此示例中，如果摘录列中的值为 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;
Code language: SQL (Structured Query Language) (sql)

在此示例中， CASE表达式比使用COALESCE函数更长。

MySQL COALESCE 与 IFNULL

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

IFNULL函数适用于两个参数，而COALESCE函数适用于n参数。如果参数数量为两个，则两个函数相同。

在本教程中，您学习了如何使用 MySQL COALESCE 函数来替换 NULL 值。

本教程有帮助吗？