聊 MySQL 函数我不想从官方文档的函数清单说起。上个月有个同事拿一条 SQL 来找我说订单查询从 500 毫秒变成了 5 秒。查到最后问题既不在索引缺失也不在数据量突然变大而是有人在 WHERE 条件里包了一个 DATE_FORMAT 函数导致索引彻底失效。这种场景我前前后后遇到过十几次几乎每个团队里都有一个人会这样写。所以这篇内容我不会给你罗列几百个函数名那种东西官方文档比我说得清楚。我想从一个实际业务视角把 MySQL 函数讲清楚它到底是什么、常用场景怎么用、哪些写法会咬人、以及存储过程和自定义函数到底值不值得碰。无论是刚接触 MySQL 的新手还是写过几年 SQL 但没细想过“为什么”的同学应该都能从中找到点东西。1. 函数不是语法糖先看它替你干了什么活1.1 一个让我熬夜的业务需求有一次接到一个统计需求订单表里存了订单号、创建时间、区域和金额但区域和年份都埋在订单号字符串里比如“EAST-2024-000123”。需要按区域、按月汇总销售额最终输出一张报表区域是中文名称月份是“2024-01”这种格式。以前很多人的做法是先把全表数据查出来搬到程序里用循环拆字符串、格式化日期、分组求和最后再拼报表。这在数据量几百条的时候没问题但订单表几百万行的时候把数据全搬到应用层纯属自找麻烦。我当时的做法是直接用函数在 SQL 里拆解和聚合一条语句出结果SELECT CASE SUBSTRING_INDEX(order_no, -, 1) WHEN EAST THEN 华东 WHEN SOUTH THEN 华南 ELSE 其他 END AS region_name, DATE_FORMAT(create_time, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-01-01 AND create_time 2025-01-01 GROUP BY region_name, month ORDER BY month, total_amount DESC;这里用到了 SUBSTRING_INDEX 拆字符串、CASE WHEN 做映射、DATE_FORMAT 格式化日期、SUM 做聚合。一次查询直接出报表程序里只需要渲染结果。这就是函数最朴素的价值把原本需要代码循环处理的逻辑下推到数据库里完成。1.2 函数的三层分类先建立坐标系很多人学函数是看一个记一个今天学 CONCAT明天学 DATE_ADD没有整体概念遇到新需求就懵。我建议你先把 MySQL 函数分成这几层分类作用典型例子标量函数对每一行独立计算输入一个值输出一个值UPPER、CONCAT、DATE_FORMAT、SUBSTRING_INDEX聚合函数对多行数据做汇总输出一行结果COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT流程控制在 SQL 里做条件判断和逻辑分支IF、CASE WHEN、COALESCE、NULLIF窗口函数在分组内逐行计算不折叠行数ROW_NUMBER、RANK、SUM() OVER()标量函数是最常用的也是性能问题的高发区。聚合函数是报表的核心。流程控制帮你把“如果否则”的逻辑写进 SQL。窗口函数则是在 MySQL 8.0 之后才完善起来的处理“每组分排名”“取组内最大”这类需求时特别好用。1.3 为什么我建议按“组合”学而不是“背清单”单独一个函数的功能都很好理解但实际业务里很少只有一个函数出场。比如我要做一个用户昵称脱敏只显示前 1 个字符和最后一个字符中间用星号SELECT CONCAT(LEFT(nickname, 1), ****, RIGHT(nickname, 1)) FROM users;再比如要判断一个 JSON 数组里有没有某个元素MySQL 5.7 之后直接用 JSON_CONTAINS 就行SELECT * FROM products WHERE JSON_CONTAINS(tags, 手机);学函数最有效的方式是带着业务问题去找解法。需求驱动学习比按目录背要记得牢得多。2. 字符串与日期转换日常需求里绕不开的四件事2.1 STR_TO_DATE 与 CAST字符串转日期的正确姿势互联网行业的数据有相当一部分日期一开始是字符串。接口传过来“2024-01-15 12:30:00”还算规范的最怕的是遇到“2024/01/15”“20240115”这种乱七八糟的格式。存量数据里清理这种脏数据STR_TO_DATE 就是主力SELECT STR_TO_DATE(2024/01/15 12:30:00, %Y/%m/%d %H:%i:%s); SELECT STR_TO_DATE(20240115, %Y%m%d);格式化占位符要记清楚%Y 是四位年份%y 是两位年份%m 是两位月份%d 是两位日期%H 是 24 小时制的小时%i 是分钟%s 是秒。这个函数的好处是只要给定了格式不管原始字符串长什么样都能解析。如果你手里的字符串是标准的“YYYY-MM-DD HH:MM:SS”格式用 CAST 更快SELECT CAST(2024-01-15 12:30:00 AS DATETIME);CAST 的缺点是格式必须标准稍微偏一点就报错。所以我的经验是数据清洗用 STR_TO_DATE临时转换用 CAST。2.2 DATE_FORMAT 与日期计算报表统计和超时判断报表需求里最经典的写法就是按“天”“月”“年”做分组SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) FROM orders GROUP BY day;但这里有个常见坑如果 create_time 上有索引上面这种写法会让索引失效后面第 4 章详细说。更好的做法是用范围筛选代替函数包裹。日期计算方面DATE_ADD、DATE_SUB、DATEDIFF 三个函数几乎覆盖所有场景。比如判断订单是否超时未发货SELECT * FROM orders WHERE status PAID AND DATEDIFF(NOW(), pay_time) 3;这里假设超过 3 天未发货就是异常。DATEDIFF 的返回值是前一个日期减后一个日期的天数差要提醒自己别搞反方向。2.3 字符串截取与拼接订单号处理实战前面提到过的 SUBSTRING_INDEX在处理有规律的字符串时非常强大。比如订单号“EAST-2024-000123”想分别取出区域、年份、流水号SELECT SUBSTRING_INDEX(order_no, -, 1) AS region, -- 取第一个分隔符之前EAST SUBSTRING_INDEX(SUBSTRING_INDEX(order_no, -, 2), -, -1) AS year, -- 取中间段2024 SUBSTRING_INDEX(order_no, -, -1) AS serial_no -- 取最后一个分隔符之后000123 FROM orders;SUBSTRING_INDEX 的第一个参数是原始字符串第二个是分隔符第三个是计数。正数表示从左往右数负数表示从右往左数。这个函数在处理“带分隔符的字符串拆分”时比其他写法直观得多。拼接方面CONCAT 和 CONCAT_WS 我更喜欢用后者。比如要把省、市、区拼成完整地址中间用空格隔开CONCAT_WS 会自动处理 NULLSELECT CONCAT_WS( , province, city, district, detail_address) FROM users;如果用 CONCAT只要有一个字段是 NULL结果整个就是 NULL这是很多人踩过的坑。2.4 ORDER BY 里用函数排序需求中的陷阱有个搜索热词叫“mysql 排序”点进去大多是问数字和字符串混排的问题。比如编码字段是 VARCHAR存了“10”“9”“101”直接 ORDER BY 会把它们按字典序排成“10、101、9”因为字符串排序是一位一位比较的9 比 1 大所以 9 反而排在后面。解决办法是排序时做类型转换SELECT * FROM goods ORDER BY CAST(goods_no AS SIGNED) DESC;也可以用 ORDER BY goods_no 0靠隐式转换把字符串转成数字。后者写起来短但从可读性和维护性角度我还是推荐写 CAST语义明确别人接手时不用猜。另外要说的是 ORDER BY RAND()这个写法在数据量小的时候无所谓上了几十万行就会非常慢因为它要给每一行生成一个随机值再排序。需要随机抽取时更稳妥的办法是先生成一个随机 ID 范围再用主键去取。3. 判断、聚合与窗口函数让数据帮你做决策3.1 IF 与 CASE WHENSQL 里的条件分支很多人都用过 Excel 的 IFMySQL 的 IF 语法也很接近常用于简单的男女转换、状态映射SELECT name, IF(gender 1, 男, 女) FROM users; SELECT name, IF(status IN (PAID, SHIPPED), 已完成, 进行中) FROM orders;但稍微复杂一点的逻辑我更推荐 CASE WHEN。比如按订单金额分层SELECT order_no, CASE WHEN amount 10000 THEN 大额订单 WHEN amount 1000 THEN 普通订单 ELSE 小额订单 END AS order_level FROM orders;CASE WHEN 是标准 SQL 语法MySQL、PostgreSQL、SQL Server 都认跨数据库迁移时不用改。IF 是 MySQL 特有写法换了数据库就得重写。所以从长远角度我建议你优先学 CASE WHEN。另外判断时间、判断多条件时CASE WHEN 的扩展性也更好。3.2 聚合函数的“坑”与正确打开方式COUNT、SUM、AVG 是报表三兄弟但细节很容易出错。COUNT() 和 COUNT(1) 在 MySQL 里性能基本没差别都是统计行数。但 COUNT(字段) 会忽略该字段为 NULL 的行。如果你的业务逻辑是统计“有多少人填过手机号”用 COUNT(mobile)如果统计表有多少行用 COUNT()。SUM 和 AVG 也有类似问题。SUM(字段) 如果该字段全是 NULL结果也是 NULL而不是 0。很多业务指标里直接把 NULL 当成 0 处理在 SQL 里要用 COALESCE 包一层SELECT COALESCE(SUM(amount), 0) FROM orders WHERE status VOID;AVG 同样是忽略 NULL 的。假设有 5 个学生一个缺考成绩表里缺考是 NULLAVG(score) 计算的是 4 个有效成绩的平均值而不是按 5 个人算。如果业务要求缺考按 0 分计入就得写成 SUM(score) / COUNT(*)。3.3 GROUP_CONCAT把多行压成一行GROUP_CONCAT 可以把分组内的多个值拼成一个字符串。最典型的需求一个用户有多个标签需要把标签全部展示在一行里。SELECT user_id, GROUP_CONCAT(tag ORDER BY tag SEPARATOR ,) AS tags FROM user_tags GROUP BY user_id;注意事项有两个。第一GROUP_CONCAT 默认最大长度是 1024 字节如果你拼接的内容很长会被自动截断需要调整参数SET SESSION group_concat_max_len 100000;第二GROUP_CONCAT 本质是在内存里做字符串累加数据量大的时候可能挤占内存。用法没问题但不要在大表上无脑拼接几千行数据。3.4 窗口函数ROW_NUMBER 与 RANK 的应用场景MySQL 8.0 之前取“每个用户最新的一条订单”要写子查询、用临时变量非常别扭。8.0 之后就舒服多了SELECT user_id, order_no, create_time FROM ( SELECT user_id, order_no, create_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE t.rn 1;ROW_NUMBER() 就是给每个分组内的行排一个序号。PARTITION BY 指定分组字段ORDER BY 指定排序规则排完序后取 rn 1 就是每个用户的最新记录。RANK 和 DENSE_RANK 的区别在于并列排名。比如四个人分数是 90、90、80、70RANK 结果是 1、1、3、4DENSE_RANK 结果是 1、1、2、3。需要严格连续排名时用 DENSE_RANK。这个区别在面试里经常被问实际业务里算奖金排名、绩效等级也会碰到。窗口函数还能配合聚合函数用比如计算累计销售额SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount FROM sales;这就是“运行总和”不用自连接一条语句就能写出来。4. 函数的性能代价我在生产环境踩过的三个坑4.1 WHERE 里套函数索引是怎么失效的这是性能问题里最经典的一幕。有一个订单表create_time 上有索引但有人写SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-15;这条 SQL 的逻辑是想查某一天的数据看起来没毛病。但它对索引列做了函数运算MySQL 在执行时只能把索引里的每一行都取出来算一遍函数再比较等于把索引变成了摆设最终全表扫描。原因在于 B 树索引存储的是原始值不是函数处理后的值。你用函数把索引列改头换面了优化器没办法利用索引直接定位。正确写法是范围查询SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;这样既走了索引逻辑也更清晰。这个坑我强调过很多次但总有人再犯。写 SQL 时看到 WHERE 后面的索引列被函数包了一层就要条件反射地警觉。4.2 隐式类型转换INT 与 VARCHAR 的相爱相杀第二个坑是隐式类型转换。比如用户表里 mobile 字段是 VARCHAR但查询条件写SELECT * FROM users WHERE mobile 13812345678;这里的 13812345678 是整数MySQL 会把 mobile 字段的字符串值转成数字来比较。结果就是索引列上发生了隐式函数转换索引失效。反之如果索引列是 INT条件里传字符串 123MySQL 会把字符串转成数字同样影响索引。更隐蔽的情况是两个表关联时一边字段是 VARCHAR另一边是 INTJOIN 的时候也会触发隐式转换导致关联查询在驱动表扫一遍再逐行转换慢得离谱。排查方法很简单用 EXPLAIN 看执行计划如果 type 是 ALL 而你能确认字段本身有索引先怀疑类型不一致再对比一下两边的字段定义。解决方案就是保证查询条件的类型和字段类型一致该加引号加引号该改字段类型就改字段类型。4.3 ORDER BY RAND() 与其他性能黑洞除了 RAND() 排序还有一个容易被忽略的性能黑洞在大表上使用 GROUP BY 时如果分组字段不是索引的一部分MySQL 会创建一个临时表来做分组和排序。临时表会占用内存或磁盘数据量大时就是灾难。解决方案有两种手段一是让 GROUP BY 的字段组合尽量命中索引二是调整 sort_buffer_size 和 tmp_table_size 参数但要谨慎这不是银弹。还有 DISTINCT 和 GROUP BY 混用时如果 select 的字段和 group by 字段不一致也可能触发临时表。所以写 SQL 时养成好习惯SELECT 的字段要么是分组字段要么被聚合函数包裹。4.4 一次 SSL 连接错误排查给我的启发有段时间很多人搜“mysql ssl 连接错误”我也遇到过类似情况项目里的应用突然连不上 MySQL报 SSL connection error。这个错误跟函数毫无关系但排查链路值得借鉴。我的排查顺序是先看错误码具体是什么SSL 相关的错误码通常显示在日志里第二步用命令行客户端直接连一遍如果能连上说明服务端没问题问题在客户端驱动或连接配置第三步检查连接字符串或者配置文件里的 ssl-mode 参数调整成兼容的模式。最后发现是客户端驱动版本太老不支持服务端新的认证插件。换成新版本的驱动就好了。这个案例说明一个通用方法遇到数据库报错别急着改 SQL先把错误信息原样复制到搜索框然后从下到上分层排查。很多时候问题根本不在函数而在连接层、权限层或者驱动层。5. 存储过程与自定义函数要不要把逻辑搬进数据库5.1 存储过程和函数本质区别在哪很多人把存储过程和函数混为一谈其实区别很明确对比项存储过程函数返回值可有可无用 OUT 参数返回必须有返回值调用方式CALL proc_name(...)在 SQL 表达式中调用如 SELECT func_name(...)事务控制可以包含 START TRANSACTION、COMMIT严格限制不能随意控制事务结果集可以返回多个结果集只能返回单个值存储过程更像一段独立的程序可以有复杂的流程控制逻辑函数则像一个计算器输入几个值给你一个结果。因为函数要嵌在 SQL 里用MySQL 对函数的副作用控制得非常严格不能随意修改全局状态。5.2 什么时候值得用存储过程存储过程在互联网场景里用得不算多但在报表系统、数据迁移、定时任务里还是很常见。比如每天凌晨算销售汇总表跑一段存储过程把当日数据聚合后写入汇总表比在应用层写定时任务更直接。我当时有一个需求每个月把过期未支付的订单批量置为取消状态同时插入一批补偿记录。这个操作涉及多张表用一个存储过程包起来CREATE PROCEDURE cancel_expired_orders() BEGIN -- 把过期订单标记为取消 UPDATE orders SET status CANCELLED WHERE status WAIT_PAY AND expire_time NOW(); -- 把取消的订单插入操作日志 INSERT INTO order_log (order_no, action, create_time) SELECT order_no, AUTO_CANCEL, NOW() FROM orders WHERE status CANCELLED AND log_inserted 0; END调用一次两个操作都完成。这类批量操作天然适合存储过程因为它在数据库内执行不需要把几万行数据搬到应用层再搬回来。但我要提醒的是存储过程一旦多了调试和维护成本会翻倍。版本管理不方便单元测试也不好做。我的原则是批量数据处理用存储过程业务流程控制放应用层。5.3 自定义函数的写法与限制自定义函数在某些场景能带来极大便利。我写过一个解析订单号的函数CREATE FUNCTION extract_order_year(order_no VARCHAR(50)) RETURNS INT DETERMINISTIC RETURN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(order_no, -, 2), -, -1) AS SIGNED);调用时直接SELECT order_no, extract_order_year(order_no) FROM orders;DETERMINISTIC 关键字表明这个函数对同样的输入永远返回同样的结果MySQL 才能在查询优化时做一些缓存和判断。如果你的函数不是确定性的一定要去掉这个关键字否则会导致结果和预期不一致。函数还有一个限制是它不能用在动态 SQL 里。如果一个需求需要函数内部拼 SQL 字符串再执行对不起做不了。另外函数会消耗 CPU在 SELECT 列表里对几百万行调用自定义函数跑死人是常有的事。所以自定义函数只适合处理数据量可控的场景。5.4 我的取舍原则应用层还是数据库层这些年我见过的团队有的恨不得把所有逻辑都写成存储过程有的连一条复杂 SQL 都不敢写。这两个极端都不好。我的取舍原则是简单的字符串处理、日期转换、聚合统计直接在 SQL 里用内置函数干净利落跨多表的批量数据更新用存储过程封装减少网络往返涉及复杂业务规则、需要调用外部接口的逻辑留在应用层自定义函数能不用就不用内置函数解决不了的需求大概率用代码解决更清晰很多人担心把逻辑放数据库层会加重数据库负担。这个担心是合理的但要看清场景如果数据库已经是瓶颈你应该先优化慢查询和索引而不是把所有逻辑都抽走。合理的使用函数和存储过程可以让整个系统更高效前提是你清楚每个操作的成本。6. 新手最容易卡壳的几个点含热搜词避坑6.1 命令行报“不是内部或外部命令”其实是环境变量问题搜索“mysql”时有个高频联想词是“无法将某项识别为 cmdlet、函数、脚本文件或可运行程序的名称”。如果你在 Windows 命令行里输入 mysql 报这个错大概率不是 MySQL 出了问题而是 MySQL 的 bin 目录没有加进系统 PATH 环境变量。解决办法安装 MySQL 的时候记下安装路径比如C:\Program Files\MySQL\MySQL Server 8.0\bin然后把这个路径加到系统环境变量的 PATH 里重开命令行再试。如果急着用也可以直接用绝对路径去调用C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql -u root -p这个坑和函数完全无关但很多新手会误以为是自己不会用函数被卡了一整天。先排查环境再排查代码。6.2 连接工具报错先看错误码再查认证插件用 Navicat 或 DBeaver 连不上 MySQL 也是新手高频问题。最常见的有两种报错一种是“Cant connect to MySQL server (10061)”说明连接被拒绝。检查 MySQL 服务是否启动端口是否正确防火墙是否放行。另一种是“Authentication plugin caching_sha2_password cannot be loaded”这是因为 MySQL 8.0 默认使用 caching_sha2_password 认证方式而你用的连接工具版本太老不支持这种插件。解决办法升级客户端工具到最新版或者把用户的认证方式改回 mysql_native_passwordALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码;改完记得执行 FLUSH PRIVILEGES。这里多说一句千万别去搜什么破解版工具。连接工具的认证机制并不复杂升级到官方版本就能解决绝大多数问题。有些破解版为了绕过授权反而会把一些驱动组件改坏让你折腾半天都不知道问题在哪。6.3 调试函数的土办法临时表加 SELECT写自定义函数或者调一个复杂 SQL 时最快的调试方式不是反复整体执行而是把中间结果一步步用 SELECT 打出来。比如你在拼一个多层嵌套的字符串函数不知道中间结果长什么样可以建一张临时表把每一步的函数结果都跑一遍SELECT SUBSTRING_INDEX(order_no, -, 1) AS step1, SUBSTRING_INDEX(SUBSTRING_INDEX(order_no, -, 2), -, -1) AS step2, CONCAT(step1, _, step2) AS step3 FROM orders LIMIT 10;看到每一步的输出对不对再决定下一步怎么改。这个土办法虽然简单但比对着报错信息猜效率高得多。6.4 别被跨领域热搜词带偏你在搜索“MySQL 函数”时经常会被“箭头函数写法”“Python 定义函数”“0-1 损失函数”这些词吸引过去。这些确实是函数但和 MySQL 没有关系属于其他编程语言和机器学习领域的内容。搜索时建议带上具体的关键词比如“MySQL 字符串函数”“MySQL 日期函数”“MySQL 自定义函数”这样出来的结果更精准。另一个技巧是把你想实现的效果描述进搜索词里比如“MySQL 按周分组统计”而不是只搜“MySQL 函数”。我在实际使用中的体会是MySQL 函数的学习曲线并不陡峭难的是养成“先想索引、再想写法、最后才想函数”的习惯。很多慢查询不是函数写错了而是函数用错了位置。多看执行计划、多用 EXPLAIN、多拆解中间步骤比死记硬背函数清单有用得多。