MySQL(二十一)：SQL控制流函数之case,if,ifnull,nullif

SQL 语句中常需要通过逻辑判断来控制流程的操作，MySQL 标准提供了 CASE，IF，IFNULL，NULLIF 控制流函数。

MySQL 5.7 官方文 档 -> 12.4 Control Flow Functions

CASE

1. 字段值比较：CASE value WHEN [compare_value]

   CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
   

   当 value = compare_value 为 true时，返回 result。如下示例：

   mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
       -> 'one'
   

2. 条件判断：CASE WHEN [condition]

   CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
   

   当条件 condition 为 true 时，返回 result；false 时返回 ELSE 后面的值，如果没有 ELSE 部分，则返回 NULL。

   mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
       -> 'true'
   mysql> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
       -> NULL
   mysql> SELECT CASE BINARY 'a' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
       -> 1
   

3. 示例：

   CASE 在开发中用的很多，例如返回状态，后端存储的大多是状态编码，但返回到前端的需要的是具体的状态名

   mysql> SELECT
       a.actor_id,
       a.first_name,
       a.last_name,
        a.state,
        ( CASE state WHEN 1 THEN '正常' WHEN 2 THEN '暂停' WHEN 3 THEN '注销' ELSE '禁用' END ) AS state_name 
   FROM
       actor a 
   WHERE
       a.actor_id < 6;
   +----------+------------+--------------+-------+------------+
   | actor_id | first_name | last_name    | state | state_name |
   +----------+------------+--------------+-------+------------+
   |        1 | PENELOPE   | GUINESS      |     1 | 正常       |
   |        2 | NICK       | WAHLBERG     |     2 | 暂停       |
   |        3 | ED         | CHASE        |     3 | 注销       |
   |        4 | JENNIFER   | DAVIS        |     4 | 禁用       |
   |        5 | JOHNNY     | LOLLOBRIGIDA |     5 | 禁用       |
   +----------+------------+--------------+-------+------------+
   

IF

1. IF 语法：如果 expr1 为 true( expr1 <> 0 and expr1 <> NULL)，返回 expr2 ；否则，返回 expr3。

   IF(expr1, expr2, expr3)
   

   IF() 的默认返回值类型（将其存储到临时表中时可能很重要）计算如下：

   如果 expr2 或 expr3 产生一个字符串，则结果是字符串类型。

   如果 expr2 或 expr3 都是字符串，如果任一字符串区分大小于，则结果区分大小写。

   如果 expr2 或 expr3 产生浮点数，则结果是浮点数。

   如果 expr2 或 expr3 产生整数，则结果为整数。

2. 示例

   mysql> SELECT IF(1>2,2,3);
       -> 3
   mysql> SELECT IF(1<2,'yes','no');
       -> 'yes'
   mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
       -> 'no'
   

IFNULL

1. 语法：如果 expr1 非 NULL，返回 expr1；否则返回 expr2

   IFNULL(expr1,expr2)
   

2. 示例：

   mysql> SELECT IFNULL(1,0);
       -> 1
   mysql> SELECT IFNULL(NULL,10);
       -> 10
   mysql> SELECT IFNULL(1/0,10);
       -> 10
   mysql> SELECT IFNULL(1/0,'yes');
       -> 'yes'
   

NULL IF

1. 语法：如果 expr1 = expr2 为 true，返回 NULL；否则，返回 expr1.

   NULLIF(expr1,expr2)
   

2. 示例：

   mysql> SELECT NULLIF(1,1);
       -> NULL
   mysql> SELECT NULLIF(1,2);
       -> 1