写在前头我接触 MySQL 这些年发现很多人对函数的理解停留在会用几个的层面真要到了写复杂报表、做数据清洗、优化慢查询的时候脑子里能用的还是那三五个。这篇不是官方文档的复读而是把我实际项目中反复用到、踩过坑、优化过的一组 MySQL 函数用法整理出来从字符处理、聚合统计、行转列到自定义函数和存储过程的选择再到函数与索引性能的相爱相杀一次说透。1. 先确认边界MySQL 函数到底能做什么、不能做什么开始列函数之前先帮大家把概念框一下。MySQL 函数分两大类内置函数和自定义函数UDFUser Defined Function。内置函数是服务端自带的直接SELECT就能用比如NOW()、COUNT()、DATE_FORMAT()这些自定义函数则是用 SQL 语句或 C/C 扩展库写出来的逻辑常见的是用 SQL 语法写的存储型函数CREATE FUNCTION ... RETURNS ...。很多初学者会把函数和存储过程搞混我简单给个判断标准函数必须有返回值可以在 SELECT 里像字段一样直接调用存储过程是独立的执行单元用CALL来调用不强制返回结果集。如果你写了一段逻辑只是为了算一个值并返回那就是函数如果你要执行一系列操作插入、更新、循环处理并且可能返回多个结果集那更倾向于存储过程。在真正介绍具体函数之前我还想强调一个很重要但常被忽略的原则函数是无状态的。除了LAST_INSERT_ID()这类特殊函数之外同一个输入在同一个会话内调用同一个函数返回的结果是确定的至少对于非随机函数而言。这个特性决定了你可以放心地把函数用在视图、生成列GENERATED COLUMN和 WHERE 条件中而不用担心副作用。另外一点是权限问题。在 MySQL 8.0 中普通用户执行函数相关操作比如创建函数需要CREATE ROUTINE权限并且在开启binlog的情况下创建函数还要求你有SUPER权限或SET_USER_ID权限否则会报 You do not have the SUPER privilege and binary logging is enabled 这个经典错误。这一点在实际生产环境配权限的时候特别容易忽略我后面专门会讲。2. 高频字符与日期函数写报表和清洗数据时最常用的一组如果你经常写统计报表、做 ETL 清洗那么你 80% 的时间在和三类函数打交道字符串函数、日期函数、聚合函数。我先把字符串和日期里最常用且容易用错的挑出来讲。2.1 字符函数CONCAT、SUBSTRING_INDEX、REPLACE 的实用组合CONCAT()应该是最基础的拼接函数了但很多人不知道它有一个坑如果拼接的任何一个字段为NULL整个结果就是NULL。在实际业务中用户表的省份和城市字段经常会出现 NULL直接CONCAT(province, city)往往得到 NULL而不是你期望的省份城市。解决办法有两个使用CONCAT_WS()第一个参数是分隔符它会自动跳过 NULL 值。使用IFNULL()先把 NULL 转成空字符串。我建议在报表开发中统一用CONCAT_WS()因为它更安全。比如SELECT CONCAT_WS(-, province, city, district) AS full_address FROM user_profile;只要province、city、district中有一个为空CONCAT_WS会忽略它并用分隔符连接其他非空值结果不会变成 NULL。SUBSTRING_INDEX()是一个容易被人低估的函数。它的作用是按分隔符截取第 N 个部分非常适合处理路径、标签列表、JSON 简化提取等场景。举个例子订单表里存了一个完整路径/api/v1/order/detail你想取出倒数第二段也就是v1可以这样写SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(/api/v1/order/detail, /, -2), /, 1 ) AS version_part;这个嵌套技巧非常实用先从右边取两段得到v1/order/detail再从左边取第一段得到v1。掌握了这个思路很多字符串拆分的需求都可以用纯 SQL 解决不必把数据拉到应用层再用 Java、Python 处理性能差异很大。REPLACE()的用途也很直白但我想提醒的是它在处理换行符和制表符时非常有用。比如从外部系统同步过来的地址字段经常带着\r\n你可以一次嵌套替换把脏字符清掉SELECT REPLACE(REPLACE(REPLACE(raw_address, \r, ), \n, ), \t, ) AS clean_address FROM external_sync_log;字符函数里还有一个常用但容易翻车的是LEFT()、RIGHT()和SUBSTRING()它们对多字节字符如中文、emoji的处理依赖字符集。简单说如果表字符集是utf8mb4LEFT(name, 2)截取的是 2 个字符而不是 2 个字节基本符合直觉但如果你的连接字符集设置不对或者表是老的utf8mb3在某些特殊字符上会出现截断成半个字符的乱码。所以在做字符串截取之前务必确认character_set_connection和表字符集一致。可以用下面这条命令快速检查SHOW VARIABLES LIKE character_set_connection; SHOW TABLE STATUS LIKE your_table;2.2 日期与时间函数DATE_FORMAT、TIMESTAMPDIFF 和时区陷阱日期函数是报表的命脉。DATE_FORMAT()用来自定义日期输出格式这没什么好说的关键是它的格式符要记牢%Y四位年份、%y两位年份、%m月份01-12、%c月份1-12、%d日01-31、%e日1-31、%H24小时制、%i分钟、%s秒。最容易写错的是分钟很多人下意识写成%M但%M英文月份名January而%m才是数字月份。我见过不止一次因为%M和%m混淆导致报表月份变成英文缩写的。DATEDIFF()和TIMESTAMPDIFF()的区别也值得讲一下。DATEDIFF(date1, date2)只看日期部分忽略时间返回的是 date1 减去 date2 的天数TIMESTAMPDIFF(unit, date1, date2)则是用指定单位SECOND、MINUTE、HOUR、DAY、MONTH、YEAR返回差值更精细。实际使用中计算年龄一般用TIMESTAMPDIFF(YEAR, birth_date, CURDATE())而不是简单地用年份相减因为后者没考虑生日是否已过SELECT name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;日期函数有一个比语法更让人头疼的问题时区。MySQL 的NOW()返回的是会话时区下的当前时间而SYSDATE()也是当前时间但两者有一个微妙差异NOW()在语句开始执行时取一次时间之后的同一条语句里所有NOW()都一样SYSDATE()则是每次执行到它时才取当前时间在长查询中两个函数返回的秒数可能不同。更关键的是如果服务器的time_zone参数设置不对NOW()返回的时间会跟业务实际时区差几个小时。建议在 JDBC 连接串里显式指定serverTimezoneAsia/Shanghai并且把 MySQL 全局时区设置为08:00SET GLOBAL time_zone 08:00; SET time_zone 08:00;顺便多说一句存时间字段强烈推荐DATETIME而不是TIMESTAMP。TIMESTAMP范围只有 1970 年到 2038 年而且会自动做时区转换容易埋雷DATETIME范围大不随时区变动适合业务数据存储。这虽然不是函数本身的内容但直接影响你用的日期函数拿到的值对不对。2.3 数字函数ROUND、CEIL、FLOOR 的精度问题数字函数相对简单但精度问题容易让报表出现差一分钱。ROUND(x, d)是四舍五入到 d 位小数TRUNCATE(x, d)是直接截断到 d 位CEIL(x)向上取整FLOOR(x)向下取整ABS(x)绝对值MOD(x, y)取余。这里我特别想提醒的是ROUND()在 MySQL 中的行为它在半数情况下是四舍五入但遇到 0.5 这种边界值时行为可能与你在 Excel 里看到的略有不同。比如ROUND(2.5, 0)在 MySQL 中返回 3ROUND(3.5, 0)返回 4这个基本符合直觉但ROUND(2.675, 2)会返回 2.67 而不是 2.68原因是 2.675 在二进制浮点里实际存储的值略小于 2.675。如果对金额精度要求极高建议使用DECIMAL类型字段或者先乘以 100 用整数计算最后再除以 100避免浮点误差累积。财务系统里我一般会这样写SELECT ROUND(amount * 100) / 100 AS exact_amount FROM payment_records;3. 排序、分组与行转列函数在复杂查询中的实战组合这一章是很多后台开发每天都要面对的排序规则、分组聚合、把行转成列。函数在这些场景里扮演的角色非常关键不少高级写法的核心其实就是一个函数选得好不好。3.1 ORDER BY 中的函数FIELD、CAST 和中文排序排序需求看起来简单但订单状态、优先级这类字段往往是字符串直接按字典序排会出问题。比如状态值有pending、paid、shipped、completed你希望按业务顺序而不是字母顺序排序这时可以用FIELD()函数自定义排序规则SELECT order_id, status FROM orders WHERE create_time 2024-01-01 ORDER BY FIELD(status, pending, paid, shipped, completed), create_time DESC;FIELD()的第一个参数是要匹配的字段后面是自定义顺序的取值列表返回值是取值在列表中的位置未匹配到的值返回 0。这个函数的性能在数据量小的时候没问题但表很大时会导致索引失效因为排序依据不是字段本身而是函数计算后的结果。如果状态字段需要频繁按业务顺序排序更优的做法是给表增加一个sort_order整数列并在该列上建索引。中文排序是另一个高频问题。默认情况下 MySQL 对中文按字符集编码排序比如utf8mb4_unicode_ci下排序结果是按拼音/编码混合的不一定符合业务期望。如果想按拼音排序可以使用CONVERT(column USING gbk)因为 GBK 编码对常用汉字按拼音排列SELECT user_name FROM users ORDER BY CONVERT(user_name USING gbk);需要注意这种写法同样无法走索引所以只适合数据量可控的场景。如果用户量在百万级以上且必须支持中文拼音排序建议在应用层用拼音字段冗余存储或者使用专门的全文检索方案。3.2 GROUP BY 配合聚合函数COUNT、SUM、AVG 的细节聚合函数是分组统计的基石但其中有几个细节很容易被忽视。COUNT(*)和COUNT(column)的区别是经典考点COUNT(*)统计所有行数包括NULL值COUNT(column)只统计该列非NULL的行数。所以如果你想统计有多少用户填了手机号应该用COUNT(mobile)想统计总共有多少条记录用COUNT(*)或COUNT(1)。SUM()也需要注意 NULL 问题。SUM(column)会忽略 NULL 值但如果整列都为 NULL结果返回 NULL 而不是 0。很多报表里用SUM(amount)得到的空值显示为空白应用层拿到 NULL 后容易报空指针或显示异常稳妥的做法是包一层IFNULLSELECT IFNULL(SUM(amount), 0) AS total_amount FROM orders WHERE status paid;AVG()同理NULL 值不参与计算。这里有一个隐藏的陷阱如果你把 NULL 当成 0 存进表里AVG(column)会把 0 计入平均结果会偏低。所以在设计表时NULL和0要区分开它们对聚合函数的语义影响完全不同。3.3 行转列的三种方案CASE WHEN、GROUP_CONCAT 和 JSON_ARRAYAGG行转列这个词在热搜里排得很靠前确实是报表开发里绕不开的需求。比如你有一张学生成绩表字段是student_id、subject、score要输出成一行一个学生、每门课一列这就是典型行转列。我讲三种办法按适用场景选。第一种是用CASE WHEN配合GROUP BY这也是最经典的方式SELECT student_id, MAX(CASE WHEN subject 语文 THEN score END) AS chinese, MAX(CASE WHEN subject 数学 THEN score END) AS math, MAX(CASE WHEN subject 英语 THEN score END) AS english FROM scores GROUP BY student_id;这里的MAX其实只是group by 后取非空值的技巧也可以换成MIN效果一样因为每个科目每人只有一条记录时MAX和MIN取到的都是那条记录本身。这种写法适合科目列是有限的、固定的场景写起来直观执行效率也不错。第二种是使用GROUP_CONCAT()把多行拼成一行适合动态列场景。比如要查出每个学生的选课列表SELECT student_id, GROUP_CONCAT(subject ORDER BY subject SEPARATOR ,) AS subjects FROM scores GROUP BY student_id;GROUP_CONCAT()有一个默认长度限制group_concat_max_len默认是 1024 字节超过的部分会被静默截断导致结果不完整。处理长文本聚合时要先调大这个变量SET SESSION group_concat_max_len 102400;第三种是 MySQL 5.7 提供的JSON_ARRAYAGG()和JSON_OBJECTAGG()适合需要 JSON 输出的场景。比如把课程和分数组成一个 JSON 数组SELECT student_id, JSON_ARRAYAGG(JSON_OBJECT(subject, score)) AS score_json FROM scores GROUP BY student_id;这种方案灵活性高列再多也不用改 SQL配合前端渲染很方便但 JSON 函数对索引不友好不适合在 WHERE 条件里频繁取值。4. 从函数到存储过程什么时候该写自定义函数内置函数虽然丰富但业务逻辑复杂时仍然不够用。热搜词里同时出现了mysql存储过程和mysql函数很多人搞不清楚这两者的选择边界。我给出一个实际可用的判断标准如果这段逻辑只是输入几个参数、算出一个返回值不涉及多条写操作优先考虑自定义函数如果逻辑里包含循环、游标、多步事务处理或者返回的是多行结果集考虑用存储过程。4.1 创建一个自定义函数语法与权限细节创建一个返回值为标量的函数基本语法长这样DELIMITER $$ CREATE FUNCTION fn_user_age(birth_date DATE) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN DECLARE age INT; SET age TIMESTAMPDIFF(YEAR, birth_date, CURDATE()); RETURN age; END$$ DELIMITER ;这里有几个容易忽略的点DELIMITER必须临时改成$$否则 MySQL 遇到分号就认为语句结束了。DETERMINISTIC表示函数对同样的输入返回同样的结果如果函数里用了NOW()、RAND()这类非确定性函数就不能声明为DETERMINISTIC。创建函数时如果开了binlogMySQL 要求函数必须是DETERMINISTIC、NO SQL或READS SQL DATA三者之一否则报错。如果你确定函数里不涉及数据修改可以直接声明READS SQL DATA。我之前在线上环境遇到过这种情况创建函数死活报错查日志发现就是binlog格式和权限的问题。有三种解法第一种是把log_bin_trust_function_creators设置为 1仅限内部可控环境的临时方案第二种是给申请账号加上SUPER或SET_USER_ID权限第三种最稳妥就是规范DETERMINISTIC声明从源头满足要求。4.2 函数在生成列与视图中的妙用函数的一个高级用法是配合生成列GENERATED COLUMN。比如订单表里有unit_price和quantity不想每次查询都算一遍unit_price * quantity可以在建表时增加一个生成列CREATE TABLE order_items ( id INT PRIMARY KEY, unit_price DECIMAL(10,2), quantity INT, total_price DECIMAL(10,2) GENERATED ALWAYS AS (unit_price * quantity) STORED );生成列的值在插入时由 MySQL 自动计算查询时直接取存储好的结果还能在上面建索引性能和可读性都有提升。但要注意生成列的计算表达式里不能使用非确定性的函数如NOW()、RAND()函数的选择也因此受限。视图里也经常用到自定义函数。比如统一处理手机号脱敏可以创建一个函数fn_mask_mobile(mobile VARCHAR(20))在视图定义里调用每次查询结果都自动脱敏安全性更好也避免了每个查询语句里都写一长串CONCAT。4.3 什么时候别用自定义函数自定义函数不是万能的我实际使用中总结出三个别用场景第一高频查询的 WHERE 条件里别用自定义函数。原因很好理解函数导致索引失效。比如WHERE fn_calc_status(create_time) overdueMySQL 无法对这个经过函数计算的结果列使用索引必须全表扫描。如果数据量只有几千行影响不大如果数据量到了千万级性能会断崖式下降。第二大数据量循环里别用自定义函数。函数如果被放在一个几十万行的 UPDATE 语句里逐行调用执行效率会非常差。MySQL 的函数调用有额外的上下文切换开销远不如直接在应用层批量算好再更新。第三跨库分布式场景别用自定义函数。在分库分表中间件如 ShardingSphere环境下自定义函数的支持程度参差不齐很多中间件不解析自定义函数会直接把 SQL 下发到所有分片结果可能错乱。这种场景应尽量在应用层用 Java 或 Python 计算。5. 函数与索引的相爱相杀EXPLAIN 里看函数如何毁掉性能热搜词里同时出现了 mysql explain详解 和 mysql排序这两个词背后其实都藏着同一个问题函数导致索引失效查询性能崩塌。我自己调过的慢查询里有相当一部分是WHERE 子句里包了函数造成的。这一章把原理讲透再给一个可复现的排查方法。5.1 为什么 WHERE 里用了函数就走不了索引索引是基于字段原始值构建的 B 树结构。当你写WHERE DATE(create_time) 2024-01-01时MySQL 必须对每一行的create_time都执行一次DATE()函数才能拿到结果跟后面的常量比较。这个过程没法利用索引的有序性只能全表扫描。道理就跟查字典一样如果你知道某个词的拼音可以快速翻到对应页码但如果要求你把所有词里第二个字是某个字的词找出来你就只能一页一页翻。解决办法也很经典把函数从字段上移到常量上。上面的查询可以改写成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这样create_time没有套函数MySQL 可以直接走索引范围扫描性能通常提升一个数量级以上。我在实际优化中见过一条原本跑 8 秒的 SQL改成这个写法后降到 0.05 秒就是这一行函数位置的区别。5.2 EXPLAIN 里如何快速识别函数索引失效用EXPLAIN分析查询时重点看type和key两列。如果type显示ALL全表扫描而key为NULL大概率是索引没走如果type是range或ref说明索引正常工作。更直接的信号是Extra列里出现Using where但不出现Using index配合key列为空基本可以断定条件里有函数包裹字段。我举个实际案例。某订单查询EXPLAIN SELECT * FROM orders WHERE DATE(create_time) 2024-06-01;结果中type为ALLrows为 124000说明全表扫了 12 万行。改写后EXPLAIN SELECT * FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;结果中type为rangerows只有 356Extra显示Using index condition这就是走了索引的效果。这组对比特别适合拿来做团队代码审查时的教学案例一眼就看出问题本质。5.3 索引列上用函数的例外情况有例外吗有。在 MySQL 8.0 中你可以直接创建函数索引Functional Index也叫表达式索引。比如你经常按DATE(create_time)分组统计可以在(DATE(create_time))上建索引ALTER TABLE orders ADD INDEX idx_create_date ((DATE(create_time)));注意这里用的是双层括号表示这是一个表达式索引。建好之后WHERE DATE(create_time) 2024-01-01就可以走这个索引了。不过建表达式索引会增加写入成本也不一定能命中所有函数形式所以我的建议是能改 SQL 就改 SQL函数索引只作为最后的兜底方案。5.4 ORDER BY 里的函数与 filesortORDER BY里使用函数同样会导致无法利用索引排序触发filesort。EXPLAIN的Extra列出现Using filesort时就说明排序没有走索引需要关注排序的数据量。数据量小无所谓几十万行以上就会有明显的排序开销。比如你要按某个时间字段的月份排序ORDER BY MONTH(create_time)就必然 filesort如果业务上确实需要按月份排建议在表中冗余一个month_col字段并在插入时算好然后对这个字段建索引。另外filesort并不代表一定会慢但如果filesort的数据量超过sort_buffer_sizeMySQL 会使用磁盘临时文件性能会断崖式下降。可以用SHOW STATUS LIKE Sort_merge_passes;查看排序合并次数如果这个值很大说明排序缓冲不够需要适当调大sort_buffer_size或优化排序逻辑。6. 容易被热搜带偏的几个 MySQL 函数问题最后聊几个热搜词里反复出现、但网上答案常常互相矛盾的点。我把结论和适用场景讲清楚省得大家反复踩坑。6.1 无法将 xxx 项识别为 cmdlet、函数到底是 MySQL 的问题吗热搜里有大量 opencode、npm、claude、git、pnpm 的报错信息表面上看跟 MySQL 无关。但这类报错的本质是Windows 环境变量没有正确配置导致你在命令行里输入命令时系统找不到对应的可执行文件。MySQL 安装后如果出现mysql 不是内部或外部命令的报错原理完全一样。解决办法很固定找到 MySQL 安装目录下的bin文件夹比如C:\Program Files\MySQL\MySQL Server 8.0\bin把它加到系统环境变量Path中重新打开命令行即可。我建议用用户变量而非系统变量避免权限问题。装完 MySQL 后第一件事就是验证mysql --version能正常输出版本号说明 PATH 没问题。6.2 RAND() 与 ORDER BY 的性能陷阱热搜里 select函数、mysql排序 等词背后常有一个需求随机抽几条记录。很多人第一反应是ORDER BY RAND() LIMIT 5这样写在小表上没问题大表上就是灾难因为 MySQL 要为每一行生成随机数然后排序相当于全表扫描加全量排序。数据量 50 万以上时这条 SQL 可能跑好几秒。更高效的做法是采用主键范围随机SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) ORDER BY id LIMIT 5;这种写法只做一次主键范围扫描速度要快得多而且满足大多数业务场景。如果用户表的 ID 不是连续整数可以先查一个随机偏移量再取数据但思路是一样的——尽量避免对每一行都调用函数。6.3 字符串函数在 JOIN 条件里造成的隐式转换字符集不一致导致的 JOIN 隐式转换是 MySQL 函数相关的一个深坑但经常被忽略。当两个表的关联字段一个是utf8mb4另一个是latin1时MySQL 可能会在关联时对其中一个字段做隐式字符集转换导致无法使用索引。检查手段是在 EXPLAIN 里看Extra列有没有Using where配合key为空的情况根本解法是统一两张表的字段字符集ALTER TABLE t2 MODIFY col VARCHAR(50) CHARACTER SET utf8mb4;再配合CONVERT()函数也可以显式地把字符集转成一致再关联。这里要格外注意参与 JOIN 的两个条件字段字符集必须完全一致否则很容易出现 数据量不大但查询慢得离谱 的问题。我排查过最诡异的一个慢查询最后发现就是一张历史遗留表的字段是utf8mb3和主流utf8mb4表关联时发生了隐式转换索引直接失效。写在最后MySQL 函数这块说到底是规则和代价的平衡。规则指的是语法和函数语义代价指的是执行计划、索引利用、临时排序这些性能指标。很多开发人员能把各种函数背得滚瓜烂熟但写出来的 SQL 在大数据量下仍然慢得一塌糊涂就是因为只理解了规则没理解代价。我自己的习惯是每一条要上生产的 SQL先写出来再 EXPLAIN 一遍最后在模拟数据量级的测试环境里跑一次。函数用得好不好不是在语法层面评判的而是在key列和rows列里体现的。如果哪天你用DATE_FORMAT算出一个很漂亮的报表却发现整条查询扫了几百万行不妨停下来想一想能不能把函数从字段上挪走能不能换成范围条件这比多背十个函数都管用。最后再分享一个小技巧MySQL 8.0 的performance_schema里可以直接查到语句级别的执行统计配合EXPLAIN ANALYZE8.0.18 新增能精确看到每一步的耗时和行数。排查函数造成的性能问题时先用EXPLAIN ANALYZE定位最耗时的步骤再针对性改写函数写法比靠感觉优化高效得多。
写在前头我接触 MySQL 这些年发现很多人对函数的理解停留在会用几个的层面真要到了写复杂报表、做数据清洗、优化慢查询的时候脑子里能用的还是那三五个。这篇不是官方文档的复读而是把我实际项目中反复用到、踩过坑、优化过的一组 MySQL 函数用法整理出来从字符处理、聚合统计、行转列到自定义函数和存储过程的选择再到函数与索引性能的相爱相杀一次说透。1. 先确认边界MySQL 函数到底能做什么、不能做什么开始列函数之前先帮大家把概念框一下。MySQL 函数分两大类内置函数和自定义函数UDFUser Defined Function。内置函数是服务端自带的直接SELECT就能用比如NOW()、COUNT()、DATE_FORMAT()这些自定义函数则是用 SQL 语句或 C/C 扩展库写出来的逻辑常见的是用 SQL 语法写的存储型函数CREATE FUNCTION ... RETURNS ...。很多初学者会把函数和存储过程搞混我简单给个判断标准函数必须有返回值可以在 SELECT 里像字段一样直接调用存储过程是独立的执行单元用CALL来调用不强制返回结果集。如果你写了一段逻辑只是为了算一个值并返回那就是函数如果你要执行一系列操作插入、更新、循环处理并且可能返回多个结果集那更倾向于存储过程。在真正介绍具体函数之前我还想强调一个很重要但常被忽略的原则函数是无状态的。除了LAST_INSERT_ID()这类特殊函数之外同一个输入在同一个会话内调用同一个函数返回的结果是确定的至少对于非随机函数而言。这个特性决定了你可以放心地把函数用在视图、生成列GENERATED COLUMN和 WHERE 条件中而不用担心副作用。另外一点是权限问题。在 MySQL 8.0 中普通用户执行函数相关操作比如创建函数需要CREATE ROUTINE权限并且在开启binlog的情况下创建函数还要求你有SUPER权限或SET_USER_ID权限否则会报 You do not have the SUPER privilege and binary logging is enabled 这个经典错误。这一点在实际生产环境配权限的时候特别容易忽略我后面专门会讲。2. 高频字符与日期函数写报表和清洗数据时最常用的一组如果你经常写统计报表、做 ETL 清洗那么你 80% 的时间在和三类函数打交道字符串函数、日期函数、聚合函数。我先把字符串和日期里最常用且容易用错的挑出来讲。2.1 字符函数CONCAT、SUBSTRING_INDEX、REPLACE 的实用组合CONCAT()应该是最基础的拼接函数了但很多人不知道它有一个坑如果拼接的任何一个字段为NULL整个结果就是NULL。在实际业务中用户表的省份和城市字段经常会出现 NULL直接CONCAT(province, city)往往得到 NULL而不是你期望的省份城市。解决办法有两个使用CONCAT_WS()第一个参数是分隔符它会自动跳过 NULL 值。使用IFNULL()先把 NULL 转成空字符串。我建议在报表开发中统一用CONCAT_WS()因为它更安全。比如SELECT CONCAT_WS(-, province, city, district) AS full_address FROM user_profile;只要province、city、district中有一个为空CONCAT_WS会忽略它并用分隔符连接其他非空值结果不会变成 NULL。SUBSTRING_INDEX()是一个容易被人低估的函数。它的作用是按分隔符截取第 N 个部分非常适合处理路径、标签列表、JSON 简化提取等场景。举个例子订单表里存了一个完整路径/api/v1/order/detail你想取出倒数第二段也就是v1可以这样写SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(/api/v1/order/detail, /, -2), /, 1 ) AS version_part;这个嵌套技巧非常实用先从右边取两段得到v1/order/detail再从左边取第一段得到v1。掌握了这个思路很多字符串拆分的需求都可以用纯 SQL 解决不必把数据拉到应用层再用 Java、Python 处理性能差异很大。REPLACE()的用途也很直白但我想提醒的是它在处理换行符和制表符时非常有用。比如从外部系统同步过来的地址字段经常带着\r\n你可以一次嵌套替换把脏字符清掉SELECT REPLACE(REPLACE(REPLACE(raw_address, \r, ), \n, ), \t, ) AS clean_address FROM external_sync_log;字符函数里还有一个常用但容易翻车的是LEFT()、RIGHT()和SUBSTRING()它们对多字节字符如中文、emoji的处理依赖字符集。简单说如果表字符集是utf8mb4LEFT(name, 2)截取的是 2 个字符而不是 2 个字节基本符合直觉但如果你的连接字符集设置不对或者表是老的utf8mb3在某些特殊字符上会出现截断成半个字符的乱码。所以在做字符串截取之前务必确认character_set_connection和表字符集一致。可以用下面这条命令快速检查SHOW VARIABLES LIKE character_set_connection; SHOW TABLE STATUS LIKE your_table;2.2 日期与时间函数DATE_FORMAT、TIMESTAMPDIFF 和时区陷阱日期函数是报表的命脉。DATE_FORMAT()用来自定义日期输出格式这没什么好说的关键是它的格式符要记牢%Y四位年份、%y两位年份、%m月份01-12、%c月份1-12、%d日01-31、%e日1-31、%H24小时制、%i分钟、%s秒。最容易写错的是分钟很多人下意识写成%M但%M英文月份名January而%m才是数字月份。我见过不止一次因为%M和%m混淆导致报表月份变成英文缩写的。DATEDIFF()和TIMESTAMPDIFF()的区别也值得讲一下。DATEDIFF(date1, date2)只看日期部分忽略时间返回的是 date1 减去 date2 的天数TIMESTAMPDIFF(unit, date1, date2)则是用指定单位SECOND、MINUTE、HOUR、DAY、MONTH、YEAR返回差值更精细。实际使用中计算年龄一般用TIMESTAMPDIFF(YEAR, birth_date, CURDATE())而不是简单地用年份相减因为后者没考虑生日是否已过SELECT name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;日期函数有一个比语法更让人头疼的问题时区。MySQL 的NOW()返回的是会话时区下的当前时间而SYSDATE()也是当前时间但两者有一个微妙差异NOW()在语句开始执行时取一次时间之后的同一条语句里所有NOW()都一样SYSDATE()则是每次执行到它时才取当前时间在长查询中两个函数返回的秒数可能不同。更关键的是如果服务器的time_zone参数设置不对NOW()返回的时间会跟业务实际时区差几个小时。建议在 JDBC 连接串里显式指定serverTimezoneAsia/Shanghai并且把 MySQL 全局时区设置为08:00SET GLOBAL time_zone 08:00; SET time_zone 08:00;顺便多说一句存时间字段强烈推荐DATETIME而不是TIMESTAMP。TIMESTAMP范围只有 1970 年到 2038 年而且会自动做时区转换容易埋雷DATETIME范围大不随时区变动适合业务数据存储。这虽然不是函数本身的内容但直接影响你用的日期函数拿到的值对不对。2.3 数字函数ROUND、CEIL、FLOOR 的精度问题数字函数相对简单但精度问题容易让报表出现差一分钱。ROUND(x, d)是四舍五入到 d 位小数TRUNCATE(x, d)是直接截断到 d 位CEIL(x)向上取整FLOOR(x)向下取整ABS(x)绝对值MOD(x, y)取余。这里我特别想提醒的是ROUND()在 MySQL 中的行为它在半数情况下是四舍五入但遇到 0.5 这种边界值时行为可能与你在 Excel 里看到的略有不同。比如ROUND(2.5, 0)在 MySQL 中返回 3ROUND(3.5, 0)返回 4这个基本符合直觉但ROUND(2.675, 2)会返回 2.67 而不是 2.68原因是 2.675 在二进制浮点里实际存储的值略小于 2.675。如果对金额精度要求极高建议使用DECIMAL类型字段或者先乘以 100 用整数计算最后再除以 100避免浮点误差累积。财务系统里我一般会这样写SELECT ROUND(amount * 100) / 100 AS exact_amount FROM payment_records;3. 排序、分组与行转列函数在复杂查询中的实战组合这一章是很多后台开发每天都要面对的排序规则、分组聚合、把行转成列。函数在这些场景里扮演的角色非常关键不少高级写法的核心其实就是一个函数选得好不好。3.1 ORDER BY 中的函数FIELD、CAST 和中文排序排序需求看起来简单但订单状态、优先级这类字段往往是字符串直接按字典序排会出问题。比如状态值有pending、paid、shipped、completed你希望按业务顺序而不是字母顺序排序这时可以用FIELD()函数自定义排序规则SELECT order_id, status FROM orders WHERE create_time 2024-01-01 ORDER BY FIELD(status, pending, paid, shipped, completed), create_time DESC;FIELD()的第一个参数是要匹配的字段后面是自定义顺序的取值列表返回值是取值在列表中的位置未匹配到的值返回 0。这个函数的性能在数据量小的时候没问题但表很大时会导致索引失效因为排序依据不是字段本身而是函数计算后的结果。如果状态字段需要频繁按业务顺序排序更优的做法是给表增加一个sort_order整数列并在该列上建索引。中文排序是另一个高频问题。默认情况下 MySQL 对中文按字符集编码排序比如utf8mb4_unicode_ci下排序结果是按拼音/编码混合的不一定符合业务期望。如果想按拼音排序可以使用CONVERT(column USING gbk)因为 GBK 编码对常用汉字按拼音排列SELECT user_name FROM users ORDER BY CONVERT(user_name USING gbk);需要注意这种写法同样无法走索引所以只适合数据量可控的场景。如果用户量在百万级以上且必须支持中文拼音排序建议在应用层用拼音字段冗余存储或者使用专门的全文检索方案。3.2 GROUP BY 配合聚合函数COUNT、SUM、AVG 的细节聚合函数是分组统计的基石但其中有几个细节很容易被忽视。COUNT(*)和COUNT(column)的区别是经典考点COUNT(*)统计所有行数包括NULL值COUNT(column)只统计该列非NULL的行数。所以如果你想统计有多少用户填了手机号应该用COUNT(mobile)想统计总共有多少条记录用COUNT(*)或COUNT(1)。SUM()也需要注意 NULL 问题。SUM(column)会忽略 NULL 值但如果整列都为 NULL结果返回 NULL 而不是 0。很多报表里用SUM(amount)得到的空值显示为空白应用层拿到 NULL 后容易报空指针或显示异常稳妥的做法是包一层IFNULLSELECT IFNULL(SUM(amount), 0) AS total_amount FROM orders WHERE status paid;AVG()同理NULL 值不参与计算。这里有一个隐藏的陷阱如果你把 NULL 当成 0 存进表里AVG(column)会把 0 计入平均结果会偏低。所以在设计表时NULL和0要区分开它们对聚合函数的语义影响完全不同。3.3 行转列的三种方案CASE WHEN、GROUP_CONCAT 和 JSON_ARRAYAGG行转列这个词在热搜里排得很靠前确实是报表开发里绕不开的需求。比如你有一张学生成绩表字段是student_id、subject、score要输出成一行一个学生、每门课一列这就是典型行转列。我讲三种办法按适用场景选。第一种是用CASE WHEN配合GROUP BY这也是最经典的方式SELECT student_id, MAX(CASE WHEN subject 语文 THEN score END) AS chinese, MAX(CASE WHEN subject 数学 THEN score END) AS math, MAX(CASE WHEN subject 英语 THEN score END) AS english FROM scores GROUP BY student_id;这里的MAX其实只是group by 后取非空值的技巧也可以换成MIN效果一样因为每个科目每人只有一条记录时MAX和MIN取到的都是那条记录本身。这种写法适合科目列是有限的、固定的场景写起来直观执行效率也不错。第二种是使用GROUP_CONCAT()把多行拼成一行适合动态列场景。比如要查出每个学生的选课列表SELECT student_id, GROUP_CONCAT(subject ORDER BY subject SEPARATOR ,) AS subjects FROM scores GROUP BY student_id;GROUP_CONCAT()有一个默认长度限制group_concat_max_len默认是 1024 字节超过的部分会被静默截断导致结果不完整。处理长文本聚合时要先调大这个变量SET SESSION group_concat_max_len 102400;第三种是 MySQL 5.7 提供的JSON_ARRAYAGG()和JSON_OBJECTAGG()适合需要 JSON 输出的场景。比如把课程和分数组成一个 JSON 数组SELECT student_id, JSON_ARRAYAGG(JSON_OBJECT(subject, score)) AS score_json FROM scores GROUP BY student_id;这种方案灵活性高列再多也不用改 SQL配合前端渲染很方便但 JSON 函数对索引不友好不适合在 WHERE 条件里频繁取值。4. 从函数到存储过程什么时候该写自定义函数内置函数虽然丰富但业务逻辑复杂时仍然不够用。热搜词里同时出现了mysql存储过程和mysql函数很多人搞不清楚这两者的选择边界。我给出一个实际可用的判断标准如果这段逻辑只是输入几个参数、算出一个返回值不涉及多条写操作优先考虑自定义函数如果逻辑里包含循环、游标、多步事务处理或者返回的是多行结果集考虑用存储过程。4.1 创建一个自定义函数语法与权限细节创建一个返回值为标量的函数基本语法长这样DELIMITER $$ CREATE FUNCTION fn_user_age(birth_date DATE) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN DECLARE age INT; SET age TIMESTAMPDIFF(YEAR, birth_date, CURDATE()); RETURN age; END$$ DELIMITER ;这里有几个容易忽略的点DELIMITER必须临时改成$$否则 MySQL 遇到分号就认为语句结束了。DETERMINISTIC表示函数对同样的输入返回同样的结果如果函数里用了NOW()、RAND()这类非确定性函数就不能声明为DETERMINISTIC。创建函数时如果开了binlogMySQL 要求函数必须是DETERMINISTIC、NO SQL或READS SQL DATA三者之一否则报错。如果你确定函数里不涉及数据修改可以直接声明READS SQL DATA。我之前在线上环境遇到过这种情况创建函数死活报错查日志发现就是binlog格式和权限的问题。有三种解法第一种是把log_bin_trust_function_creators设置为 1仅限内部可控环境的临时方案第二种是给申请账号加上SUPER或SET_USER_ID权限第三种最稳妥就是规范DETERMINISTIC声明从源头满足要求。4.2 函数在生成列与视图中的妙用函数的一个高级用法是配合生成列GENERATED COLUMN。比如订单表里有unit_price和quantity不想每次查询都算一遍unit_price * quantity可以在建表时增加一个生成列CREATE TABLE order_items ( id INT PRIMARY KEY, unit_price DECIMAL(10,2), quantity INT, total_price DECIMAL(10,2) GENERATED ALWAYS AS (unit_price * quantity) STORED );生成列的值在插入时由 MySQL 自动计算查询时直接取存储好的结果还能在上面建索引性能和可读性都有提升。但要注意生成列的计算表达式里不能使用非确定性的函数如NOW()、RAND()函数的选择也因此受限。视图里也经常用到自定义函数。比如统一处理手机号脱敏可以创建一个函数fn_mask_mobile(mobile VARCHAR(20))在视图定义里调用每次查询结果都自动脱敏安全性更好也避免了每个查询语句里都写一长串CONCAT。4.3 什么时候别用自定义函数自定义函数不是万能的我实际使用中总结出三个别用场景第一高频查询的 WHERE 条件里别用自定义函数。原因很好理解函数导致索引失效。比如WHERE fn_calc_status(create_time) overdueMySQL 无法对这个经过函数计算的结果列使用索引必须全表扫描。如果数据量只有几千行影响不大如果数据量到了千万级性能会断崖式下降。第二大数据量循环里别用自定义函数。函数如果被放在一个几十万行的 UPDATE 语句里逐行调用执行效率会非常差。MySQL 的函数调用有额外的上下文切换开销远不如直接在应用层批量算好再更新。第三跨库分布式场景别用自定义函数。在分库分表中间件如 ShardingSphere环境下自定义函数的支持程度参差不齐很多中间件不解析自定义函数会直接把 SQL 下发到所有分片结果可能错乱。这种场景应尽量在应用层用 Java 或 Python 计算。5. 函数与索引的相爱相杀EXPLAIN 里看函数如何毁掉性能热搜词里同时出现了 mysql explain详解 和 mysql排序这两个词背后其实都藏着同一个问题函数导致索引失效查询性能崩塌。我自己调过的慢查询里有相当一部分是WHERE 子句里包了函数造成的。这一章把原理讲透再给一个可复现的排查方法。5.1 为什么 WHERE 里用了函数就走不了索引索引是基于字段原始值构建的 B 树结构。当你写WHERE DATE(create_time) 2024-01-01时MySQL 必须对每一行的create_time都执行一次DATE()函数才能拿到结果跟后面的常量比较。这个过程没法利用索引的有序性只能全表扫描。道理就跟查字典一样如果你知道某个词的拼音可以快速翻到对应页码但如果要求你把所有词里第二个字是某个字的词找出来你就只能一页一页翻。解决办法也很经典把函数从字段上移到常量上。上面的查询可以改写成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这样create_time没有套函数MySQL 可以直接走索引范围扫描性能通常提升一个数量级以上。我在实际优化中见过一条原本跑 8 秒的 SQL改成这个写法后降到 0.05 秒就是这一行函数位置的区别。5.2 EXPLAIN 里如何快速识别函数索引失效用EXPLAIN分析查询时重点看type和key两列。如果type显示ALL全表扫描而key为NULL大概率是索引没走如果type是range或ref说明索引正常工作。更直接的信号是Extra列里出现Using where但不出现Using index配合key列为空基本可以断定条件里有函数包裹字段。我举个实际案例。某订单查询EXPLAIN SELECT * FROM orders WHERE DATE(create_time) 2024-06-01;结果中type为ALLrows为 124000说明全表扫了 12 万行。改写后EXPLAIN SELECT * FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;结果中type为rangerows只有 356Extra显示Using index condition这就是走了索引的效果。这组对比特别适合拿来做团队代码审查时的教学案例一眼就看出问题本质。5.3 索引列上用函数的例外情况有例外吗有。在 MySQL 8.0 中你可以直接创建函数索引Functional Index也叫表达式索引。比如你经常按DATE(create_time)分组统计可以在(DATE(create_time))上建索引ALTER TABLE orders ADD INDEX idx_create_date ((DATE(create_time)));注意这里用的是双层括号表示这是一个表达式索引。建好之后WHERE DATE(create_time) 2024-01-01就可以走这个索引了。不过建表达式索引会增加写入成本也不一定能命中所有函数形式所以我的建议是能改 SQL 就改 SQL函数索引只作为最后的兜底方案。5.4 ORDER BY 里的函数与 filesortORDER BY里使用函数同样会导致无法利用索引排序触发filesort。EXPLAIN的Extra列出现Using filesort时就说明排序没有走索引需要关注排序的数据量。数据量小无所谓几十万行以上就会有明显的排序开销。比如你要按某个时间字段的月份排序ORDER BY MONTH(create_time)就必然 filesort如果业务上确实需要按月份排建议在表中冗余一个month_col字段并在插入时算好然后对这个字段建索引。另外filesort并不代表一定会慢但如果filesort的数据量超过sort_buffer_sizeMySQL 会使用磁盘临时文件性能会断崖式下降。可以用SHOW STATUS LIKE Sort_merge_passes;查看排序合并次数如果这个值很大说明排序缓冲不够需要适当调大sort_buffer_size或优化排序逻辑。6. 容易被热搜带偏的几个 MySQL 函数问题最后聊几个热搜词里反复出现、但网上答案常常互相矛盾的点。我把结论和适用场景讲清楚省得大家反复踩坑。6.1 无法将 xxx 项识别为 cmdlet、函数到底是 MySQL 的问题吗热搜里有大量 opencode、npm、claude、git、pnpm 的报错信息表面上看跟 MySQL 无关。但这类报错的本质是Windows 环境变量没有正确配置导致你在命令行里输入命令时系统找不到对应的可执行文件。MySQL 安装后如果出现mysql 不是内部或外部命令的报错原理完全一样。解决办法很固定找到 MySQL 安装目录下的bin文件夹比如C:\Program Files\MySQL\MySQL Server 8.0\bin把它加到系统环境变量Path中重新打开命令行即可。我建议用用户变量而非系统变量避免权限问题。装完 MySQL 后第一件事就是验证mysql --version能正常输出版本号说明 PATH 没问题。6.2 RAND() 与 ORDER BY 的性能陷阱热搜里 select函数、mysql排序 等词背后常有一个需求随机抽几条记录。很多人第一反应是ORDER BY RAND() LIMIT 5这样写在小表上没问题大表上就是灾难因为 MySQL 要为每一行生成随机数然后排序相当于全表扫描加全量排序。数据量 50 万以上时这条 SQL 可能跑好几秒。更高效的做法是采用主键范围随机SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) ORDER BY id LIMIT 5;这种写法只做一次主键范围扫描速度要快得多而且满足大多数业务场景。如果用户表的 ID 不是连续整数可以先查一个随机偏移量再取数据但思路是一样的——尽量避免对每一行都调用函数。6.3 字符串函数在 JOIN 条件里造成的隐式转换字符集不一致导致的 JOIN 隐式转换是 MySQL 函数相关的一个深坑但经常被忽略。当两个表的关联字段一个是utf8mb4另一个是latin1时MySQL 可能会在关联时对其中一个字段做隐式字符集转换导致无法使用索引。检查手段是在 EXPLAIN 里看Extra列有没有Using where配合key为空的情况根本解法是统一两张表的字段字符集ALTER TABLE t2 MODIFY col VARCHAR(50) CHARACTER SET utf8mb4;再配合CONVERT()函数也可以显式地把字符集转成一致再关联。这里要格外注意参与 JOIN 的两个条件字段字符集必须完全一致否则很容易出现 数据量不大但查询慢得离谱 的问题。我排查过最诡异的一个慢查询最后发现就是一张历史遗留表的字段是utf8mb3和主流utf8mb4表关联时发生了隐式转换索引直接失效。写在最后MySQL 函数这块说到底是规则和代价的平衡。规则指的是语法和函数语义代价指的是执行计划、索引利用、临时排序这些性能指标。很多开发人员能把各种函数背得滚瓜烂熟但写出来的 SQL 在大数据量下仍然慢得一塌糊涂就是因为只理解了规则没理解代价。我自己的习惯是每一条要上生产的 SQL先写出来再 EXPLAIN 一遍最后在模拟数据量级的测试环境里跑一次。函数用得好不好不是在语法层面评判的而是在key列和rows列里体现的。如果哪天你用DATE_FORMAT算出一个很漂亮的报表却发现整条查询扫了几百万行不妨停下来想一想能不能把函数从字段上挪走能不能换成范围条件这比多背十个函数都管用。最后再分享一个小技巧MySQL 8.0 的performance_schema里可以直接查到语句级别的执行统计配合EXPLAIN ANALYZE8.0.18 新增能精确看到每一步的耗时和行数。排查函数造成的性能问题时先用EXPLAIN ANALYZE定位最耗时的步骤再针对性改写函数写法比靠感觉优化高效得多。
空间)
