MySQL的CASE WHEN是我见过的被低估得最惨的SQL功能很多人只在刷面试题的时候看到过它真到自己写业务代码却总是想不起来用。实际上它就是SQL世界里的if-else却比if-else更值钱因为判断是在数据库内部完成的不需要把几十万行数据全部拉回应用层再一条条循环处理。这篇文章我会把CASE WHEN的两种语法、聚合统计、行转列、批量更新、自定义排序、存储过程配合使用、以及NULL和性能相关的坑一次讲清楚兼顾正在学MySQL的新手、写业务的老手、以及准备面试的同学。读完你至少能少写几十行代码还能在排查问题的时候多一种思路。1. CASE WHEN的两种写法从语法本质开始很多教程会把CASE WHEN分成简单CASE和搜索CASE两种但没有把为什么分成两种讲明白。我的理解很简单一种解决这个字段等于哪个值的问题一种解决这个条件是否成立的问题。本质上它们都是表达式最终都会算出一个值可以被放在SELECT、WHERE、ORDER BY、GROUP BY、UPDATE甚至存储过程里。所以不要把它当成一条语句它是一个能产出值的表达式。1.1 简单CASE表达式适合等值判断简单CASE的语法是这样的CASE 字段或表达式 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ... ELSE 默认结果 END它做的事情很直白把WHEN后面的值和CASE后面的字段做等值比较谁先匹配上就返回THEN后面的结果后面的就不会再看了。你可以把它理解成编程语言里的switch-case。举一个最典型的场景订单表里状态字段存的是0、1、2这类数字页面展示要显示待支付已支付已发货。新手最容易写出的方式是先全表查出来然后在程序里一个if一个else判断稍微懂点SQL的人会写CASESELECT id, CASE status WHEN 0 THEN 待支付 WHEN 1 THEN 已支付 WHEN 2 THEN 已发货 ELSE 其他 END AS status_text FROM orders;这里有一个我在工作中踩过的小坑简单CASE的WHEN比较是等值比较如果status列里混入了字符串比如0MySQL可能会做隐式转换。大多数情况能匹配上但一旦索引列参与比较隐式转换极有可能让索引失效。所以能用简单CASE的前提是确定这个字段的值域是干净的等值集合。另外ELSE不是必须的。如果不写ELSE所有未匹配的行会返回NULL。NULL在Java、Python里都能处理但在报表工具里很容易显示成空白前端同事可能就会拿着空数据来找你。如果业务上确实有其他这种兜底值建议还是写上ELSE成本极低收益是一眼就能看出数据完整性。1.2 搜索CASE表达式适合范围与复合条件搜索CASE的语法是CASE WHEN 布尔条件1 THEN 结果1 WHEN 布尔条件2 THEN 结果2 ... ELSE 默认结果 END注意CASE后面不跟字段了WHEN后面直接写完整条件可以是大于、小于、BETWEEN、LIKE、甚至是AND、OR组合的复合条件。我们平时说的SQL里的if-else其实就是指这种写法。还是用订单数据举例。假设要根据订单金额打标签1000元以上是大单500到999是中单100以下算小单SELECT id, amount, CASE WHEN amount 1000 THEN 大单 WHEN amount 500 THEN 中单 ELSE 小单 END AS order_level FROM orders;这里要注意两个非常重要的细节。第一个是条件顺序CASE WHEN会从上到下逐条判断一旦某个WHEN满足后面的条件就不再执行。所以写的时候要把更严格、更具体的条件放在前面。比如上面这个例子如果把amount 500写在amount 1000前面那么一笔1500元的订单也会被判断成中单因为它在第一个WHEN就满足了根本走不到后面的条件。第二个是THEN和ELSE返回结果的类型尽量保持一致。MySQL会在所有THEN和ELSE里推断最终结果的类型如果有的返回字符串大单有的返回数字100就会发生隐式类型转换轻则结果是字符串100重则影响排序和比较。我的习惯是返回文本就全部加引号返回数字就全写数字绝不混写。那这两种写法什么时候用哪个我的经验是等值判断、字段值可以枚举时用简单CASE代码短、阅读成本低范围判断、多字段联合判断、甚至判断NULL时必须用搜索CASE。因为简单CASE完成不了大于小于IS NULL这类条件判断。2. 聚合统计中的CASE WHEN把条件变成列CASE WHEN真正显示出威力是在配合聚合函数的时候。很多人脑子里的统计逻辑是要几个数就跑几条SQL然后在应用层把它们拼起来。这种做法不是不行但性能和代码维护性都很差。CASE WHEN可以让你一次扫描表、一次分组然后把多个条件的统计结果一次性算出来。2.1 用SUM和CASE WHEN做条件计数先看一个最经典的场景统计每天新增的订单总数、已支付订单数、已发货订单数。新手写法可能是三条SQL分别跑SELECT COUNT(*) FROM orders WHERE DATE(created_at) 2025-01-01; SELECT COUNT(*) FROM orders WHERE DATE(created_at) 2025-01-01 AND status 1; SELECT COUNT(*) FROM orders WHERE DATE(created_at) 2025-01-01 AND status 2;如果只是跑一次还好要是每天定时任务都要算就等于把同一张表扫了三遍。用CASE WHEN可以一条SQL搞定SELECT DATE(created_at) AS order_day, COUNT(*) AS total_cnt, SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS paid_cnt, SUM(CASE WHEN status 2 THEN 1 ELSE 0 END) AS shipped_cnt FROM orders GROUP BY DATE(created_at) ORDER BY order_day DESC;核心原理其实就一句话聚合函数SUM会对每一行的表达式结果求和。满足条件时CASE WHEN算出1累加下来就是满足条件的行数不满足时算出0不影响总数。用求和来计数本质上就是把条件变成了数值1或0。这里有两种等价写法有的人喜欢用COUNT(CASE WHEN status 1 THEN id END)因为COUNT会忽略NULL值不满足条件时CASE返回NULL就不计数也能得到结果。但我不推荐这种写法主要问题是它绕了一个弯读代码的人要手动理解THEN id只是为了凑一个非空值。如果没有ELSE还会让不满足条件的行返回NULL虽然COUNT不计它但可读性真的不好。SUM(CASE WHEN ... THEN 1 ELSE 0 END)无论从可读性还是逻辑清晰度来说都更适合绝大多数人。2.2 行转列经典面试题的底层逻辑CASE WHEN配合聚合函数还能把一列里的多个值转成多列展示这就是面试题里常说的行转列。业务场景比如每个产品在订单表里可能有多种状态我想让一行记录里同时看到这个产品的支付数、取消数、退款中数。SELECT product_name, SUM(CASE WHEN status 已支付 THEN 1 ELSE 0 END) AS paid_cnt, SUM(CASE WHEN status 已取消 THEN 1 ELSE 0 END) AS canceled_cnt, SUM(CASE WHEN status 退款中 THEN 1 ELSE 0 END) AS refunding_cnt FROM orders GROUP BY product_name;这样查出来的结果一行就是一个产品的状态概览。应用层拿到的数据可以直接渲染成表格不需要自己再循环累加。我再多说一个进阶用法如果我想在这个基础上算支付率可以直接复用前面的表达式不用把数据查出来在代码里除一遍SELECT product_name, COUNT(*) AS total_cnt, SUM(CASE WHEN status 已支付 THEN 1 ELSE 0 END) AS paid_cnt, CONCAT( ROUND( SUM(CASE WHEN status 已支付 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2 ), % ) AS paid_rate FROM orders GROUP BY product_name;我在实际报表开发里很喜欢这么干因为聚合计算放在SQL里最大的好处是减少数据传输量。十万行订单数据应用层只需要拿到几十个产品的统计结果。不过要注意如果条件里的列走了索引而聚合时又加了很多CASE WHEN计算MySQL未必会使用索引做覆盖扫描所以数据量特别大时要重点看执行计划别以为一条SQL就一定比多条SQL快。用CASE WHEN合并成一条SQL的核心优势是减少扫描次数而不是总能奇迹般地利用索引。3. UPDATE和ORDER BY中的CASE WHEN两个高频场景CASE WHEN不只是花式查询它在数据修改和排序上也非常实用。很多人在UPDATE语句里只会写SET column 固定值遇到不同条件要更新成不同值就开始发怵然后写一堆UPDATE语句或者循环一条条执行。我建议你试试把CASE WHEN用在SET子句里。3.1 批量更新一段UPDATE完成多分支赋值举个例子电商后台要做促销活动绿标商品打9折黄标商品打8折红标商品不参与活动保持原价。新手可能会写三条UPDATEUPDATE product SET price price * 0.9 WHERE tag green; UPDATE product SET price price * 0.8 WHERE tag yellow; UPDATE product SET price price * 1.0 WHERE tag red;三条SQL没问题但缺点很明显首先要扫描表三次其次如果中间某条执行失败会出现部分商品改了价、部分没改价的情况还得再做数据校验。用CASE WHEN一条搞定UPDATE product SET price CASE WHEN tag green THEN price * 0.9 WHEN tag yellow THEN price * 0.8 WHEN tag red THEN price * 1.0 ELSE price END, updated_at NOW() WHERE status on_sale;这条SQL会把所有在售商品按标签规则更新一次。加ELSE price的意义是即使将来出现一个不在枚举范围内的新标签也不会把价格更新成NULL。这算是我踩过的坑里最值得提醒的一项UPDATE语句里如果用CASE WHEN给字段赋值忘了写ELSE所有不满足WHEN条件的行字段都会被更新成NULL。一旦线上执行修改恢复都麻烦。需要特别提醒的是UPDATE的CASE WHEN不会减少锁的范围。即便只用一条SQLMySQL也是扫描匹配的行并逐一加锁。如果匹配的行非常多比如全表几十万行那这条SQL执行期间就会锁住大量数据。我实际处理大批量更新时会把它拆成多批来执行比如每次只更新一部分数据用WHERE id BETWEEN ... AND ...限制范围批量之间停顿几秒这样可以明显降低锁冲突的概率。这个经验对生产环境尤其重要毕竟谁都不想半夜被DBA叫起来说锁表了。3.2 自定义排序让结果按业务规则排列普通的ORDER BY只能按字段值排序但业务里经常需要自定义优先级。比如订单状态要按退款中优先处理、已支付其次、待支付再次、已取消最后这样一个业务规则去排。单纯按status字段排MySQL默认按0、1、2、3的数字或字典序排根本表达不出这种业务顺序。解决办法就是ORDER BY后面跟CASE WHENSELECT order_id, status FROM orders ORDER BY CASE status WHEN 3 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 0 THEN 3 ELSE 4 END, created_at DESC;原理非常直观排序时需要的是一个排序列的值CASE WHEN正好能为每一行算出一个根据规则生成的值。MySQL会把计算后的结果当作排序键来用所以结果就会按我们定义的业务顺序显示。我常用的另一个替代写法是MySQL的FIELD函数ORDER BY FIELD(status, 3, 1, 2, 0)。它比CASE WHEN短但只支持等值匹配不支持范围判断。如果优先级规则里夹杂了金额大于1000的排最前退款超过3天的排前面这类复杂条件还是老老实实用CASE WHEN搜索表达式。这里有一个性能上的注意点在ORDER BY里使用CASE WHEN本质上是给每一行做一次计算。数据量小的时候问题不大数据量大到几百万行这个计算可能让排序变慢因为MySQL很难用上索引直接给出的顺序。我的建议是如果业务排序规则是长期固定且频繁使用的可以考虑在表里增加一个sort_field字段在写入或更新时同步维护如果只是为了临时看数据直接用CASE WHEN最方便不必过度设计。4. 存储过程中怎么用CASE WHENCASE WHEN在存储过程里有两种完全不同的身份这一点经常把人绕晕。一种是在SELECT或UPDATE里继续当表达式使用作用跟前面几章一样另一种是作为控制流语句类似程序里的if-elseif-else用来决定接下来执行哪一段SQL。后者语法上有一个容易被忽略的区别表达式CASE用END结尾控制流CASE用END CASE结尾而且THEN后面跟的不是结果值而是一条语句。4.1 流程控制分支执行不同的处理逻辑举例来说我要写一个存储过程根据不同订单金额等级设置不同的折扣并把这个折扣记录到日志表里。用存储过程U里的CASE语句DELIMITER $$ CREATE PROCEDURE sp_calc_discount( IN p_order_id INT, IN p_amount DECIMAL(10,2), OUT p_discount DECIMAL(10,2) ) BEGIN DECLARE v_level VARCHAR(20); SELECT CASE WHEN p_amount 1000 THEN VIP WHEN p_amount 500 THEN 银卡 ELSE 普通 END INTO v_level; CASE WHEN v_level VIP THEN SET p_discount 0.8; WHEN v_level 银卡 THEN SET p_discount 0.9; ELSE SET p_discount 1.0; END CASE; INSERT INTO discount_log(order_id, level, discount, created_at) VALUES (p_order_id, v_level, p_discount, NOW()); END$$ DELIMITER ;注意区分一下SELECT CASE WHEN ... INTO v_level是在用表达式给变量赋值这里CASE的结尾是END没有CASE后缀后面从CASE WHEN v_level VIP THEN SET ...开始是真正的流程控制语句结尾必须写END CASETHEN后面跟的是SET语句。两者在同一个存储过程里出现很容易写混。我的个人建议是存储过程适合封装那些需要多步SQL、多次写入的固定业务规则。如果只是算一个折扣值那直接写在业务SQL里就够了完全没必要绕一圈建存储过程。因为存储过程在运维上的成本偏高版本管理困难、SQL审核不方便、线上排查问题得多开一个通道。不要为了用存储过程而用要用在真正能简化复杂链条的地方。4.2 在动态SQL里拼接CASE WHEN还有一种比较高级的玩法是用CASE WHEN生成动态SQL。这类场景在报表系统、后台管理系统里很常见接口传入不同的排序类型或筛选规则SQL的结构也会跟着变普通参数化查询无法满足就需要拼SQL字符串。最简单的例子根据排序类型参数动态生成ORDER BYSET sort_sql CASE WHEN p_sort_type 1 THEN ORDER BY created_at DESC WHEN p_sort_type 2 THEN ORDER BY amount DESC ELSE ORDER BY id DESC END; SET sql CONCAT(SELECT * FROM orders , sort_sql); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE;这个用法在存储过程或应用层里都成立。CASE WHEN在这里的作用是根据条件选择一个字符串片段本质上还是返回一个值。要注意的是动态SQL拼接时一定要检查变量来源不能在拼字符串时把用户输入直接塞进去否则容易出安全问题。用PREPARE EXECUTE的目的是让MySQL对SQL做语法准备有注入风险时至少要在拼接前做好校验和白名单我通常不会允许用户输入直接变成SQL关键字。再进阶一步动态SQL还可以配合行列转换。比如做报表时需要在结果集里动态生成每个产品名称作为一列产品名称是不断新增的写死列名不现实。可以用GROUP_CONCAT拼出一段CASE WHEN表达式SET cols ( SELECT GROUP_CONCAT(DISTINCT CONCAT( SUM(CASE WHEN product_name , product_name, THEN 1 ELSE 0 END) AS , product_name, ) SEPARATOR , ) FROM orders ); SET sql CONCAT(SELECT customer_id, , cols, FROM orders GROUP BY customer_id); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE;由于product_name来自数据库字段如果字段本身可能包含单引号就必须提前处理或转义。这套做法很强大但可读性确实要差一些我一般只在报表中心这种确实有动态列需求的场景里使用普通业务代码不建议为了秀操作而用。5. 性能、NULL与常见坑CASE WHEN很好用但用不好也会踩坑。我把平时见到的高频问题集中讲一下这些问题在面试里也经常被当成考察点。5.1 在WHERE里用CASE WHEN可能导致索引失效很多人会用CASE WHEN临时改变某个条件的计算结果但WHERE里用它会带来很大的性能隐患。比如这个写法SELECT * FROM orders WHERE CASE WHEN status 1 THEN created_at NOW() - INTERVAL 1 DAY ELSE 1 1 END;表面上逻辑没问题status等于1的订单只查最近一天的数据其他状态的数据全部返回。但问题在于MySQL优化器很难把一个基于CASE WHEN的复杂表达式转换成可以走索引的范围扫描。它很可能选择全表扫描每一行都先计算一遍CASE再判断是否满足结果性能完全不可控。这种场景更应该写成SELECT * FROM orders WHERE (status 1 AND created_at NOW() - INTERVAL 1 DAY) OR status 1;这样status和created_at都有机会各自走索引。注意OR条件也不总能同时用到两个不同列的索引如果数据量很大更稳妥的写法是分成两个查询再UNION ALL。我把CASE WHEN放在WHERE里的原则很简单能不用就不用它更适合在SELECT、GROUP BY、ORDER BY里做结果计算不适合在WHERE里做过滤条件。5.2 小心NULLELSE缺失和列值为NULL很多人包括我自己早期写CASE WHEN都不爱写ELSE认为数据库返回NULL也无所谓。但无数经验告诉我NULL引发的问题往往比报错还难排查。看这个例子SELECT id, CASE WHEN status 1 THEN 已支付 WHEN status 2 THEN 已发货 END AS status_text FROM orders;如果status是0这个表达式会返回NULL而不是空字符串。应用层如果拿String接收有的框架会变成null有的框架会变成空串前后端联调时根本看不出来是数据问题还是代码问题。老老实实写上ELSE既清晰又少一个隐性Bug。另一个经典塌陷是简单CASE判断NULL无效。你可能觉得CASE NULL WHEN NULL THEN 空 ELSE 非空 END能匹配NULL但事实上这个表达式永远返回非空因为简单CASE用等值比较去匹配而NULL与任何值做等值比较的结果都是NULL不是TRUE。要判断NULL必须用搜索CASESELECT id, CASE WHEN remark IS NULL THEN 没有备注 ELSE remark END AS remark_text FROM orders;这个坑在SQL面试里十有八九会被问到答出来就能刷掉一批人。5.3 CASE WHEN和IF函数怎么选MySQL里有一个IF函数用法是IF(expr, value_if_true, value_if_false)功能上和CASE WHEN有重叠。很多人经常纠结用哪个。我的看法是二选一、逻辑简单的时候用IF也没问题三选一甚至更多分支的时候必须用CASE WHEN因为IF嵌套一旦超过两层代码就没法看了别人维护成本很高。从跨数据库角度看CASE WHEN是标准SQL语法从MySQL迁到PostgreSQL、Oracle基本不用改IF是MySQL方言换数据库要重新改。从执行性能上两者的差异并不大SQL优化器一般都能处理好。所以我的选择标准是可读性优先分支多就用CASE WHEN迁移性要求高就用CASE WHEN。再往前说一层存储过程里也有IF语句语法是IF ... THEN ... ELSEIF ... THEN ... ELSE ... END IF;那个是给流程控制用的。要注意它和MySQL的IF函数不是一回事这又是另一个容易混淆的点。如果在存储过程里想根据条件执行一段逻辑用IF语句如果只是想算一个结果值用CASE表达式或IF函数别混用。最后分享一个我自己的习惯每当我要写超过两个分支的判断时都会先在草稿里把条件和顺序列一遍保证每个WHEN之间互斥、覆盖完整、顺序合理然后才去写SQL。CASE WHEN最迷人的地方就是它以表达式的身份安安静静待在SQL里但最危险的地方也在这里——一个小疏忽比如忘写ELSE、类型不一致、条件顺序错位就会让数据结果出现偏差。多花一点时间把条件和边界想清楚比写一大段事后修复逻辑划算得多。