MySQL中的流程函数

MySQL中的流程函数也是很常用的一类函数，使用这类函数可以在SQL语句中实现条件选择。下表列出了MySQL 5.7支持的所有流程函数。

我们通过实例来研究下用法。

• CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END，返回第一个value=compare_value的result

root@database-one 00:24:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)

root@database-one 00:24:  [gftest]> select ename,case sal when 6000 then 'low' when 9100 then 'high' else 'mid' end from emp;
+--------+--------------------------------------------------------------------+
| ename  | case sal when 6000 then 'low' when 9100 then 'high' else 'mid' end |
+--------+--------------------------------------------------------------------+
| 郭军   | mid                                                                |
| 刘杰   | high                                                               |
| 王艳   | low                                                                |
| 马丽   | mid                                                                |
| 肖伟   | mid                                                                |
+--------+--------------------------------------------------------------------+
5 rows in set (0.00 sec)

• CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END，返回第一个condition为true的result

root@database-one 00:27:  [gftest]> select ename,case when sal<=7000 then 'low' when sal>=9000 then 'high' else 'mid' end from emp;
+--------+--------------------------------------------------------------------------+
| ename  | case when sal<=7000 then 'low' when sal>=9000 then 'high' else 'mid' end |
+--------+--------------------------------------------------------------------------+
| 郭军   | mid                                                                      |
| 刘杰   | high                                                                     |
| 王艳   | low                                                                      |
| 马丽   | mid                                                                      |
| 肖伟   | mid                                                                      |
+--------+--------------------------------------------------------------------------+
5 rows in set (0.05 sec)

• IF(expr1,expr2,expr3)，如果expr1为TRUE (expr1 <> 0 and expr1 <> NULL)，返回expr2，否则返回expr3

root@database-one 00:32:  [gftest]> SELECT IF(1>2,2,3),IF(1<2,'yes','no'),IF(STRCMP('test','test1'),'no','yes');
+-------------+--------------------+---------------------------------------+
| IF(1>2,2,3) | IF(1<2,'yes','no') | IF(STRCMP('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
|           3 | yes                | no                                    |
+-------------+--------------------+---------------------------------------+
1 row in set (0.03 sec)

root@database-one 00:33:  [gftest]> select ename,if(sal>8000,'high','low') from emp;
+--------+---------------------------+
| ename  | if(sal>8000,'high','low') |
+--------+---------------------------+
| 郭军   | high                      |
| 刘杰   | high                      |
| 王艳   | low                       |
| 马丽   | low                       |
| 肖伟   | high                      |
+--------+---------------------------+
5 rows in set (0.02 sec)

• IFNULL(expr1,expr2)，如果expr1不为NULL，返回expr1，否则expr2

root@database-one 00:35:  [gftest]> SELECT IFNULL(1,0),IFNULL(NULL,10),IFNULL(1/0,10),IFNULL(1/0,'yes');
+-------------+-----------------+----------------+-------------------+
| IFNULL(1,0) | IFNULL(NULL,10) | IFNULL(1/0,10) | IFNULL(1/0,'yes') |
+-------------+-----------------+----------------+-------------------+
|           1 |              10 |        10.0000 | yes               |
+-------------+-----------------+----------------+-------------------+
1 row in set (0.08 sec)

• NULLIF(expr1,expr2)，如果expr1 = expr2，返回NULL，否则返回expr1

root@database-one 00:39:  [gftest]> SELECT NULLIF(1,1),NULLIF(1,2);
+-------------+-------------+
| NULLIF(1,1) | NULLIF(1,2) |
+-------------+-------------+
|        NULL |           1 |
+-------------+-------------+
1 row in set (0.04 sec)