MySQL常用流程控制函数小结

备注:测试数据库版本为MySQL 8.0

Table of Contents

• 一.CASE语句
• 二.IF函数
• 三.IFNULL函数
• 四.nullif 函数

这个blog我们来聊聊常见的流程控制函数

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

流程控制函数

一.CASE语句

case语句语法:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END

-- 值判断，根据职位英文名判断职位中文名
select e.empno,
     e.job,
     case when e.job = 'SALESMAN' then '销售员'
          when e.job = 'MANAGER' then '管理人员'
          else '未知' end  as job_china
from emp e;

-- 条件判断,小于某个时间点入职的是老员工，大于等于的是新员工
select e.empno,
     e.hiredate,
     case when e.hiredate < '1982-01-01' then '老员工' else '新员工' end is_old
from emp e;

-- 没有else语句且判断不成功的时候，会返回为空
SELECT CASE 'c'
    WHEN 'a' THEN 1 WHEN 'b' THEN 2 END is_c;

mysql> -- 值判断，根据职位英文名判断职位中文名
mysql> select e.empno,
  ->        e.job,
  ->        case when e.job = 'SALESMAN' then '销售员'
  ->             when e.job = 'MANAGER' then '管理人员'
  ->             else '未知' end  as job_china
  -> from emp e;
+-------+-----------+--------------+
| empno | job       | job_china    |
+-------+-----------+--------------+
|  7369 | CLERK     | 未知         |
|  7499 | SALESMAN  | 销售员       |
|  7521 | SALESMAN  | 销售员       |
|  7566 | MANAGER   | 管理人员     |
|  7654 | SALESMAN  | 销售员       |
|  7698 | MANAGER   | 管理人员     |
|  7782 | MANAGER   | 管理人员     |
|  7788 | ANALYST   | 未知         |
|  7839 | PRESIDENT | 未知         |
|  7844 | SALESMAN  | 销售员       |
|  7876 | CLERK     | 未知         |
|  7900 | CLERK     | 未知         |
|  7902 | ANALYST   | 未知         |
|  7934 | CLERK     | 未知         |
+-------+-----------+--------------+
14 rows in set (0.00 sec)

mysql>
mysql> -- 条件判断,小于某个时间点入职的是老员工，大于等于的是新员工
mysql> select e.empno,
  ->        e.hiredate,
  ->        case when e.hiredate < '1982-01-01' then '老员工' else '新员工' end is_old
  -> from emp e;
+-------+------------+-----------+
| empno | hiredate   | is_old    |
+-------+------------+-----------+
|  7369 | 1980-12-17 | 老员工    |
|  7499 | 1981-02-20 | 老员工    |
|  7521 | 1981-02-22 | 老员工    |
|  7566 | 1981-04-02 | 老员工    |
|  7654 | 1981-09-28 | 老员工    |
|  7698 | 1981-05-01 | 老员工    |
|  7782 | 1981-06-09 | 老员工    |
|  7788 | 1987-06-13 | 新员工    |
|  7839 | 1981-11-17 | 老员工    |
|  7844 | 1981-09-08 | 老员工    |
|  7876 | 1987-06-13 | 新员工    |
|  7900 | 1981-12-03 | 老员工    |
|  7902 | 1981-12-03 | 老员工    |
|  7934 | 1982-01-23 | 新员工    |
+-------+------------+-----------+
14 rows in set (0.00 sec)

mysql>
mysql> -- 没有else语句且判断不成功的时候，会返回为空
mysql> SELECT CASE 'c'
  ->       WHEN 'a' THEN 1 WHEN 'b' THEN 2 END is_c;
+------+
| is_c |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>

二.IF函数

if语法:
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

-- 如果1>2条件成立,则返回2，否则返回3
select if(1>2,2,3);
-- 如果第一个值不为0或null，则返回2，否则返回3
select if(0,2,3);
select if(null,2,3);
select if(1,2,3);

mysql> -- 如果1>2条件成立,则返回2，否则返回3
mysql> select if(1>2,2,3);
+-------------+
| if(1>2,2,3) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> -- 如果第一个值不为0或null，则返回2，否则返回3
mysql> select if(0,2,3);
+-----------+
| if(0,2,3) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

mysql> select if(null,2,3);
+--------------+
| if(null,2,3) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

mysql> select if(1,2,3);
+-----------+
| if(1,2,3) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

三.IFNULL函数

ifnull语法:
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
如果expr1非空,则返回expr1，否则返回expr2

-- 将奖金为0的员工显示为0
select e.ename,e.job,e.comm comm from emp e;
select e.ename,e.job,ifnull(e.comm,0) comm from emp e;

mysql> -- 将奖金为0的员工显示为0
mysql> select e.ename,e.job,e.comm comm from emp e;
+--------+-----------+---------+
| ename  | job       | comm    |
+--------+-----------+---------+
| SMITH  | CLERK     |    NULL |
| ALLEN  | SALESMAN  |  300.00 |
| WARD   | SALESMAN  |  500.00 |
| JONES  | MANAGER   |    NULL |
| MARTIN | SALESMAN  | 1400.00 |
| BLAKE  | MANAGER   |    NULL |
| CLARK  | MANAGER   |    NULL |
| SCOTT  | ANALYST   |    NULL |
| KING   | PRESIDENT |    NULL |
| TURNER | SALESMAN  |    0.00 |
| ADAMS  | CLERK     |    NULL |
| JAMES  | CLERK     |    NULL |
| FORD   | ANALYST   |    NULL |
| MILLER | CLERK     |    NULL |
+--------+-----------+---------+
14 rows in set (0.00 sec)

mysql> select e.ename,e.job,ifnull(e.comm,0) comm from emp e;
+--------+-----------+---------+
| ename  | job       | comm    |
+--------+-----------+---------+
| SMITH  | CLERK     |    0.00 |
| ALLEN  | SALESMAN  |  300.00 |
| WARD   | SALESMAN  |  500.00 |
| JONES  | MANAGER   |    0.00 |
| MARTIN | SALESMAN  | 1400.00 |
| BLAKE  | MANAGER   |    0.00 |
| CLARK  | MANAGER   |    0.00 |
| SCOTT  | ANALYST   |    0.00 |
| KING   | PRESIDENT |    0.00 |
| TURNER | SALESMAN  |    0.00 |
| ADAMS  | CLERK     |    0.00 |
| JAMES  | CLERK     |    0.00 |
| FORD   | ANALYST   |    0.00 |
| MILLER | CLERK     |    0.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

四.nullif 函数

nullif语法:
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1
This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

如果expr1=expr2则返回null，否则返回expr1

mysql> select nullif(1,1);
+-------------+
| nullif(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select nullif(1,2);
+-------------+
| nullif(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)