大家天天写 SELECT天天跟 function 打交道但说实话我见过不少同事对 MySQL 函数的态度就两种一种是「不知道还有这函数每次都是临时查」另一种是「这个函数看着挺好用拿来就用结果上线就出事故」。这篇文章就不做那种大而全的函数手册了那种东西官方文档比我写得好。我想用实际做项目时踩过的坑和常用的套路聊聊 MySQL 函数到底应该怎么用、怎么组合、怎么避坑。尤其是那些在 WHERE 条件里用函数导致索引失效、隐式转换搞出脏数据、窗口函数 MySQL 8.0 才支持这类问题新手老手都值得再过一遍。如果你正在处理报表统计、数据清洗、或者优化一条慢 SQL这篇应该能给你省不少时间。1. 先搞清楚函数在 MySQL 里的准确定位1.1 函数本质对每一行数据的「加工厂」还是「过滤器」很多开发同学对函数的理解停留在「用来算点东西」这个没错但太模糊了。我更喜欢把 MySQL 函数分成两个完全不同的使用场景来理解一个是输出阶段的加工一个是过滤阶段的判断。输出阶段的加工典型像是CONCAT拼接字段、DATE_FORMAT格式化时间、ROUND保留两位小数它们作用在查询结果列上把原始数据变成我们想要的展示格式。这个场景用函数基本没有太多性能坑只是让数据库多做一点计算而已。过滤阶段的判断就是在WHERE条件里用函数比如WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-01、WHERE YEAR(create_time) 2024。这是我最想让大家警惕的场景。因为一旦你在索引列上套了函数MySQL 的优化器在绝大多数情况下就没办法走索引了只能老老实实做全表扫描。这句话我说过很多遍WHERE 里用函数本质上就是告诉优化器「这个列我已经加了工了你原来建的索引我看不上」。这不是函数的问题是使用姿势的问题。1.2 函数体系的框架别背文档先建脑图MySQL 函数那么多字符串函数、数值函数、日期时间函数、聚合函数、流程控制函数、加密函数、JSON 函数、窗口函数背是背不完的。我建议脑子里只放一个框架用到哪个去哪里找。日常业务里面最核心的永远是三类字符串处理、日期处理、聚合统计。这三类基本覆盖了 80% 以上的报表需求。其次是流程控制因为CASE WHEN在数据清洗和指标口径映射里简直无处不在。再往后是窗口函数它在排行榜、同环比、分组 TopN 场景里比传统写法好用十倍。剩下那些加密函数、JSON 函数属于「用到再查」的范畴不用花太多时间。我见过很多同学把大量时间花在背各种函数名上实际上 MySQL 的函数语法迭代得很快今天背了明天可能就忘了更重要的是理解每个函数族之间的关联比如DATE_SUB和INTERVAL的组合、CASE WHEN与聚合函数的嵌套、GROUP_CONCAT和GROUP BY的配合这些组合拳才是真正能提升效率的地方。2. 从实际场景拆解字符串、日期、流程控制怎么组合2.1 字符串处理不只是拼接关键是清洗和抽取先说几个我在实际项目里用得非常多的字符串函数。我做过一个用户信息清洗的需求源数据是从别的系统导出来的手机号有的带空格、有的带横线、有的带区号简直是灾难现场。这时候就需要组合使用TRIM、REPLACE、SUBSTRING_INDEX这些函数做标准化。SELECT TRIM(BOTH FROM phone) AS trimmed_phone, REPLACE(REPLACE(phone, , ), -, ) AS cleaned_phone, SUBSTRING_INDEX(email, , 1) AS email_prefix FROM user_import_temp;这里的核心思路是先观察脏数据长什么样再设计清洗链路。比如手机号有横线REPLACE两次嵌套能把空格和横线都干掉如果只需要邮箱前缀SUBSTRING_INDEX比LOCATE、SUBSTRING的组合要简洁得多。还有一个高频需求是CONCAT_WS它跟CONCAT的区别在于可以指定分隔符并且自动跳过 NULL。比如导出客户地址时省、市、区、详细地址字段可能有空值用CONCAT_WS就不会出现广东省广州市天河区这种因为某个字段为空导致分隔符错乱的情况。再就是LEFT、RIGHT、SUBSTRING这类截取函数在脱敏场景里也很有用。比如只保留身份证号的前三位和后四位SELECT CONCAT(LEFT(id_card, 3), ****, RIGHT(id_card, 4)) AS masked_id_card FROM user_info;注意脱敏逻辑要考虑到不同字段长度的差异如果直接用固定的SUBSTRING位置遇到长度不同的数据可能截取错位保险做法是先LENGTH判断长度再加一层CASE WHEN。2.2 日期处理报表按天、按周、按月统计的万能套路日期函数是报表开发的重头戏。我最常用的几个是DATE_FORMAT、DATE_SUB、DATEDIFF、TIMESTAMPDIFF。统计每天订单量的时候大家最习惯的写法是SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS order_cnt FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);这个写法没问题但如果你要统计的是最近 7 天、并且每天的数据不想漏掉没有订单的日期那GROUP BY天生就会丢掉空数据的日期。这个场景我后面会结合案例详细讲这里想先强调一个核心概念日期函数的核心是先裁剪到「粒度」再去做聚合。什么叫裁剪粒度DATE_FORMAT(create_time, %Y-%m-%d)就是把时间裁剪到天DATE_FORMAT(create_time, %Y-%m)就是裁剪到月。整个思路很自然先想清楚报表的粒度是小时、天、周、月然后用DATE_FORMAT去裁剪再用GROUP BY聚合。对于日期加减DATE_ADD和DATE_SUB是最常用的。比如计算上个月的同期就是先把当前日期裁剪到第一天再减一个月SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) AS first_day_last_month;这里CURDATE()是当前日期DATE_SUB减一个月再用DATE_FORMAT把结果归一化成月初。这种写法在月度对比报表里非常常用。2.3 流程控制CASE WHEN 才是数据口径的最终翻译官CASE WHEN说它是流程控制函数其实更多时候承担的是「业务口径映射」的工作。比如订单表里有个status字段业务方的需求是1代表待支付、2代表已支付、3代表已取消但报表里不能显示数字要显示中文。这种映射用CASE WHEN很快SELECT order_id, CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已取消 ELSE 未知状态 END AS status_text FROM orders;但我想强调的是CASE WHEN真正的高阶用法是跟聚合函数配合实现「条件聚合」。比如统计订单表里支付成功和取消的订单数不用写多个 SQL一条就能搞定SELECT SUM(CASE WHEN status 2 THEN 1 ELSE 0 END) AS paid_cnt, SUM(CASE WHEN status 3 THEN 1 ELSE 0 END) AS canceled_cnt FROM orders;注意这里的技巧是SUM里面套CASE WHEN满足条件记 1不满足记 0然后累加。这比COUNT(CASE WHEN ... THEN 1 END)的写法更直观也不容易踩到COUNT不统计 NULL 的坑。IF函数是CASE WHEN的简化版适合只有两种分支的场景比如IF(gender 1, 男, 女)。但它有个缺点就是嵌套多个IF时可读性特别差我见过有人写出五层IF嵌套的 SQL那简直就是在写压缩包。我的建议是超过两个分支老老实实用CASE WHEN。3. 实操案例订单统计报表中函数的组合应用3.1 需求场景某电商后台的销售日报前阵子有个朋友的公司要做一套销售日报需求是这样的运营后台每天要看到最近 7 天每天的订单量、支付金额、客单价同时还要看到这几个指标跟上个周期前 7 天的环比变化。订单表orders结构大概是order_id、create_time、amount、status其中status 2表示已支付。这个需求看起来简单但真正写起来要考虑的点非常多。先看最基本的按天统计SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS order_cnt, SUM(CASE WHEN status 2 THEN amount ELSE 0 END) AS paid_amount, ROUND(SUM(CASE WHEN status 2 THEN amount ELSE 0 END) / NULLIF(COUNT(CASE WHEN status 2 THEN 1 END), 0), 2) AS avg_price FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ORDER BY day;这里藏着好几个容易踩坑的知识点。COUNT(CASE WHEN status 2 THEN 1 END)这个写法COUNT只统计非 NULL 值CASE WHEN条件不满足时返回 NULL所以这行统计的是支付订单数。如果把它写成COUNT(CASE WHEN status 2 THEN 1 ELSE 0 END)因为0也不是 NULLCOUNT会把未支付的也数进去结果就错了。NULLIF在这里是为了防止除零错误如果支付订单数为 0这里返回 NULL结果也是 NULL而不是程序报错或出现 Inf 这种脏值。3.2 补全空日期连续日期的生成技巧上面的 SQL 有个明显问题如果某一天没有订单这天的数据在结果里根本不会出现。运营看报表的时候看到的七天只有六天第六天还是断的体验很差。解决这个问题的思路分两步先生成连续的日期序列再左关联聚合结果。连续日期序列怎么生成MySQL 8.0 可以用递归 CTEMySQL 5.7 及以下版本没有递归需要用别的手段。5.7 场景下我也做过用一个足够大的数字辅助表或者直接UNION ALL拼 7 个数然后再DATE_ADD到基准日期上SELECT DATE_ADD(CURDATE(), INTERVAL -6 n.id DAY) AS day FROM ( SELECT 0 AS id UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 ) n;这样不管有没有订单日期都会补齐。左关联聚合结果后没有订单的日期就显示 0。注意我这里说的是「数位辅助表」的思路实际项目里如果报表周期经常变建议直接维护一张日期维度表从2000-01-01到2100-12-31全铺好后面要做任何连续日期报表都不用再拼 SQL 了。3.3 同环比窗口函数 LAG 的正确姿势MySQL 8.0 引入窗口函数之后同环比写法优雅了很多。上面的需求要对比上一个周期的数据其实可以用LAG把前 7 天的对应值拿过来WITH daily AS ( SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 14 DAY) GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ) SELECT day, order_cnt, LAG(order_cnt, 7) OVER (ORDER BY day) AS prev_cnt, ROUND((order_cnt - LAG(order_cnt, 7) OVER (ORDER BY day)) / NULLIF(LAG(order_cnt, 7) OVER (ORDER BY day), 0) * 100, 2) AS mom_percent FROM daily;这里的核心是LAG(order_cnt, 7)表示取当前行往前第 7 行的值也就是前一周同一天的数据。窗口函数让同环比从「自关联聚合」的苦海里解脱出来了。但要注意窗口函数有一个性能陷阱OVER (ORDER BY day)会引入排序如果结果集很大排序成本会很高。建议在子查询里先按day做GROUP BY缩小数据量再在外面套窗口函数。3.4 GROUP_CONCAT把分组内的多行压成一行报表里还有一个高频需求比如要查某个用户最近买了哪些商品希望结果是一行里用逗号拼接商品名。GROUP_CONCAT就是干这个的SELECT user_id, GROUP_CONCAT(product_name ORDER BY buy_time DESC SEPARATOR 、) AS recent_buy_products FROM orders WHERE status 2 GROUP BY user_id;这里有两个细节值得注意。一个是ORDER BY写在GROUP_CONCAT内部控制的是拼接顺序不是最终结果的排序。另一个是GROUP_CONCAT默认最大长度只有 1024 字节如果拼接内容很长会被静默截断。这时候可以通过SET SESSION group_concat_max_len 1048576;调大。这个问题很容易被忽略我见过生产环境里导出的报表内容不完整查了半天最后发现是这里截断了。3.5 汇总行WITH ROLLUP 自动补总计最后再补一个实用技巧。如果报表既要明细又要合计可以用WITH ROLLUP自动在分组结果后面追加一行总计SELECT IFNULL(DATE_FORMAT(create_time, %Y-%m-%d), 总计) AS day, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) WITH ROLLUP;这里IFNULL是为了把 NULL 变成「总计」因为ROLLUP生成的汇总行分组字段的值是 NULL。注意WITH ROLLUP在结果里增加的行GROUP BY的字段会被置 NULL这个坑也很经典。4. 函数用的不对性能就是灾难现场4.1 WHERE 条件里套函数索引失效的经典教训前面反复提到这个现在用一个具体例子说明。很多同学习惯写这种查询SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-06-01;这条 SQL 的create_time字段如果建了索引是完全用不上的。因为DATE_FORMAT(create_time, %Y-%m-%d)把create_time先加工成了字符串索引里存的原始时间值没法参与匹配。正确写法应该用范围查询SELECT * FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;这个技巧我记得特别清楚有一次线上一个报表接口从 200ms 变成 3 秒多排查后发现就是同事为了图方便在WHERE里套了DATE_FORMAT。改成范围查询后性能瞬间恢复。核心逻辑是能用范围比较就不要在索引列上套函数。4.2 隐式转换字符串和数字混用的脏数据问题MySQL 里一个非常隐蔽的坑是隐式类型转换。比如某个表里phone字段是 VARCHAR 类型里面存的是手机号。如果查询写成SELECT * FROM users WHERE phone 13800138000;MySQL 会把phone字段转化成数字再跟13800138000比较结果就是索引失效更严重的是可能匹配到错误数据。比如一个手机号是13800138000另一个是13800138000xx或者138001380001因为隐式转换的关系可能就匹配到了不该匹配的行。解决办法很简单写 SQL 的时候保证两边类型一致字符串就加引号数字就不加SELECT * FROM users WHERE phone 13800138000;这个坑平时不太会踩到但一旦踩到就是审核不出来的逻辑错误因为它不影响语法只影响结果。4.3 DISTINCT 函数 / GROUP BY 函数的排序开销还有一个隐藏性能问题出现在GROUP BY和DISTINCT配合函数时。比如SELECT DISTINCT DATE_FORMAT(create_time, %Y-%m-%d) FROM orders;这个 SQL 要先对全表做函数计算再对结果去重排序成本很高。更高效的做法是直接GROUP BY create_time然后在外面套一层DATE_FORMAT因为GROUP BY可以走索引归并而DISTINCT加函数相当于先全表计算再内存去重。类似的还有ORDER BY里套函数的情况比如ORDER BY MONTH(create_time)索引完全失效。能预处理成字段就预处理不能的话至少要意识到排序开销会变大。4.4 聚合函数对 NULL 的处理SUM 与 COUNT 的差别聚合函数有几个和 NULL 相关的坑值得单独说说。COUNT(*)统计的是行数COUNT(字段)统计的是该字段非 NULL 的数量SUM(字段)对 NULL 直接忽略。所以如果字段里有 NULLAVG(字段)的分子分母都可能和你想的不一样。典型的例子是算客单价SUM(amount) / COUNT(*)和AVG(amount)的结果不同。前者分母是所有订单数后者分母是 amount 非空的订单数。如果业务上已经把未支付订单的 amount 置为 0那AVG没问题如果置为 NULLAVG会自动忽略这笔订单逻辑可能就变了。我的建议是涉及金额的指标用SUM/COUNT显式控制分母不要直接依赖 AVG 对 NULL 的默认行为这样才能保证口径清晰。5. 排查函数问题的方法和工具5.1 EXPLAIN 看执行计划索引有没有失效一目了然排查函数导致的索引失效问题最直接的办法就是EXPLAINEXPLAIN SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-06-01;看结果里的type和key字段。如果type是ALL说明是全表扫描基本上就是没走索引如果key是 NULL说明可能用了索引但没法有效利用大概率是函数改造了列。正常走索引的type应该是range或refkey显示实际用的索引名。这个习惯应该刻进骨子里执行任何 SQL 之前先跑一下EXPLAIN看执行计划再决定要不要优化。不要等线上慢查询报警了才想起来看。5.2 SHOW WARNINGS 查看隐式转换和类型警告SHOW WARNINGS可能很多新手没听过它主要用来查看 SQL 执行过程中的警告信息。比如隐式类型转换、数据截断这类问题MySQL 会在警告里告诉你。我写存储过程或者复杂 SQL 的时候习惯执行完后立刻执行SHOW WARNINGS;看有没有提示类型不一致或数据截断。举个例子把123abc跟数字比较时MySQL 可能会报警告说转换出错。这类问题在正常的返回结果里看不到但会导致数据不准确所以这个习惯能帮你提前发现问题。5.3 函数结果缓存与确定性函数的取舍MySQL 内置函数大多数是确定性的也就是说同样的输入永远得到同样的输出。但UUID()、NOW()、RAND()这类非确定性函数在需要函数索引或者复制场景下要特别注意。MySQL 8.0 支持函数索引但只支持确定性的函数而且NOW()这类函数因为每次计算都不同无法用于最优化。如果确实需要更高的查询性能我建议把函数计算的结果落成字段比如新增一个day字段在写入时就算好查询直接用字段过滤。5.4 自定义函数能用内置就别自己造轮子MySQL 支持创建自定义函数CREATE FUNCTION但我的立场一直很明确能用内置函数解决的就别自己造。原因很简单自定义函数默认不会缓存执行计划每次调用都要重新解析和计算性能差异明显而且调试和维护成本高团队成员之间的交接也麻烦。如果确实有必要写自定义函数比如复杂的业务校验逻辑在多个地方复用那至少要注意函数体尽量精简、避免在函数内部查询大表、给函数命名加上前缀区分自定义和内置、在测试环境充分验证后再上生产。6. 关于函数使用的一些个人体会说几个我在实际项目中总结出来的习惯。第一个是写 SQL 之前先问自己三个问题这个函数是作用在 SELECT 后面还是 WHERE 里面它会不会让索引失效如果用在聚合里对 NULL 的处理是否符合业务口径这三个问题过一遍能挡住大部分低级错误。第二个习惯是对于常用报表尽量把函数计算的结果固化成字段。比如每天跑的任务写入时就把day、week、month这些粒度字段算好存下来查询时就完全不用再套DATE_FORMAT了性能和使用体验都是最大化的。第三个习惯是遇到复杂 SQL先拆解再组合。很多朋友喜欢在一个 SELECT 里堆十几个函数看起来很高端但一报错就不知道错在哪。我的做法是先用子查询把每层逻辑拆开每一层只做一件事最后再合并。这样既方便调优也方便别人看代码。第四也是最重要的一点MySQL 8.0 之后函数能力已经有了质的飞跃窗口函数、公共表表达式CTE、函数索引都在生产环境稳定应用。如果你还在用 5.7 甚至 5.6我建议尽早规划升级这不仅能让你用上LAG、LEAD、ROW_NUMBER这些窗口函数还意味着你可以彻底告别那些为了补日期、做同环比而写的自虐式 SQL。升级有成本但从开发效率和运维稳定性来看这笔投资是值得的。函数说到底是 SQL 的一种表达工具工具本身没有好坏关键是使用场景和姿势。把函数的边界搞清楚、把性能影响记在脑子里、把常用的组合方式练熟了写出来的 SQL 自然会又准又快。希望这篇分享对你有用。