MySQL函数—COALESCE|极客笔记

MySQL函数—COALESCE

1. 介绍

在MySQL中，COALESCE函数用于返回参数列表中的第一个非空值。如果所有参数都是空值，则返回NULL。

COALESCE函数的语法如下：

COALESCE(value1, value2, value3, ...)

其中，value1, value2, value3等为需要进行比较的值，可以是字段名、表达式、字符串等。

下面将详细解释COALESCE函数的用法和示例。

2. COALESCE函数的用法

2.1 基本用法

COALESCE函数可以接受多个参数，用逗号分隔。函数会按照参数列表的顺序进行比较，返回第一个非空值。

SELECT COALESCE(value1, value2, value3)
FROM table;

在上述示例中，COALESCE函数会从value1、value2和value3中选择第一个非空值作为结果返回。

2.2 返回NULL

当所有参数都为空值时，COALESCE函数会返回NULL。

SELECT COALESCE(value1, value2, value3)
FROM table;

在上述示例中，如果value1、value2和value3均为NULL，则COALESCE函数返回NULL。

2.3 COALESCE函数和NULLIF函数的组合

在某些情况下，我们可能需要将某个值替换为NULL。这时可以使用COALESCE函数和NULLIF函数的组合。

SELECT COALESCE(NULLIF(value, 'replace_value'), 'new_value')
FROM table;

上述示例中，如果value等于’replace_value’，则COALESCE函数会返回’new_value’；否则返回value本身。

3. COALESCE函数的实际应用

3.1 替换NULL值

COALESCE函数可以用于将NULL值替换为其他指定的非空值。

SELECT COALESCE(column1, 'N/A') AS column1
FROM table;

上述示例中，如果column1为空值，COALESCE函数会返回’N/A’作为替代值。

3.2 处理空字符串

COALESCE函数还可以用于处理空字符串。当字段值为空字符串时，可以返回其他非空值。

SELECT COALESCE(column1, 'N/A') AS column1
FROM table;

在上述示例中，如果column1的值为空字符串，COALESCE函数会返回’N/A’作为替代值。

3.3 多个字段取第一个非空值

COALESCE函数可以用于从多个字段中取出第一个非空值。

SELECT COALESCE(column1, column2, column3) AS result
FROM table;

在上述示例中，COALESCE函数会按照column1、column2、column3的顺序进行比较，并返回第一个非空值作为结果。

3.4 COALESCE函数的嵌套使用

COALESCE函数可以进行嵌套使用，以处理更复杂的情况。

SELECT COALESCE(COALESCE(column1, column2), column3) AS result
FROM table;

在上述示例中，COALESCE函数会从column1和column2中选择第一个非空值，然后再和column3进行比较，最终返回第一个非空值作为结果。

4. COALESCE函数的运行结果

以下是一些示例代码的运行结果。

4.1 替换NULL值

CREATE TABLE test_table (
  column1 VARCHAR(10),
  column2 VARCHAR(10)
);

INSERT INTO test_table (column1, column2) VALUES (NULL, 'value2');
INSERT INTO test_table (column1, column2) VALUES ('value1', NULL);
INSERT INTO test_table (column1, column2) VALUES ('value1', 'value2');

SELECT COALESCE(column1, 'N/A') AS column1, COALESCE(column2, 'N/A') AS column2
FROM test_table;

column1	column2
N/A	value2
value1	N/A
value1	value2

4.2 处理空字符串

CREATE TABLE test_table (
  column1 VARCHAR(10),
  column2 VARCHAR(10)
);

INSERT INTO test_table (column1, column2) VALUES ('', 'value2');
INSERT INTO test_table (column1, column2) VALUES ('value1', '');
INSERT INTO test_table (column1, column2) VALUES ('value1', 'value2');

SELECT COALESCE(NULLIF(column1, ''), 'N/A') AS column1, COALESCE(NULLIF(column2, ''), 'N/A') AS column2
FROM test_table;

column1	column2
N/A	value2
value1	N/A
value1	value2

4.3 多个字段取第一个非空值

CREATE TABLE test_table (
  column1 VARCHAR(10),
  column2 VARCHAR(10),
  column3 VARCHAR(10)
);

INSERT INTO test_table (column1, column2, column3) VALUES ('', 'value2', 'value3');
INSERT INTO test_table (column1, column2, column3) VALUES (NULL, 'value2', 'value3');
INSERT INTO test_table (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

SELECT COALESCE(column1, column2, column3) AS result
FROM test_table;

result
value2
value2
value1

4.4 COALESCE函数的嵌套使用

CREATE TABLE test_table (
  column1 VARCHAR(10),
  column2 VARCHAR(10),
  column3 VARCHAR(10)
);

INSERT INTO test_table (column1, column2, column3) VALUES ('', '', 'value3');
INSERT INTO test_table (column1, column2, column3) VALUES (NULL, '', 'value3');
INSERT INTO test_table (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

SELECT COALESCE(COALESCE(column1, column2), column3) AS result
FROM test_table;