MySQL函数实战：从字符串到JSON的高效用法与性能优化

1. MySQL函数全景为什么要重视函数做后端开发或者数据分析的MySQL函数这关是绕不过去的。我见过不少开发者业务代码写得很溜结果一到写SQL就习惯性把所有数据捞回程序里再加工——明明一条DATE_FORMAT就能搞定的事非要在Java里循环处理几百条记录。说实话我早年也这么干过直到有一次被数据量教做人才老老实实把MySQL函数捡起来。这个标题里的8.MySQL函数看起来像是某个系列教程的第八篇。不管你是刚接触MySQL的新手还是写了好几年SQL但一直靠搜索引擎拼函数的半熟练工这篇文章的核心目标只有一个把MySQL里真正高频、真正能帮你省事的函数讲透。我会按函数类型拆开讲每个函数都配上真实业务场景和完整写法最后再把我这些年踩过的坑和排查经验一并交代了。MySQL的函数体系其实分得很清晰大致可以归成这么几类字符串处理、数值计算、日期时间、聚合统计、控制流判断、JSON解析。你在业务里遇到的90%以上的数据处理需求都能用这几类函数组合解决。关键在于你要建立一种思维能用数据库解决的就不要拖到应用层。数据库的索引、优化器、底层C实现比你用Python写个循环再去筛数据快一两个数量级这是常态。2. 字符串函数与数值函数业务开发里的主力军2.1 字符串函数拼接、截取、替换、大小写处理先聊字符串函数。这类函数在日常业务里出现频率最高我挑几个真正干活儿用的来讲。CONCAT与CONCAT_WS拼接字符串的两个核心函数。CONCAT(a, b, c)的结果是abc这个没啥好说的。但有一个特别容易踩的坑CONCAT只要有一个参数为NULL整个结果就是NULL。比如用户表里有first_name和last_name两个字段如果允许last_name为空你直接CONCAT(first_name, last_name)出来的可能就是NULL白拼接了。解决办法有两个一是用CONCAT_WS它在拼接时可以跳过NULL值二是先用IFNULL把NULL转成空字符串。CONCAT_WS(-, a, b, c)语法上第一个参数是分隔符结果是a-b-c这个函数在处理多个字段拼接、中间用逗号或横线隔开的场景时特别好用比如生成CSV导出文件的字段行.SUBSTRING和LEFT/RIGHT截取类函数。SUBSTRING(str, pos, len)从指定位置截取指定长度注意MySQL的字符串位置是从1开始数的不是0。LEFT(str, n)取左侧n个字符RIGHT(str, n)取右侧n个字符。我实际用得最多的场景是处理手机号脱敏CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4))一行SQL搞定用户列表的手机号隐私保护。这个写法在开发环境和生产环境都通用比在程序里写字符串切片干净多了。REPLACE做字符串替换语法是REPLACE(str, from_str, to_str)。我在电商项目里遇到过一个很实际的场景商品详情里存的是相对路径图片上线后域名变更了需要把所有数据里的老域名替换成新域名。一条UPDATE语句用REPLACE处理即可但要注意这类全表更新的操作上线前一定要备份并且先SELECT出来看看影响行数。大小写处理有UPPER和LOWER。这个看似简单但有一个隐藏的坑用函数处理字段后索引会失效。比如WHERE UPPER(username) ADMIN就算username字段上有索引MySQL也走不了因为索引是按原始值建的你套了一层函数优化器没法用它做查找。正确的做法是存储时就统一大小写或者查询时传入正确的格式。还有一个容易被忽略的是TRIM函数去掉字符串首尾的空格。别小看这个用户在网页表单里输入内容经常带着不可见的前后空格存储的时候不处理查询比对的时候就会出问题。TRIM(str)去两端空格LTRIM只去左边RTRIM只去右边。我一般在做登录校验时提醒一下用户名比对前先在SQL层面TRIM一下别让一个空格卡掉用户的登录流程。2.2 数值函数四舍五入、取整、绝对值与随机数数值函数相对简单但细节也不少。ROUND(num, d)四舍五入保留d位小数。这里有个老生常谈的坑ROUND是四舍五入但MySQL的ROUND在某些版本和精度场景下表现可能跟你在纸上算的不一样——因为它底层走的是浮点数或者精确数值运算取决于参数类型。稳妥的做法是涉及金额计算的场景字段类型用DECIMAL而不是FLOAT/DOUBLE。我曾经排查过一个线上对账不平的问题最后定位到原因就是金额字段用了FLOAT多个订单累计运算后出现了几分钱的差异。所以记住钱永远用DECIMAL绝不用浮点类型。FLOOR和CEIL一个是向下取整一个是向上取整。FLOOR(3.7)得到3CEIL(3.2)得到4。分页计算时我经常用CEIL来算总页数CEIL(total_count / page_size)。ABS(num)取绝对值处理差值、误差分析时很常用。RAND()生成0到1之间的随机数不带参数每次调用结果不同。实际业务中常见需求是随机取N条记录很多人的第一反应是ORDER BY RAND() LIMIT 10。但这在大表上是一个性能灾难——MySQL会对全表每行都生成一个随机数然后再排序代价非常高。表里有几十万条数据时这个查询能把数据库拖垮。我自己的经验是如果表有自增主键且相对连续可以用WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM table))) ORDER BY id LIMIT 10这种方式来近似随机取如果表很大且id有空洞可以接受一定的概率偏移。总之ORDER BY RAND()只适合小数据量或者一次性跑批的场景。还有MOD(num1, num2)取余数POW/POWER求幂。这些函数理解成本低遇到了直接查手册就行我不多展开。3. 日期时间函数与聚合统计数据分析和报表的基石3.1 日期时间函数格式化、提取、计算、时区日期时间函数在我的日常工作中使用频率极高做报表、做统计、做定时任务排查全部离不开。MySQL的日期时间函数体系非常完整核心的几个掌握透了基本能覆盖所有需求。NOW()返回当前日期时间CURDATE()返回当前日期CURTIME()返回当前时间。这三个是基础中的基础。插入数据的场景我喜欢用NOW()来记录创建时间。但要注意一个细节如果你的业务涉及多时区用户NOW()返回的是数据库服务器所在时区的时间不是客户端的时间。如果数据库和应用服务器不在同一时区或者你的用户分布在不同地域那就要考虑时区转换的问题。我做过一个跨境电商项目数据库设在亚太但大量用户在欧洲直接用NOW()记录订单时间欧洲用户看到的下单时间比实际晚了几个小时。后来统一改用应用层传入UTC时间展示时再转本地时区。DATE_FORMAT(date, format)是我最常用的格式化函数。把日期转成各种格式的字符串比如DATE_FORMAT(created_at, %Y-%m-%d)得到2024-08-15DATE_FORMAT(created_at, %Y-%m-%d %H:%i:%s)得到2024-08-15 14:30:00。写日报、周报、月报的聚合统计时我经常用GROUP BY DATE_FORMAT(created_at, %Y-%m-%d)来按天分组。这个函数的问题和UPPER一样在WHERE条件里对字段套DATE_FORMAT会导致索引失效比如WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-08-15即使created_at上有索引也用不上。改成WHERE created_at 2024-08-15 00:00:00 AND created_at 2024-08-16 00:00:00这样的范围条件索引就能正常使用性能差很远。YEAR()、MONTH()、DAY()分别提取年份、月份、日。做环比、同比统计时很常用。DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)做日期加减。DATE_ADD(NOW(), INTERVAL 7 DAY)就是七天后的时间。我常用的场景是写定时清理任务DELETE FROM temp_log WHERE created_at DATE_SUB(NOW(), INTERVAL 90 DAY)清理90天前的临时日志。这种写法比在代码里拼日期字符串安全得多直接在SQL里表达相对当前时间往前推90天这个语义清晰又不易出错。DATEDIFF(date1, date2)返回两个日期相差的天数注意是date1 - date2的结果。TIMESTAMPDIFF(unit, start, end)返回指定单位的时间差支持SECOND、MINUTE、HOUR、DAY、MONTH等。我算用户活跃周期、订单超时时效时常用TIMESTAMPDIFF(MINUTE, payment_time, NOW())来算订单支付后过了多少分钟。这里必须提醒一个时区相关的坑如果数据库服务器的time_zone设置不正确NOW()返回的时间就可能是错的。排查这类问题先执行SHOW VARIABLES LIKE time_zone确认。还有TIMESTAMP和DATETIME的存储差异TIMESTAMP会受时区影响会随time_zone变动而自动转换DATETIME则不会。选字段类型时如果你需要跟随服务器时区自动调整用TIMESTAMP如果你要保存一个固定时间点比如评论发布时间且不希望受时区干扰用DATETIME更稳。3.2 聚合函数COUNT、SUM、AVG、MAX、MIN的使用边界聚合函数是报表统计分析的核心。COUNT()、SUM()、AVG()、MAX()、MIN()这五个函数配合GROUP BY能解决绝大多数统计需求。先说COUNT()这个函数有两个容易忽略的细节。第一COUNT(*)和COUNT(字段)的区别COUNT(*)统计行数包括NULL值的行COUNT(字段)只统计该字段非NULL的行数。如果你统计的是有多少用户填了手机号用COUNT(phone)如果你统计的是一共有多少条订单不管订单金额是不是NULL用COUNT(*)。第二COUNT(DISTINCT 字段)可以统计去重后的数量比如COUNT(DISTINCT user_id)统计活跃用户数。SUM()求和需要注意字段类型。如果字段是字符串类型且内容不是纯数字SUM的结果可能不符合预期或者是MySQL能隐式转换但性能很差。AVG()求平均值自动忽略NULL值。这里有一个实际案例统计用户订单的平均金额如果直接用AVG(amount)amount为NULL的订单会被忽略可能导致平均值偏高。如果你希望NULL当作0参与计算先IFNULL(amount, 0)再求平均。MAX()和MIN()除了取最大值最小值之外还有一个妙用在GROUP BY分组后取每组中某个字段的极值对应的整行数据。这就需要一个子查询技巧。比如查每个用户最近的一笔订单先GROUP BY user_id取MAX(created_at)再拿这个时间去关联原表。这种写法比窗口函数简单直白适合MySQL 8.0以下版本的环境虽然8.0以上有窗口函数更优雅但线下环境不一定能用。聚合函数有一个必须注意的搭配规则WHERE子句在分组之前过滤HAVING子句在分组之后过滤。WHERE不能使用聚合函数条件HAVING可以。比如查出订单数超过10单的用户SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) 10这里COUNT(*) 10必须放在HAVING里写在WHERE会直接报错。4. 控制流函数与JSON函数让SQL具备逻辑判断能力4.1 IF、IFNULL、CASE WHEN的业务化应用很多开发者没意识到SQL里其实可以做条件判断不用把所有数据取出来再在Java或Python里分支处理。MySQL提供了IF、IFNULL、CASE WHEN这几个控制流函数能让你的SQL更加紧凑高效。IF(expr, if_true, if_false)是最简单的判断函数。比如在用户列表里根据状态字段显示正常或冻结SELECT username, IF(status 1, 正常, 冻结) AS status_text FROM users。这个函数适合单条件判断多条件嵌套写起来会很丑。IFNULL(expr, default)专门处理NULL。字段为空时给一个默认值比如展示用户备注信息时没有备注就显示暂无SELECT name, IFNULL(remark, 暂无) FROM users。注意IFNULL只能判断NULL如果是空字符串它不会帮你兜底。要同时处理NULL和空字符串用CASE WHEN remark IS NULL OR remark THEN 暂无 ELSE remark END。CASE WHEN是SQL里的switch-case也是我最推荐的条件判断写法。它有两种语法格式。第一种是简单函数CASE value WHEN match_value THEN result ELSE default END。第二种是搜索函数CASE WHEN condition THEN result ELSE default END支持多个WHEN条件。我举一个实际业务场景根据订单金额划分等级做报表。需求是把订单金额分成低、中、高三档统计各档订单数SELECT CASE WHEN amount 100 THEN 低 WHEN amount 1000 THEN 中 ELSE 高 END AS amount_level, COUNT(*) AS order_cnt FROM orders GROUP BY CASE WHEN amount 100 THEN 低 WHEN amount 1000 THEN 中 ELSE 高 END;这里注意GROUP BY后面要重复写一遍CASE表达式因为MySQL的GROUP BY不允许直接用SELECT里的别名——不过在新版本MySQL里已经支持GROUP BY别名了8.0以上可以简写GROUP BY amount_level但在兼容性优先的团队规范里我建议还是写完整表达式。CASE WHEN最大的价值是把复杂的多分支逻辑下推到数据库层避免在应用层写一大串if-else。而且配合聚合函数使用可以做条件统计比如统计金额大于100的订单总数用SUM(CASE WHEN amount 100 THEN 1 ELSE 0 END)比先查全表再在程序里过滤高效得多。4.2 JSON函数MySQL 5.7的隐藏利器MySQL从5.7版本开始正式支持JSON类型和JSON函数到了8.0更是完整支持。如果你的项目用了JSON字段存一些动态属性这些函数是必修课。JSON_EXTRACT(json_doc, path)用于提取JSON文档中指定路径的值。比如字段extra里存了{color: red, size: L}要取colorJSON_EXTRACT(extra, $.color)结果是red——注意返回值自带双引号要用JSON_UNQUOTE()去掉引号才能得到纯文本。我早期被这个细节坑过取出值来带着引号直接拼进字符串里显示出来就多了一对引号。MySQL 5.7以上还有一个简化写法-操作符。extra-$.color等价于JSON_EXTRACT(extra, $.color)。而-操作符等价于JSON_UNQUOTE(JSON_EXTRACT(...))直接返回不带引号的字符串。我实际开发中几乎都用-省一步是一个。JSON_ARRAYAGG()和JSON_OBJECTAGG()是配合GROUP BY用的聚合函数。JSON_ARRAYAGG(field)把分组内字段的值聚合成JSON数组JSON_OBJECTAGG(key_field, value_field)聚合成JSON对象。这个在生成报表JSON数据时很实用。我做过一个场景按城市分组把每个城市下的用户ID聚合到一个数组里一条SQL直接出结果不用在应用层循环拼接。JSON_CONTAINS()判断JSON文档是否包含指定值JSON_LENGTH()返回JSON数组的长度或对象中键的数量。做标签系统时我经常用JSON_CONTAINS(tags, VIP)来判断用户是否有某个标签。注意路径写法、引号处理这个函数对JSON值做精确匹配字符串必须带双引号我见过不少人在这一步写错。还要强调JSON字段虽然灵活但不要滥用。在JSON字段上无法建常规索引虽然可以建虚拟列索引查询性能天然受限。我的建议是明确要参与WHERE条件过滤、排序的字段一定拆出来单独建列JSON字段只用来存那些真正动态、无法预估结构的附加属性。5. 实操经验汇总从SQL编写到线上排查的完整链路5.1 函数使用与性能的平衡艺术前面分散提了很多性能问题这里做一个系统的梳理。函数本身没有好坏之分关键在于使用位置和方式。第一WHERE条件中尽量避免对字段套函数。这是最核心的性能原则。WHERE DATE(created_at) 2024-08-15和WHERE created_at 2024-08-15 00:00:00 AND created_at 2024-08-16 00:00:00前者无法走索引后者可以。同样的道理适用于字符串函数、数值函数。查询条件里的字段保持原样把函数用在等号右侧的常量上。第二SELECT子句里用函数要注意返回数据量。如果只是把DATE_FORMAT用在一个10行结果集上毫无压力但如果你让数据库对100万行先做全表扫描再逐行格式化日期这活儿就重了。能用程序层做格式化就放在程序层数据库的职责是过滤和聚合不是做无谓的展示格式化。第三GROUP BY后配合聚合函数是数据库最擅长的事。我曾经处理过一个性能问题某同事把100万条订单数据全部查出再在Java里用Stream流做分组和求和JVM一度FullGC。改成SQL里的GROUP BY加SUM后查询从几十秒降到几百毫秒。数据库的聚合操作底层有优化是经过几十年的工程锤炼的别用自己的代码去挑战它。第四函数嵌套层级多了务必先跑EXPLAIN。写复杂的SQL时我习惯先EXPLAIN SELECT ...看一眼执行计划确认没有全表扫描、没有Using filesort。很多开发者在本地测功能没问题就直接上生产结果几十万数据就把数据库拖垮了根因就是没看执行计划。5.2 高频问题排查与避坑记录我整理了一些这些年真正遇到过的问题和解决方案做成一个速查表给各位参考。问题现象根因分析解决方案COUNT(phone)统计结果比预期少COUNT忽略NULL值确认统计口径需要包含NULL就用COUNT(*)CONCAT拼接结果出现NULL某个拼接字段为NULL用CONCAT_WS或先用IFNULL处理金额累加后不精确字段用了FLOAT/DOUBLE金额字段统一改DECIMAL(10,2)查询条件带格式化函数后变慢字段套函数导致索引失效改用范围条件查询JSON_EXTRACT返回值带引号返回值包含JSON引号用-操作符或JSON_UNQUOTE处理随机取数ORDER BY RAND()查询超时大表全表随机排序改用主键区间随机采样NOW()比业务时间早/晚数小时数据库时区配置不正确SET time_zone 08:00或统一应用层传UTCGROUP BY后条件过滤报错在WHERE里用了聚合函数聚合条件放HAVING子句除了表格里的问题还有一个我每次培训新人都会强调的细节SQL函数的大小写和引号规范。团队协作时统一风格能减少很多低级问题。比如字符串和日期常量一定要用单引号别用双引号虽然MySQL默认模式下两者都能用但双引号在部分sql_mode如ANSI_QUOTES下会被当作标识符的引用符很容易出诡异错误。另外写涉及日期字符串的SQL时我坚持用YYYY-MM-DD HH:MM:SS这个格式因为MySQL对这个格式的字符串可以直接隐式转换为DATETIME类型。有没有遇到过这样的情况查某天的数据你把日期写成2024/08/15结果在某些版本下查不出来这就是格式不统一导致的隐式转换问题。统一用横线分隔的格式能避免这类问题。最后再分享一个运维层面的经验上线一个包含函数处理的新SQL之前先估算数据量和影响行数再在测试环境用真实数据量压一遍。我在项目里加过一条UPDATE语句用REPLACE替换了几十万行商品描述里的图片域名因为没提前评估数据量锁表时间过长差点搞成事故。后来养成习惯所有UPDATE、DELETE操作都先跑SELECT COUNT(*)看清楚影响范围再在低峰期分批执行。MySQL函数这个主题看起来刻板实际是SQL能力的分水岭。能把函数用得恰当SQL就能写得既简洁又高效用不好要么逻辑复杂到没人能维护要么性能差到生产环境报警。我这些年最大的一个体会是每写一条SQL都问自己一句——这个操作放在数据库做合理吗索引能走吗数据量大时还扛得住吗带着这三个问题去写你的MySQL函数使用水平会提升得很快。