我们做开发的几乎天天跟MySQL打交道写SQL的时候内置函数就是手里的工具箱。但很多人对函数的理解停留在“用的时候查一下”的层面没有系统梳理过结果就是同一个需求别人三行搞定你得写一长串临时逻辑还容易出bug。这篇文章我想换个讲法不按官方文档一条条念而是从实际业务场景出发把MySQL里最常用的几类内置函数揉碎了讲清楚。你跟着走一遍能直接用在项目里。1. 先建立函数选择的整体认知1.1 别把函数当字典背按场景分类记忆MySQL内置函数官方文档列了几百个你要是真一个个背一个月都不够。我平时带人的时候习惯让大家按使用场景去分组而不是按函数名去记。我的分组逻辑很简单字符串处理数据清洗、格式转换、拼接拆分这类用得最频繁数值计算统计报表、价格处理、百分比计算日期时间时间戳转换、区间判断、按天/周/月聚合条件逻辑IF分支、NULL值处理、CASE WHEN聚合统计配合GROUP BY做汇总JSON处理现在接口数据大量用JSON存储你脑子里有了这张图碰到问题时就会自然反应哦这是字符串处理场景然后直接定位到对应分类效率会高很多。1.2 为什么说内置函数是SQL优化的第一道关卡我见过不少性能问题根源不在索引、不在表结构而是在函数使用上。比如在WHERE条件里对索引列用了函数索引就废了。这不是函数本身的问题是使用姿势的问题。举个例子你要查2024年1月份的订单-- 不推荐对索引列create_time用了DATE函数 SELECT * FROM orders WHERE DATE(create_time) 2024-01-15; -- 推荐直接写成范围查询 SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;明明两个结果一样但性能差很多。前者如果create_time有索引也白搭。这就是我强调函数使用姿势的原因——函数本身没毛病用错位置才是大问题。2. 字符串函数实战拆解2.1 CONCAT与CONCAT_WS拼接字符串的正确打开方式做报表的时候经常需要把姓和名拼成全名或者把省市区拼成完整地址。CONCAT的用法很简单SELECT CONCAT(last_name, first_name) AS full_name FROM users;但这里有个坑如果某个字段是NULLCONCAT的结果会变成NULL。比如用户没填省份你拼地址的时候整条记录就没了。这时候用CONCAT_WS更稳妥-- CONCAT_WS第一个参数是分隔符后面的分隔符会自动加 SELECT CONCAT_WS(-, province, city, district) AS full_address FROM users;CONCAT_WS遇到NULL会自动跳过不会像CONCAT那样整体变NULL。我当初处理一批地址数据时就栽过这个跟头后来统一改成CONCAT_WS再也没出过问题。2.2 SUBSTRING_INDEX取字符串片段的神器SUBSTRING_INDEX是处理分隔符字符串特别好用的函数格式是SUBSTRING_INDEX(str, delim, count)count为正数时从左边数负数时从右边数。举个例子要从192.168.1.100里取最后一段IPSELECT SUBSTRING_INDEX(192.168.1.100, ., -1); -- 返回100取倒数第二段SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(192.168.1.100, ., -2), ., 1); -- 返回1这个函数在处理URL解析、日志分析、IP分类时非常实用。之前做过一个流量分析系统就是从访问日志里用这个函数拆出URL的域名和路径几百万条数据一条SQL就处理完了根本不用写程序逻辑。2.3 REPLACE与正则替换数据清洗的左右手数据清洗时REPLACE是基础款UPDATE products SET name REPLACE(name, 旧品牌, 新品牌) WHERE name LIKE %旧品牌%;但REPLACE只能做精确替换遇到变化的格式就束手无策了。MySQL 8.0以上可以用REGEXP_REPLACE-- 把手机号中间四位替换成星号 SELECT REGEXP_REPLACE(13812345678, (\\d{3})\\d{4}(\\d{4}), \\1****\\2);对于手机号脱敏这类的需求REGEXP_REPLACE是一行搞定的事你要是用REPLACE硬写遇到长度不一的手机号就得写好几条CASE WHEN去判断。8.0以下版本没有这个函数一般的处理思路是先取出来在程序里做或者用多套REPLACE嵌套硬解效果都差一些。2.4 LEFT/RIGHT/SUBSTRING按位置取子串的场景差异按位置取子串三个函数各有侧重LEFT(str, n)取左边n个字符适合取年份的前缀RIGHT(str, n)取右边n个字符适合取文件后缀SUBSTRING(str, pos, len)从指定位置取len个字符实际业务里RIGHT常用于截取银行卡号后四位做核对展示SELECT RIGHT(bank_card_no, 4) AS card_tail FROM payments;LEFT常用来取身份证前六位判断地区SELECT LEFT(id_card, 6) AS region_code FROM users;注意一点MySQL里SUBSTRING的pos是从1开始的很多从其他数据库转过来的朋友会习惯性从0开始一写就是OFF BY ONE错误这个细节要留意。3. 数值函数与日期函数的组合应用3.1 ROUND、FLOOR、CEILING在报表统计中的使用边界数值函数看着简单用错了就出大事。ROUND是四舍五入FLOOR是向下取整CEILING是向上取整。但做金额计算时你要清楚业务的取整规则。财务场景里算折扣后金额-- 金额单位是分算打8折 SELECT ROUND(price * 0.8) FROM products;但分转元的时候有人用FLOOR有人用ROUND结果能差出几分钱。月底对账的时候几分钱的差异能折腾你好几天。我的建议是涉及金额的取整规则必须由业务方明确开发不能自己拍脑袋定。还有一个小细节ROUND的第二个参数可以控制小数位SELECT ROUND(3.1415926, 3); -- 返回3.142 SELECT ROUND(3.1415926, 0); -- 返回3这在计算百分比保留两位小数时很实用。3.2 DATE_FORMAT日期格式化的核心用法DATE_FORMAT应该是我使用频率最高的日期函数之一。它的用法非常灵活SELECT DATE_FORMAT(create_time, %Y-%m-%d) FROM orders;但更重要的是它在分组统计里的应用。比如按天统计订单数SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);按周统计、按月统计同理只需要改格式化字符串-- 按周 SELECT DATE_FORMAT(create_time, %x-W%v) AS week, COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %x-W%v); -- 按月 SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);注意到%x-W%v这个写法没有它表示ISO周格式跨年时不会出错。如果用%Y-%W年跟周数匹配可能错位。3.3 TIMESTAMPDIFF与DATEDIFF时间差计算的坑与解法计算两个时间之间的差值DATEDIFF返回的是天数TIMESTAMPDIFF更灵活可以指定单位-- 年龄计算 SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users; -- 两个时间相隔多少小时 SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) AS duration FROM tasks;TIMESTAMPDIFF的第一个参数支持FRAC_SECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR覆盖了几乎所有场景。这里有个常见坑DATEDIFF只比较日期部分不比较时间部分。比如2024-01-01 23:59:59和2024-01-02 00:00:01DATEDIFF返回1。但如果你算的是间隔可能实际只隔了2秒。所以选哪个函数取决于你的业务口径是跨了几个自然日还是实际间隔了多少时间。3.4 UNIX_TIMESTAMP与FROM_UNIXTIME时间戳转换的完整方案现在很多接口传时间都是时间戳存储到MySQL后展示时要转可读格式-- 时间戳转日期时间 SELECT FROM_UNIXTIME(create_time) FROM orders; -- 指定格式 SELECT FROM_UNIXTIME(create_time, %Y-%m-%d %H:%i:%s) FROM orders; -- 日期时间转时间戳 SELECT UNIX_TIMESTAMP(2024-01-15 10:30:00);还有一个容易忽略的点FROM_UNIXTIME默认使用数据库服务器的时区。如果你的应用服务器和数据库服务器在不同时区比如数据库部署在境外机房展示出来的时间就全乱了。这种情况下SQL层面比较难处理我通常建议在应用层做时区转换或者统一把时间以UTC存、展示时按用户时区换算。4. 条件判断与聚合函数在生产环境的使用经验4.1 IF、IFNULL、COALESCE空值处理的三种层次空值处理是写SQL最容易忽略的地方。IF适合二选一分支SELECT IF(score 60, 及格, 不及格) FROM exams;IFNULL是IF的特例专门处理NULLSELECT IFNULL(remark, 无备注) FROM orders;COALESCE更灵活可以接多个参数返回第一个非NULL值SELECT COALESCE(real_name, nick_name, 匿名用户) FROM users;这三种看起来差不多但选择逻辑不一样IF用来做布尔判断IFNULL专门兜底NULLCOALESCE用于多级备选值。我见过有人把IFNULL当IF用写得很别扭还要嵌套好几层其实换COALESCE一行就清楚了。4.2 CASE WHEN与IF的高级用法从单行到分组统计CASE WHEN不只是用来加个别名字段它是可以做条件聚合的。比如要统计订单表中不同价格区间的数量SELECT SUM(CASE WHEN amount 100 THEN 1 ELSE 0 END) AS cheap_cnt, SUM(CASE WHEN amount 100 AND amount 1000 THEN 1 ELSE 0 END) AS mid_cnt, SUM(CASE WHEN amount 1000 THEN 1 ELSE 0 END) AS expensive_cnt FROM orders;这就是很典型的行转列思路把条件判断放到SUM里在不改变分组粒度的前提下一行就把多个统计值求出来了。如果不用这个技巧你就得写三个SQL再合并结果费时费资源。还有NULLIF用于避免除零错误-- 计算订单转化率分母可能为0 SELECT SUM(success_cnt) / NULLIF(SUM(total_cnt), 0) FROM stats;NULLIF(a, b)的意思是如果a等于b则返回NULL否则返回a。分母为0时结果为NULLMySQL里任何数除以NULL都是NULL不会报错。这个在计算环比、转化率时特别好用避免了因为除零导致整个查询失败。4.3 GROUP_CONCAT把多行数据拼成一行字符串GROUP_CONCAT是个宝藏函数可以把分组内的多行数据拼接成一行字符串。比如查询每个用户的全部订单号SELECT user_id, GROUP_CONCAT(order_no ORDER BY create_time SEPARATOR ,) FROM orders GROUP BY user_id;默认的分隔符是逗号可以自己指定SEPARATOR。我还常配合DISTINCT去重SELECT user_id, GROUP_CONCAT(DISTINCT category) FROM user_tags GROUP BY user_id;不过有个大坑GROUP_CONCAT的结果有长度限制默认是1024字节。一旦拼接的字符串超过这个长度会被静默截断你还看不到任何报错。处理方法是修改group_concat_max_len参数SET SESSION group_concat_max_len 102400;所以用这个函数做数据导出时要提前评估单组的数据量必要时在会话级别调大这个参数免得导出数据不完整还傻傻不知道原因。4.4 COUNT、SUM、AVG的隐性陷阱聚合函数看着简单实际使用中有几个容易踩的坑COUNT(1)与COUNT(*)的区别在MySQL里两者几乎没有性能差异都包含NULL值的行。真正需要注意的坑是COUNT(column)不统计NULL用错会导致结果差异。-- 统计有填手机号的用户数没填的会被忽略 SELECT COUNT(mobile) FROM users;SUM空集返回NULL对空表做SUM结果是NULL而不是0。如果前端直接展示就会出现空白数字。处理方式是配合IFNULLSELECT IFNULL(SUM(amount), 0) FROM orders WHERE status closed;AVG的NULL处理AVG自动忽略NULL行这个行为有时候是期望内的有时候则不是。比如统计某班平均成绩如果缺考记录是NULLAVG确实应该忽略但统计每日人均订单金额时没单的日子会被漏掉口径就不对了。遇到这种需求可以用SUM和COUNT拼出来自己控制分母。5. JSON函数现代开发绕不开的新能力5.1 JSON_EXTRACT与-操作符快速取JSON字段现在很多业务表设计了JSON字段存扩展信息比如用户表的extra字段里存了爱好、职业、年龄等。MySQL 5.7以上就支持JSON函数了但很多人还在用老办法整条取出来交给程序解析。MySQL原生取JSON字段有三种写法-- 方式一JSON_EXTRACT函数 SELECT JSON_EXTRACT(extra, $.age) FROM users; -- 方式二-操作符等价于JSON_EXTRACT SELECT extra-$.age FROM users; -- 方式三-操作符自动去引号 SELECT extra-$.age FROM users;注意第三种-取出来的值不带JSON引号更适合直接展示。如果写入的是字符串用JSON_EXTRACT或-会得到带双引号的值。我经常遇到新人用错在前端看到带引号的年龄还以为是数据类型问题。5.2 JSON_UNQUOTE与JSON_VALUE扩展到普通数据类型从JSON里取出来的字符串本质上还是JSON类型要用JSON_UNQUOTE转成普通字符串SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, $.hobby)) FROM users;MySQL 8.0以后提供了JSON_VALUE函数跟JSON_UNQUOTE(JSON_EXTRACT(...))的效果差不多但有个特点如果字段路径不存在JSON_VALUE返回NULL而JSON_EXTRACT会直接报错。所以在写不太确定路径是否存在的查询时JSON_VALUE更安全一点。还有一点JSON字段上无法直接建普通索引要建虚拟列加索引ALTER TABLE users ADD COLUMN age INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(extra, $.age))) VIRTUAL; ALTER TABLE users ADD INDEX idx_age (age);这样就能用索引查询JSON里的值了。这个方法在数据量大的场景很关键否则每次都要全表扫描解析JSON。5.3 JSON_ARRAYAGG与JSON_OBJECT结构化数据构建除了读取JSON函数也能用于构建数据。JSON_ARRAYAGG可以把多行聚合成JSON数组SELECT user_id, JSON_ARRAYAGG(order_no) FROM orders GROUP BY user_id;JSON_OBJECT可以构造JSON对象SELECT JSON_OBJECT(id, id, name, name) FROM users;这在对接口提供数据时很好用可以一次性在数据库层完成数据组装免去应用层循环拼装的逻辑。不过要注意如果聚合的数据量很大JSON_ARRAYAGG同样会受group_concat_max_len的影响需要一并考虑。6. 多类型组合场景实战6.1 一个电商报表的完整函数组合示例我把前面讲的内容串起来做一个组合案例。业务需求是统计2024年1月每天的下单用户数、订单数和客单价日期显示成2024-01-01格式。SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(DISTINCT user_id) AS user_cnt, COUNT(*) AS order_cnt, ROUND(IFNULL(SUM(amount), 0) / NULLIF(COUNT(*), 0), 2) AS avg_amount FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ORDER BY day;这个示例里同时包含了日期格式化、去重计数、空值处理、除零保护、分组聚合、排序是一个很典型的日报统计SQL。注意我用了NULLIF(COUNT(*), 0)防止除零用了IFNULL(SUM(amount), 0)防止SUM返回NULL这两个细节能让报表更健壮。6.2 手机号脱敏与用户信息统计的SQL级方案再举一个例子很常见的需求是把用户手机号脱敏后展示。SELECT CONCAT(LEFT(mobile, 3), ****, RIGHT(mobile, 4)) AS masked_mobile FROM users;这里用了LEFT、RIGHT、CONCAT三个函数的组合一条SQL就完成了脱敏逻辑。有些同学觉得这是在SQL里做业务太重但这种场景经常是数据导出、报表展示临时要用不值得为此改应用代码。SQL里做掉是最快的方式只要注意不要在持久化的时候应用就可以了。6.3 函数在导出数据时的实际应用做数据导出时最大的痛点是格式问题。比如导出CSV格式时日期默认带时间用户只想要日期金额要带千分位导出后却是原始数字。一个完整的导出SQL可能是这样SELECT order_no, DATE_FORMAT(create_time, %Y-%m-%d) AS order_date, FORMAT(amount, 2) AS amount_with_comma, CONCAT_WS( , province, city) AS region FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;FORMAT函数会自动在千位加逗号但要注意导出后再导入数据库时这种格式化后的字符串可能会有兼容问题。所以导出层做格式化没问题存储层无论如何都要存原始值。7. 常见问题与性能优化思路7.1 函数使用位置导致的索引失效问题关于函数导致索引失效的问题我再强调一次。在WHERE、JOIN、GROUP BY、ORDER BY等条件中对索引列使用函数会导致索引失效。比如你查某天的数据WHERE DATE(create_time) 2024-01-15不会走索引。解决办法是改写成范围查询。如果因业务固定只能按天查而且数据量很大可以考虑在表上建一个日期字段如order_date专门存格式化的日期值再加索引这样就直接了。7.2 隐式类型转换函数帮了倒忙有时候我们以为在正常使用函数实际触发了隐式类型转换-- mobile字段是varchar类型 SELECT * FROM users WHERE mobile 13812345678;这个查询会把数字13812345678转成字符串再比较MySQL会对mobile类型做隐式转换导致索引失效。对比一下手写转换SELECT * FROM users WHERE mobile 13812345678;看起来差异不大但在大表查询时第一条是慢查询第二条就走上了索引。这类问题的咨询量一直不少排查时建议用EXPLAIN先看执行计划发现type是ALL、rows很大优先检查类型是否匹配、有没有函数套在索引列上。7.3 函数嵌套过深时的可读性与性能取舍函数嵌套太深可读性会很差维护也难。比如SELECT IFNULL(ROUND(SUM(IFNULL(amount, 0)) / NULLIF(COUNT(*), 0), 2), 0) FROM orders;这种SQL虽然能跑但后来者要花很多时间才能看懂。我的习惯是嵌套超过三层就拆成子查询或者CTE每一步看得清清楚楚。WITH tmp AS ( SELECT IFNULL(amount, 0) AS amount FROM orders ) SELECT IFNULL(ROUND(SUM(amount) / NULLIF(COUNT(*), 0), 2), 0) FROM tmp;一眼看过去先清洗数据再计算均值逻辑清楚了不少性能上也没太大差异。代码是写给人读的SQL同理。7.4 函数版本兼容性从5.7到8.0的注意事项MySQL版本升级后函数的可用性和行为可能发生变化。比如REGEXP_REPLACE、JSON_VALUE这些函数在5.7里是没有的如果用着老版本SQL拿到8.0上就能跑反之不行。还有个典型例子5.7群组中执行GROUP BY对非聚合列要求比较宽松但8.0的默认模式开启了ONLY_FULL_GROUP_BY很多旧SQL在8.0上直接报错。我们线上就踩过这个坑批量迁移时一批统计SQL全废了只能改写。建议代码里的SQL尽量用标准写法GROUP BY的列跟SELECT的非聚合列保持一致不依赖宽松模式。这样版本升级时才不会出幺蛾子。8. 我踩过的坑和给你的建议8.1 关于GROUP_CONCAT长度限制的血泪史前面提到过GROUP_CONCAT的1024字节限制这里展开讲。之前做标签导出每个用户可能有好几十个标签一条SQL导出时标签被截断到1024字节结果导出去的数据一半用户标签不完整。排查了大半天才发现是group_concat_max_len的问题。后来我定了规矩凡是涉及GROUP_CONCAT的SQL上线前统一检查字段量级超过几百字节的就设置会话级参数。就算你感觉数据量不大也先估算一下。8.2 日期函数与时区的交集问题还有一次很头疼的问题业务方反映报表数据对不上后来发现是一部分数据用CURDATE()取的日期一部分直接用PHP端传过来的日期两边时区设置不一样导致昨天定义不一致。MySQL的CURDATE()用的是数据库服务器的系统时区应用服务器可能用UTC结果一到凌晨复盘就串台。建议是应用层和数据库层统一时区最好都设置成同一个值或者统一用UTC存储应用层转换展示千万别一半一半。如果一定要用数据库函数生成业务时间优先取服务器当前时间并明确时区避免各写各的。8.3 内置函数不是越多越好最后一个建议反直觉能用简单SQL解决问题别硬套函数。有的开发者喜欢把所有格式化、拼接、转换都扔给SQL搞得一条查询动不动就几十个函数数据库CPU飙高业务还得抱怨接口慢。函数该用在刀刃上数据清洗、报表统计、临时查询、接口取数确实需要时合理地用。如果一个复杂的逻辑在SQL里写得很累但在应用代码里写很顺手那就别犹豫在应用层做。数据库最擅长的是扫描、过滤、聚合不是当万能计算器用。我个人的态度是内置函数是工具箱不是必选项。你理解了每个工具的用途和边界关键时候选对工具SQL就能写得又快又清晰。平时写代码遇到同类问题不妨先想想MySQL有没有内置函数能做这个多翻翻官方文档的Function分类慢慢你会发现自己写SQL的效率和准确度都上一个台阶。