MySQL 命令之 CAST WHEN

前言

在MySQL中有两个地方用到了关键字case：

在CASE Statement中不能有ELSE NULL子句，并且以END CASE结尾，而不是END。

CASE Statement主要用在复合语句中，比如存储过程；而CASE Operator则是在单条语句中用作函数。

本文介绍的主要是CASE Operator的用法。

Case语法

有两种语法：

Case 搜索函数

1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
  • WHEN condition THEN result：这一部分定义了条件和对应的返回结果。当条件 condition 为真时，表达式将返回 result。
  • ELSE result：这一部分是可选的。如果所有条件都不满足，且存在 ELSE 子句，则返回 ELSE 中定义的结果。如果没有 ELSE 子句且无条件满足，则表达式结果为 NULL。

简单 Case 函数

1
2
3
4
5
CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

两种用法的区别：

  • 第一种 CASE 语法返回的是第一个 value=compare_value 为 true 的分支的结果。
  • 第二种 CASE 语法返回的是第一个 condition 为 true 的分支的结果。

如果没有一个 value=compare_value 或者 condition 为 true，那么就会返回 ELSE 对应的结果，如果没有 ELSE 分支，那么返回 NULL。

示例

准备数据

1
2
3
4
5
6
7
8
9
10
mysql> select * from person;
+----+---------+--------+------+--------+
| id | country | name   | age  | gender |
+----+---------+--------+------+--------+
|  3 | china   | tim    |   10 |      1 |
|  7 | usa     | kobe   |   10 |      1 |
|  8 | usa     | james  |   20 |      1 |
|  9 | usa     | grace  |   30 |      0 |
| 10 | japan   | monica |   40 |      2 |
+----+---------+--------+------+--------+

查询

1
2
3
4
5
6
7
8
9
10
mysql> SELECT id, `name`,  CASE gender WHEN 1 THEN '男'  WHEN 0 THEN '女'  ELSE '其他' END AS gender FROM person;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  3 | tim    | 男     |
|  7 | kobe   | 男     |
|  8 | james  | 男     |
|  9 | grace  | 女     |
| 10 | monica | 其他   |
+----+--------+--------+

分组统计

  • 根据不同国家统计用户的性别
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select country, 
    -> sum(case when gender=1 then 1 else 0 end) as male, 
    -> sum(case when gender=0 then 1 else 0 end) as female, 
    -> sum(case when gender>1 then 1 else 0 end) as unknown 
    -> from person 
    -> group by country;
+---------+------+--------+---------+
| country | male | female | unknown |
+---------+------+--------+---------+
| china   |    1 |      0 |       0 |
| japan   |    0 |      0 |       1 |
| usa     |    2 |      1 |       0 |
+---------+------+--------+---------+

更新

  • 简单Case函数
1
2
3
4
5
6
7
8
UPDATE person 
SET `status` = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN 1
END
WHERE id IN (1, 2, 3, 4)

注意事项：一定要有 WHERE id IN 的限制，否则不在 WHEN 中的记录就会被置为 NULL

  • Case搜索函数
1
2
3
4
5
UPDATE person 
SET `status` = CASE 
WHEN id%2=1 THEN 1
WHEN id%2=0 THEN 0
END

区别：简单Case函数只能实现相等条件判断，Case搜 索函数适合复杂条件判断，比如大于、小于等

条件查询

1
2
SELECT * FROM person
WHERE CASE name="" THER true ELSE name="lp"

删除

1
2
3
4
5
DELETE FROM person
WHERE (CASE
    WHEN country IN ('chain', 'usa') THEN 0
    ELSE DATEDIFF(CURDATE(), timestamp) > 365
END);
  • 如果用户国家为 chain 或 usa，表达式返回0，表示不删除记录。
  • 对于其他用户，如果记录的时间戳超过一年（即 DATEDIFF(CURDATE(), timestamp) > 365 ），则删除记录。

Reference

MySQL
#MySQL
MySQL 命令之 CAST WHEN
https://flepeng.github.io/042-MySQL-21-命令-MySQL-命令之-CAST-WHEN/
作者
Lepeng
发布于
2021年3月6日
许可协议