我刚工作那阵对 MySQL 函数的理解一直停留在“用到哪个查哪个”的阶段直到后来接手了几个慢查询排查才开始意识到函数在 SQL 里的位置和用法直接决定了查询能不能走索引、逻辑是不是清晰、统计口径对不对。MySQL 函数体系其实不小字符串、聚合、日期时间、条件判断、类型转换每一类都对应着一批高频业务场景。这篇我不打算背一遍函数字典而是按实际开发的思路把这几个大类的常用函数、执行逻辑和容易踩的坑系统梳理一遍适合刚把 SQL 基础语法学完、准备在真实场景里大量写查询的同学查漏补缺。1. 别急着背函数名先把 MySQL 函数的组织逻辑想明白1.1 函数在 SQL 语句里的位置不同代价完全不同MySQL 函数本质上是“输入一组值返回一个固定结果”的表达式它可以出现在 SELECT 的列表里也可以出现在 WHERE、GROUP BY、HAVING、ORDER BY 这些子句里。这一点看起来简单实际影响却非常大。位置不一样函数在查询执行计划里的计算时机就不一样。举个例子SELECT UPPER(name) FROM user这个函数是在检索结果返回前才做一次计算对表的扫描范围没有任何影响。但如果写成SELECT name FROM user WHERE UPPER(name) ALICE函数就参与到了 WHERE 的行过滤阶段name 字段上的索引会被 UPPER 包裹得没法正常使用。同样一个函数放在不同位置代价一个天一个地。所以我一直觉得学 MySQL 函数的第一课不是记函数签名而是意识到“在哪里用”比“用什么”更重要。1.2 按业务场景把函数分成两大阵营官方手册喜欢把函数分成字符串函数、数值函数、日期时间函数、聚合函数、流程控制函数等等这种分类对检索索引目录有帮助但写 SQL 的时候参考意义不大。我更习惯按“执行时行数变化”分成两类单行函数和聚合函数。单行函数的特点是“一行进去一行出来”比如字符串拼接、日期格式化它对结果集的每一行分别计算结果行数和输入行数一致。聚合函数的特点是“多行进去一行出来”比如 SUM、COUNT、MAX它会把一批行压缩成一个统计值通常要配合 GROUP BY 使用。搞清这个区别以后写复杂 SQL 时就不会再把单行函数和聚合函数莫名其妙混在一起。很多人写SELECT name, COUNT(*) FROM user这种报错语句本质原因就是没意识到聚合函数会改变结果集的行数结构。1.3 函数不是越多越好克制才是成熟写法这是我最想强调的一个观点。MySQL 函数并不等于高手气息那些线上运行稳定、查询速度又快的好 SQL往往对函数使用得非常克制。原因有两个。第一函数本身是计算成本。尤其在大表的 WHERE 条件中每一行都要先执行一次函数计算再做条件判断数据量大时这部分开销会被无限放大。第二函数是索引失效的常见导火索。哪怕你在字段上建了完美的复合索引只要在 WHERE 里对字段套上函数优化器通常就不得不放弃索引扫描改为全表扫描。所以我现在写 SQL 有一条默认原则能不用函数的过滤条件就坚决不用确实需要函数处理数据优先考虑它对字段是否造成“包裹”如果一定要在过滤字段上做运算写完必须用 EXPLAIN 验证一次。注意索引列上套函数导致全表扫描这是慢查询排查里最高频的问题。很多慢 SQL 的解决方案根本不是多加索引而是把函数从 WHERE 条件里挪走。2. 字符串函数日常最常用也最容易在编码上翻车2.1 拼接、截取、替换三件套我先说使用细节CONCAT、SUBSTRING、REPLACE 是我个人使用频率最高的三个字符串函数。CONCAT 把多个字段拼成一个字段SUBSTRING 从长字符串中截取一段REPLACE 对字符串做局部替换这三个几乎能覆盖日常七八成的文本清洗需求。SELECT CONCAT(last_name, first_name) AS full_name FROM user; SELECT SUBSTRING(id_card, 7, 8) AS birth_date FROM user; SELECT REPLACE(phone, SUBSTRING(phone, 4, 4), ****) AS masked_phone FROM user;CONCAT 有一个特别容易被忽略的细节只要拼接的任意一个参数是 NULL整个拼接结果就会变成 NULL。我在实际业务里踩过这个坑比如后台配置页里要拼接省市区和详细地址只要某个字段为空前端拿到的完整地址就整个变成空白。解决方式有两种用 CONCAT_WS 加分隔符拼接它遇到 NULL 会跳过或者用 IFNULL 先把可能的 NULL 处理掉。SELECT CONCAT_WS(-, country, city, area) AS full_address FROM address_book;这个写法比 CONCAT 更贴合真实业务因为地址缺省太常见了用绝对分隔符拼接反而能保留已存在的信息。2.2 长度判断和大小写转换注意字符和字节的差别LENGTH 返回的是字节长度CHAR_LENGTH 返回的是字符长度。这两个函数在中文场景下差异特别大一个中文字符在 utf8mb4 编码下通常占用 3 个字节如果拿 LENGTH 去判断一个中文字符串长度结果会比肉眼看多出一倍。需要做用户名长度校验、留言长度控制这类需求时CHAR_LENGTH 更符合业务直觉。UPPER 和 LOWER 用于大小写转换最典型的应用是查重、去重或者登录名校验。但这类函数有一个使用陷阱如果本身字段上的排序规则不区分大小写那直接用字段名去匹配就行完全不需要套 UPPER 函数如果排序规则区分大小写套了 UPPER 又容易让索引失效。正确的思路是在数据模型设计阶段就确定好排序规则而不是每次查询临时做转换。2.3 填充、去除空格、正则匹配这些工具函数偶尔也能救命LPAD 和 RPAD 常用来补位比如订单号不足十位前面补零TRIM、LTRIM、RTRIM 用来清理字符串首尾空格REGEXP 提供比 LIKE 更强的正则匹配能力。这些函数单独看起来都很简单但组合使用时会让 SQL 变成一长串天书所以我建议只在必要场景用。举个例子编号补位的场景非常典型SELECT LPAD(order_no, 10, 0) AS padded_no FROM orders;一行代码就搞定了完全不需要应用层循环补位。REGEXP 虽然表达能力强但对索引的支持比较弱大表过滤时慎用。能通过 LIKE 前缀匹配解决的问题尽量别用正则。2.4 字符集和排序规则才是字符串函数的最大暗坑字符串函数的结果会受到当前连接的字符集影响。字段是 utf8mb4连接字符集却还是 utf8mb3 或 latin1 时SUBSTRING、REPLACE、LENGTH 的计算结果都可能出现乱码或长度错乱。遇到这种问题第一件事是统一字符集建议整个链路都使用 utf8mb4。检查当前连接字符集的命令很简单SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;如果已经产生了乱码数据可以用 CONVERT 临时修复SELECT CONVERT(name USING utf8mb4) FROM user WHERE id 1;但这是事后补救思路不能当成常规写进业务代码。我从实践中得到的结论是建表、连接、查询三个环节全都统一字符集和排序规则字符串函数相关的怪问题能少八成。3. 聚合函数统计报表的核心别小看这几个细节3.1 COUNT(*)、COUNT(1)、COUNT(字段) 的差别比想象中小很多老文章说 COUNT(1) 比 COUNT() 更快这个说法在现代 MySQL 版本里基本不成立。优化器早就对 COUNT() 做了专门优化完全没有必要为了“性能”去改成 COUNT(1)。真正要注意的是 COUNT(字段)因为 COUNT 会跳过字段值为 NULL 的行。如果字段建表时设置了 NOT NULL那 COUNT(字段) 和 COUNT(*) 结果完全一致如果字段允许为空你可能在统计“有效记录数”时恰好用到这个特性。举一个实际场景SELECT COUNT(*) AS total, COUNT(agree_time) AS agreed_cnt FROM user_agreement;这里 agree_time 是空代表用户还没签署协议那么 agreed_cnt 统计的就是已经签署的用户数语义非常清楚。3.2 SUM 和 AVG 对 NULL 的处理决定了你的统计口径是否正确SUM 和 AVG 对 NULL 的处理方式都是忽略而不是当作 0。这在多数场景下是合理的但也很容易把报表统计口径带偏。比如要统计“人均消费金额”如果直接用 AVG(amount)没消费过的行不会参与计算得到的是“已消费人群的均值”而不是“全量客户的均值”。如果你需要把未消费的人按 0 纳入统计就得在聚合之前先处理 NULLSELECT COALESCE(AVG(amount), 0) AS avg_amount FROM orders;还有一个特别容易踩的细节SUM(amount) 在没有命中任何行时返回的是 NULL而不是 0。前端直接展示就会显示成空白。正确的写法是COALESCE(SUM(amount), 0)这可以让报表展示更友好。3.3 GROUP BY 和 HAVING 的执行顺序很多人一直搞反WHERE 和 HAVING 的过滤阶段完全不同。WHERE 在 GROUP BY 分组之前做行级过滤HAVING 在分组之后对聚合结果过滤。两个连用的时候能用 WHERE 过滤掉的记录就先别留到 HAVING 里处理。比如统计“支付金额大于 100 元的订单每个城市的总金额”正确写法是先 WHERE 过滤掉金额小于等于 100 的行再按城市分组SELECT city, SUM(amount) AS total_amount FROM orders WHERE amount 100 GROUP BY city;如果非要把金额条件写进 HAVING也不是完全不行但数据量大时等于把所有金额记录全部分组之后再做过滤计算量白白翻了一倍。另外MySQL 在开启 ONLY_FULL_GROUP_BY 模式时SELECT 列表中的非聚合字段必须出现在 GROUP BY 中。不少老项目换个环境就报错多半就是没适配这个模式。3.4 GROUP_CONCAT 做字符串拼接有隐藏的长度限制GROUP_CONCAT 可以把同组的多行字段值拼成一个字符串比如把某个用户的所有角色名聚合成一个列表。它有一个非常容易踩的坑默认最大长度只有 1024 字节超过部分会被静默截断。我做过一个后台配置页用户角色多了一截页面上的角色列表就莫名少了一部分排查到这个隐藏限制时已经是几个小时后的事了。调整方式有两种一个是会话级调整变量另一个是用 GROUP_CONCAT 的固有参数控制去重和排序SET SESSION group_concat_max_len 102400; SELECT user_id, GROUP_CONCAT(role_name ORDER BY role_id SEPARATOR ,) AS roles FROM user_role GROUP BY user_id;注意 group_concat_max_len 是会话级变量连接关闭后就失效。生产项目中应用层每次建立连接时都要重新设置否则随时可能复现截断问题。4. 日期时间函数统计口径和索引优化的双重考验4.1 先分清 MySQL 的日期时间类型再谈函数DATE、TIME、DATETIME、TIMESTAMP 这几种类型适用场景完全不同。DATE 只保存年月日TIME 只保存时分秒DATETIME 同时保存年月日和时分秒TIMESTAMP 底层存的是时间戳值会受到时区设置影响。业务系统中经常用到的“订单时间”“创建时间”到底选 DATETIME 还是 TIMESTAMP我的经验是如果系统有国际化、多时区展示的需求TIMESTAMP 会随时区自动转换表面上看起来数据在变容易让不懂内情的同事误以为数据错了。跨境业务里我更倾向于 DATETIME 保存标准时间展示层再根据用户时区做转换把复杂度隔离在应用层。4.2 常用日期函数一次说清NOW、CURDATE、DATE_FORMAT 等NOW() 返回当前日期时间CURDATE() 返回当前日期CURTIME() 返回当前时间DATE_FORMAT() 做格式化输出YEAR()、MONTH()、DAY() 取日期字段上的某一部分DATE_ADD() 和 DATE_SUB() 做日期加减DATEDIFF() 返回两个日期间隔的天数TIMESTAMPDIFF() 可以按单位精确计算差值。SELECT NOW(), CURDATE(), CURRENT_TIMESTAMP(); SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); SELECT DATE_ADD(2024-06-01, INTERVAL 1 MONTH); SELECT TIMESTAMPDIFF(DAY, 2024-06-01, 2024-06-20);DATE_FORMAT 的格式符在大小写上很坑。%Y 是四位年份%y 是两位年份%M 是英文月份名%m 是数字月份%i 是分钟%s 是秒。一旦写错格式符号函数不会报错而是返回一串看起来莫名其妙的值这种错误比直接报错更让人头疼。4.3 字符串转日期和时间戳转换格式不匹配是主要翻车点STR_TO_DATE() 可以把字符串按指定格式解析成日期FROM_UNIXTIME() 把时间戳转成可读的日期时间UNIX_TIMESTAMP() 把日期时间转成时间戳。这类转换最怕的是字符串格式和解析格式不匹配。以前我处理过一个前端传来的日期参数格式有时是2024-06-01有时是2024/06/01。在 SQL 里直接用 STR_TO_DATE 解析一部分数据能解析成功一部分返回 NULL查了好半天才发现是格式符不一致导致的。从那以后我基本把日期解析和格式统一的事放到了应用层数据库层只认标准格式字符串。4.4 日期字段上套函数索引就是这样悄悄失效的这是排查慢查询时最常见的问题。很多同学查“某天创建的订单”习惯性写成SELECT * FROM orders WHERE DATE(create_time) 2024-06-01;这个写法逻辑完全正确但 create_time 字段上如果有索引DATE() 包上去之后基本就废了。正确的写法是使用范围比较SELECT * FROM orders WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;两种写法结果一致后者却能让索引正常工作。养成这个习惯以后你的慢查询能少相当大一部分。5. 条件逻辑和类型转换让 SQL 学会“做决定”5.1 IF、IFNULL 的使用边界和语义差异IF(expr, true_value, false_value) 是 SQL 里最简单的分支函数比如IF(score 60, pass, fail)适合做单层判断嵌套两层以上可读性就明显下降不建议当主力逻辑用。IFNULL(expr1, expr2) 专门解决 NULL 问题语义是“如果 expr1 是 NULL返回 expr2”。还有一个长得像、语义却相反的 NULLIF(expr1, expr2)它是在 expr1 和 expr2 相等时返回 NULL不相等时返回 expr1。比如用它来防止除零SELECT amount / NULLIF(total, 0)当 total 为 0 时NULLIF 返回 NULL最终结果是 NULL而不是让整条 SQL 报除零错误。5.2 CASE WHEN 是老大哥复杂分支写它更稳妥CASE WHEN 支持两种写法。简单 CASE 适合等值匹配搜索 CASE 适合范围判断。日常开发里我几乎统一用搜索写法因为它的表达方式更接近自然语言SELECT CASE WHEN amount 1000 THEN 高 WHEN amount 100 THEN 中 ELSE 低 END AS level FROM orders;分支数超过两个或者判断条件涉及范围比较就优先选择 CASE WHEN别用多层 IF 嵌套。它还有一个特别强大的用法是和聚合函数搭配做“条件统计”SELECT SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS active_cnt FROM orders;这种写法在报表里很常见用一条 SQL 就能汇总多个条件计数比拆成多条子查询高效得多。5.3 CAST 和 CONVERT显式类型转换的正确姿势CAST(expr AS type) 和 CONVERT(expr, type) 功能接近主要用来把字符串转数字、日期转字符串、整型转小数等。显式转换最大的价值是避免隐式转换带来的不可控结果。举一个实际例子SELECT CAST(123abc AS UNSIGNED);这段语句会返回 123而且不报错。也就是说MySQL 对字符串转数字的容忍度很高会自动提取数字部分。如果业务代码里对用户输入做隐式比较就很容易出现“看着明明不相等却查到了数据”的诡异情况。5.4 隐式类型转换的坑最好彻底掌握MySQL 在比较“字符串字段”和“数字常量”时会尝试把字符串转换数字。这意味着如果字段类型是 varchar而查询条件传入的是整数就可能触发隐式转换导致索引失效或匹配到意料之外的记录。比如用户表的编号列是 varchar查询条件写成WHERE no 10001看起来没问题实际上 MySQL 会把每一行的 no 先转成数字再比较。比较过程一多索引优势就被削弱了。我现在的做法是写 SQL 之前先确认字段类型再决定参数类型尽量做到类型一致不给隐式转换留机会。6. 实盘排查记录那些年我踩过的函数大坑6.1 同样一条 SQL为什么这台机器快那台机器慢同一个 SQL 在不同环境出现性能差异第一时间该怀疑的不是机器配置而是函数作用在哪个字段上。大部分函数慢就慢在让索引失效所以排查第一步就是跑一下 EXPLAIN看 type 字段是不是从 ref、range 退化成了 ALL。我排查过的一个典型案例用户表主键是 varchar 类型业务代码里条件写的是WHERE id 1000001id 是 varchar参数是整型MySQL 忍着隐式转换硬对比。数据量一上来全表扫描的代价就非常明显。后来把参数改成字符串或者统一主键成整型查询速度立刻就不一样了。6.2 聚合结果为空和 NULL 的区别要分清楚COUNT 的返回值永远是一个数字SUM 和 AVG 在没有命中任何行时返回 NULL。这个细节很致命因为应用层直接把聚合结果拿来展示NULL 会显示成空白甚至报错。以前做报表模块时我就踩过这个坑某维度没有任何订单时SUM 的结果不是 0而是 NULL前端页面无端出现空白格。正确写法是包装一层SELECT COALESCE(SUM(amount), 0) AS total_amount FROM orders WHERE user_id 999;这属于最简单的防护但确实能避免很多生产事故。6.3 函数越堆越多的 SQL维护起来就是灾难字符串清洗、日期格式化、条件判断全堆在一个 SQL 里看起来像一行写在代码里的“米其林大餐”实际维护起来却是灾难。我见过有人在一条 SELECT 里叠加了 CONCAT、LEFT、REPLACE、CASE WHEN 七八层函数自己过一个月回来看也得拆半小时才能看懂。这种情况我的建议是超过三段以上转换逻辑宁可在应用层用代码处理也别硬塞进 SQL。如果一定要在数据库层做要么加注释把每一步结果标注清楚要么把复杂表达式封装成一个视图让后续查询变得干净。MySQL 函数的价值是让数据加工更简单而不是给你提供炫技平台。6.4 我日常遵守的一个 MySQL 函数使用清单最后把我平时检查 SQL 时的心得整理成一个小的自检列表每次写完复杂查询都会过一遍查询参数的字段类型和表结构是否完全一致有没有触发隐式转换。日期过滤是否用了范围比较而不是在字段上套 DATE() 等函数。SUM、AVG 聚合结果会不会出现 NULL需不需要 COALESCE 兜底。GROUP BY 是否触发了 ONLY_FULL_GROUP_BY 限制。函数是否叠加太多层逻辑是否适合搬到应用层。对执行计划不放心时跑一次 EXPLAIN 确认索引有没有真正用上。我自己现在写 SQL 默认遵循一条原则能用 CASE WHEN 就不用 IF能用范围比较就不用函数去包字段能用 EXPLAIN 验证就绝不靠猜。MySQL 函数说到底只是一批工具关键看你什么时候拿起它、什么时候放下它。你在排查慢查询时如果总感觉没思路先把所有函数的位置拎出来逐个复盘基本就能锁定一半问题。希望这篇整理能让你少踩几个我踩过的坑。