最近接手一个不算复杂的订单统计需求时，发现组里的开发同学把上百万行订单明细拉到应用层，再用 Python 一层 for 循环去算月度同比。我问了一句：这条 SQL 里先 GROUP BY 再 SUM 不行吗？对方愣了一下，说没想过。这大概是很多项目里每天都在发生的真实缩影——MySQL 里常见函数，尤其是字符串、日期、数值、条件分支这四类，明明几行 SQL 就能解决的问题，最后被绕到了应用层，既慢又难维护。
这篇文章我就拿真实业务场景做底，系统聊一聊 MySQL 中常见函数的用法边界、性能取舍，以及我在几个项目里总结出来的避坑笔记。不管你是刚写 SQL 不久，还是已经在日常取数里摸爬滚打了一段时间，这几组函数都值得再对照一遍。
1. 函数为什么是 SQL 基本功的分水岭
1.1 从“查出来”到“算好再返回”的思维转变
刚开始学 SQL 的时候，很多人觉得 SELECT、WHERE、JOIN 会了就能干活。但真实业务里，数据永远不会整整齐齐摆在那等你查。线上表里存的可能是datetime字符串，可能是华东-上海-浦东这种多级拼接的行政区划，可能是156****7890这种脱敏手机号，还可能是状态字段0/1/2需要翻译成业务含义。你要做的不是“查出来”，而是“查出来并且按业务口径算好”。
这时函数就是你手里的工具集。把原来在应用层用循环、判断、格式化做的事，下沉到 SQL 里完成，至少有三个直接收益：
- 减少数据传输量。不用再把几十万行原始数据拉到内存再慢慢处理；
- 利用 MySQL 的聚合和索引能力，让数据库分担计算；
- SQL 本身成为口径的唯一载体，避免应用层多套代码实现同一套业务规则。
1.2 同一个统计需求，两种写法的差距有多大
举一个最朴素的例子。要统计某段时间内每个月的销售额，最原始的做法是查出所有订单记录，在应用层按月份循环累加。SQL 侧大概是这样：
SELECT order_time, order_amount FROM orders WHERE order_time >= '2024-01-01' AND order_time < '2025-01-01';然后应用层按月分组求和。而用函数改写后：
SELECT DATE_FORMAT(order_time, '%Y-%m') AS month, SUM(order_amount) AS total_amount FROM orders WHERE order_time >= '2024-01-01' AND order_time < '2025-01-01' GROUP BY DATE_FORMAT(order_time, '%Y-%m');两个结果完全一样，但第一种写法可能传输几十万行明细，占用几十 MB 网络开销，应用层还要维护一个复杂的分组逻辑；第二种写法在 MySQL 内部完成聚合，只回传 12 行结果。差距就是这么大。
1.3 常见函数的分类地图
MySQL 的函数体系大致可以分成几组，日常开发里用到最多的是下面这几类。先给一张总表，后面逐一展开：
| 分类 | 代表函数 | 典型场景 |
|---|---|---|
| 字符串函数 | CONCAT、SUBSTRING_INDEX、REPLACE、TRIM、LENGTH | 拼接、截取、清洗、脱敏 |
| 数值函数 | ROUND、CEIL、FLOOR、MOD、ABS | 金额处理、取整、分页、奇偶切分 |
| 日期时间函数 | DATE_FORMAT、STR_TO_DATE、DATEDIFF、TIMESTAMPDIFF | 报表分组、时间差计算、格式化 |
| 条件流程函数 | IF、IFNULL、CASE WHEN、COALESCE | 空值兜底、业务分支、多维统计 |
| 聚合函数 | COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT | 分组汇总，配合 GROUP BY 使用 |
| 加密/杂项 | MD5、AES_ENCRYPT、UUID、JSON_EXTRACT | 脱敏、加密、JSON 字段提取 |
这里只展开前四类，因为它们是“写了就见效、错了难排查”的重灾区，也是从初级 SQL 迈向熟练 SQL 的必经台阶。
2. 字符串函数：最常用也最容易写出坏 SQL 的一组
2.1 拼接和分隔：CONCAT 与 CONCAT_WS
字段拼接应该是业务里最高频的字符串需求。CONCAT('a', 'b')最基础，但有几个点新手容易踩：
CONCAT里任何一个参数是NULL，结果就是NULL。比如CONCAT(user_name, '-' , user_phone)，只要user_phone是NULL，整个字段就没了；- 要拼多段带分隔符的内容时，手写
CONCAT(a, '-', b, '-', c)很啰嗦，而且容易漏分隔符。
更好的选择是CONCAT_WS(separator, a, b, c)。它第一个参数指定分隔符，后面的参数自动拼接，还会跳过NULL。比如：
SELECT CONCAT_WS('-', '华东', '上海', NULL, '浦东'); -- 结果：华东-上海-浦东日常做地址拼接、日志路径拼接，我基本都是用CONCAT_WS，代码可读性高，处理NULL的行为也更符合直觉。
2.2 按分隔符截取：SUBSTRING_INDEX 的魅力
截取子串，SUBSTRING(str, pos, len)是按字符位置截取，但真实业务里更常见的是“按分隔符切第几段”。比如行政区划字段存的是华东-上海-浦东，现在只要省级这一层，用SUBSTRING_INDEX非常痛快：
SELECT SUBSTRING_INDEX('华东-上海-浦东', '-', 1); -- 华东 SELECT SUBSTRING_INDEX('华东-上海-浦东', '-', -1); -- 浦东正数表示从左往右取到第 N 个分隔符之前的全部内容，负数表示从右往左取。这里有个容易绕的坑：SUBSTRING_INDEX('华东-上海-浦东', '-', 2)取到的是华东-上海，不是只取第二个段。想要“上海的上一级”“浦东的下一级”这类连续切分，通常是外层再包一层SUBSTRING_INDEX，或者配合SUBSTRING使用。
LEFT和RIGHT也很好用，尤其是手机号脱敏场景：
SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)); -- 156****78902.3 字符数还是字节数：LENGTH 与 CHAR_LENGTH 的隐性差异
这个坑我真是帮人排查过不少次。LENGTH()返回的是字节数，CHAR_LENGTH()返回的是字符数。在 UTF-8 字符集下，一个中文占 3 个字节，所以：
SELECT LENGTH('你好世界'); -- 12 SELECT CHAR_LENGTH('你好世界'); -- 4如果需求是“昵称超长截断到 20 个字”，用LEFT(nickname, 20)没问题，因为LEFT按字符截取；但如果你用WHERE LENGTH(nickname) > 20来判断超长，英文和中文混排时结果就会和你预期的完全不一样。业务口令是“展示多少个字”时，一律用CHAR_LENGTH；只有计算存储大小、判断字段长度是否够用时，才用LENGTH。
2.4 WHERE 条件里别随手套函数
字符串函数最容易被滥用的场景是 WHERE 条件。例如想查所有姓“张”的用户，新手可能会写：
SELECT * FROM users WHERE LEFT(name, 1) = '张';这个写法看起来没问题，但LEFT(name, 1)对每一行都要先做一次函数计算，索引彻底失效。改成WHERE name LIKE '张%'，在name有前缀索引或普通索引的情况下，优化器还能尝试走索引范围扫描。
MySQL 8.0 开始支持函数索引，可以给LEFT(name, 1)建索引，但老项目里 5.7 仍然占多数，而且函数索引也会增加写放大。我的原则是：能用范围查询表达的条件，就不要用函数包裹字段；确实需要函数时，优先考虑加冗余字段或函数索引。
3. 日期与数值函数：统计报表里的硬骨头
3.1 DATEDIFF 与 TIMESTAMPDIFF：一字之差，天上地下
日期差计算是报表需求里最常出现的计算之一。DATEDIFF(d1, d2)只比较日期部分，忽略时分秒，返回相差的天数。TIMESTAMPDIFF(unit, start, end)则可以按指定单位计算差异，单位可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR。
两者的差别非常微妙。看这个例子：
SELECT DATEDIFF('2024-06-01 23:00:00', '2024-05-31 22:00:00'); -- 返回 1，因为它只按日期算 SELECT TIMESTAMPDIFF(HOUR, '2024-05-31 22:00:00', '2024-06-01 23:00:00'); -- 返回 25，精确按小时算业务上算“隔了多少天”用DATEDIFF，算“两个时间点之间精确差多少小时/月/年”用TIMESTAMPDIFF。比如会员剩余天数、优惠券过期剩余天数，用DATEDIFF合适；算用户在线时长、任务超时时长，必须用TIMESTAMPDIFF。
3.2 日期格式化与索引失效：DATE_FORMAT 的代价
DATE_FORMAT(date, format)是报表分组的神器，把2024-06-01 12:30:22这种完整时间格式化成2024-06，再配GROUP BY，就能得到月度汇总。但它一旦出现在 WHERE 左侧，就是索引杀手：
-- 坏写法：DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-06-01' SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-06-01'; -- 好写法：范围查询 SELECT * FROM orders WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-06-02 00:00:00';坏写法的问题在于，DATE_FORMAT作用在索引列上，优化器无法使用索引直接定位，只能全表扫描。好写法虽然多写了两个条件，但对于create_time上有索引的表，性能差距可以是很惊人的。我这里给一个简单换算：一张 100 万行的订单表，两种写法实测下来，范围查询通常能在几十毫秒内返回，而函数套列的写法很可能要扫几百毫秒甚至更久。
日期转字符串的另一个场景是从外部导入数据时，用户给的日期是'2024/06/01'这种格式，需要STR_TO_DATE转回日期类型再入库。这里同样要注意格式串必须和输入字符串严格匹配，%Y-%m-%d和%Y/%m/%d不能混用。
3.3 数值处理：ROUND 与 FORMAT 的坑
金额计算里，ROUND(x, 2)是四舍五入保留两位小数，用途最广。FLOOR向下取整、CEIL向上取整，常用于分页计算、库存切割、人数统计。MOD(a, b)取模，在分库分表和数据抽样里经常出现。
一个特别容易踩的坑是FORMAT。FORMAT(x, 2)虽然也是保留两位小数，但它返回的是字符串，而且会自动加千分位逗号：
SELECT FORMAT(12345.678, 2); -- 12,345.68如果业务上要的是“12345.68”这种纯数字字符串，FORMAT的结果传到前端再拿去排序或二次运算，大概率会出问题。我在一个报表项目里就遇到过：导出 Excel 后金额列被 Excel 识别成了文本，并且带着逗号，用户直接看懵了。像这种需要进一步计算的金额，用ROUND保留两位就好，别用FORMAT。
RAND()也值得一提。ORDER BY RAND()取随机 N 条记录看起来很爽，但它会对全表每一行生成随机数再排序，数据量大时非常慢。如果只是随机抽几条，更好的做法是应用层先SELECT MIN(id), MAX(id)，随机生成 ID 区间后再查。
4. 条件与流程函数：让 SQL 自己“做决定”
4.1 IF 与 IFNULL：空值处理的边界条件
IF(expr, v1, v2)的作用类似三目运算符，比如判断某个订单是不是大单：
SELECT order_id, IF(order_amount > 10000, '大单', '普通单') AS order_level FROM orders;IFNULL(expr, default)专门处理NULL：当表达式为NULL时返回默认值，否则返回表达式原值。一个常见的误用是拿它去判断空字符串：
SELECT IFNULL(user_remark, '无备注') FROM users; -- user_remark 为空字符串 '' 时，IFNULL 并不会返回 '无备注'因为空字符串不是NULL。如果要“空值和空字符串都兜底”，需要IF(user_remark IS NULL OR user_remark = '', '无备注', user_remark)，或者用后面说的CASE WHEN。这个差异在导入外部系统数据时特别容易中招，因为很多业务系统的空值习惯是存''而不是NULL。
4.2 CASE WHEN 的聚合场景：一次扫描完成多维统计
如果说函数里只能挑一个最推荐的，我会投CASE WHEN。它比IF表达能力更强，支持多分支，还能直接嵌在聚合函数里做条件统计。
最经典的玩法是配合SUM完成“一行 SQL 统计多个口径”。比如要统计某个时间段内，每个月的已支付订单金额、待支付订单金额、退款订单金额：
SELECT DATE_FORMAT(pay_time, '%Y-%m') AS month, SUM(CASE WHEN pay_status = 1 THEN order_amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN pay_status = 0 THEN order_amount ELSE 0 END) AS pending_amount, SUM(CASE WHEN pay_status = 2 THEN order_amount ELSE 0 END) AS refund_amount FROM orders WHERE pay_time >= '2024-01-01' AND pay_time < '2024-07-01' GROUP BY DATE_FORMAT(pay_time, '%Y-%m');这种写法只扫一次表，就同时拿到三个口径的汇总。很多刚开始写 SQL 的同学会拆成三条 SQL 分别统计，再在应用层拼接。一次扫描和三次扫描，在大表上差距就是成倍的执行时间。
4.3 COALESCE：多字段兜底的另一种思路
COALESCE(val1, val2, ...)返回参数列表里第一个非NULL的值。它的好处是可以一长串兜底：
SELECT COALESCE(mobile, telephone, email, '无联系方式') FROM users;这在多个联系方式字段“哪个非空取哪个”的场景里，比嵌套多层IFNULL清爽得多。不过要注意，它同样不处理空字符串，和IFNULL的边界问题一致。
5. 组合实战：从零写一个月度经营统计 SQL
5.1 需求描述与最初的笨办法
之前某个模拟项目里有这样一张订单表：
| 字段 | 类型 | 说明 |
|---|---|---|
| order_id | varchar | 订单号 |
| user_id | bigint | 用户 ID |
| order_amount | decimal(10,2) | 订单金额 |
| pay_status | tinyint | 1 已支付，0 待支付，2 退款 |
| pay_time | datetime | 支付时间 |
需求是：输出某年 1 到 6 月每个月的订单数、支付人数、支付金额、退款金额、客单价（支付金额 / 支付人数）。
最初的实现方案是应用层写循环，每个月查一条 SQL，或者把半年的明细全部拉出来再统计。前者代码冗余，后者数据量一大就把服务器内存吃满。其实一条 SQL 就能把上面几个函数全部串起来：
5.2 函数组合后的最终 SQL
SELECT DATE_FORMAT(pay_time, '%Y-%m') AS month, COUNT(DISTINCT order_id) AS order_cnt, COUNT(DISTINCT user_id) AS pay_user_cnt, ROUND(SUM(order_amount), 2) AS pay_amount, ROUND(SUM(CASE WHEN pay_status = 2 THEN order_amount ELSE 0 END), 2) AS refund_amount, ROUND(SUM(order_amount) / COUNT(DISTINCT user_id), 2) AS price_per_user FROM orders WHERE pay_status IN (1, 2) AND pay_time >= '2024-01-01' AND pay_time < '2024-07-01' GROUP BY DATE_FORMAT(pay_time, '%Y-%m') ORDER BY month;这里用到的函数包括：DATE_FORMAT做月份分组、ROUND做金额精度控制、CASE WHEN做退款口径统计、COUNT(DISTINCT)做去重计数。整条 SQL 只扫一次订单表，返回 6 行结果，应用层几乎不需要再做任何加工。
5.3 进一步优化：冗余统计字段与函数索引的取舍
上面这条 SQL 有一个隐患：GROUP BY DATE_FORMAT(pay_time, '%Y-%m')是对支付时间这个列应用函数后再分组，优化器基本没法利用pay_time上的普通索引做分组。如果订单表有上千万行，月度统计这种查询每次都要扫全表。
如果这个月度报表是每日跑一次的定时任务，我倾向于多加一个冗余字段，而不是硬扛。做法是在订单表加一个生成列：
ALTER TABLE orders ADD COLUMN stat_month CHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(pay_time, '%Y-%m')) STORED, ADD INDEX idx_stat_month (stat_month);之后统计 SQL 直接按stat_month分组，索引就能派上用场。MySQL 8.0 里也可以直接建函数索引：
CREATE INDEX idx_pay_month ON orders ((DATE_FORMAT(pay_time, '%Y-%m')));但函数索引会带来写入时的额外计算成本。我的习惯是：表写入频繁，用生成列冗余字段；表几乎只读，函数索引更省事。两种方案都建议先在测试环境验证执行计划，不要拍脑袋。
6. 老手的函数使用原则：我踩过的几个坑
6.1 索引列函数化：什么时候可以打破“禁条”
几乎每个 MySQL 性能文章里都会写“不要在索引列上使用函数”。这句话的背景是针对没有函数索引的老版本。MySQL 8.0.13 之后，函数索引已经是一个正规能力，问题是它并不能解决所有场景。
比如WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-06-01'，即便建了对应函数索引，优化器也要能识别并选择走这个索引，有时还需要改写查询写法才能命中。相比之下，用范围查询永远是最稳的方式。所以我的执行顺序是：优先改写 SQL 为范围查询；改不动再加冗余列；最后才考虑函数索引。
6.2 隐式转换：一个字符型字段引发的索引失效
这个坑几乎每个项目里都会出现。某些业务表里的订单号是varchar类型，但统计脚本里写成了：
SELECT * FROM orders WHERE order_no = 20240601001;order_no是字符型，等号右边是数字，MySQL 会做隐式转换，把字符转成数字再比较。这样即便order_no上有索引，也无法正常走索引范围扫描，因为每行都要先转换。
正确写法是右侧加引号：
SELECT * FROM orders WHERE order_no = '20240601001';排查这种问题有一个非常简单的办法：执行完查询后看EXPLAIN结果里的type是不是ALL，再用SHOW WARNINGS看 MySQL 是否提示了隐式转换。
6.3 字符集与排序规则不一致：跨表拼接的“Illegal mix”
多表关联时如果两边的字符集或排序规则不一致，MySQL 会直接报错：
Illegal mix of collations我在一个跨库同步项目里遇到过，旧库是latin1，新库是utf8mb4，两张表直接 JOIN 时就炸了。解决方式要么统一库表字符集，要么在 SQL 里显式转换：
SELECT * FROM t1 JOIN t2 ON t1.name = CONVERT(t2.name USING utf8mb4);这类问题不常见，但一旦出现，排查成本很高。我的建议是：新项目建库时统一utf8mb4，字符串比较和拼接前先确认两边字符集一致，别等线上报错再回头改表。
6.4 我是怎么记函数的：按场景记，不按函数名硬背
函数列表几十上百个，死记硬背很快会忘。我自己的办法是建立“场景到函数”的映射，碰到需求时直接对号入座：
| 业务需求 | 首选函数 |
|---|---|
| 拼地址、拼日志 | CONCAT_WS |
| 按分隔符取第几段 | SUBSTRING_INDEX |
| 判断中英文字符长度是否超限 | CHAR_LENGTH |
| 日期按月/日分组 | DATE_FORMAT |
| 算精确时间差 | TIMESTAMPDIFF |
| 算跨天数 | DATEDIFF |
| 字段为 NULL 时给默认值 | IFNULL / COALESCE |
| 一个查询统计多个口径 | SUM + CASE WHEN |
| 金额保留两位 | ROUND，别用 FORMAT |
这样遇到需求时，我脑子里跳出来的不是“哪个函数”，而是“这个场景以前怎么写的”，再顺手翻一下本地笔记，比对着文档海搜效率高得多。
最后说一点我自己的体会：函数本身不难写，真正难的是知道它会在背后触发什么行为。日期格式化会让索引失效，字符串匹配可能触发隐式转换，空值判断可能漏掉空字符串——这些细节单看文档不容易发现，只有把每个函数放进真实查询里，用EXPLAIN和实际数据验证过，才会形成肌肉记忆。接新需求时先问一句“能不能一条 SQL 解决”，再动手写，你的 SQL 质量和执行效率都会明显不一样。
