MySQL系列(21): SQL控制流函数之 case,if,ifnull,nullif

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

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

CASE

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

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

    value = compare_valuetrue时,返回 result。如下示例:

    1
    2
    mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
    -> 'one'
  2. 条件判断:CASE WHEN [condition]

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

    当条件 conditiontrue 时,返回 resultfalse 时返回 ELSE 后面的值,如果没有 ELSE 部分,则返回 NULL

    1
    2
    3
    4
    5
    6
    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 在开发中用的很多,例如返回状态,后端存储的大多是状态编码,但返回到前端的需要的是具体的状态名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
       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 语法:如果 expr1true( expr1 <> 0 and expr1 <> NULL),返回 expr2 ;否则,返回 expr3

    1
    IF(expr1, expr2, expr3)

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

    如果 expr2expr3 产生一个字符串,则结果是字符串类型。

    如果 expr2expr3 都是字符串,如果任一字符串区分大小于,则结果区分大小写。

    如果 expr2expr3 产生浮点数,则结果是浮点数。

    如果 expr2expr3 产生整数,则结果为整数。

  2. 示例

    1
    2
    3
    4
    5
    6
    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. 语法:如果 expr1NULL,返回 expr1;否则返回 expr2

    1
    IFNULL(expr1,expr2)
  2. 示例:

    1
    2
    3
    4
    5
    6
    7
    8
    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 = expr2true,返回 NULL;否则,返回 expr1.

    1
    NULLIF(expr1,expr2)
  2. 示例:

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

MySQL系列(21): SQL控制流函数之 case,if,ifnull,nullif

http://blog.gxitsky.com/2019/11/09/MySQL-21-case-if-ifnull/

作者

光星

发布于

2019-11-09

更新于

2023-03-06

许可协议

评论