MySQL函数（一）—流程控制函数

一、CASE函数

需要对查询结果进行多种情况分析的时候可以使用case函数。

1.1举个例子

 SELECT eno,
    CASE eno WHEN '111' THEN '1' WHEN '112' THEN '2' ELSE 'nomatch'  END result1,
    CASE WHEN eno like '%2%' THEN '1' WHEN eno like '%3%' THEN '2' ELSE 'nomatch' END result2,
    CASE WHEN eno like '%12%' THEN eno  END result3
 FROM emp_info;

结果如下

eno	result1	result2	result3
111	1	nomatch	NULL
112	2	1	112
113	nomatch	2	NULL
114	nomatch	nomatch	NULL
120	nomatch	1	120
121	nomatch	1	121
122	nomatch	1	122
123	nomatch	1	123

1.2使用格式

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
	#拆解
	CASE 值 WHEN 被比较值 THEN 结果 WHEN 被比较值  THEN 结果 ELSE 结果 END ,
	CASE WHEN 条件 THEN 结果 WHEN 条件 THEN 结果 ELSE 结果 END ,
	CASE WHEN 条件 THEN 结果  END 

译：第一个CASE语法返回第一个value=compare_value比较的结果，该比较为真。第二个语法返回第一个条件为真的结果。如果没有比较或条件为真，则返回ELSE之后的结果，如果没有ELSE部分则返回NULL。

1.3语句分析

1.3.1需求一

查询eno列，当eno的值为111时显示‘1’，当eno的值为112时显示‘2’，否则显示‘nomatch’

	CASE eno WHEN '111' THEN '1' WHEN '112' THEN '2' ELSE 'nomatch'  END result1

结果分析：

eno	result1	说明
111	1	匹配第一个值’111’
112	2	匹配第二个值’112’
113	nomatch	没有匹配

1.3.2需求二

当eno的值匹配到2时显示‘1’，当eno的值匹配到3时显示‘2’，否则显示‘nomatch’

	CASE WHEN eno like '%2%' THEN '1' WHEN eno like '%3%' THEN '2' ELSE 'nomatch' END result2

结果分析：

eno	result2	说明
112	1	第一个条件“eno like ‘%2%’”为真
113	2	第二个条件“”eno like ‘%3%为真’
123	1	两个条件都为真，但是返回首次匹配为真的结果

特别的，当eno的值为‘123’时，同时满足“like ‘%2%’”和“like ‘%3%’”的条件，但是查询结果为“1”，由此可知匹配具有顺序性，当第一个条件满足时不再执行后续条件。

1.3.3需求三

当eno的值匹配到‘12’时显示eno的值

	CASE WHEN eno like '%12%' THEN eno  END result3

结果分析：

eno	result3	说明
111	NULL	不匹配，且没有ELSE处理，返回空值
112	112	匹配，返回列值
120	120	匹配，返回列值

使用case语句时可以不写else，可以只设置一个条件，如果不写else就会返回null值，满足条件后的值可以返回固定的某个值，也可以返回列值。

1.3.4特别说明

使用第一种CASE函数时，可以使用哪些值

SELECT edept,
    #当edept 为‘0’时显示‘1’，为‘生产部’时显示‘2’，否则显示‘nomatch’
    CASE edept WHEN '0' THEN 'FALSE' WHEN '1' THEN 'TRUE' ELSE 'nomatch'  END result4,
    #当edept 为0时显示‘FALSE’，为1时显示‘TRUE’，否则显示‘nomatch’
    CASE edept WHEN 0 THEN 'FALSE' WHEN 1 THEN 'TRUE' ELSE 'nomatch'  END result4,
    #判断edept是否为空，当结果为false时显示部门，为true时显示‘暂无’，否则显示‘nomatch’
    case edept is null WHEN false THEN edept WHEN true THEN '暂无' ELSE 'nomatch'  END result4,
    #判断edept是否为空，当结果为true时显示‘有’，为false时显示‘暂无’，否则显示‘nomatch’
    case edept is not null WHEN true THEN '有' WHEN false THEN '暂无' ELSE 'nomatch'  END result4
 FROM emp_info;

edept	result4	result4	result4	result4
生产部	nomatch	FALSE	生产部	有
生产部	nomatch	FALSE	生产部	有
销售部	nomatch	FALSE	销售部	有
销售部	nomatch	FALSE	销售部	有

在一般程序语言里数值0对应FALS、1对应TRUE。首先看最后两列“result4”，

	#当edept 为‘0’时显示‘1’，为‘生产部’时显示‘2’，否则显示‘nomatch’
	case edept is null WHEN false THEN edept WHEN true THEN '暂无' ELSE 'nomatch'  END result4,
	#判断edept是否为空，当结果为true时显示‘有’，为false时显示‘暂无’，否则显示‘nomatch’
	case edept is not null WHEN true THEN '有' WHEN false THEN '暂无' ELSE 'nomatch'  END result4

“edept is null”不成立，值为FALSE，“edept is not null”成立，值为TRUE，所以都匹配第一个条件。
然后看前三列，以及对应的SQL语句：

	#当edept 为‘0’时显示‘1’，为‘生产部’时显示‘2’，否则显示‘nomatch’
	CASE edept WHEN '0' THEN 'FALSE' WHEN '1' THEN 'TRUE' ELSE 'nomatch'  END result4,
	#当edept 为0时显示‘FALSE’，为1时显示‘TRUE’，否则显示‘nomatch’
	CASE edept WHEN 0 THEN 'FALSE' WHEN 1 THEN 'TRUE' ELSE 'nomatch'  END result4,

上两条语句的区别在于第一个“WHEN”的被比较值，一个是数值0，一个是字符‘0’，需要做出区分。那么为什么“edept”中没有0值，也没有false值，第二条语句“CASE edept WHEN 0 THEN ‘FALSE’”却匹配到0值并输出了false呢？

SELECT edept,edept=0,edept=1,
    CASE edept WHEN 0 THEN 'FALSE' WHEN 1 THEN 'TRUE' ELSE 'nomatch'  END result1
 FROM emp_info;

edept	edept=0	edept=1	result1
生产部	1	0	FALSE
生产部	1	0	FALSE
生产部	1	0	FALSE
销售部	1	0	FALSE
销售部	1	0	FALSE

“edept”中没有0值，但是“edept=0”输出的是1，也就是true，MySQL判定二者相等，但是为什么相等呢？
MySQL在进行判断时，会判断比较值和被比较值的类型，如果是传入的是字符串，被比较值是整形，且字符串不是数值，则字符串会被转换为0，故“edept=0”在“edept”不是数值的情况下统一转换为0。

Tips

比较值时需要规范数据类型，否则会遇到隐式类型转换，出现结果集与预期不符的情况。

二、IF()函数

需要对查询结果进行处理时可以使用if函数，在使用上，偏向三目运算符。

2.1举个例子

SELECT eno,
	IF(STRCMP(eno, '111'), 'yes', 'Yes') as result1,
    IF(STRCMP(eno, '112'), 2, '3') as result2,
    IF(STRCMP(eno, '112'), 2, 3) as result3
FROM
    emp_info;

结果如下：

eno	result1	result2	result3
111	Yes	2	2
112	yes	3	3
120	yes	2	2
121	yes	2	2

2.2使用格式

	#原
	IF(expr1,expr2,expr3)

译：如果expr1为TRUE (expr1 <> 0且expr1 is NOT NULL)， If()返回expr2。否则，返回expr3。

2.3语句分析

2.3.1需求一

比较eno和‘111’，为真时返回’yes’，否则返回‘Yes’

	IF(STRCMP(eno, '111'), 'yes', 'Yes') AS result1

结果如下

eno	result1
111	Yes
112	yes

返回结果区分大小写

2.3.2需求二

比较eno和‘112’，为真时返回2，否则返回3

    IF(STRCMP(eno, '112'), 2, '3') as result2,
    IF(STRCMP(eno, '112'), 2, 3) as result3

eno	result2	result3
111	2	2
112	3	3
120	2	2
121	2	2

返回结果看起来相同，但是返回的数据类型并不相同：

可以看出，result2的返回类型时varchar也就是字符型，而result3返回的是bigint也就是长整型。当expr2或者expr3有一个是字符型，那么结果就是字符型。例如：

SELECT 
	IF(STRCMP(eno, '112'), sqrt(5), '3') AS result1,
    IF(STRCMP(eno, '112'), current_date(), '3') AS result2,
    IF(STRCMP(eno, '112'), true, '3') AS result3
FROM
    emp_info;

返回结果：

result1	result2	result3
2.23606797749979	2023-02-21	1
3	3	3
2.23606797749979	2023-02-21	1
2.23606797749979	2023-02-21	1

返回类型：

2.3.3特别说明

如果expr1为null值或者为0值会出现什么情况？

SELECT 
    IF(null, 1, 2) AS result1,
    IF(0, 1, 2) AS result2;

result1	result2
2	2

会一直返回expr3。

Tips

注意expr2和expr3的类型

三、IFNULL()函数

当需要对查询结果进行null值处理时可以使用IFNULL函数

3.1举个例子

SELECT 
    IFNULL(ename, 'lose') AS result1,
    IFNULL(ename, CURDATE()) AS result2,
    IFNULL(ename, 1 / 2) AS result3
FROM
    emp_info;

结果如下：

result1	result2	result3
程小鸭	程小鸭	程小鸭
lose	2023-02-21	0.5000
陈二狗	陈二狗	陈二狗
lose	2023-02-21	0.5000

3.2使用格式

	#原
	IFNULL(expr1,expr2)

译：如果expr1不是NULL, IFNULL()返回expr1;否则返回expr2。

3.3语句分析

3.1需求一

如果ename是null值，返回lose，否则返回姓名

	IFNULL(ename, 'lose') AS result1,

这里需要注意null值和空值‘ ’的区别，null值如下

空值如下：

3.2特别说明

数据的返回类型遵循什么规则？
样例的返回类型如下：

Tips

IFNULL(expr1,expr2)的默认返回类型是两个表达式中更“通用”的，顺序为STRING、REAL或INTEGER。

四、NULLIF()函数

4.1举个例子

SELECT 
    NULLIF(eno, '111') AS result1,
    NULLIF(salary, 6000) AS result2
FROM
    emp_info;

结果如下：

result1	result2
NULL	5000
112	5600
113	5900
114	NULL

4.2使用格式

#原
NULLIF(expr1,expr2)

译：如果expr1 = expr2为真则返回NULL，否则返回expr1。这与CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。返回值的类型与第一个参数相同。

4.3语句分析

4.3.1需求一

当eno为‘111’时，返回null值

	NULLIF(eno, '111') AS result1,

4.3.2特别说明

此函数返回null值，而ifnull函数是判断是否为null并选值返回，注意区分。

5、小结

对于函数的使用，需要注意MySQL默认的类型转换，如果用在程序中，需要注意返回类型。