COALESCE()

函数说明

COALESCE (expression_1, expression_2, ...,expression_n) 依次参考各参数表达式，遇到非 null 值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

使用 COALESCE 在于大部分包含空值的表达式最终将返回空值。

语法

  1. > COALESCE(value1, value2, ...., value_n)

参数释义

ArgumentsDescription
value1, value2, value_nRequired. The values to test

示例

  • 示例：计算
  1. mysql> SELECT COALESCE(1)+COALESCE(1);
  2. +---------------------------+
  3. | coalesce(1) + coalesce(1) |
  4. +---------------------------+
  5. |                         2 |
  6. +---------------------------+
  • 示例：比较运算
  1. drop table if exists t2;
  2. create table t2(a float, b datetime);
  3. insert into t2 values (12.345, '2022-02-20 10:10:10.999999');
  4. insert into t2 values (3.45646, NULL);
  5. insert into t2 values(NULL, '2023-04-03 22:10:29.999999');
  6. insert into t2 values (NULL, NULL);
  8. mysql> select * from t2;
  9. +---------+---------------------+
  10. | a       | b                   |
  11. +---------+---------------------+
  12. |  12.345 | 2022-02-20 10:10:11 |
  13. | 3.45646 | NULL                |
  14. |    NULL | 2023-04-03 22:10:30 |
  15. |    NULL | NULL                |
  16. +---------+---------------------+
  17. mysql> select coalesce(a, 1.0) from t2;
  18. +--------------------+
  19. | coalesce(a, 1.0)   |
  20. +--------------------+
  21. | 12.345000267028809 |
  22. | 3.4564599990844727 |
  23. |                  1 |
  24. |                  1 |
  25. +--------------------+
  26. mysql> select coalesce(a, 1) from t2;
  27. +--------------------+
  28. | coalesce(a, 1)     |
  29. +--------------------+
  30. | 12.345000267028809 |
  31. | 3.4564599990844727 |
  32. |                  1 |
  33. |                  1 |
  34. +--------------------+
  35. mysql> select coalesce(b, 2022-01-01) from t2;
  36. +---------------------------+
  37. | coalesce(b, 2022 - 1 - 1) |
  38. +---------------------------+
  39. | 2022-02-20 10:10:11       |
  40. |                           |
  41. | 2023-04-03 22:10:30       |
  42. |                           |
  43. +---------------------------+
  • 示例：含有 ORDER BY 子句
  1. CREATE TABLE t1 ( a INTEGER, b varchar(255) );
  2. INSERT INTO t1 VALUES (1,'z');
  3. INSERT INTO t1 VALUES (2,'y');
  4. INSERT INTO t1 VALUES (3,'x');
  6. mysql> SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a');
  7. +-------+
  8. | min_b |
  9. +-------+
  10. | x     |
  11. | y     |
  12. | z     |
  13. +-------+
  15. mysql> SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(min_b, 'a');
  16. +-------+
  17. | min_b |
  18. +-------+
  19. | x     |
  20. | y     |
  21. | z     |
  22. +-------+
  24. mysql> SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a') DESC;
  25. +-------+
  26. | min_b |
  27. +-------+
  28. | z     |
  29. | y     |
  30. | x     |
  31. +-------+
  • 示例：含有 Case When 子句
  1. mysql> select if(1, cast(1111111111111111111 as unsigned), 1) i,case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co;
  2. +---------------------+---------------------+---------------------+
  3. | i                   | c                   | co                  |
  4. +---------------------+---------------------+---------------------+
  5. | 1111111111111111111 | 1111111111111111111 | 1111111111111111111 |
  6. +---------------------+---------------------+---------------------+
  • 示例：IN Subquery
  1. CREATE TABLE ot (col_int_nokey int(11), col_varchar_nokey varchar(1));
  2. INSERT INTO ot VALUES (1,'x');
  3. CREATE TABLE it (col_int_key int(11), col_varchar_key varchar(1));
  4. INSERT INTO it VALUES (NULL,'x'), (NULL,'f');
  6. mysql> SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN(SELECT col_varchar_key FROM it WHERE coalesce(col_int_nokey, 1) );
  7. +---------------+
  8. | col_int_nokey |
  9. +---------------+
  10. |             1 |
  11. +---------------+
  • 示例：含有 WHERE 子句
  1. CREATE TABLE ot1(a INT);
  2. CREATE TABLE ot2(a INT);
  3. CREATE TABLE ot3(a INT);
  4. CREATE TABLE it1(a INT);
  5. CREATE TABLE it2(a INT);
  6. CREATE TABLE it3(a INT);
  7. INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
  8. INSERT INTO ot2 VALUES(0),(2),(4),(6);
  9. INSERT INTO ot3 VALUES(0),(3),(6);
  10. INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
  11. INSERT INTO it2 VALUES(0),(2),(4),(6);
  12. INSERT INTO it3 VALUES(0),(3),(6);
  14. mysql> SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3);
  15. +------+------+
  16. | a    | a    |
  17. +------+------+
  18. |    0 |    0 |
  19. |    1 | NULL |
  20. |    3 | NULL |
  21. |    5 | NULL |
  22. |    6 |    6 |
  23. |    7 | NULL |
  24. +------+------+
  • 示例：HAVING
  1. drop table if exists t1;
  2. create table t1(a datetime);
  3. INSERT INTO t1 VALUES (NULL), ('2001-01-01 00:00:00.12'), ('2002-01-01 00:00:00.567');
  5. mysql> select a from t1 group by a having COALESCE(a)<"2002-01-01";
  6. +---------------------+
  7. | a                   |
  8. +---------------------+
  9. | 2001-01-01 00:00:00 |
  10. +---------------------+
  • 示例：ON CONDITION
  1. drop table if exists t1;
  2. drop table if exists t2;
  3. create table t1(a INT,  b varchar(255));
  4. create table t2(a INT,  b varchar(255));
  5. insert into t1 values(1, "你好"), (3, "再见");
  6. insert into t2 values(2, "日期时间"), (4, "明天");
  7. > SELECT t1.a, t2.a FROM t1 JOIN t2 ON (length(COALESCE(t1.b)) = length(COALESCE(t2.b)));
  8. +------+------+
  9. | a    | a    |
  10. +------+------+
  11. |    1 |    4 |
  12. |    3 |    4 |
  13. +------+------+