MySQL内置函数实战：从字符串清洗到窗口函数与性能避坑

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 函数族

我把日常用得最多的函数按用途分了个类，先给你一张速查表，后面逐类展开：

分类的意义不是背名字，而是建立肌肉记忆：遇到"清洗"想字符串函数，遇到"算数"想数值函数，遇到"按时间统计"想日期加聚合，遇到"取组内排名"想窗口函数。下面逐个拆。

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"这类字段特别好用，比如文件路径、多级分类 ID：

SELECT 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; -- 1200

8.0 里REGEXP_REPLACE的第三个参数是替换字符串，不是模式修饰符，很多人刚用的时候容易传错。5.7 版本没有这两个函数，只能用嵌套SUBSTRING加REPLACE硬凑，那体验完全不一样。

2.2 数值函数与金额精度：别让浮点数坑了账

数值函数里要特别讲精度。ROUND()在 MySQL 里对ROUND(2.5)返回 3，ROUND(-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_ci，CONCAT或者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为 NULL，COUNT(status)不会把它算进去，而COUNT(*)会。写统计 SQL 之前要明确意图：是统计"订单总数"还是"有状态值的订单数"。

SUM同样跳过 NULL，但全部是 NULL 时会返回NULL而不是 0，很多报表程序直接把结果展示成空白。处理办法是包一层IFNULL：

SELECT 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_len：

SET 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 BY，5.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-else

6.1 IF、IFNULL、NULLIF、CASE WHEN 怎么选

流程控制函数处理的是"按条件产生不同结果"的逻辑。IF()是三目运算，IFNULL()是空值兜底，NULLIF()是两个值相等则返回 NULL，CASE 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是 0，NULLIF返回 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'转成 10，'abc'转成 0。这个"尝试"会导致一批不符合直觉的结果。避免的办法是让比较双方类型一致，函数返回的结果也要注意类型。

比如SUBSTRING返回字符串，和数值比较时可能触发转换。平时写 SQL 时，把类型转换函数用起来：CAST('123' AS UNSIGNED)显式转成数值，免得隐式转出问题。函数嵌套时也要注意每一层返回值是什么类型，最外层再和字段比较才不会踩坑。

8.3 MySQL 5.7 与 8.0 函数差异速查

5.7 和 8.0 函数差异挺大，踩坑的时候先问一句：这个函数是哪个版本才有的？

升级到 8.0 最受益的就是窗口函数和正则替换，SQL 写法整体提升一个段位。但升级时要注意排序规则默认从utf8mb4_general_ci变成utf8mb4_0900_ai_ci，如果库里有大量字符串函数和排序操作，需要提前做一轮兼容性验证。

最后说点个人习惯。我写 SQL 有个原则：能不改表结构就先改写 SQL，能走索引就绝不让索引列套函数，能用窗口函数就不用临时表模拟，能显式转换类型就绝不依赖隐式转换。MySQL 函数本身不复杂，复杂的是把它们放进真实业务场景里的各种边界情况。你只需要记住，所有函数都只是工具，真正决定 SQL 好不好用的，是写的人对数据语义和执行计划的理解。踩过几次 NULL、精度、隐式转换的坑之后，你自然就能写出又快又准的 SQL 了。