写SQL这事儿入门靠增删改查进阶拼的就是函数用得熟不熟。拿我自己的经历说前几年给某业务部门做日报统计数据要按天汇总订单、还得分渠道、算同比环比当时我对窗口函数和各种日期函数也不熟愣是写了一堆子查询去套结果跑得慢不说改起来还特别费劲。后来系统把MySQL内置函数过了一遍同样的需求几条SQL就完事性能还快了一个量级。从那之后我就一个感受函数不是会不会用的问题而是想没想到的问题。这篇文章我就把自己在项目里沉淀下来的MySQL函数实用经验整理一遍不讲那种每个函数抄一遍文档的事而是讲怎么用、什么时候用、坑在哪。分几个层面字符串和日期这类日常高频的聚合和条件控制这类做报表离不开的窗口函数和JSON函数这类能明显提升效率的进阶功能最后把函数用不对导致索引失效、类型转换出错的典型坑一并梳理出来。适合谁看刚入门的开发或者数据分析同学可以把这当一份照着就能写的参考干了几年但对函数总是记一个查一个的朋友我建议重点关注第五节那些性能和排查经验这玩意儿踩坑一次印象才深刻。1. 先说清楚MySQL函数到底解决什么问题1.1 函数的本质与分类MySQL函数本质上就是内置的一组黑盒逻辑你给它若干输入参数它返回一个结果。比如DATE_FORMAT(create_time, %Y-%m-%d)把一个日期时间类型变成字符串SUM(amount)把某一组数据里的金额全部加起来。这些能力SQL标准本身没有完整覆盖靠函数补齐所以MySQL内置了上百个函数覆盖字符串处理、数值计算、日期时间、聚合统计、流程控制、JSON解析、加密哈希、系统信息等大类。把函数分类这件事不只是为了好记更是为了出问题时能快速定位思路。做数据清洗时遇到脏数据大概率是字符串类的活跑报表发现日期分组不对那就是日期函数的细节没拿捏准要算排名、累计值8.0以后的窗口函数是首选。我先按高频使用场景把分类和对应函数列一下分类代表函数典型场景字符串CONCAT、SUBSTRING、REPLACE、TRIM拼接姓名、截取编号、清洗空格数值ROUND、CEIL、FLOOR、ABS、MOD金额取整、分页偏移、奇偶判断日期时间DATE_FORMAT、DATE_ADD、DATEDIFF日报统计、有效期计算、时间差计算聚合COUNT、SUM、AVG、MAX、MIN订单量、销售额、均值、极值流程控制IF、CASE WHEN、IFNULL、COALESCE字段翻译、默认值兜底、多分支逻辑窗口ROW_NUMBER、RANK、LAG、SUM() OVER()分组排名、环比计算、累计值JSONJSON_EXTRACT、JSON_TABLE接口扩展字段、配置表解析1.2 学习函数的核心思路很多人学函数喜欢从第一个背到最后一个我完全不推荐。MySQL官方文档里函数列表一长串今天记了明天不用照样忘。我的经验是反过来从需求出发带着问题查函数。你想对字符串做什么搜索MySQL string function你想处理时间搜MySQL date function。用多了自然记住高频的那些生僻的留在文档里当字典即可。第二个思路是先测后用。在正式写业务SQL之前直接开一个SELECT不接from表就能验证函数行为。比如不确定SUBSTRING_INDEX到底是按哪边截取执行一句SELECT SUBSTRING_INDEX(a,b,c, ,, 2);结果一目了然。这个习惯能帮你避免很多自以为是的错误尤其是不同数据库之间函数行为有差异的时候。提示不同数据库的函数名和语法并不通用。SQL Server的GETDATE()、Oracle的SYSDATE、MySQL的NOW()各是各的习惯跨库迁移时函数部分几乎必然要改提前做好兼容层比事后改SQL省太多力气。2. 字符串与日期时间函数日常用得最多的两类2.1 字符串处理拼接、截取、替换的实战用法字符串函数是我平时写分析SQL用到频率最高的一类。先说拼接。CONCAT(a, b, c)返回abcCONCAT_WS(-, 2024, 11, 05)返回2024-11-05。这里第一个坑就是CONCAT和任何一个参数为NULL时会直接返回NULL。某次我拼接用户名和手机号做展示结果有个用户没填手机号整列变空排查了半天才发现是CONCAT遇到NULL吞掉了整个结果。改法是用CONCAT_WS或者先用IFNULL把可能为空的字段兜底。截取函数SUBSTRING和SUBSTRING_INDEX的差别要分清。SUBSTRING(hello world, 1, 5)从第1位开始取5个字符得到hello注意MySQL的起始位置是从1开始不是0。SUBSTRING_INDEX(a,b,c,d, ,, 2)则是按分隔符截取返回第二个逗号之前的部分a,b参数如果是负数则从右往左数。做身份证号处理的时候SUBSTRING(id_card, 7, 8)可以抽出出生日期SUBSTRING_INDEX(email, , 1)可以提取邮箱前缀。这类需求在用户画像和数据清洗里太常见了。REPLACE(str, from_str, to_str)做的是全局替换比如把手机号里的空格去掉REPLACE(phone, , )。TRIM系列函数可以去掉字符串首尾空格但要注意它默认只处理空格如果要去掉换行符或者制表符需要配合TRIM(BOTH \r\n FROM str)这种带指定字符的写法或者用REPLACE先把这些符号替换掉。实操心得处理用户录入的备注文本时我习惯先用REPLACE(text, CHAR(13), )去掉回车再用TRIM去首尾空格最后才做长度判断。直接从接口落库的数据经常带着各种不可见字符不清理干净后面展示或导出都是雷。2.2 日期时间处理格式化、计算与常见误区日期函数是报表开发的重灾区。DATE_FORMAT(create_time, %Y-%m-%d)是格式化日期最常用的写法格式化串里的标识符一定要记准%Y四位年份%m两位月份%d两位日期%H24小时制小时%i分钟%s秒。我见过不止一次把分钟写成%m导致时间显示成月份这种错特别隐蔽因为看起来格式差不多实际上数据全偏了。日期计算方面DATE_ADD(date, INTERVAL 1 DAY)和DATE_SUB(date, INTERVAL 3 MONTH)用来做偏移。算两个日期之间差多少天直接DATEDIFF(2024-12-01, 2024-11-01)返回30。但如果要精确到小时或者想按自然月工作日去算就要用TIMESTAMPDIFF比如TIMESTAMPDIFF(HOUR, start_time, end_time)。注意DATEDIFF忽略时间部分只比较日期TIMESTAMPDIFF不会自动忽略时间单位要传对。跟时间戳互转也是高频操作。UNIX_TIMESTAMP(2024-11-05 10:00:00)把日期时间转成秒级时间戳FROM_UNIXTIME(1730762400)把时间戳转回日期时间。秒级时间戳在对接外部接口时经常遇到尤其要注意是秒还是毫秒毫秒要先除以1000再转否则日期会是1970年附近。某次对接第三方推送对方给的是毫秒时间戳我没换算直接FROM_UNIXTIME结果线上日期显示1969年排查半天才发现。NOW()、CURDATE()、CURTIME()这组函数也要说清楚。NOW()返回当前日期时间CURDATE()只返回日期CURTIME()只返回时间。在事务里执行NOW()返回的是事务开始的时间点在存储过程或者批处理里这是个容易忽略的细节。如果你要的是当前语句执行时刻用SYSDATE()。注意日期函数返回类型要盯紧。DATE_FORMAT返回的是字符串不是日期类型。把它拿去跟日期字段比较MySQL会尝试把字符串转成日期格式对还好格式一不对就容易隐式转换出错甚至索引失效。能用日期类型就直接用日期类型别贪图格式化后的好看去制造隐式转换的隐患。3. 聚合函数与条件控制从多行到一行的数据透视逻辑3.1 聚合函数COUNT、SUM、AVG背后容易被忽略的细节聚合函数做的事情是把多行数据压缩成一行结果报表里离不开它们。但越是常用的函数越有一些细节被忽略。COUNT(*)统计的是所有行数COUNT(1)效果一样COUNT(column_name)则只统计该列非NULL的行数。这三者的差别在业务上有实际意义统计下单用户数时如果你COUNT(下单时间)恰好有人下单时间为空比如说下到一半未提交结果就会比真实用户数少。我一般先看数据质量再决定用哪个。SUM和AVG会自动忽略NULL行也不会报错。这带来一个隐含问题AVG(amount)如果金额列存在大量NULL均值是忽略NULL算出来的。业务上可能期望把NULL当0参与平均那就得先COALESCE(amount, 0)处理再聚合。MAX、MIN不止能处理数值字符串、日期都能比大小日期字段直接取最早最晚时间点非常方便。GROUP_CONCAT是一个容易被低估的函数它能把分组内某个字段拼成一个字符串。比如查某个用户的所有订单号GROUP_CONCAT(order_no SEPARATOR ,)。但它有个默认长度限制参数group_concat_max_len默认值是1024字节拼的字符串一长就被静默截断。业务上有次我拼商品ID列表给下游接口结果列表不全找了半天才发现是这个参数的问题。调大它的办法是SET SESSION group_concat_max_len 102400;会话级设置即可不影响全局。聚合函数配合GROUP BY使用时WHERE和HAVING的过滤顺序要清楚WHERE在分组前过滤行HAVING在分组后过滤组。想筛订单数大于10的用户HAVING COUNT(*) 10是对的写在WHERE里MySQL直接报错。这里推荐开启ONLY_FULL_GROUP_BY模式它强制SELECT列表里的非聚合字段必须出现在GROUP BY中能帮你从根上避免很多语法不报错但结果随机的问题。3.2 条件函数IF、CASE WHEN、COALESCE怎么选流程控制函数在SQL里相当于写分支逻辑。IF(expr, value_if_true, value_if_false)是三元表达式适合简单的二选一。CASE WHEN则适合多分支两种语法都可以。一种叫简单CASECASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 END一种叫搜索CASECASE WHEN amount 1000 THEN 大单 WHEN amount 100 THEN 中单 ELSE 小单 END。搜索CASE更灵活判断条件可以复杂。IFNULL(expr1, expr2)是处理NULL的专用函数第一个参数非NULL就返回它否则返回第二个参数。COALESCE更通用它可以接受多个参数COALESCE(a, b, c)从前往后找第一个非NULL值。设计默认值的时候我更喜欢用COALESCE因为它能串联多个候选字段。比如展示用户昵称时昵称为空用用户名用户名为空用手机号COALESCE(nickname, username, phone)一行搞定。NULLIF(a, b)这个函数知道的人相对少它的逻辑是如果a等于b就返回NULL否则返回a。用它可以在聚合时排除特定值。比如统计平均时长时把时长刚好为0的排除掉AVG(NULLIF(duration, 0))比在子查询里WHERE duration 0要简洁。实操心得CASE WHEN里忘写END是新人报错高频点。每次写完CASE我都习惯性地数一下有没有配对的END条件多了之后这毛病特别容易犯。另外CASE WHEN的分支是按顺序匹配的第一个满足就不再往下走写判断条件时要把精确的条件放前面宽松的兜底放最后别反过来。4. 窗口函数与JSON函数进阶功能如何提升开发效率4.1 窗口函数分组排名、累计值、环比一次讲透MySQL从8.0开始稳定支持窗口函数这可以说是近年来SQL写法最大的一次升级。窗口函数的特点是不改变行数它在每一行上额外追加计算结果。比如ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC)按用户分组、组内按创建时间倒序编号就能方便地取出每个用户最近一条下单记录。做每组Top N需求时用子查询包一层再WHERE rn 3即可这在以前要靠各种自连接和临时变量烦得很。排名相关的窗口函数有三个要分清ROW_NUMBER编号不重复排序值相同也硬分出先后RANK遇到相同排序值会并列且跳号比如两个并列第1下一个就是第3DENSE_RANK并列不跳号下一个是第2。选哪个取决于业务含义。排绩效名次一般用DENSE_RANK取Top N用ROW_NUMBER更稳。聚合函数也可以配OVER()做累计值。SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time)会按时间顺序累加得到每个用户截至当前行的累计消费金额。环比计算是另一个高频场景LAG(amount, 1) OVER (ORDER BY stat_date)取上一行的值与当前值相减再除以上一值就是环比增长率。LEAD则是往后取行一般用来算下一个事件距离当前事件的时间间隔。注意窗口函数要关注MySQL版本。5.7及以下没有内置支持我当年是在低版本上用用户变量自连接模拟实现的既难读又容易出错。如果生产环境还是5.7我建议优先推动升级或者明确告诉业务方这类SQL只能临时凑合。另外OVER()里ORDER BY和PARTITION BY的顺序有讲究分组在前、组内排序在后写反了结果完全不对。4.2 JSON函数JSON_EXTRACT到JSON_TABLE的跃迁现代业务接口越来越爱用JSON字段存扩展信息MySQL的JSON函数也变得实用了。最基础的是JSON_EXTRACT(json_doc, $.field)按JSON路径取某个值。为了省去处理引号的麻烦MySQL提供了-操作符等价于JSON_UNQUOTE(JSON_EXTRACT(...))直接返回去引号的字符串。比如SELECT>