MySQL里让我又爱又恨的语法CASE必须算一个。日常写SQL无论是字段值翻译、区间分组、报表行转列还是批量更新数据CASE用法几乎是无处不在。有些同学用了很久却只停留在最基础的等值翻译一遇到需要“判断区间”或者“根据条件做聚合”就绕回子查询和临时表走了远路。这篇就把CASE的两种形态、实际案例和容易踩的坑一次说透适合刚接触SQL不久的新手也适合写过一段时间但没系统整理过CASE细节的同学。下面的示例基于MySQL 8.05.7同样适用。1. 先搞清楚CASE到底能解决什么问题1.1 CASE表达式和CASE语句是两回事CASE关键字在MySQL里其实有两副面孔很多人看文档时容易混淆。第一副是我们平时用得最多的CASE表达式它写在SELECT、WHERE、ORDER BY、GROUP BY、UPDATE的SET子句里作用是基于条件返回一个标量值。第二副是CASE语句它只能出现在存储过程、函数、触发器这类程序体中作用不是返回一个值而是控制一组语句的执行流程。区分这两个东西最直接的办法看结尾。CASE表达式以END结尾后面不跟分号在SQL语句中是表达式的一部分。CASE语句以END CASE结尾并且通常需要配分号。-- 表达式返回一个值 SELECT order_id, CASE WHEN amount 500 THEN 大额 ELSE 普通 END AS order_level FROM order_detail; -- 存储过程中的语句执行一段逻辑 DELIMITER // CREATE PROCEDURE proc_demo(IN p_type INT) BEGIN CASE p_type WHEN 1 THEN SET result 类型一; WHEN 2 THEN SET result 类型二; ELSE SET result 其他; END CASE; END// DELIMITER ;很多人会把存储过程中的END CASE写成END或者反过来在查询里画蛇添足加个END CASE然后抱怨语法报错。其实只要记住一个使用场景SQL语句里要的是表达式写ENDBEGIN...END块里要的是流程控制写END CASE。这样就不会搞混了。1.2 为什么我建议优先用CASE而不是一层层IFMySQL也有IF函数比如IF(score 60, 及格, 不及格)二元判断用它很简洁。但一旦分支多起来IF嵌套的可读性和维护性就是灾难。你可以感受一下同样的逻辑用两种写法-- IF嵌套括号一多容易乱 SELECT name, IF(level 1, 初级, IF(level 2, 中级, IF(level 3, 高级, 未知))) AS level_text FROM emp_info; -- CASE写法层次一目了然 SELECT name, CASE level WHEN 1 THEN 初级 WHEN 2 THEN 中级 WHEN 3 THEN 高级 ELSE 未知 END AS level_text FROM emp_info;CASE表达式的优势不只是好看。CASE是标准表达式语法可以直接嵌套在聚合函数里用比如COUNT(CASE WHEN ... THEN 1 END)这在做分类统计时几乎离不开。另外CASE还能出现在GROUP BY、ORDER BY、UPDATE这些IF函数很难优雅处理的位置。后面我会逐个场景展开。2. 两种语法形态别搞混简单CASE与搜索CASE2.1 简单CASE只做等值翻译简单CASE的语法结构是CASE后面紧跟一个字段或表达式WHEN后面写等值判断的目标值THEN后面写匹配后返回的结果CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE resultN END它的语义等同于把column_name和每个WHEN后的value做等值比较。举个最常见的翻译场景订单状态存的是0、1、2展示要变成中文SELECT order_id, order_status, CASE order_status WHEN 0 THEN 待支付 WHEN 1 THEN 已支付 WHEN 2 THEN 已发货 ELSE 未知状态 END AS status_text FROM order_detail;简单CASE有一个很大的限制它只能做等值判断。你不能直接在WHEN后面写WHEN amount 1000因为CASE后面的字段要拿去和WHEN值比对WHEN后面出现的不是一个布尔表达式而是一个等值目标。凡是遇到区间、范围、多字段组合判断都要切换到搜索CASE。这也是我见过最多新手写错的地方。2.2 搜索CASE条件判断的完整形态搜索CASE的语法是CASE后面不跟字段直接跟WHEN布尔表达式CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE resultN END每个WHEN后面是一个完整的逻辑表达式可以是比较运算、逻辑组合、甚至子查询。比如想给用户打消费等级标签SELECT user_id, amount, CASE WHEN amount 5000 THEN 高消费 WHEN amount 1000 AND amount 5000 THEN 中消费 ELSE 低消费 END AS user_tier FROM order_detail;这条SQL的可读性比嵌套IF强得多。搜索CASE还支持更复杂的条件比如判断用户是否存在未支付订单SELECT u.user_id, u.name, CASE WHEN EXISTS ( SELECT 1 FROM order_detail o WHERE o.user_id u.user_id AND o.pay_status 0 ) THEN 有未付款订单 ELSE 全部已处理 END AS order_flag FROM user_info u;这种带EXISTS子查询的CASE适合处理“是否满足某个集合条件”的场景但要注意给关联字段加索引否则在大表上性能会比较难看。2.3 嵌套、顺序和ELSE这几个细节决定结果对不对CASE的条件判断是从上往下的一旦某个WHEN条件为真后面的条件就不会再判断。这个特性决定了WHEN的书写顺序必须谨慎。比如CASE WHEN score 60 THEN 及格 WHEN score 90 THEN 优秀 ELSE 不及格 END如果你是新手可能觉得先写及格再写优秀很自然。但实际执行时一个95分的学生会先命中score 60直接返回“及格”后面的score 90根本不执行。正确做法是把更具体的条件放在前面CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 ENDELSE也很容易被忽略。搜索CASE如果所有WHEN都不匹配并且没有ELSE那么返回NULL。在SELECT里显示NULL可能无所谓但如果CASE参与了聚合运算NULL会被COUNT忽略、会被SUM跳过统计结果就会和预期差得很远。我建议除非你明确知道要利用NULL否则尽量显式写ELSE。尤其在聚合场景里写不写ELSE往是“结果对”和“结果错”的分水岭。3. 实战场景从聚合统计到批量更新的完整案例3.1 用CASE配合GROUP BY做区间分类统计先准备一张订单表模拟实际业务里的明细数据CREATE TABLE order_detail ( order_id INT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, order_date DATE NOT NULL );现在有个典型需求统计每个用户小额100、中额100~500、大额500订单各有多少笔。很多人第一反应是写三次查询再合并其实一个CASE加GROUP BY就能解决SELECT user_id, COUNT(CASE WHEN amount 100 THEN 1 END) AS low_orders, COUNT(CASE WHEN amount 100 AND amount 500 THEN 1 END) AS mid_orders, COUNT(CASE WHEN amount 500 THEN 1 END) AS high_orders FROM order_detail GROUP BY user_id;这里的关键点是COUNT配合CASE时不匹配的分支返回NULLCOUNT只统计非NULL值所以天然只计数符合条件的行。但有一个坑我必须强调不要写成COUNT(CASE WHEN ... THEN 1 ELSE 0 END)。因为ELSE 0会让每一行都返回一个非NULL值COUNT会把不符合条件的行也统计进去最后所有档位都是总行数。如果你习惯写ELSE那就配合SUM使用写成SUM(CASE WHEN ... THEN 1 ELSE 0 END)SUM忽略NULL、加0不影响结果两种写法都行但别混用。3.2 一次CASE实现行转列把竖表变横表报表里经常遇到“月份在行、商品在列”的展示需求。数据在表里是纵向的一单一单记录但报表要横向展开成1月、2月、3月这样的固定列。MySQL没有PIVOT函数行转列最经典的做法就是CASE加聚合。比如要统计2024年上半年每个商品的月度销售额横向展示1月到6月SELECT product_name, SUM(CASE WHEN MONTH(order_date) 1 THEN amount ELSE 0 END) AS jan_amt, SUM(CASE WHEN MONTH(order_date) 2 THEN amount ELSE 0 END) AS feb_amt, SUM(CASE WHEN MONTH(order_date) 3 THEN amount ELSE 0 END) AS mar_amt, SUM(CASE WHEN MONTH(order_date) 4 THEN amount ELSE 0 END) AS apr_amt, SUM(CASE WHEN MONTH(order_date) 5 THEN amount ELSE 0 END) AS may_amt, SUM(CASE WHEN MONTH(order_date) 6 THEN amount ELSE 0 END) AS jun_amt FROM order_detail WHERE order_date BETWEEN 2024-01-01 AND 2024-06-30 GROUP BY product_name;执行过程可以这么理解先按product_name分组然后在每一组内部对每一行判断MONTH(order_date)等不等于目标月份等于就返回amountSUM把本月的金额累加不等于就返回0不影响SUM结果。这里的ELSE 0写不写都不影响最终数值因为SUM会忽略NULL但写出来更直白同事接手时一眼就明白意图。这种方案的局限是列数固定。如果月份不是确定的6列而是根据数据动态变化就需要用预处理语句和GROUP_CONCAT拼接SQL复杂度会高不少。多数报表场景的列是固定的CASE方案足够用。3.3 把CASE用在UPDATE里批量修正数据的正确姿势CASE不只在查询里好用在UPDATE的SET子句里同样威力巨大。它能把“逐行写循环”变成一条SQL完成批量修正。举例某次接口同步故障订单表的pay_status字段出现了错误状态现在需要按规则修正原本为1且订单金额大于1000的改为2原本为1且金额小于等于1000的改为3原本为2的改为4。UPDATE order_detail SET pay_status CASE WHEN pay_status 1 AND amount 1000 THEN 2 WHEN pay_status 1 AND amount 1000 THEN 3 WHEN pay_status 2 THEN 4 ELSE pay_status END WHERE pay_status IN (1, 2);SET子句里的CASE会逐行评估每读一行就按顺序匹配WHEN条件命中了就返回新值。这里的ELSE pay_status是为了兜底让不符合规则的行保持原值。实际操作中我有两个强烈建议。第一UPDATE之前一定先用同条件的SELECT看一眼数据SELECT order_id, pay_status, amount, CASE WHEN pay_status 1 AND amount 1000 THEN 2 WHEN pay_status 1 AND amount 1000 THEN 3 WHEN pay_status 2 THEN 4 ELSE pay_status END AS new_pay_status FROM order_detail WHERE pay_status IN (1, 2);先确认每条记录会被改成什么值再执行UPDATE。第二如果线上环境数据量大务必开启事务先做一轮START TRANSACTION; UPDATE order_detail SET pay_status CASE ... END WHERE pay_status IN (1, 2); -- 核对影响行数和数据 COMMIT; -- 有问题就 ROLLBACK;这种批量更新方式比写游标循环强太多一条UPDATE就能完成效率高、事务也容易控制。3.4 排序场景的奇技用CASE定制ORDER BYORDER BY后面其实可以跟任意表达式CASE就是其中非常好用的一个。它可以实现“自定义优先级排序”。比如列表要优先展示待支付订单pay_status0然后其他状态按金额倒序排SELECT order_id, pay_status, amount FROM order_detail ORDER BY CASE WHEN pay_status 0 THEN 0 ELSE 1 END, amount DESC;执行逻辑是第一排序键把pay_status0的订单映射成0其余映射成1于是待支付订单排在最前面同一个优先级内再按amount倒序排列。再比如状态字段是字符串希望按“已发货、已支付、待支付”这个自定义顺序展示而不是默认字典序SELECT order_id, order_status FROM order_detail ORDER BY CASE order_status WHEN 已发货 THEN 1 WHEN 已支付 THEN 2 WHEN 待支付 THEN 3 ELSE 4 END;这种写法的优势是灵活不需要额外维护一个排序权重表。缺点是当数据量很大时CASE在ORDER BY里通常没法走索引MySQL大概率要用filesort。所以在百万级以上的表里做这种排序更稳妥的方案是在表里加一个sort_weight冗余字段用程序维护数值然后直接ORDER BY那个字段。4. 常见效率陷阱与排查心得4.1 命中顺序与NULL结果不对的第一排查点我在帮同事排查SQL时CASE结果不对的案例里命中顺序错误和NULL处理错误占了绝大多数。先说命中顺序。前面讲过CASE从上往下短路执行一旦命中就退出。排查时可以简单记一句话能覆盖更多记录的广义条件往后放更精确的狭义条件往前放。判断成绩区间、金额区间时尤其要注意。再说NULL。SQL里任何和NULL做等值比较都会得到UNKNOWN不会进入THEN分支。举例SELECT CASE WHEN NULL THEN A ELSE B END; -- 结果BWHEN NULL本身不是合法条件它不会匹配NULL字段。如果你要判断某个字段是否为NULL必须用IS NULLSELECT CASE WHEN order_date IS NULL THEN 无日期 ELSE 有日期 END AS date_flag FROM order_detail;还有人会踩CASE col WHEN NULL的坑简单CASE里写CASE col WHEN NULL THEN...也是不生效的因为NULL和NULL比较不会为真。判断NULL只能用搜索CASE的IS NULL。4.2 类型隐式转换简单CASE最常见的天坑简单CASE的本质是等值比较MySQL在做比较时会触发类型转换规则。字符串和数字比较时字符串通常会被转成数字。看一个例子SELECT CASE 1 WHEN 1 THEN 匹配 ELSE 不匹配 END; -- 结果匹配如果字符串不是纯数字比如1aMySQL也会把它转换成1再比较所以同样可能匹配。这在业务里是个隐患假如status列是字符串类型你写CASE status WHEN 1 THEN 是可能把status1a的记录也匹配进去而你自己完全没意识到。我的建议很直接CASE后面是什么类型WHEN后面就写什么类型。列是VARCHARWHEN里就写字符串比如WHEN paid列是INTWHEN里就写整数。不要依赖MySQL的隐式转换它虽然能勉强工作但迟早会坑你一次。如果确实需要跨类型比较先显式CAST再比较至少行为是明确的。4.3 CASE和IF、IFNULL怎么选不少人在写条件判断时会在CASE、IF、IFNULL之间犹豫这里我总结一个选型逻辑只是想把NULL换成默认值比如IFNULL(remark, 暂无备注)用IFNULL最简洁。只有二分叉比如性别显示男或女用IF也行逻辑不复杂。分支超过两个或者需要在GROUP BY、ORDER BY、UPDATE中使用直接选CASE。需要统计分类计数必须配合CASE写聚合IF虽然能写但可读性差。CASE和IF还有一个重要区别CASE是表达式可以嵌入到任何表达式位置包括索引表达式少数情况、生成列定义、视图定义IF是函数底层实现的灵活性不如CASE。从SQL标准兼容性角度看CASE也是更通用的写法换到其他数据库迁移成本更低。4.4 性能问题CASE会不会导致索引失效很多人一看到CASE就担心性能。这里得区分场景。CASE写在SELECT或ORDER BY里通常只是对查询结果进行逐行计算本身不直接导致全表扫描。比如SELECT CASE WHEN ... END FROM table决定扫描范围的是WHERE条件不是SELECT里的CASE。真正要小心的是把CASE写在WHERE里-- 这种写法通常会导致索引失效 SELECT order_id FROM order_detail WHERE CASE WHEN amount 100 THEN 1 ELSE 0 END 1;优化器很难对CASE表达式推导出索引范围大概率变成全表扫描。遇到这种需求正确思路是把条件直接改写为WHERE amount 100让索引正常工作。另一个性能点是CASE配合ORDER BY前面已经提到不能走索引大表会有filesort。还有CASE配合UPDATE更新范围越精确越好WHERE条件尽量缩小影响行数否则InnoDB的行锁和undo log都会放大。说实话CASE的性能问题往往不在CASE本身而在你把它放错了位置。能用普通条件表达的地方不要用CASE包装用得上CASE的地方分类聚合、行转列、自定义排序它的性能完全可以接受。5. 一点使用体会当作结尾我自己用CASE用得最多的地方反而不是SELECT而是GROUP BY和UPDATE。很多同事看到复杂报表第一反应是写子查询或者拼临时表其实用一个CASE把分类维度放进去SQL又短又不容易错。我踩过最狠的坑是COUNT里写ELSE 0报表数据直接翻倍查了半天才意识到COUNT统计的是非NULL行数不是数值本身。后来自检SQL时我养成一个习惯凡是CASE参与聚合先在脑子里走一行样例数据或者直接把明细SELECT出来看一遍修改生产数据前一定开事务、备份表。这个习惯救过我很多次。如果你刚开始用CASE建议按这个顺序练先从简单CASE的字段翻译练手再尝试搜索CASE做区间判断然后把它塞进GROUP BY做分类统计最后试着在UPDATE里做批量修正。这几步走完CASE的基本功就扎实了。
MySQL里让我又爱又恨的语法CASE必须算一个。日常写SQL无论是字段值翻译、区间分组、报表行转列还是批量更新数据CASE用法几乎是无处不在。有些同学用了很久却只停留在最基础的等值翻译一遇到需要“判断区间”或者“根据条件做聚合”就绕回子查询和临时表走了远路。这篇就把CASE的两种形态、实际案例和容易踩的坑一次说透适合刚接触SQL不久的新手也适合写过一段时间但没系统整理过CASE细节的同学。下面的示例基于MySQL 8.05.7同样适用。1. 先搞清楚CASE到底能解决什么问题1.1 CASE表达式和CASE语句是两回事CASE关键字在MySQL里其实有两副面孔很多人看文档时容易混淆。第一副是我们平时用得最多的CASE表达式它写在SELECT、WHERE、ORDER BY、GROUP BY、UPDATE的SET子句里作用是基于条件返回一个标量值。第二副是CASE语句它只能出现在存储过程、函数、触发器这类程序体中作用不是返回一个值而是控制一组语句的执行流程。区分这两个东西最直接的办法看结尾。CASE表达式以END结尾后面不跟分号在SQL语句中是表达式的一部分。CASE语句以END CASE结尾并且通常需要配分号。-- 表达式返回一个值 SELECT order_id, CASE WHEN amount 500 THEN 大额 ELSE 普通 END AS order_level FROM order_detail; -- 存储过程中的语句执行一段逻辑 DELIMITER // CREATE PROCEDURE proc_demo(IN p_type INT) BEGIN CASE p_type WHEN 1 THEN SET result 类型一; WHEN 2 THEN SET result 类型二; ELSE SET result 其他; END CASE; END// DELIMITER ;很多人会把存储过程中的END CASE写成END或者反过来在查询里画蛇添足加个END CASE然后抱怨语法报错。其实只要记住一个使用场景SQL语句里要的是表达式写ENDBEGIN...END块里要的是流程控制写END CASE。这样就不会搞混了。1.2 为什么我建议优先用CASE而不是一层层IFMySQL也有IF函数比如IF(score 60, 及格, 不及格)二元判断用它很简洁。但一旦分支多起来IF嵌套的可读性和维护性就是灾难。你可以感受一下同样的逻辑用两种写法-- IF嵌套括号一多容易乱 SELECT name, IF(level 1, 初级, IF(level 2, 中级, IF(level 3, 高级, 未知))) AS level_text FROM emp_info; -- CASE写法层次一目了然 SELECT name, CASE level WHEN 1 THEN 初级 WHEN 2 THEN 中级 WHEN 3 THEN 高级 ELSE 未知 END AS level_text FROM emp_info;CASE表达式的优势不只是好看。CASE是标准表达式语法可以直接嵌套在聚合函数里用比如COUNT(CASE WHEN ... THEN 1 END)这在做分类统计时几乎离不开。另外CASE还能出现在GROUP BY、ORDER BY、UPDATE这些IF函数很难优雅处理的位置。后面我会逐个场景展开。2. 两种语法形态别搞混简单CASE与搜索CASE2.1 简单CASE只做等值翻译简单CASE的语法结构是CASE后面紧跟一个字段或表达式WHEN后面写等值判断的目标值THEN后面写匹配后返回的结果CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE resultN END它的语义等同于把column_name和每个WHEN后的value做等值比较。举个最常见的翻译场景订单状态存的是0、1、2展示要变成中文SELECT order_id, order_status, CASE order_status WHEN 0 THEN 待支付 WHEN 1 THEN 已支付 WHEN 2 THEN 已发货 ELSE 未知状态 END AS status_text FROM order_detail;简单CASE有一个很大的限制它只能做等值判断。你不能直接在WHEN后面写WHEN amount 1000因为CASE后面的字段要拿去和WHEN值比对WHEN后面出现的不是一个布尔表达式而是一个等值目标。凡是遇到区间、范围、多字段组合判断都要切换到搜索CASE。这也是我见过最多新手写错的地方。2.2 搜索CASE条件判断的完整形态搜索CASE的语法是CASE后面不跟字段直接跟WHEN布尔表达式CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE resultN END每个WHEN后面是一个完整的逻辑表达式可以是比较运算、逻辑组合、甚至子查询。比如想给用户打消费等级标签SELECT user_id, amount, CASE WHEN amount 5000 THEN 高消费 WHEN amount 1000 AND amount 5000 THEN 中消费 ELSE 低消费 END AS user_tier FROM order_detail;这条SQL的可读性比嵌套IF强得多。搜索CASE还支持更复杂的条件比如判断用户是否存在未支付订单SELECT u.user_id, u.name, CASE WHEN EXISTS ( SELECT 1 FROM order_detail o WHERE o.user_id u.user_id AND o.pay_status 0 ) THEN 有未付款订单 ELSE 全部已处理 END AS order_flag FROM user_info u;这种带EXISTS子查询的CASE适合处理“是否满足某个集合条件”的场景但要注意给关联字段加索引否则在大表上性能会比较难看。2.3 嵌套、顺序和ELSE这几个细节决定结果对不对CASE的条件判断是从上往下的一旦某个WHEN条件为真后面的条件就不会再判断。这个特性决定了WHEN的书写顺序必须谨慎。比如CASE WHEN score 60 THEN 及格 WHEN score 90 THEN 优秀 ELSE 不及格 END如果你是新手可能觉得先写及格再写优秀很自然。但实际执行时一个95分的学生会先命中score 60直接返回“及格”后面的score 90根本不执行。正确做法是把更具体的条件放在前面CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 ENDELSE也很容易被忽略。搜索CASE如果所有WHEN都不匹配并且没有ELSE那么返回NULL。在SELECT里显示NULL可能无所谓但如果CASE参与了聚合运算NULL会被COUNT忽略、会被SUM跳过统计结果就会和预期差得很远。我建议除非你明确知道要利用NULL否则尽量显式写ELSE。尤其在聚合场景里写不写ELSE往是“结果对”和“结果错”的分水岭。3. 实战场景从聚合统计到批量更新的完整案例3.1 用CASE配合GROUP BY做区间分类统计先准备一张订单表模拟实际业务里的明细数据CREATE TABLE order_detail ( order_id INT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, order_date DATE NOT NULL );现在有个典型需求统计每个用户小额100、中额100~500、大额500订单各有多少笔。很多人第一反应是写三次查询再合并其实一个CASE加GROUP BY就能解决SELECT user_id, COUNT(CASE WHEN amount 100 THEN 1 END) AS low_orders, COUNT(CASE WHEN amount 100 AND amount 500 THEN 1 END) AS mid_orders, COUNT(CASE WHEN amount 500 THEN 1 END) AS high_orders FROM order_detail GROUP BY user_id;这里的关键点是COUNT配合CASE时不匹配的分支返回NULLCOUNT只统计非NULL值所以天然只计数符合条件的行。但有一个坑我必须强调不要写成COUNT(CASE WHEN ... THEN 1 ELSE 0 END)。因为ELSE 0会让每一行都返回一个非NULL值COUNT会把不符合条件的行也统计进去最后所有档位都是总行数。如果你习惯写ELSE那就配合SUM使用写成SUM(CASE WHEN ... THEN 1 ELSE 0 END)SUM忽略NULL、加0不影响结果两种写法都行但别混用。3.2 一次CASE实现行转列把竖表变横表报表里经常遇到“月份在行、商品在列”的展示需求。数据在表里是纵向的一单一单记录但报表要横向展开成1月、2月、3月这样的固定列。MySQL没有PIVOT函数行转列最经典的做法就是CASE加聚合。比如要统计2024年上半年每个商品的月度销售额横向展示1月到6月SELECT product_name, SUM(CASE WHEN MONTH(order_date) 1 THEN amount ELSE 0 END) AS jan_amt, SUM(CASE WHEN MONTH(order_date) 2 THEN amount ELSE 0 END) AS feb_amt, SUM(CASE WHEN MONTH(order_date) 3 THEN amount ELSE 0 END) AS mar_amt, SUM(CASE WHEN MONTH(order_date) 4 THEN amount ELSE 0 END) AS apr_amt, SUM(CASE WHEN MONTH(order_date) 5 THEN amount ELSE 0 END) AS may_amt, SUM(CASE WHEN MONTH(order_date) 6 THEN amount ELSE 0 END) AS jun_amt FROM order_detail WHERE order_date BETWEEN 2024-01-01 AND 2024-06-30 GROUP BY product_name;执行过程可以这么理解先按product_name分组然后在每一组内部对每一行判断MONTH(order_date)等不等于目标月份等于就返回amountSUM把本月的金额累加不等于就返回0不影响SUM结果。这里的ELSE 0写不写都不影响最终数值因为SUM会忽略NULL但写出来更直白同事接手时一眼就明白意图。这种方案的局限是列数固定。如果月份不是确定的6列而是根据数据动态变化就需要用预处理语句和GROUP_CONCAT拼接SQL复杂度会高不少。多数报表场景的列是固定的CASE方案足够用。3.3 把CASE用在UPDATE里批量修正数据的正确姿势CASE不只在查询里好用在UPDATE的SET子句里同样威力巨大。它能把“逐行写循环”变成一条SQL完成批量修正。举例某次接口同步故障订单表的pay_status字段出现了错误状态现在需要按规则修正原本为1且订单金额大于1000的改为2原本为1且金额小于等于1000的改为3原本为2的改为4。UPDATE order_detail SET pay_status CASE WHEN pay_status 1 AND amount 1000 THEN 2 WHEN pay_status 1 AND amount 1000 THEN 3 WHEN pay_status 2 THEN 4 ELSE pay_status END WHERE pay_status IN (1, 2);SET子句里的CASE会逐行评估每读一行就按顺序匹配WHEN条件命中了就返回新值。这里的ELSE pay_status是为了兜底让不符合规则的行保持原值。实际操作中我有两个强烈建议。第一UPDATE之前一定先用同条件的SELECT看一眼数据SELECT order_id, pay_status, amount, CASE WHEN pay_status 1 AND amount 1000 THEN 2 WHEN pay_status 1 AND amount 1000 THEN 3 WHEN pay_status 2 THEN 4 ELSE pay_status END AS new_pay_status FROM order_detail WHERE pay_status IN (1, 2);先确认每条记录会被改成什么值再执行UPDATE。第二如果线上环境数据量大务必开启事务先做一轮START TRANSACTION; UPDATE order_detail SET pay_status CASE ... END WHERE pay_status IN (1, 2); -- 核对影响行数和数据 COMMIT; -- 有问题就 ROLLBACK;这种批量更新方式比写游标循环强太多一条UPDATE就能完成效率高、事务也容易控制。3.4 排序场景的奇技用CASE定制ORDER BYORDER BY后面其实可以跟任意表达式CASE就是其中非常好用的一个。它可以实现“自定义优先级排序”。比如列表要优先展示待支付订单pay_status0然后其他状态按金额倒序排SELECT order_id, pay_status, amount FROM order_detail ORDER BY CASE WHEN pay_status 0 THEN 0 ELSE 1 END, amount DESC;执行逻辑是第一排序键把pay_status0的订单映射成0其余映射成1于是待支付订单排在最前面同一个优先级内再按amount倒序排列。再比如状态字段是字符串希望按“已发货、已支付、待支付”这个自定义顺序展示而不是默认字典序SELECT order_id, order_status FROM order_detail ORDER BY CASE order_status WHEN 已发货 THEN 1 WHEN 已支付 THEN 2 WHEN 待支付 THEN 3 ELSE 4 END;这种写法的优势是灵活不需要额外维护一个排序权重表。缺点是当数据量很大时CASE在ORDER BY里通常没法走索引MySQL大概率要用filesort。所以在百万级以上的表里做这种排序更稳妥的方案是在表里加一个sort_weight冗余字段用程序维护数值然后直接ORDER BY那个字段。4. 常见效率陷阱与排查心得4.1 命中顺序与NULL结果不对的第一排查点我在帮同事排查SQL时CASE结果不对的案例里命中顺序错误和NULL处理错误占了绝大多数。先说命中顺序。前面讲过CASE从上往下短路执行一旦命中就退出。排查时可以简单记一句话能覆盖更多记录的广义条件往后放更精确的狭义条件往前放。判断成绩区间、金额区间时尤其要注意。再说NULL。SQL里任何和NULL做等值比较都会得到UNKNOWN不会进入THEN分支。举例SELECT CASE WHEN NULL THEN A ELSE B END; -- 结果BWHEN NULL本身不是合法条件它不会匹配NULL字段。如果你要判断某个字段是否为NULL必须用IS NULLSELECT CASE WHEN order_date IS NULL THEN 无日期 ELSE 有日期 END AS date_flag FROM order_detail;还有人会踩CASE col WHEN NULL的坑简单CASE里写CASE col WHEN NULL THEN...也是不生效的因为NULL和NULL比较不会为真。判断NULL只能用搜索CASE的IS NULL。4.2 类型隐式转换简单CASE最常见的天坑简单CASE的本质是等值比较MySQL在做比较时会触发类型转换规则。字符串和数字比较时字符串通常会被转成数字。看一个例子SELECT CASE 1 WHEN 1 THEN 匹配 ELSE 不匹配 END; -- 结果匹配如果字符串不是纯数字比如1aMySQL也会把它转换成1再比较所以同样可能匹配。这在业务里是个隐患假如status列是字符串类型你写CASE status WHEN 1 THEN 是可能把status1a的记录也匹配进去而你自己完全没意识到。我的建议很直接CASE后面是什么类型WHEN后面就写什么类型。列是VARCHARWHEN里就写字符串比如WHEN paid列是INTWHEN里就写整数。不要依赖MySQL的隐式转换它虽然能勉强工作但迟早会坑你一次。如果确实需要跨类型比较先显式CAST再比较至少行为是明确的。4.3 CASE和IF、IFNULL怎么选不少人在写条件判断时会在CASE、IF、IFNULL之间犹豫这里我总结一个选型逻辑只是想把NULL换成默认值比如IFNULL(remark, 暂无备注)用IFNULL最简洁。只有二分叉比如性别显示男或女用IF也行逻辑不复杂。分支超过两个或者需要在GROUP BY、ORDER BY、UPDATE中使用直接选CASE。需要统计分类计数必须配合CASE写聚合IF虽然能写但可读性差。CASE和IF还有一个重要区别CASE是表达式可以嵌入到任何表达式位置包括索引表达式少数情况、生成列定义、视图定义IF是函数底层实现的灵活性不如CASE。从SQL标准兼容性角度看CASE也是更通用的写法换到其他数据库迁移成本更低。4.4 性能问题CASE会不会导致索引失效很多人一看到CASE就担心性能。这里得区分场景。CASE写在SELECT或ORDER BY里通常只是对查询结果进行逐行计算本身不直接导致全表扫描。比如SELECT CASE WHEN ... END FROM table决定扫描范围的是WHERE条件不是SELECT里的CASE。真正要小心的是把CASE写在WHERE里-- 这种写法通常会导致索引失效 SELECT order_id FROM order_detail WHERE CASE WHEN amount 100 THEN 1 ELSE 0 END 1;优化器很难对CASE表达式推导出索引范围大概率变成全表扫描。遇到这种需求正确思路是把条件直接改写为WHERE amount 100让索引正常工作。另一个性能点是CASE配合ORDER BY前面已经提到不能走索引大表会有filesort。还有CASE配合UPDATE更新范围越精确越好WHERE条件尽量缩小影响行数否则InnoDB的行锁和undo log都会放大。说实话CASE的性能问题往往不在CASE本身而在你把它放错了位置。能用普通条件表达的地方不要用CASE包装用得上CASE的地方分类聚合、行转列、自定义排序它的性能完全可以接受。5. 一点使用体会当作结尾我自己用CASE用得最多的地方反而不是SELECT而是GROUP BY和UPDATE。很多同事看到复杂报表第一反应是写子查询或者拼临时表其实用一个CASE把分类维度放进去SQL又短又不容易错。我踩过最狠的坑是COUNT里写ELSE 0报表数据直接翻倍查了半天才意识到COUNT统计的是非NULL行数不是数值本身。后来自检SQL时我养成一个习惯凡是CASE参与聚合先在脑子里走一行样例数据或者直接把明细SELECT出来看一遍修改生产数据前一定开事务、备份表。这个习惯救过我很多次。如果你刚开始用CASE建议按这个顺序练先从简单CASE的字段翻译练手再尝试搜索CASE做区间判断然后把它塞进GROUP BY做分类统计最后试着在UPDATE里做批量修正。这几步走完CASE的基本功就扎实了。
