控制流程函数

TiDB 支持使用 MySQL 8.0 中提供的所有控制流程函数。

CASE

CASE 操作符可以根据指定的条件进行条件逻辑判断并自定义查询结果。

语法：

1. CASE
2. WHEN condition1 THEN result1
3. WHEN condition2 THEN result2
4. ...
5. ELSE default_result
6. END

示例：

1. WITH RECURSIVE d AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM d WHERE n<10)
2. SELECT n, CASE WHEN n MOD 2 THEN "odd" ELSE "even" END FROM d;

1. +----+----------------------------------------------+
2. | n  | CASE WHEN n MOD 2 THEN "odd" ELSE "even" END |
3. +----+----------------------------------------------+
4. |  1 | odd                                          |
5. |  2 | even                                         |
6. |  3 | odd                                          |
7. |  4 | even                                         |
8. |  5 | odd                                          |
9. |  6 | even                                         |
10. |  7 | odd                                          |
11. |  8 | even                                         |
12. |  9 | odd                                          |
13. | 10 | even                                         |
14. +----+----------------------------------------------+
15. 10 rows in set (0.00 sec)

IF()

IF() 函数可以根据值或表达式是否为真执行不同的操作。

语法：

1. IF(condition, value_if_true, value_if_false)

示例：

1. WITH RECURSIVE d AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM d WHERE n<10)
2. SELECT n, IF(n MOD 2, "odd", "even") FROM d;

1. +----+----------------------------+
2. | n  | IF(n MOD 2, "odd", "even") |
3. +----+----------------------------+
4. |  1 | odd                        |
5. |  2 | even                       |
6. |  3 | odd                        |
7. |  4 | even                       |
8. |  5 | odd                        |
9. |  6 | even                       |
10. |  7 | odd                        |
11. |  8 | even                       |
12. |  9 | odd                        |
13. | 10 | even                       |
14. +----+----------------------------+
15. 10 rows in set (0.00 sec)

IFNULL()

IFNULL(expr1,expr2) 函数用于处理查询中的 NULL 值。如果 expr1 不为 NULL，该函数返回 expr1；否则返回 expr2。

示例：

1. WITH data AS (SELECT NULL AS x UNION ALL SELECT 1 )
2. SELECT x, IFNULL(x,'x has no value') FROM data;

1. +------+----------------------------+
2. | x    | IFNULL(x,'x has no value') |
3. +------+----------------------------+
4. | NULL | x has no value             |
5. |    1 | 1                          |
6. +------+----------------------------+
7. 2 rows in set (0.0006 sec)

NULLIF()

NULLIF(expr1,expr2) 函数用于在两个参数相同或第一个参数为 NULL 时返回 NULL。否则，返回第一个参数。

示例：

1. WITH RECURSIVE d AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM d WHERE n<10)
2. SELECT n, NULLIF(n+n, n+2) FROM d;

1. +----+------------------+
2. | n  | NULLIF(n+n, n+2) |
3. +----+------------------+
4. |  1 |                2 |
5. |  2 |             NULL |
6. |  3 |                6 |
7. |  4 |                8 |
8. |  5 |               10 |
9. |  6 |               12 |
10. |  7 |               14 |
11. |  8 |               16 |
12. |  9 |               18 |
13. | 10 |               20 |
14. +----+------------------+
15. 10 rows in set (0.00 sec)

在该示例中，当 n 等于 2 时，n+n 和 n+2 都等于 4，两个参数值相同，因此函数返回 NULL。