MySQL 内置函数这东西单拎任何一个出来都不难难的是组合、嵌套、边界情况和性能影响揉在一起的时候。我做了十几年数据开发见过太多函数都会SQL 写崩的例子日期格式化一把梭导致全表扫描、COUNT 和 SUM 对 NULL 的不同语义导致报表对不上账、窗口函数用错排名类型导致榜单数据错位。这篇文章不打算做成 API 手册而是按真实业务场景把 MySQL 函数拆开讲字符串清洗、数值精度、日期时间计算、聚合统计、窗口排名、流程控制外加自定义存储函数和性能避坑。无论你是刚学 SQL 的新手还是被线上慢查询折磨过的老手都可以对照自查。1. 学函数前先搞明白MySQL 内置函数的分类与执行逻辑1.1 函数的基本调用规则与返回值语义函数是纯计算式的输入一行参数输出一个结果。MySQL 内置函数大多数是确定性的同样的输入必然给同样的输出个别函数像UUID()、NOW()、RAND()是非确定性的每次调用结果不同。搞清楚这一点你才明白为什么某些场景不能用函数包索引列。函数可以出现在SELECT、WHERE、ORDER BY、GROUP BY、HAVING、ON等任意子句里也可以嵌套调用。比如查最近一天的订单日均金额SELECT DATE_FORMAT(created_at, %Y-%m-%d) AS day, ROUND(AVG(amount), 2) AS avg_amount FROM orders WHERE created_at 2024-11-01 00:00:00 AND created_at 2024-11-20 00:00:00 GROUP BY DATE_FORMAT(created_at, %Y-%m-%d) ORDER BY day DESC;DATE_FORMAT、AVG、ROUND在这里嵌套使用顺序是先按天格式化再对分组内的金额求平均最后四舍五入保留两位。这种嵌套调用很常见关键是理解执行顺序先过滤WHERE再分组GROUP BY再聚合最后排序。NULL 传播是 MySQL 函数最容易被忽略的语义。绝大多数字符串、数值函数在遇到 NULL 参数时会直接返回 NULL而不是报错或跳过。比如CONCAT(a, NULL)结果是NULL而不是a。很多人写满减、优惠券金额计算时某个字段是 NULL一算整行变 NULL最后报表数据莫名少一片排查半天才发现是 NULL 传播导致的。记住一个原则任何函数调用之前先想清楚参数有没有可能出现 NULL必要时用IFNULL或COALESCE兜底。1.2 一张表理清 MySQL 函数族我把日常用得最多的函数按用途分了个类先给你一张速查表后面逐类展开函数族代表函数典型用途字符串CONCAT, SUBSTRING, REPLACE, REGEXP_SUBSTR, TRIM字段清洗、脱敏、拼接数值ROUND, FLOOR, CEIL, MOD, FORMAT金额精度、分页取整、取余日期时间NOW, DATE_FORMAT, STR_TO_DATE, DATE_ADD, DATEDIFF, TIMESTAMPDIFF时间窗口、续期判断、报表分组聚合COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT统计汇总、TopN、字符串聚合流程控制IF, IFNULL, NULLIF, CASE WHEN字段映射、条件计算、降级兜底窗口ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER排行榜、同比环比、组内累加类型转换CAST, CONVERT字符串转数值、日期转换分类的意义不是背名字而是建立肌肉记忆遇到清洗想字符串函数遇到算数想数值函数遇到按时间统计想日期加聚合遇到取组内排名想窗口函数。下面逐个拆。2. 字符串与数值函数清洗、脱敏、精度处理的实战套路2.1 字符串拼接、截取、替换的高频组合实际开发里字符串函数用得最多。CONCAT_WS、LPAD、SUBSTRING_INDEX、REGEXP_REPLACE这几个是我在项目里反复用的。CONCAT_WS是带分隔符拼接比CONCAT更安全。比如生成订单流水号SELECT CONCAT_WS(-, OD, DATE_FORMAT(NOW(), %Y%m%d), LPAD(id, 6, 0)) FROM orders WHERE id 1024;结果类似OD-20241120-001024。LPAD是左补零RPAD是右补零生成固定宽度的编号、流水号时非常有用。手机号脱敏是另一个高频场景最常见的是CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4))把中间四位换成星号。这里LEFT和RIGHT都是按字符位置截取记住 MySQL 的字符串位置是从 1 开始这个后面会单独说。SUBSTRING_INDEX是按分隔符截取处理xx/xx/xx这类字段特别好用比如文件路径、多级分类 IDSELECT SUBSTRING_INDEX(a/b/c, /, 2); -- a/b SELECT SUBSTRING_INDEX(a/b/c, /, -1); -- c负数表示从右往左取这个技巧在处理取最后一段时很省事。MySQL 8.0.4 以上自带REGEXP_SUBSTR、REGEXP_REPLACE做正则清洗是真的方便。比如把字段里所有非数字字符去掉SELECT REGEXP_REPLACE(订单金额1200元, [^0-9], ) AS digits; -- 12008.0 里REGEXP_REPLACE的第三个参数是替换字符串不是模式修饰符很多人刚用的时候容易传错。5.7 版本没有这两个函数只能用嵌套SUBSTRING加REPLACE硬凑那体验完全不一样。2.2 数值函数与金额精度别让浮点数坑了账数值函数里要特别讲精度。ROUND()在 MySQL 里对ROUND(2.5)返回 3ROUND(-2.5)返回 -3也就是 0.5 总是向远离 0 的方向取整。而FORMAT()是格式化输出的函数返回字符串默认带千分位比如FORMAT(1234567.891, 2)返回1,234,567.89。业务上算金额我建议优先考虑用DECIMAL而不是FLOAT或DOUBLE。FLOAT 是近似存储做乘法加法之后误差会累积对账就对不上。DECIMAL(10,2)存金额配合ROUND和SUM才能保证结果一致。这是我在一个财务系统项目里踩过坑后的铁律涉及钱永远不用浮点。FLOOR向下取整CEIL向上取整MOD取余。MOD可以用来做分表路由、奇偶判断、周期取模。比如按用户 ID 哈希取模分桶统计SELECT MOD(user_id, 16) AS bucket, COUNT(*) FROM users GROUP BY MOD(user_id, 16);这个写法在分库分表方案里做数据倾斜分析很常用。还有分页场景里算总页数CEIL(total_count / page_size)比手写整除加判断更简洁。2.3 三个容易翻车的字符串细节第一个字符集与排序规则不一致。两个表 JOIN 时一个utf8mb4_general_ci一个utf8mb4_0900_ai_ciCONCAT或者REPLACE在非等值比较时可能出现诡异结果或者慢扫描。建议统一用utf8mb4_0900_ai_ci这是 8.0 的默认排序规则。第二个LENGTH()统计的是字节数不是字符数。一个中文在 utf8mb4 下占 3 个字节CHAR_LENGTH()才是字符数。做截断、校验、计数时别混用。我有一次按字符数给文章摘要做长度限制用LENGTH统计所有中文内容全部超长被截断排查了半天才意识到是两个函数语义不一样。第三个SUBSTRING()从 1 开始计数。程序语言里习惯从 0 开始的人很容易在这翻车SUBSTRING(abcd, 0, 2)返回空字符串而不是ab这是 MySQL 的既定行为不算 bug但确实反直觉。记住这个特性就好。3. 日期时间函数从格式化到业务窗口计算3.1 DATE_FORMAT 与 STR_TO_DATE 的配对使用日期时间函数大概是业务 SQL 里出现频率最高的函数族。DATE_FORMAT和STR_TO_DATE是前后对应的两兄弟一个把日期格式化成字符串一个把字符串解析成日期。SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2024-11-20 15:30:45 SELECT STR_TO_DATE(2024/11/20 15:30, %Y/%m/%d %H:%i); -- 2024-11-20 15:30:00这两个函数的格式符体系要记几个常用的%Y四位年、%m两位月、%d两位日、%H24 小时制、%i分钟、%s秒。不常用的格式符用到再查不用背。这里有个重要提醒在WHERE中不要用DATE_FORMAT(created_at) 2024-11-20这种写法因为这个写法让created_at无法走索引。改成范围比较SELECT * FROM orders WHERE created_at 2024-11-20 00:00:00 AND created_at 2024-11-21 00:00:00;同样的数据这个改动能把扫描时间从秒级降到毫秒级。MySQL 函数使用的一个核心性能原则就是尽量让索引列独立出现在比较运算符的一侧不要在索引列外面套函数。3.2 日期加减与差值算续期、算超时、算年龄DATE_ADD、DATE_SUB、DATEDIFF、TIMESTAMPDIFF这四个函数几乎覆盖了所有时间窗口场景。DATE_ADD(2024-11-20, INTERVAL 30 DAY)算会员到期日DATE_SUB(NOW(), INTERVAL 7 DAY)算一周前的时间点。INTERVAL后面的单位支持DAY、HOUR、MINUTE、SECOND、MONTH、QUARTER、YEAR还有比较灵活的WEEK。DATEDIFF返回两个日期相差的天数结果只看日期部分忽略时间。TIMESTAMPDIFF更强大可以指定单位还能处理时间部分。比如算用户年龄SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;这个写法比单纯用YEAR(NOW()) - YEAR(birth_date)准确因为能正确处理生日还没到的场景。我见过生产环境的年龄统计用后者硬减每年 1 月 1 日算出来的年龄集体虚长一岁这种 bug 往往要等报表数据被业务质疑很久才被发现。还有一个订单超时场景15 分钟未支付自动关闭。UPDATE orders SET status closed WHERE status pending AND created_at DATE_SUB(NOW(), INTERVAL 15 MINUTE);这里用DATE_SUB把当前时间减 15 分钟放在右侧created_at索引列保持独立走索引就很稳。这个模式在优惠券过期、任务超时、会话清理等场景里都是通用写法。4. 聚合函数与分组统计GROUP BY 的正确姿势4.1 COUNT、SUM、AVG 对 NULL 的不同态度聚合函数最常用的是COUNT。但COUNT得细说COUNT(*)统计所有行COUNT(expr)统计expr不为 NULL 的行。这个区别非常关键。比如订单状态字段如果某些行status为 NULLCOUNT(status)不会把它算进去而COUNT(*)会。写统计 SQL 之前要明确意图是统计订单总数还是有状态值的订单数。SUM同样跳过 NULL但全部是 NULL 时会返回NULL而不是 0很多报表程序直接把结果展示成空白。处理办法是包一层IFNULLSELECT IFNULL(SUM(amount), 0) AS total_amount FROM orders WHERE status paid;AVG、MAX、MIN也有类似语义。AVG只计算非 NULL 行的平均值这符合一般统计预期但新手容易因为 NULL 混入导致计算结果莫名变小。比如一组金额里有几条记录没填AVG(amount)把所有空值忽略平均值和直觉上全量平均不一样。4.2 GROUP_CONCAT 的妙用与三个坑GROUP_CONCAT是 MySQL 特别好用也特别容易忽略的函数它能把分组内多个值拼成一个字符串。SELECT user_id, GROUP_CONCAT(order_no ORDER BY created_at DESC SEPARATOR ,) FROM orders GROUP BY user_id;这一句能把一个用户的所有订单号按时间倒序用逗号拼在一起做用户订单流水摘要非常方便。三个坑要记住第一GROUP_CONCAT默认最大长度是 1024 字节拼超了会被静默截断结果悄悄变短。解决方法是在会话级别调大group_concat_max_lenSET SESSION group_concat_max_len 10240;第二GROUP_CONCAT的结果是二进制字符串如果拼接的字段类型不一可能产生隐式转换尽量先把字段统一转成相同字符集和类型。第三它执行的时候会在内存里构建临时字符串数据量大时比较吃内存别把几十万行记录往一个字段里塞否则sort_buffer和临时表内存都可能被撑爆。4.3 ONLY_FULL_GROUP_BY 与分组查询规范MySQL 5.7 开始默认开启ONLY_FULL_GROUP_BY模式。这个模式的意思是SELECT 里出现的非聚合列必须出现在 GROUP BY 中或者被聚合函数包裹否则直接报错。SELECT user_id, order_no, SUM(amount) FROM orders GROUP BY user_id;在开启模式的 8.0 下会直接报错因为order_no既不在 GROUP BY 里也没被聚合。这个报错其实是在提醒你这条 SQL 语义不严谨因为一个user_id可能对应多个order_no你要么把order_no加进 GROUP BY要么明确用聚合函数处理它。配套问题是SELECT 里直接出现未聚合列同时加 GROUP BY5.7 以前可以运行但不保证正确。某些情况下它会返回分组内第一条但这个行为没有官方保证完全取决于执行计划不要依赖。5. 窗口函数从取前几到组内排名SQL 能力分水岭5.1 窗口函数是什么和 GROUP BY 有什么区别窗口函数是 MySQL 8.0 引入的很多老开发还在靠临时表和用户变量模拟排名。窗口函数的核心概念是窗口它定义了一个行的观察范围然后在这个范围内做聚合或排名但每一行都保留自己。基本语法函数 OVER ( [PARTITION BY 分组字段] [ORDER BY 排序字段 [ASC|DESC]] [ROWS BETWEEN ... AND ...] )PARTITION BY是分组ORDER BY是在组内排序ROWS子句定义窗口边界。没有PARTITION BY时整个结果集是一组。窗口函数和 GROUP BY 的最大区别GROUP BY 会把多行压缩成一行窗口函数不会减少行数。这决定了窗口函数适合做明细加排名同时存在的报表。比如订单明细表后面要带一个用户维度累计消费金额用窗口函数可以一行写出用 GROUP BY 得先子查询再关联。5.2 ROW_NUMBER、RANK、DENSE_RANK、LAG 的实战场景ROW_NUMBER()给每组按顺序编号不会重复RANK()排名并列时会跳过序号比如两个人并列第二下一个是第四DENSE_RANK()并列不跳号下一个是第三。三者的差异在排行榜场景里直接决定业务口径。经典场景是查每个用户最近一笔订单SELECT user_id, order_no, created_at FROM ( SELECT user_id, order_no, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE t.rn 1;这个先编号再过滤的模式是窗口函数最经典的双层查询写法。子查询里按用户分组编号外层把编号为 1 的行挑出来。类似的写法还可以实现每个品类销量前 3 的商品。LAG()和LEAD()还能做环比场景。比如当月销售额与上月的差值SELECT month, SUM(amount) AS sales, LAG(SUM(amount), 1) OVER (ORDER BY month) AS prev_sales, SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY month) AS diff FROM sales GROUP BY month;这里要特别注意窗口函数里可以用聚合函数的结果做参数因为窗口函数是在聚合之后执行的。这个先聚合再开窗的执行顺序是理解这个 SQL 的关键。6. 流程控制函数在 SQL 里实现 if-else6.1 IF、IFNULL、NULLIF、CASE WHEN 怎么选流程控制函数处理的是按条件产生不同结果的逻辑。IF()是三目运算IFNULL()是空值兜底NULLIF()是两个值相等则返回 NULLCASE WHEN是完整的多路分支。最常用的是CASE WHEN因为可读性好。比如订单金额分档SELECT order_id, amount, CASE WHEN amount 100 THEN 小额订单 WHEN amount 1000 THEN 中额订单 ELSE 大额订单 END AS level FROM orders;IFNULL用来兜底金额计算很常见刚才聚合函数部分已经出现过。NULLIF(expr1, expr2)的用法比如计算一个比率时避免除零SELECT COUNT(*) / NULLIF(paid_count, 0) FROM stats;如果paid_count是 0NULLIF返回 NULL除法的结果是 NULL比直接报错好在能暴露问题不引起计算中断。如果业务上需要返回 0就再套一层IFNULL。选择原则很简单CASE WHEN适合多分支且条件复杂IF适合简单二选一IFNULL专门处理 NULL 兜底NULLIF负责制造 NULL用在防止除零这种场景。6.2 递归 CTE 与流程控制结合树形结构计算MySQL 8.0 支持递归 CTE配合函数可以做很多树形结构计算。比如以某个节点向上回溯所有祖先WITH RECURSIVE cte AS ( SELECT id, parent_id, name FROM category WHERE id 128 UNION ALL SELECT c.id, c.parent_id, c.name FROM category c JOIN cte ON c.id cte.parent_id ) SELECT * FROM cte;这个 CTE 有两个部分锚点部分查出起始节点递归部分不断通过parent_id向上关联父节点直到没有匹配为止。在组织架构、商品类目、权限树场景里非常实用。注意递归 CTE 里递归部分不能引用聚合函数、窗口函数和某些子查询这是版本限制。另外必须保证递归有终点否则会死循环生产环境一定要在递归查询中设置合理的终止条件或者MAX深度限制。7. 自定义函数存储函数把常用逻辑固化下来7.1 创建与调用一个自定义函数内置函数不够用的时候MySQL 允许自定义存储函数用CREATE FUNCTION声明。比如把手机号脱敏逻辑固化成函数DELIMITER $$ CREATE FUNCTION mask_phone(phone VARCHAR(20)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN RETURN CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)); END$$ DELIMITER ;创建后就可以在查询里直接调用SELECT id, mask_phone(phone) FROM users;DETERMINISTIC关键字要记住函数返回值确定才能标它这对复制、缓存、查询优化都有影响。不确定函数不建议标DETERMINISTIC否则可能在主从复制时出现结果不一致。函数内部还可以读表但这会引入额外开销不如直接在查询里 JOIN。7.2 权限、复制与维护注意事项存储函数需要CREATE ROUTINE权限MySQL 8.0 默认也是受限的。生产环境创建函数需要 DBA 配合不是想建就能建。另一个重点是开启 binlog 时函数如果标记为DETERMINISTIC才能被安全复制否则 MySQL 会拒绝在主从架构下创建函数报错提示需要设置log_bin_trust_function_creators参数。这个参数设置是个双刃剑开了能方便创建函数但你得确保所有函数真的是确定性的否则主从数据不一致的风险由你来背。项目上我一般建议少建函数能不建就不建。自定义函数虽然方便但滥用会让 SQL 变得难维护也影响数据库优化器对索引的使用。如果只是单项目里偶然用一次优先直接在 SQL 里写如果需要跨查询复用同一逻辑再考虑存储函数。8. 常见问题与性能避坑手册8.1 索引为什么失效函数在索引列上的三种解法最典型的是在WHERE条件里对索引列套函数。前面日期函数部分提过这里系统说一下MySQL 8.0 对某些函数有优化但并没有覆盖所有场景的函数索引DATE_FORMAT(col)...、SUBSTRING(col, 1, 3)abc这类写法基本都不能走索引。解决方案有三种改写为范围条件col 2024-11-20 00:00:00 AND col 2024-11-21 00:00:00使用生成的列加索引定义GENERATED COLUMN然后创建索引查询时直接按生成列过滤新增冗余字段字段值在写入时就处理好查询直接用具体用哪种取决于能改 SQL 还是能改表结构。生产环境表结构不能动优先改写 SQL如果能动生成列是个很干净的办法ALTER TABLE orders ADD COLUMN day_col CHAR(10) GENERATED ALWAYS AS (DATE_FORMAT(created_at, %Y-%m-%d)) STORED, ADD INDEX idx_day_col (day_col); SELECT * FROM orders WHERE day_col 2024-11-20;这种生成列是 8.0 的正规做法查询速度接近索引扫描而且不增加应用层维护成本。8.2 隐式类型转换函数结果里藏着的雷另一个高频坑是隐式类型转换。字符串和数值比较时MySQL 会尝试把字符串转成数值比如10abc转成 10abc转成 0。这个尝试会导致一批不符合直觉的结果。避免的办法是让比较双方类型一致函数返回的结果也要注意类型。比如SUBSTRING返回字符串和数值比较时可能触发转换。平时写 SQL 时把类型转换函数用起来CAST(123 AS UNSIGNED)显式转成数值免得隐式转出问题。函数嵌套时也要注意每一层返回值是什么类型最外层再和字段比较才不会踩坑。8.3 MySQL 5.7 与 8.0 函数差异速查5.7 和 8.0 函数差异挺大踩坑的时候先问一句这个函数是哪个版本才有的函数/能力5.7 支持8.0 支持REGEXP_SUBSTR / REGEXP_REPLACE否是窗口函数ROW_NUMBER/RANK/LAG否是递归 CTE否是JSON 函数JSON_EXTRACT/JSON_UNQUOTE基础支持增强GROUP_CONCAT 的 SEPARATOR是是STR_TO_DATE是是升级到 8.0 最受益的就是窗口函数和正则替换SQL 写法整体提升一个段位。但升级时要注意排序规则默认从utf8mb4_general_ci变成utf8mb4_0900_ai_ci如果库里有大量字符串函数和排序操作需要提前做一轮兼容性验证。最后说点个人习惯。我写 SQL 有个原则能不改表结构就先改写 SQL能走索引就绝不让索引列套函数能用窗口函数就不用临时表模拟能显式转换类型就绝不依赖隐式转换。MySQL 函数本身不复杂复杂的是把它们放进真实业务场景里的各种边界情况。你只需要记住所有函数都只是工具真正决定 SQL 好不好用的是写的人对数据语义和执行计划的理解。踩过几次 NULL、精度、隐式转换的坑之后你自然就能写出又快又准的 SQL 了。