摘要：MySQL中的常用流程函数介绍及实测。
MySQL中的流程函数也是很常用的一类函数，使用这类函数可以在SQL语句中实现条件选择。下表列出了MySQL 5.7支持的所有流程函数。
| Name | Description |
|---|---|
| CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END | Case operator |
| CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END | Case operator |
| IF() | If/else construct |
| IFNULL() | Null if/else construct |
| NULLIF() | Return NULL if expr1 = expr2 |
我们通过实例来研究下用法。
- 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)
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。