★ FEATURED ARTICLE 1. CASE 到底是干嘛的先聊明白做 MySQL 开发或者日常写 SQL 的人多多少少都会遇到这样的场景查出来的结果是一堆数字状态码比如 1、2、3可业务上想看的是未支付已支付已退款或者一张订单表里存了城市字段想按华东、华北、华南这样分组统计但表里只有省份名又或者查询结果想按照某个自定义的优先级排序而不是简单的升序降序。这些需求如果只会用 WHERE 和 GROUP BY往往会卡住WHERE 是过滤GROUP BY 是分组它们都改变不了这条记录在结果里长什么样。可很多时候我们需要的是——同一行数据按照某个条件输出不同的值。这就是 CASE 表达式最核心的价值在 SQL 里写条件分支逻辑。我在实际项目里第一次被 CASE 救场是做一个报表接口。业务方要求把订单状态从数字码翻译成中文同时还要把金额按照区间打上小额中额大额的标签。当时刚接触 MySQL 不久脑子里第一个想法是查出数据后在 Java 里循环判断。后来发现一次要查几千条循环翻译不仅代码啰嗦性能还难看。换成 CASE 之后一条 SQL 直接搞定翻译、打标、分组统计全在数据库里完成接口响应时间直接降了一个量级。从那以后我就养成了习惯能用 SQL 表达的就别拖到业务代码里。另外CASE 不是某个特定版本的特性MySQL 4.x 之后就一直支持你手上的 5.7、8.0 全都能用不存在兼容性顾虑。它真正的价值在于四个方面结果集内做字段值转换、配合聚合函数做条件统计、在 ORDER BY 里实现自定义排序、在 UPDATE 里做批量条件更新。这篇文章我不打算讲什么高深理论就围绕这几个实际场景把 CASE 的两种写法、易踩的坑、性能注意事项一条一条掰扯清楚。适合刚接触 SQL 不久的新手也适合写过一段时间但没系统整理过 CASE 用法的同学。2. CASE 的两种写法与核心语法拆解2.1 简单 CASE 表达式等值判断的快捷方式先看第一种写法它的语法结构是CASE 列名或表达式 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ... ELSE 默认结果 END这种写法叫简单 CASE 表达式核心逻辑是拿一个固定的表达式去跟后面的值做等值比较。举个例子订单表里有 status 字段1 代表待支付2 代表已支付3 代表已发货4 代表已完成SELECT order_id, status, CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 WHEN 4 THEN 已完成 ELSE 未知状态 END AS status_text FROM orders;这段 SQL 执行后查出来的每一行会多出一个 status_text 字段原来的数字状态被翻译成了人类能看懂的文字。这里的执行逻辑很好理解MySQL 逐行读取数据把 status 的值依次跟 1、2、3、4 比较匹配到哪个就返回对应的 THEN 结果全都匹配不上就返回 ELSE 兜底。这里有一个重要的隐藏行为简单 CASE 只支持等值比较也就是 判断。如果你想表达金额大于 1000 走这个分支、金额小于 500 走那个分支简单 CASE 写不了因为 和 不是等值关系。这种场景必须用下面第二种写法。2.2 搜索式 CASE 表达式真正的条件判断利器第二种写法叫搜索式 CASE 表达式语法如下CASE WHEN 条件表达式1 THEN 结果1 WHEN 条件表达式2 THEN 结果2 ... ELSE 默认结果 END注意区别CASE 关键字后面不跟列名直接跟 WHEN每个 WHEN 后面是一个完整的条件表达式可以是任意合法的布尔判断。还是拿金额打标签举例SELECT order_id, amount, CASE WHEN amount 10000 THEN 大额 WHEN amount 1000 THEN 中额 ELSE 小额 END AS amount_level FROM orders;搜索式 CASE 比简单 CASE 灵活得多它可以写范围判断、模糊匹配、多条件组合比如WHEN status 2 AND pay_type wechat THEN 微信已支付。实际开发中90% 的场景我用的都是搜索式写法因为它覆盖了简单 CASE 的全部能力——等值判断本身就是一种特殊条件。两种写法还有一个微妙的执行顺序问题WHEN 分支是从上往下依次判断的一旦命中某个 WHEN后面的分支就不再执行。这一点在写范围条件时尤其要注意。比如上面金额分级的例子如果把条件顺序反过来写CASE WHEN amount 1000 THEN 中额 WHEN amount 10000 THEN 大额 ELSE 小额 END那么 amount 20000 的记录会命中第一个 WHEN被错误打上中额标签。因为 20000 1000 是成立的MySQL 不会继续往后看第二个条件。这种错误很难通过报错发现因为它不报错只是结果悄悄错了。所以写范围判断时一定要想清楚分支顺序范围大的往下放范围小的往上放或者用 AND 把边界限定死。2.3 CASE 表达式的位置到底能放哪很多人以为 CASE 只能放在 SELECT 后面这误会太深了。CASE 是一个表达式只要 SQL 语法上允许出现表达式的地方它都能出现。具体来说有这么几类SELECT 子句这个最常见用来做字段值转换、打标签WHERE 子句可以用 CASE 做复杂的动态过滤条件虽然用得不多但某些场景很管用ORDER BY 子句实现自定义排序逻辑比如把特定状态排在最前面这个是高频用法GROUP BY 子句对分组依据做加工比如把省份映射成大区再分组统计HAVING 子句对聚合结果做条件判断时也能用。甚至 UPDATE 语句的 SET 子句里也能用 CASE实现一条 SQL 批量更新多种规则。理解表达式这三个字是关键。比如1 2是表达式CONCAT(a,b)是表达式函数调用是表达式那 CASE 这个能返回值的结构当然也是表达式。它返回什么类型取决于 THEN 分支里写的是什么类型。另外要记住CASE 的返回值类型会做类型转换统一MySQL 会尽量把多个分支的结果类型调整为兼容类型所以别在一个分支返回字符串另一个分支返回数字虽然 MySQL 通常不会报错但隐式类型转换容易埋雷。3. 四个高频实战场景直接抄作业3.1 行转列把多行记录变成一行多列行转列是 CASE 最经典的实战场景之一我愿称之为报表开发必修课。什么叫行转列就是原本一张明细表里一个维度对应多行数据我们希望把这些行里的某些值转成不同的列展示。举个例子有一张学生成绩表结构是 student_name、subject、score每个学生有三行记录分别对应语文、数学、英语SELECT student_name, MAX(CASE WHEN subject 语文 THEN score END) AS chinese_score, MAX(CASE WHEN subject 数学 THEN score END) AS math_score, MAX(CASE WHEN subject 英语 THEN score END) AS english_score FROM student_scores GROUP BY student_name;这段 SQL 的思路是这样的先按学生分组然后用 CASE 把每个科目对应的分数挑出来——当 subject 等于语文时返回 score否则返回 NULL。因为每个学生在同一分组里只有一条语文记录所以再用 MAX 把这条分数取出来其他科目的行不会干扰。最终结果就是每个学生一行语文、数学、英语各占一列。为什么这里要套 MAX很多人第一次看会蒙。因为 GROUP BY 之后每个分组里有三行数据如果不套聚合函数直接写CASE WHEN subject 语文 THEN score ENDMySQL 会报错或者返回不确定值。MAX 的作用不是求最大值而是从多行里挑一个非 NULL 的值。你也可以用 MIN、SUM效果一样但 MAX 和 MIN 语义最清晰。我之前遇到过有人问能不能不用聚合函数答案是不能除非你改用子查询或者 GROUP_CONCAT 拼接但那些写法在性能和维护性上通常不如 CASE 方案。3.2 条件聚合一次 GROUP BY 统计多组指标另一个高频场景是条件聚合。普通 GROUP BY 只能按某个字段分成多组然后每组算一个指标。但业务里经常出现这样的需求统计每个销售员的总订单数、成功订单数、失败订单数这三个数字分布在同一个字段里靠一个字段值区分。如果不用 CASE你可能要写三条 SQL 分别统计再在代码里拼装。用 CASE 可以一条 SQL 搞定SELECT sales_name, COUNT(*) AS total_orders, COUNT(CASE WHEN order_status SUCCESS THEN 1 END) AS success_orders, COUNT(CASE WHEN order_status FAIL THEN 1 END) AS fail_orders FROM orders GROUP BY sales_name;这里有一个非常实用的小技巧COUNT(CASE WHEN 条件 THEN 1 END)这种写法统计的是满足条件的行数。原理是 COUNT 只统计非 NULL 值当条件不满足时 CASE 返回 NULLCOUNT 会忽略它条件满足时返回 1被计入。这比SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)更简洁也少写了 ELSE 分支。我第一次看到这个写法时觉得挺巧妙的后来发现这是很多资深开发者的习惯写法。同样的思路还能扩展到求和、求平均。比如统计每个品类的线上销售额和线下销售额就可以写SUM(CASE WHEN channel online THEN amount END) AS online_amount。这套写法的好处是原本需要多次扫描表或写多条 SQL 的统计现在一次扫描全出结果对大数据量场景的性能帮助非常明显。3.3 自定义排序ORDER BY 里塞 CASEORDER BY 默认只能按升序或降序排可业务排序往往不按常理出牌。最典型的需求是把未处理的工单排最前面处理中的排第二已关闭的排最后每个状态内部再按创建时间倒序。这种自定义优先级排序就是 CASE 的拿手好戏SELECT ticket_id, status, created_at FROM support_tickets ORDER BY CASE status WHEN OPEN THEN 1 WHEN PROCESSING THEN 2 WHEN CLOSED THEN 3 ELSE 4 END, created_at DESC;排序时MySQL 先计算 CASE 表达式得到每个状态对应的数字优先级然后按这个数字排序数字相同的情况下再按 created_at 倒序。我实际使用中发现这个方案比在业务代码里排序靠谱得多——分页场景下尤其如此。如果你先把数据查出来再在代码里排序一旦分页就会出问题每页数据单独排序整体顺序就乱了。把排序逻辑放进 SQL数据库在分页前就完成了全局排序结果才是对的。再分享一个更复杂的排序需求。有一次做后台列表要求 VIP 用户排在普通用户前面同时未读消息的用户要在 VIP 用户里优先已读未读状态又要交叉。这种多级排序用 CASE 嵌套也能解决ORDER BY CASE WHEN is_vip 1 THEN 0 ELSE 1 END, CASE WHEN has_unread 1 THEN 0 ELSE 1 END, last_active_time DESC;逻辑是先分 VIP 和非 VIP 两大梯队VIP 靠前在同一个梯队内有未读消息的靠前最后按活跃时间倒序。这里的每个 CASE 都只负责一个维度的优先级多个 CASE 串联形成多级排序。这种写法比在程序里写 Comparator 要直接得多数据库引擎原生支持排序过程还不怎么消耗应用服务器内存。3.4 数据清洗与字段标准化数据分析场景里经常遇到脏数据。比如用户填写的省份字段有的写北京市有的写北京有的写北京省真的会有人这么写。如果要按省级维度做统计直接 GROUP BY province 会得到一堆乱七八糟的桶。用 CASE 做映射清洗是成本最低的方案SELECT CASE WHEN province IN (北京市, 北京, 北京省) THEN 北京 WHEN province IN (上海市, 上海, 上海省) THEN 上海 WHEN province IN (广东省, 广东) THEN 广东 ELSE province END AS province_clean, COUNT(*) AS user_cnt FROM users GROUP BY province_clean;注意这里 GROUP BY 后面可以直接写province_clean这个别名MySQL 在 GROUP BY 里是允许使用 SELECT 别名的这一点和有些数据库不同。如果你不确定自己用的数据库行为保险做法是在 GROUP BY 里重复写一遍 CASE 表达式或者写成子查询。另一个常见的清洗场景是日期和布尔值处理。比如某个字段存的是 Y 和 N展示层想显示成是和否同样是 CASE 直接转换。用 CASE 清洗的好处是所有转换逻辑都收敛在 SQL 层业务代码拿到的直接是干净数据不用再做二次处理。当然如果清洗规则特别复杂、映射表很大我会建议建一张配置表用 JOIN 解决而不是写一个几百行的 CASE。CASE 适合规则清晰、分支数量可控的场景一般超过十来个分支就该考虑其他方案了。4. 常见问题与排查技巧实录4.1 忘了写 ELSE结果全是 NULL这是我见过新手最容易踩的坑。CASE 表达式的 ELSE 是可选的不写 ELSE 时如果所有 WHEN 条件都不满足表达式返回 NULL。考虑这个例子SELECT order_id, CASE order_status WHEN PAID THEN 已支付 WHEN UNPAID THEN 未支付 END AS status_text FROM orders;如果表里存在状态为 REFUNDED 的订单查出来的 status_text 就是 NULL而不是你预想中的未知或者原始值。更麻烦的是NULL 在后续处理里往往会引起连锁问题比如拼接字符串时整条变 NULL排序时位置飘忽不定。我的习惯是只要不是明确知道数据里只有列出的几个取值就一律写 ELSE。哪怕 ELSE 写一个兜底提示词也比 NULL 强。排查这类问题的方法很简单——用WHERE 列 IS NULL过滤看看就知道有多少行落到了 ELSE 的坑里。4.2 分支顺序写反结果悄悄错误前面提过的范围判断分支顺序问题值得在这里再强调一次。因为这种错误完全不报错数据结果看起来也像模像样特别容易被忽略。这类问题的排查思路是把 CASE 的条件列出来画一条数轴看边界值落在哪个区间。比如金额大于等于 10000 是大额、1000 到 9999 是中额、小于 1000 是小额。你可以在数轴上标出 999、1000、9999、10000 这几个关键边界然后逐一验证写条件时的上下限是否覆盖了正确区间。我自己还会用一个笨办法验证先单独执行一条不加 CASE 的原始查询看看数据的原始分布再对比 CASE 之后的结果分布如果某个分组的数量和金额区间明显对不上多半就是条件范围写错了。数据库不会给你提示必须自己拿原始数据做交叉验证。4.3 ELSE 的类型与长度不匹配问题CASE 的多个分支返回值会做类型统一但如果你写得过于随意比如一个分支返回 VARCHAR(10)另一个分支返回一个超长字符串或者一个返回数字一个返回字符串MySQL 会按照隐式类型转换规则处理。多数情况下它能容忍但转换规则可能和你想的不一样。举个例子CASE WHEN flag 1 THEN 10 ELSE 0 END最后返回的类型是字符串还是数字实测结果是字符串因为 MySQL 会把数值 0 转成字符串 0。如果你把这个结果拿去和其他字段比较索引可能就用不上了。这类问题很难一眼发现我的建议是同一个 CASE 里所有分支尽量保持类型一致。如果你明明要返回字符串就老老实实写 0 而不是 0。另外要注意返回字符串时类型长度由所有分支中最长的那个决定实际去重或排序时不会因为类型长度出问题但使用 DISTINCT 或 UNION 时可能影响性能。4.4 CASE 在 WHERE 里用导致索引失效虽然 CASE 可以放在 WHERE 里但有一个性能陷阱值得说如果你把 CASE 用到 WHERE 的过滤条件中比如WHERE CASE WHEN type 1 THEN create_date ELSE update_date END 2024-01-01MySQL 无法对这种方式形成的过滤条件使用普通索引。因为索引建立在列上而 CASE 的结果是运行时计算出来的。数据量小的时候没问题数据上了百万级别全表扫描的代价就出来了。遇到这种需求我的做法是第一优先级改成 UNION ALL 分两条查询各自走独立索引第二优先级改成在表结构上冗余一个字段提前把计算好的值存储进去并建索引实在不行才接受全表扫描。这里要区分清楚CASE 放在 SELECT 或者 ORDER BY 里通常不会影响主查询的索引使用但放在 WHERE 里基本等于放弃索引。记住这个边界可以帮你避免不少线上性能事故。4.5 常见问题速查表问题现象可能原因解决方案结果出现不该有的 NULLELSE 分支省略显式补充 ELSE 兜底值分类数量偏多或偏少分支顺序覆盖了边界调整 WHEN 顺序或限定 AND 条件返回类型和预期不符分支类型不一致统一所有分支返回类型WHERE 中加 CASE 后查询变慢索引失效改写 UNION ALL 或冗余字段行转列结果出现多条重复忘记 GROUP BY 或漏了聚合函数补上 GROUP BY 和 MAX/MINGROUP BY 使用 CASE 别名报错不同数据库对别名的支持不同重复写 CASE 表达式或使用子查询5. 进阶玩法CASE 的更多组合场景5.1 UPDATE 里做条件批量更新CASE 用在 UPDATE 语句里可以做到一条语句按不同条件更新不同值。这个能力在处理状态流转类业务时非常高效。比如订单超时后要根据订单类型走不同的处理逻辑UPDATE orders SET status CASE WHEN order_type NORMAL AND created_at NOW() - INTERVAL 30 MINUTE THEN TIMEOUT_CLOSED WHEN order_type PRESALE AND created_at NOW() - INTERVAL 24 HOUR THEN TIMEOUT_CLOSED WHEN order_type NORMAL AND pay_deadline NOW() THEN CANCEL_PENDING ELSE status END WHERE status UNPAID;这里有个非常重要的细节ELSE 分支写的是status也就是保持原值不变。为什么必须写因为 CASE 返回的结果会直接覆盖 status 字段。如果不写 ELSE不满足任何条件的行status 会被更新成 NULL那是灾难性的。我曾经在一次生产事故复盘里见过类似的案例一条 UPDATE 忘了 ELSE几万行订单状态被清空。从那以后我给自己定了一条铁律UPDATE 语句里用 CASEELSE 必写且通常写原字段名。这种批量更新方式比逐行在应用代码里判断然后发 UPDATE 要快得多。一条 SQL 只扫描一次表在内存中完成逻辑判断和写入网络往返次数大幅减少。尤其在定时任务、批量补偿场景下性能差别是数量级的。5.2 CASE 与聚合函数组合的更多姿势除了前面说的 COUNT 和 SUMCASE 还可以和 AVG、MAX、MIN、GROUP_CONCAT 等组合出很多花样。举两个我实际用过的例子。统计某个月每天的经营状况可以用日期函数配合 CASE 做条件汇总但更有意思的是算完成率环比变化。比如要统计每个销售当月的目标完成率目标存在另一个配置表里实际销售额需要按状态过滤SELECT sales_id, SUM(CASE WHEN status SUCCESS THEN amount ELSE 0 END) AS actual_amount, SUM(CASE WHEN status IN (SUCCESS, PENDING) THEN amount ELSE 0 END) AS forecast_amount FROM orders WHERE MONTH(order_date) MONTH(CURRENT_DATE()) GROUP BY sales_id;这里两个 CASE 分别统计了实际已确认的金额和含待执行订单的预估金额一次查询出两列数据后续算完成率时直接用这两列相除就行。同样的逻辑如果拆成两条 SQL不仅要扫两遍表还要在代码里再合并维护起来也麻烦。再比如 GROUP_CONCAT 和 CASE 的配合可以做多值字段的条件拼接。当你想在结果里展示这个用户的异常标签时可以用 GROUP_CONCAT 把所有命中的标签拼成一串用 CASE 把条件变成标签文字SELECT user_id, GROUP_CONCAT( CASE WHEN login_count 0 THEN 未登录 WHEN order_count 0 THEN 未下单 WHEN refund_ratio 0.5 THEN 高退款 END ) AS abnormal_tags FROM user_stat GROUP BY user_id;这种拼接结果适合直接丢给前端做标签展示省去了在 Java/Python 里再循环判断的逻辑。5.3 CASE 与 IF、COALESCE、NULLIF 的对比选型MySQL 里和 CASE 功能有重叠的还有几个函数IF、IFNULL、COALESCE、NULLIF。日常开发中经常有人纠结用哪个我把它们的区别整理成一句话IF 适合单条件二选一CASE 适合多条件分支COALESCE 适合取第一个非 NULL 值NULLIF 适合把某个值转为 NULL。如果只有两个分支IF(条件, 真值, 假值)确实比 CASE 简洁。比如IF(sex1, 男, 女)一目了然。但一旦超过两个分支IF 就要嵌套写出来是这样的IF(status1, 待支付, IF(status2, 已支付, IF(status3, 已发货, 其他)))多层 IF 嵌套的可读性非常差维护起来更是灾难。这种场景直接用搜索式 CASE结构清晰得多。我个人的选型标准很简单两个分支用 IF三个及以上用 CASE需要取多个字段里的第一个非 NULL 用 COALESCE。另外要注意 IF 和 CASE 在 NULL 处理上的差异——IF(条件, a, b) 里如果条件为 NULL会被当作假值处理而 CASE 的简单写法遇到 NULL 比较可能返回 ELSE 或 NULL需要你明确处理。5.4 CASE 在存储过程与函数中的使用如果项目里写存储过程或函数CASE 也有两种用法值得提一下。一种是在 SQL 查询语句中作为表达式使用这前面已经讲透了另一种是作为程序流控制语句使用语法长这样CASE WHEN score 90 THEN SET grade A; WHEN score 75 THEN SET grade B; ELSE SET grade C; END CASE;注意这里的区别查询里的 CASE END 后面没有分号语句结束而存储过程里的 CASE 是独立的流程控制语句以 END CASE 结尾每个分支里通常是一组 SQL 语句。我见过有人把这两种写法搞混在查询里写 END CASE直接语法报错。查一下官方文档就能明白它们是同名但不同语境的两个东西。虽然存储过程在现代开发中用得越来越少但碰到了还是要知道这个区别。6. 从入门到顺手我的几点体会用了这几年 MySQL CASE我最深的感受是它不是什么炫技技巧而是一种用数据库思维解决问题的基础能力。很多刚开始写 SQL 的同学习惯把逻辑都搬到业务代码里总觉得数据库就是存取数据的地方。这种思维没有错但当你面对百万级数据、要求秒级响应时把条件判断、分类统计这些操作尽量下沉到 SQL 层带来的性能收益和代码简洁度是非常可观的。再分享一个我踩过多次坑之后总结出来的小习惯每次写完包含 CASE 的查询先在 LIMIT 10 的小数据集上跑一遍肉眼检查每个分支的结果是否符合预期然后再去掉 LIMIT对比一下 CASE 前后的数据分布是否合理特别要注意新增的 CASE 字段有没有意外出现的 NULL以及分组统计的数字是否和业务预期吻合。这个验证流程只需要多花一分钟但能避免大量返工。CASE 本身不难难的是在合适的位置、用合适的写法、避开那些不报错但结果错误的坑。希望这篇文章能把你在 CASE 上踩过的坑、还没踩的坑都提前摆到台面上。下次遇到状态翻译、行转列、自定义排序、条件批量更新这些需求你可以放心大胆地写 CASE——它就是你 SQL 工具箱里那个最靠谱的瑞士军刀。
阅读完成 · 觉得有帮助？
