写SQL的时候最绕不开的一件事就是同一列在不同条件下要给出不同结果。比如状态码1要翻成“待付款”2要翻成“已付款”成绩要按分数判断“及格/不及格”报表要按日期统计不同状态的订单金额……这种需求几乎每个MySQL使用者都会碰到。处理这类需求最常用的就是if和case但很多同学一开始总是分不清为什么if后面要带括号case最后要写end为什么在select里写的if到了存储过程里又变成了“if...then...else...end if”这篇文章我把MySQL条件判断的if与case一次讲透包含IF()函数、CASE表达式以及存储过程中的IF语句和CASE语句配合具体例子和踩坑记录适合正在学MySQL、或者工作中经常写条件判断SQL的同学收藏照着用。1. 看起来很像其实是三种不同的东西1.1 IF()函数、CASE表达式和IF/CASE语句的区别很多人学MySQL学了半天搞不清if和case其实不是因为复杂是因为这几个名字长得太像了IF()函数、CASE表达式、IF语句、CASE语句。它们都能实现“按条件做不同的事”但出现的位置、语法和结束方式完全不一样。可以这么理解IF()函数像一个计算器你丢给它一个条件和两个结果它吐出其中一个。它写在SELECT、WHERE、UPDATE这些普通的SQL语句里面。CASE表达式像一个公式写在SELECT列表、ORDER BY、GROUP BY里根据条件算出一个值然后继续参与外层SQL的计算。IF语句和CASE语句像路口指挥员它们不返回某个值给SELECT而是决定程序接下来执行哪一段SQL分支。它们只能出现在存储过程、存储函数、触发器、事件这些“过程化”代码里。我把它们的核心差异整理成一张表你一眼就能看懂形态出现位置作用本质结尾写法IF()函数SELECT、WHERE、ORDER BY等SQL语句中返回一个值右括号)CASE表达式SELECT、WHERE、ORDER BY等SQL语句中返回一个值ENDIF语句存储过程/函数/触发器/事件中控制分支执行SQLEND IFCASE语句存储过程/函数/触发器/事件中控制分支执行SQLEND CASE最容易踩的坑就是把它们的语法混着用。比如有人在SELECT里面写-- 这样写会报错 SELECT IF score 60 THEN 及格 ELSE 不及格 END FROM student;因为SELECT里那个是IF()函数必须用括号包起来写成IF(score 60, 及格, 不及格)。而IF score 60 THEN这种写法是语句语法只能出现在存储过程里。同样的有人把CASE表达式写成CASE ... END CASE这也是错的表达式用END收尾语句才用END CASE。1.2 什么场景只能选谁把身份搞清楚之后选型就没那么纠结了想在SELECT列表里根据每一行的值生成一个新列比如翻译状态码、判断及格与否用IF()函数或CASE表达式都行。想在聚合函数里做条件统计比如“统计状态为已付款的金额”用IF()函数或CASE表达式都行常见写法是SUM(IF(...))或COUNT(CASE WHEN ... THEN 1 END)。想按业务自定义顺序排序比如“待付款排最前已付款其次”在ORDER BY里用CASE表达式最舒服。想在存储过程里根据不同条件执行不同的SQL比如“状态是0就更新为1状态是1就更新为3”请用IF语句或CASE语句。一句话总结要“值”就用函数/表达式要“执行分支”就用语句。2. IF()函数的写法、嵌套与应用“配方”2.1 基础语法和最简单的使用场景IF()函数的完整语法是IF(expr1, expr2, expr3)。MySQL会先判断expr1如果它是真值非0且非NULL就返回expr2否则返回expr3。第二个参数和第三个参数可以是常数、字段、表达式甚至可以是子查询。最简单的用法SELECT IF(10 1, 大, 小); -- 结果大 SELECT name, IF(score 60, 及格, 不及格) AS result FROM student;第二个例子很典型查询学生成绩时不需要在Java或Python里做二次判断SQL直接就把“及格/不及格”算出来了。这也说明IF()函数最常见的定位就是在查询结果集中生成计算列。写IF()函数有几个容易忽略的点第一个条件参数如果是字符串MySQL会尝试把它转成数字再判断比如IF(abc, 1, 2)这里abc会被转成0所以返回2。这种写法本身就不推荐条件位置尽量写布尔表达式、数值比较或字段判断。第二个和第三个结果参数类型最好保持一致。如果一个是字符串一个是数字MySQL在返回时会发生隐式类型转换可能得到和你预期完全不一样的结果。后面我会专门讲这个坑。2.2 配合聚合函数的经典套路由IF()函数在单独使用的时候功能其实很有限真正发光发热的场景是和聚合函数配合做条件统计。举个工作中最常见的例子有一张订单表里面有个status字段0代表待付款1代表已付款2代表已取消需要按日期统计“已付款金额”和“待付款金额”。SELECT DATE(create_time) AS d, SUM(IF(status 1, amount, 0)) AS paid_amount, SUM(IF(status 0, amount, 0)) AS pending_amount FROM orders GROUP BY DATE(create_time);这段SQL的逻辑很简单IF(status 1, amount, 0)意思是如果这行订单状态是1就在金额求和里加上amount否则加0。SUM函数把所有行的返回值累加就得到了当天已付款订单的总金额。用同样的思路也可以统计数量SELECT COUNT(IF(status 1, 1, NULL)) AS paid_cnt, COUNT(IF(status 0, 1, NULL)) AS pending_cnt FROM orders;这里有个非常容易踩的坑COUNT(IF(status 1, 1, NULL))的第二个参数必须写NULL不能写0。因为COUNT函数只统计“非NULL”的值条件不满足时返回NULL那一行就会被COUNT忽略从而只统计出状态为1的行数。如果你写成COUNT(IF(status 1, 1, 0))条件不满足时返回0而0是非NULL会被COUNT算进去结果就变成统计全表行数了。我第一次用这个写法的时候就在这坑里蹲了半小时。2.3 嵌套写法的可读性风险IF()函数可以多层嵌套比如要根据分数打等级SELECT name, IF(score 90, A, IF(score 80, B, IF(score 70, C, D))) AS grade FROM student;嵌套两三层还能忍再往上就不推荐了。括号一层套一层稍不留神就少写一个右括号而且别人接手你的SQL时得数半天括号才知道逻辑。遇到分支超过三个的等级判断我强烈建议改用CASE表达式可读性会好很多。另外提醒一句IF()函数的短路问题。很多同学会想当然地认为只要第一个条件满足了第二个参数就不会被计算。实际上MySQL官方并没有对IF()函数的分支求值顺序做严格保证如果某个分支里放着很耗时的子查询最好不要依赖这种“隐含短路”改用CASE表达式更安全。3. CASE表达式的两种写法与分支顺序3.1 简单CASE和搜索CASE到底怎么选CASE表达式在SQL里有两种写法它们的适用场景不一样。第一种叫做“简单CASE”专门用来做等值匹配SELECT id, CASE status WHEN 0 THEN 待付款 WHEN 1 THEN 已付款 WHEN 2 THEN 已取消 ELSE 未知 END AS status_name FROM orders;简单CASE后面的status是待比较的字段WHEN 0其实会被MySQL翻译成status 0所以它只能做等值判断。你要判断某个字段等于几个值分别返回什么用这种写法最简洁。第二种叫做“搜索CASE”可以写任意复杂条件SELECT name, CASE WHEN score 90 THEN 优秀 WHEN score 80 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS grade FROM student;搜索CASE的WHEN后面跟的是完整的布尔表达式所以你可以做范围比较、模糊匹配、多条件组合甚至子查询。简单CASE能做的事搜索CASE都能做只是当判断条件就是一个字段等于多个固定值时简单CASE写起来更节省字符。两种写法的对比对比点简单CASE搜索CASE语法结构CASE 字段 WHEN 值 THEN ...CASE WHEN 条件 THEN ...支持的判断等值比较范围、模糊、多条件组合适用场景字段值到显示值的映射复杂业务条件判断类似编程语言switch/caseif/else if3.2 WHEN的顺序和ELSE缺省的坑CASE表达式是从上往下依次判断的一旦某个WHEN条件命中就立刻返回对应的结果后面的分支不再执行。这个特性在用搜索CASE的时候必须刻在脑子里否则就会写出逻辑完全错误的SQL。最常见的错误是把从小到大排序写反了-- 错误示范90分以上的也会命中第一个分支永远到不了第二个 CASE WHEN score 80 THEN 良好 WHEN score 90 THEN 优秀 ELSE 不及格 END80分以上的同学全部被划成“良好”90分以上根本走不到score 90那条分支。解决办法很简单要么从高到低排要么从低到高排总之必须按范围严格排列不能随意写。关于ELSE它本身是可选的。如果所有WHEN分支都没命中同时你也没写ELSE那么整个CASE表达式不会返回空字符串也不会返回0而是直接返回NULL。在SELECT里显示NULL可能只是不好看但在UPDATE语句里漏写ELSE会造成大面积数据被置成NULL这个我在后面实战场景里会重点演示。4. 实战场景演练翻译、统计、更新、排序一次说全4.1 场景一状态字段翻译业务系统里最普遍的需求就是把数字状态翻译成可读文字。比如订单系统里status字段存的是0、1、2、3页面要显示“待付款”“已付款”“已取消”“退款中”。SELECT id, order_no, CASE status WHEN 0 THEN 待付款 WHEN 1 THEN 已付款 WHEN 2 THEN 已取消 WHEN 3 THEN 退款中 ELSE 未知 END AS status_name FROM orders;这种就是简单CASE的典型用法。你可能会想直接用IF嵌套不也能实现吗IF(status 0, 待付款, IF(status 1, 已付款, IF(status 2, 已取消, 退款中)))能实现但四个分支就已经很费眼了将来要加第五个状态改起来也痛苦。所以状态码越多越应该用CASE。我个人的习惯是两个分支用IF三个以上用CASE。4.2 场景二分组统计报表报表需求经常要求“同一组数据按不同条件统计多个指标”。比如按日期统计订单总数、已付款数量、已付款金额SELECT DATE(create_time) AS d, COUNT(*) AS total_cnt, COUNT(IF(status 1, 1, NULL)) AS paid_cnt, SUM(IF(status 1, amount, 0)) AS paid_amount FROM orders GROUP BY DATE(create_time);这里要特别说明一下COUNT(IF(status 1, 1, NULL))里IF的第二个参数必须是NULL。因为COUNT不会统计NULL值所以只有状态为1的行才会被计数如果第二个参数写0那所有行都会返回非NULL统计结果就变成全表数量报表数据就会莫名翻倍。我用COUNT(IF(...))做统计时报过一次“数据对不上”的故障最后排查下来就是这个细节。CASE在聚合里同样能用等价写法是COUNT(CASE WHEN status 1 THEN 1 END)这里THEN后面的1不能省略而且没写ELSE也不影响因为不满足条件时自动返回NULL。相对而言我更喜欢用CASE因为多条件判断时更自然不用套一堆IF。4.3 场景三用CASE做批量条件更新CASE表达式最容易被忽视的战场是UPDATE语句。举个例子我需要把所有“待付款”状态0改为“已付款”1同时把所有“已付款”1改为“退款中”3。如果一条条UPDATE要写两条SQL中间还可能遇到业务并发问题。用CASE可以一条搞定UPDATE orders SET status CASE status WHEN 0 THEN 1 WHEN 1 THEN 3 ELSE status END WHERE status IN (0, 1);这里最关键的是ELSE status这一句。它的作用是如果当前行的状态不是0也不是1就保持原值不变。一旦漏掉这个ELSE所有WHERE条件覆盖之外的行status都会变成NULL数据直接损毁。这种SQL影响的行数非常大执行前一定要先SELECT查一遍看看CASE表达式到底会算出来什么值。另外再提醒一点上面这条UPDATE执行完之后如果看影响行数你可能会发现它把status IN (0, 1)的所有行都算进去了即使WHEN 0 THEN 1把0变成1这也算一次变更。如果你只想统计“真正发生了值变化”的行数需要靠客户端返回结果结合业务判断不能只看MySQL的“Rows matched”。4.4 场景四按业务规则自定义排序常见的排序是按时间倒序、按金额正序。但业务上经常有一种自定义顺序比如“待付款的订单要在列表最前面已付款其次已取消排最后”。直接用ORDER BY status ASC按0、1、2排序出来的顺序未必符合业务预期。CASE表达式可以把业务顺序映射成排序权重SELECT id, order_no, status FROM orders ORDER BY CASE status WHEN 1 THEN 0 WHEN 0 THEN 1 WHEN 3 THEN 2 ELSE 3 END, create_time DESC;这里的意思是状态为1的排最前权重0状态为0的排第二权重1状态为3的排第三权重2其他状态最后。后面再用create_time DESC做次级排序这样同一状态内部依然按时间倒序排。需要注意这种写法一般会导致Using filesort因为排序依据不是字段本身而是一个计算出来的临时值。数据量小无所谓数据量很大时可以考虑在业务表里加一个sort_weight字段提前算好并加上索引查询时直接ORDER BY sort_weight。5. 存储过程里的IF语句与CASE语句5.1 IF语句的基本写法和语法要点前面讲的IF()函数和CASE表达式都是“返回一个值”。如果你需要在存储过程里根据某个条件执行不同的SQL语句那就轮到IF语句出场了。基础语法是这样的IF 条件 THEN 语句; ELSEIF 另一个条件 THEN 语句; ELSE 语句; END IF;举个实际的例子写一个存储过程根据传入的订单编号和状态码更新订单状态CREATE PROCEDURE update_order_status( IN p_order_id INT, IN p_status TINYINT ) BEGIN DECLARE current_status TINYINT; SELECT status INTO current_status FROM orders WHERE id p_order_id; IF current_status 0 THEN UPDATE orders SET status p_status WHERE id p_order_id; ELSEIF current_status 1 THEN UPDATE orders SET status p_status 1 WHERE id p_order_id; ELSE SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT status not supported; END IF; END;写IF语句有几个语法细节特别容易错ELSEIF是一个词中间没有空格不是ELSE IF。写成ELSE IF会直接报语法错误。结尾是END IF中间有空格不是ENDIF。每个分支里面的SQL语句要以分号结尾这和普通SQL一致。IF语句没有“返回值”的概念它只负责选择执行哪一段代码。5.2 CASE语句的两种格式以及和CASE表达式的本质差异存储过程里也有CASE语句但它的语法规则跟SELECT里的CASE表达式不太一样很容易搞混。先看简单格式CASE p_status WHEN 0 THEN UPDATE orders SET status 1 WHERE id p_order_id; WHEN 1 THEN UPDATE orders SET status 3 WHERE id p_order_id; ELSE SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT invalid status; END CASE;再看搜索格式CASE WHEN p_status 0 THEN UPDATE orders SET status 1 WHERE id p_order_id; WHEN p_status 1 THEN UPDATE orders SET status 3 WHERE id p_order_id; ELSE SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT invalid status; END CASE;这两种格式和查询里的CASE表达式最大的区别有三个结尾不同。语句版本用END CASE表达式版本用END。用处不同。语句版本THEN后面跟的是SQL语句可以是一个UPDATE、一个DELETE或者一个SET表达式版本THEN后面跟的是值。无匹配时的行为不同。表达式版本在没有任何分支匹配且没有ELSE时返回NULL语句版本在没有任何分支匹配且没有ELSE时直接抛出错误错误代码是1339报错信息类似Case not found for CASE statement。这一点非常关键。如果你在存储过程里写了一个CASE语句心想“没匹配就算了”结果它会直接报错中断执行。有没有ELSE行为天差地别。所以我在存储过程里写CASE语句时几乎总是显式带一个ELSE至少也要SIGNAL一个明确的错误信息避免数据库误吞数据。6. 选型参考与踩坑记录6.1 IF还是CASE我的选择标准很多初学者会纠结“到底哪个快”。先说结论在普通查询场景下IF()函数和CASE表达式的性能差异完全可以忽略数据库优化器都会把它们处理成类似的内部表达式你真正的瓶颈基本都在表设计、索引和连接方式上。我自己的选型标准很简单只有两个分支而且条件很直观用IF()函数因为一行写得完。三个或以上分支一律用CASE表达式因为分支再多也能从上往下读得清清楚楚。在存储过程里要做流程控制优先IF语句如果条件是某个字段等值分派用CASE语句写起来更紧凑。在聚合函数里做条件统计两者都行但涉及多个条件时CASE更合适。6.2 必须刻进脑子里的三个坑第一个坑NULL判断。IF(NULL, 1, 0)返回0CASE WHEN NULL THEN 1 ELSE 0 END返回0CASE NULL WHEN NULL THEN 1 ELSE 0 END也返回0因为NULL NULL这个比较本身结果是NULL既不是真也不是假。判断某个字段是不是NULL必须用IS NULL不能用 NULL。这个坑在IF和CASE里都一样。第二个坑分支类型不一致导致隐式转换。比如IF(1, 100, abc)一个分支是数字一个分支是字符串MySQL在返回结果时可能会把字符串转成数字返回结果会变得很奇怪。这不是MySQL故意刁难你而是类型不同时它必须选一个类型来输出。解决方法是让所有分支返回同一类型。CASE表达式也有同样问题CASE WHEN 1 THEN 100 ELSE abc END的上限也会出现意料外的转换。第三个坑把函数包在索引列上导致索引失效。比如你要查“类型为1且名字等于a或者类型不为1但金额大于10”的数据有人可能会写WHERE CASE WHEN type 1 THEN name a ELSE amount 10 END能跑但大概率无法用索引全表扫描。这种复杂条件我建议改写成逻辑等价的普通条件组合WHERE (type 1 AND name a) OR (type 1 AND amount 10)改写之后优化器才有机会走索引。写完SQL用EXPLAIN看一眼如果看到Using where和Using filesort同时出现就要停下来想想有没有更好的写法。6.3 结果不对时的排查顺序最后分享一个排查套路。遇到IF/CASE算出来的结果跟预期不一致我的排查顺序是固定的先查NULL。条件里是不是有字段本身就是NULL导致判断结果既不是真也不是假。再查分支顺序。搜索CASE是不是因为范围顺序不对被前面的分支抢先命中了。然后查类型。比较的字段是不是字符串和数字在隐式转换结果分支类型是否一致。最后看执行计划。确认有没有因为函数包裹索引列导致全表扫描或文件排序。这四步走完绝大多数if/case相关的问题都能定位。我自己处理过的所谓“SQL结果诡异”案例十有八九都出在前两步上NULL和顺序就是隐藏最深的两只黑手。写SQL这些年我对if和case的最大体会是它们不是能背诵的语法点而是解决问题的工具。遇到需求先想清楚要“值”还是要“执行分支”再决定用函数、表达式还是语句。把这篇里的例子练一遍再结合自己的业务场景改一改很快你就能形成自己的判断标准。最后再叮嘱一句凡是在UPDATE里用CASEELSE一定不能省这是很多生产事故的源头。