很多人觉得MySQL函数没啥好学的，无非就是COUNT、SUM、NOW这几个来回用。但我在实际开发和排障中见过太多因为函数用错导致的线上事故——有的是字符集判断错误导致查询结果稀疏，有的是GROUP BY之后选错列直接报错，有的是日期边界少算了一天。说白了，MySQL函数不难，难的是把函数的边界行为、隐式规则和底层执行逻辑吃透。这篇内容我尽量不写成文档式罗列，而是按真实业务场景来拆，把那些踩过的坑、看过的报错、优化过的慢查询，一次讲明白。

这篇东西适合谁看？刚学SQL的新手可以把它当进阶手册，写过一两年业务的老手也能从中找到一些平时没留意的细节。我尽量做到每个函数都讲清楚“为什么这么用”，而不是只丢给你一个语法糖。

1. 字符与文本处理：你以为是小事，其实最容易翻车

1.1 字符长度别傻傻分不清：CHAR_LENGTH 与 LENGTH 的差别

先说一个最常见的坑：统计字符串长度。

LENGTH()返回的是字节数，CHAR_LENGTH()返回的是字符数。这两个函数在纯英文场景下结果一样，一旦出现中文、表情符号，结果就分道扬镳了。MySQL的utf8mb4字符集下，一个汉字占3个字节，一个emoji占4个字节，而CHAR_LENGTH只按字符个数数。

举个例子：

SELECT LENGTH('数据库') AS byte_len, CHAR_LENGTH('数据库') AS char_len; -- byte_len: 9, char_len: 3

我见过有人用LENGTH去判断用户昵称是否超长，结果明明只写了10个汉字，却因为字节数超限被误判。做输入校验、数据清洗的时候，统一用CHAR_LENGTH按字符维度算，业务含义才准确。如果你确实要按存储空间估算字段大小，那才用LENGTH。

另一个容易被忽略的细节是CHAR_LENGTH在MySQL 8.0中对于VARCHAR类型是直接读取内部存储的字符长度元数据，性能开销极低，可以放心使用。而老版本中部分字符集下会存在额外计算，问题不大，但在大规模数据扫描场景里，能用元数据解决的不要用函数硬算，这条原则后面还会碰到好几次。

1.2 字符串拼接的三个层次：CONCAT、CONCAT_WS 与 GROUP_CONCAT

CONCAT函数本身不复杂，但有个特性让很多人吃过亏：任何一个参数为NULL，整个结果就是NULL。

你想想这个场景：用户表里有姓、名、中间名三个字段，你要拼全名。

SELECT CONCAT(last_name, first_name) FROM user;

只要中间名是空的，整个拼接结果全没了。稳妥做法是用CONCAT_WS，第一个参数指定分隔符，它会自动跳过NULL值：

SELECT CONCAT_WS(' ', last_name, middle_name, first_name) FROM user;

这个函数是我处理报表拼接时最常用的，没有之一。它把“分隔符管理”和“NULL跳过”一次性解决，输出干净得多。

GROUP_CONCAT则是分组场景下的行转列神器。把某个分组内的多个值拼成一个逗号分隔的字符串，用法很直白：

SELECT dept_id, GROUP_CONCAT(user_name ORDER BY create_time SEPARATOR ',') FROM user GROUP BY dept_id;

注意几个坑：默认最大长度限制是1024字节，字符串长了会被静默截断，结果就是不完整的一串。解决方法是先查一下当前值：

SHOW VARIABLES LIKE 'group_concat_max_len';

如果业务需要更长的拼接结果，在会话级别调大：

SET SESSION group_concat_max_len = 102400;

另一个坑是GROUP_CONCAT里用了ORDER BY之后，结果顺序是确定的，但如果你不加ORDER BY，拼接顺序是不保证的。做汇总报表时，顺序不稳定意味着每次导出的数据可能不一样，排查问题时极难复现。所以只要业务上对顺序有隐含依赖，一定要显式写明排序规则。

1.3 用 SUBSTRING_INDEX 优雅拆字段，少写三层嵌套

SUBSTRING_INDEX(str, delim, count)是我处理脏数据时的高频工具，尤其是那些把多个值塞进一个字段的历史遗留表。

举例：某个表把“省-市-区”存在一个字段里，你要拆出省份：

SELECT SUBSTRING_INDEX(region, '-', 1) AS province FROM address;

要拆出最后一段（区），用负数：

SELECT SUBSTRING_INDEX(region, '-', -1) AS district FROM address;

配合SUBSTRING还能玩出更多花样。比如去掉前两段，只留第三段：

SELECT SUBSTRING(SUBSTRING_INDEX(region, '-', 3), LOCATE('-', region) + 1) FROM address;

不过有件事得提醒：字段内分隔符不统一（有时候是-有时候是/）的时候，硬拆会翻车。我一般先做一轮数据探查，用REGEXP或者LIKE找出脏数据样本，再写清洗逻辑。直接上SUBSTRING_INDEX的前提是数据格式已经被确认过，否则你会得到一堆莫名其妙的空串。

1.4 LIKE 之外的两个高效定位函数：LOCATE 与 INSTR

想在字符串里找某个子串的位置，新手第一反应是写LIKE，但有时候你需要的是“位置信息”而不是“是否存在”。

LOCATE(substr, str)返回子串首次出现的位置，INSTR(str, substr)参数顺序相反，它俩功能几乎一样。区别在于LOCATE支持第三个参数作为起始位置，跳过前面匹配到的内容：

SELECT LOCATE('a', 'banana'); -- 2 SELECT LOCATE('a', 'banana', 3); -- 4

这个跳过功能在处理“取第二个分隔符后面的内容”时非常有用。比如从一堆键值对里提取第二个等号后面的值，不需要写复杂的正则，配合SUBSTRING就能干净地拆出来。

另外一个实用技巧：当你要判断某个字段是否以指定前缀开头时，用LEFT(field, len)或LIKE 'prefix%'都能命中前缀索引；但如果用LOCATE或INSTR去判断“子串是否在字段任意位置”，索引就不太好用了。这条经验在慢查询优化时很关键——能走前缀就用前缀，别图省事写全模糊匹配。

2. 日期与时间函数：边界条件才是最考验人的地方

2.1 NOW、CURRENT_TIMESTAMP 与 SYSDATE 的区别

先说一个很多人不知道的事实：NOW()和CURRENT_TIMESTAMP是同义词，它们返回的是语句开始执行的时间点，而SYSDATE()返回的是函数被调用那一刻的时间。在一条复杂的多行INSERT或存储过程里，NOW()在整个语句执行期间是恒定不变的，SYSDATE()则会随着时间推移产生变化。

这意味着如果你在一个批量插入操作中依赖created_at标记时间，使用NOW()能保证同一批次的数据时间一致；用SYSDATE()则可能出现同一条语句内前后行时间戳不同。数据一致性场景下，我几乎只用NOW()。

再补充一个点：NOW()的精度由time_zone和系统时间共同决定，默认到秒。如果同一秒内多条记录需要区分先后顺序，用NOW(3)取毫秒，NOW(6)取微秒。注意DATETIME字段的精度定义必须和写入精度匹配，我见过字段是DATETIME(0)但代码里写NOW(6)的情况，时间精确到微秒写入后一截断，白忙活。

2.2 DATEDIFF 的“天”和 TIMESTAMPDIFF 的“段”，别混着用

DATEDIFF(date1, date2)返回的是两个日期相差的天数，它只关注“日期部分”，压根不管时间。只要跨过午夜，就算一天：

SELECT DATEDIFF('2024-01-02 00:00:01', '2024-01-01 23:59:59'); -- 输出1

看到没？实际只差了2秒，但因为跨过了零点，DATEDIFF按“自然日”算出来是1天。这在统计“今天和昨天差几天”时没问题，但如果你的业务是“两个时刻相差不满24小时”，DATEDIFF就完全不合适了，得用TIMESTAMPDIFF：

SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 23:00:00', '2024-01-02 01:00:00'); -- 输出2 SELECT TIMESTAMPDIFF(DAY, '2024-01-01 23:00:00', '2024-01-02 01:00:00'); -- 输出0

TIMESTAMPDIFF是根据单位计算边界的，跨过2小时算2小时，不足1天则不增加天数。选择哪个函数，取决于你到底想表达“自然日”还是“完整24小时段”。我处理订单超时逻辑时就踩过这个坑：一个订单23:59创建，次日00:01完成，用DATEDIFF算耗时是1天，严重误导结果，换成TIMESTAMPDIFF(MINUTE, ...)才算出了真实消耗时间。

2.3 月份计算的两种方式：DATE_ADD 与直接算

每月1号生成上个月账单这种需求，很多人会直接写：

WHERE create_time >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01')

这样不好，因为CURDATE() - INTERVAL 1 MONTH在某些日期上会出问题，比如3月31日减一个月是什么？MySQL会返回2月28日或29日，具体取决于当年是否是闰年。这种“结果正确但机制晦涩”的写法，在跨年、跨月时容易导致账单区间出错。

更稳的做法是用DATE_FORMAT先把当前日期归一到当月1号，再减一个月：

WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 1 MONTH

这样3月31日先变成3月1日，减一个月正好2月1日，无歧义。凡是涉及“按月切分区间”的逻辑，我都建议先归一再偏移，别直接用日期加减。这是我用真金白银的线上账单错误换来的经验。

2.4 把秒数转成“时分秒”或者反过来，一次性搞定

有时候你会拿到一个时长字段，单位是秒，要展示成“02:15:30”的格式。方法很简单：

SELECT SEC_TO_TIME(8130); -- 输出 02:15:30

反过来，要把“02:15:30”转回秒数：

SELECT TIME_TO_SEC('02:15:30'); -- 输出 8130

这两个函数在处理视频时长、通话时长、反垃圾系统里的处理耗时展示场景时极其顺手。要提醒的是，SEC_TO_TIME的结果类型是TIME，超过24小时它会显示成25:00:00而不是“天”，这点在报表展示时要注意。如果你需要“1天1小时”这种人类可读格式，还得自己拼一层。

2.5 日期格式化：DATE_FORMAT 的常见规格与坑

DATE_FORMAT的格式化串，我在项目里见到的坑基本集中在两点：一是把%m（月份，01-12）和%i（分钟，00-59）搞混，二是年份用了%y（两位年份）导致跨世纪数据混乱。

推荐一套稳定的模板：

DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') -- 2024-05-06 14:23:11 DATE_FORMAT(create_time, '%Y-%m-%d') -- 2024-05-06 DATE_FORMAT(create_time, '%Y%m%d') -- 20240506，适合做分区键

注意%H是24小时制，%h是12小时制，如果用后者还没带%p（AM/PM），下午的时间会被显示成下午的“小时数”，很容易让人误读。做日志流水展示，我统一用%H不带%p。

3. 聚合函数与分组统计：理解执行顺序才是王道

3.1 COUNT(*)、COUNT(1)、COUNT(字段) 别再争了

这三个写法的区别，网上说法很多，我直接说结论：

• COUNT(*)统计的是行数，包含NULL值行。
• COUNT(1)在逻辑上和COUNT(*)等价，也是统计所有行，MySQL优化器会把COUNT(1)当作COUNT(*)处理，性能基本没有差别。
• COUNT(字段)只统计该字段非NULL的行数。

问题常出在第三个。比如统计“有多少用户填了手机号”：

SELECT COUNT(mobile) FROM user;

若mobile列允许NULL，这个结果和“总用户数”不相等，很容易让人误以为数据丢失。你要的是“总行数”，就用COUNT(*)；你要的是“非空值的数量”，用COUNT(字段)。语义要明确，别混。

还有个小细节：COUNT(*)在InnoDB引擎下不能像MyISAM那样直接读表元数据返回，它需要实际扫描行数，所以大表上的COUNT(*)是很重的操作。如果业务上频繁需要精确总数，要么加汇总表，要么走缓存，别指望数据库硬算。

3.2 GROUP BY 背后的 ONLY_FULL_GROUP_BY

GROUP BY本身不复杂，但配合ONLY_FULL_GROUP_BY模式，很多人的SQL就当场暴毙。

MySQL 5.7及以后版本默认开启ONLY_FULL_GROUP_BY，要求SELECT中出现的非聚合列必须和GROUP BY列一致。比如：

SELECT user_id, MAX(create_time) FROM order GROUP BY user_id;

这条没问题，因为user_id在分组列表里。但如果你写成：

SELECT user_id, order_no, MAX(create_time) FROM order GROUP BY user_id;

在ONLY_FULL_GROUP_BY模式下直接报错，因为order_no并不在分组维度里，按理说“分组后取哪个order_no”存在语义歧义。老版本的MySQL允许这种写法，并且会随机从组内取一条——这个“随机”行为很容易掩盖真实的数据问题。我的建议是：不要跟这个模式对着干，既然开了就遵守它。如果你确实想取“分组内某个字段对应的值”，用窗口函数或子查询关联来实现，这也是更严谨的SQL写法。

顺带说一个GROUP BY常见的隐式行为：按字符串分组时，默认排序规则是字段的collation。如果你用utf8mb4_general_ci，它是大小写不敏感的，那么'abc'和'ABC'会被分到同一组里。这在做用户标签统计时可能不符合预期，要特别留意。

3.3 过滤别放错位置：WHERE 与 HAVING 的执行顺序

WHERE在分组之前过滤，HAVING在分组之后过滤，这条规则所有人都知道，但实际操作中还是会有人写错。比如统计每个部门的订单数，并且只保留订单数大于10的部门：

SELECT dept_id, COUNT(*) FROM order WHERE status = 'PAID' GROUP BY dept_id HAVING COUNT(*) > 10;

注意status = 'PAID'这类行级过滤必须放WHERE，放HAVING虽然也能得到正确结果，但会把所有状态的数据都分组计算一遍，白白多做一次聚合，大表上性能差距非常大。

HAVING里能不能写别名？MySQL是支持的，比如HAVING cnt > 10，前提是cnt在SELECT列表里定义了别名。但Oracle、SQL Server并不允许这样，跨数据库迁移时要小心。我在团队里规定：HAVING一律写完整聚合表达式，别依赖别名，这样可以少踩各种兼容性坑。

3.4 聚合函数搭配 CASE WHEN 做条件统计

这是很实用的技能：在同一组数据里统计多个维度的条件数量，不需要拆多个SQL。

比如统计本月订单里“已支付”“已取消”“待发货”各有多少单：

SELECT COUNT(CASE WHEN status = 'PAID' THEN 1 END) AS paid_cnt, COUNT(CASE WHEN status = 'CANCELED' THEN 1 END) AS canceled_cnt, COUNT(CASE WHEN status = 'PENDING' THEN 1 END) AS pending_cnt FROM order WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01');

CASE WHEN返回NULL时COUNT不计数，所以不需要写ELSE 0。如果要的是“占比”，用SUM(CASE WHEN ... THEN 1 ELSE 0 END)或更简洁的SUM(status = 'PAID')，MySQL里布尔表达式直接当数字用，TRUE是1，FALSE是0，这个写法很多开发者不熟，但真的很省事。

4. 逻辑函数与空值处理：NULL 才是万恶之源

4.1 IF、IFNULL、COALESCE、NULLIF 的取舍

这几个函数长得像，实际语义差别很大，挨个说。

IF(expr, val1, val2)是三目运算符，MySQL特有写法。常见用法：

SELECT name, IF(score >= 60, '及格', '不及格') FROM student;

IFNULL(val1, val2)只在val1为NULL时返回val2，简单直接：

SELECT IFNULL(remark, '暂无备注') FROM order;

但要小心：IFNULL只判断NULL，空字符串''不会被兜底。很多表单系统会把“未填写”存成空字符串而不是NULL，这时候IFNULL起不到作用，得配合NULLIF或者CASE：

SELECT IFNULL(NULLIF(remark, ''), '暂无备注') FROM order;

NULLIF(a, b)的含义是：如果a=b，返回NULL，否则返回a。上面的写法相当于“先把空字符串转成NULL，再用IFNULL兜底”，这是处理脏数据的经典组合拳。

COALESCE可以接收多个参数，返回第一个非NULL值：

SELECT COALESCE(mobile, email, wechat, '无联系方式') FROM user;

这个在“多选一取首非空”的场景是首选。它的执行逻辑是逐个判断，一旦遇到非NULL就停止，所以顺序很重要，把优先级高的字段放前面。

4.2 空值判断的标准姿势：IS NULL 而不是 = NULL

这是SQL新手最容易犯的错，也是线上BUG高发点：

SELECT * FROM user WHERE email = NULL;

这条SQL永远返回空结果。因为NULL表示“未知”，“未知 = 未知”的结果还是未知，不是真。正确写法：

SELECT * FROM user WHERE email IS NULL;

反过来，“非空”也别写!= NULL，要用IS NOT NULL。

还有一个细节：在WHERE条件里写NOT IN (subquery)时，如果子查询结果中包含NULL，那么整个NOT IN的结果会变成“未知”，导致一行都查不出来。这是一个极其隐蔽的坑，我排查过一起线上数据“消失”问题，最后定位到就是因为被排除列表里有NULL。替代方案是改用NOT EXISTS，它对NULL的处理更符合直觉。

4.3 用 COALESCE 和 CASE WHEN 控制计算字段

很多报表里的“同比”“环比”会有除数为0或结果为NULL的情况。比如计算增长率：

SELECT curr.month, ROUND((curr.amount - prev.amount) / NULLIF(prev.amount, 0) * 100, 2) AS growth_rate FROM monthly_sales curr LEFT JOIN monthly_sales prev ON prev.month = curr.month - 1;

这里NULLIF(prev.amount, 0)把分母为0的情况变成NULL，整个除法结果为NULL，ROUND返回NULL，展示层再统一处理为“——”。这比你在应用层写一堆if (prevAmount == 0)的判断干净得多。

如果你希望除数为0时直接显示0或某个默认值，套一层COALESCE：

COALESCE(ROUND((curr.amount - prev.amount) / NULLIF(prev.amount, 0) * 100, 2), 0)

这种层层嵌套的写法看起来有点唬人，但它表达的逻辑链非常清晰：先防除零，再算比例，再兜底空值。

5. 窗口函数：分组排序与更复杂的分析需求

5.1 窗口函数与 GROUP BY 的本质差别

窗口函数是MySQL 8.0引入的重磅特性，它的核心特点是：不会像GROUP BY那样把多行压缩成一行，而是保留每一行，同时把聚合结果附加到行上。

举个例子，查每个部门里工资最高的员工信息：

SELECT name, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM employee;

如果写成GROUP BY dept_id，你只能得到每个部门的最高工资值，拿不到员工名字。窗口函数则是在不破坏原始行结构的前提下，为每一行计算分组排名，然后你在外层过滤rk = 1就能拿到完整记录。

这个差异我看着简单，实际用起来对报表逻辑的简化效果非常明显。很多过去靠子查询、临时表实现的“分组后取某条”逻辑，用窗口函数几行就能写清楚。

5.2 ROW_NUMBER、RANK、DENSE_RANK 三兄弟的差异

这三个函数是窗口函数里的高频选手，区别只在于并列排名的处理：

• ROW_NUMBER()按顺序编号，即使值相同也分先后，编号连续不重复。适合做分页、去重。
• RANK()值相同并列，但会跳过后续编号，比如1、1、3。
• DENSE_RANK()值相同并列，且编号连续，比如1、1、2。

我做过一个成绩排名系统，需求是“并列名次不占后续名次”——第1名有3人，下一个还是第2名，那就用DENSE_RANK；需求是“第1名有3人，下一个名次是第4名”——抽签排名或比赛排名场景，就得用RANK。

日常分页取“每组前N条”时，我基本使用ROW_NUMBER()，因为它不会出现并列带来的数量膨胀。需要精确控制每组分页大小的情况下，ROW_NUMBER是最稳的选择。

5.3 用窗口函数优雅实现“分组TopN”与“相邻记录对比”

分组TopN是窗口函数最典型的应用场景，比如“每个分类下销量最高的前3个商品”：

SELECT category_id, product_name, sales FROM ( SELECT category_id, product_name, sales, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM product_sales ) t WHERE rn <= 3;

外层包的这层子查询非常关键，因为窗口函数在WHERE之后才生效，不能直接在WHERE里写rn <= 3。这个执行顺序问题我曾经在评审代码时反复强调过，一不留神就会踩中。

“相邻记录对比”是另一个高级玩法。比如计算每笔订单与上一笔订单的时间间隔：

SELECT order_id, create_time, LAG(create_time) OVER (ORDER BY create_time) AS prev_time, TIMESTAMPDIFF(MINUTE, LAG(create_time) OVER (ORDER BY create_time), create_time) AS diff_minutes FROM orders WHERE user_id = 123;

LAG取上一行值，LEAD取下一行值。这在分析用户下单频次、会话间隔、连续登录天数时都非常好用。再配合IF判断diff_minutes是否超过阈值，就能把“会话”从行为流里切分出来。

5.4 连续记录的模拟窗口：用户变量法

MySQL 5.7及以下版本不支持窗口函数，但生产环境里存量系统还不少。我在老版本上实现“按用户分组排序”时，会借助用户变量：

SELECT user_id, amount, create_time, @rn := IF(@prev_user = user_id, @rn + 1, 1) AS rn, @prev_user := user_id FROM orders, (SELECT @rn := 0, @prev_user := NULL) vars ORDER BY user_id, create_time DESC;

这里有一个执行顺序的坑：MySQL会先按ORDER BY排好序，再逐行读取并计算用户变量，所以ORDER BY必须出现在SELECT里之前？写反了，正确说法是：计算用户变量的行读取顺序依赖最终排序结果，因此ORDER BY user_id, create_time DESC必须保证先按用户再按时间排序，这样同一个用户的所有记录才会连续出现，变量逻辑才能正确工作。

用户变量法的性能比窗口函数差不少，也会受到优化器行为影响，所以我只建议在无法升级MySQL版本的情况下做过渡方案。新项目一律用窗口函数。

6. 写在最后的几条实在经验

这篇文章看起来讲的是一个个函数，但串起来其实是同一件事：SQL不是“能查出结果”就行，而是“在任何数据形态下都稳定、正确地查出结果”。我这些年排查线上问题，发现大部分和数据相关的故障，根源不是表结构设计得不好，而是函数使用中对NULL、边界、隐式规则的理解不到位。

几句个人体会：

第一，写任何涉及日期的SQL，先自己脑子里过一遍跨年、跨月、闰年、时区的边界情况。你把2024-02-29这种日期顺手写进去测一下，比上线后让用户帮你测强一万倍。

第二，所有涉及NULL判断的地方，严格按照“要么IS NULL要么COALESCE兜底”的规范写，不要依赖三目运算符或者隐式转换。因为你不知道未来的数据源会不会突然给你灌入一堆空值。

第三，函数不是越高级越好。能用普通字段索引解决的，不要绕一层函数；能明确语义的写法，不要写那种“碰巧能跑”的SQL。可读性、稳定性、可维护性，永远排在“看起来精简”前面。

最后再分享一个小技巧：我习惯给每个常用函数建立一个“边界行为测试表”，里面放一行SELECT包含各种边界值，比如空字符串、NULL、超大数、负数、零点时间。遇到业务问题，先跑一遍测试表，就能快速确认是函数行为还是业务逻辑的问题。这套方法帮我省下了大量的排查时间，你也可以试试。