前阵子帮同事排查一个报表慢查询光看 SQL 前几行我还觉得写得挺标准分组、聚合、排序都齐全结果 EXPLAIN 一出来type 是 ALLrows 扫了几百万行。问题出在哪出在WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-07-01这句话上。DATE_FORMAT 本身没错错的是把它用在了索引字段上硬生生把一个范围查询变成了全表扫描。MySQL 内置函数看着简单大部分人日常翻来覆去也就用 CONCAT、DATE_FORMAT、ROUND 这几个可真到了报表统计、数据清洗、接口对接的时候函数用得对不对、用得巧不巧直接决定 SQL 是跑 200 毫秒还是 6 秒决定统计结果是准的还是错的。这篇东西我不打算按官方文档把函数逐个抄一遍而是按业务场景把这些年真正用得上、用得频繁、还特别容易踩坑的内置函数重新捋一遍。适合刚接触 MySQL 的同学建立知识框架也适合写过一两年 SQL 但没系统整理过函数用法的朋友查漏补缺。1. 先用一个真实事故说清楚函数用错不是小事1.1 一次统计事故一条本该走索引的SQL跑了6秒当时的需求很简单统计最近 30 天每天的订单金额同事第一版 SQL 长这样SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, SUM(order_amount) AS amount FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 29 DAY), %Y-%m-%d) GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);单看结果数据没毛病但它暴露了两个典型问题。第一WHERE DATE_FORMAT(create_time, %Y-%m-%d) ...把 create_time 字段用函数包了一层MySQL 的普通 B 树索引是按原始字段值排序的一旦对字段做完函数计算再比较索引就失效了优化器只能老老实实全表扫描。orders 表几百万行这条 SQL 跑了 6 秒多。第二明明可以用create_time 2024-07-01 AND create_time 2024-08-01这种半开区间去扫索引何必非要把每一行都格式化一遍再去比较呢这就像你有一本按电话号码排序的通讯录想找某个号段的人结果你偏要把每个号码都重新排版之后再翻那不是给自己找事吗。函数本身没有错错的是使用位置和使用方式。后面第 7 章我会单独讲完整的排查链路这里先记住一个结论在 WHERE 条件里对索引字段套函数是大忌。1.2 内置函数分类地图这么多函数先学哪类MySQL 内置函数看着多实际按用途也就那么几大类先建个全局认知比死记硬背重要得多。分类典型函数常用场景字符串函数CONCAT、SUBSTRING_INDEX、LENGTH、REPLACE拼接、截取、清洗数据数值函数ROUND、TRUNCATE、CEIL、FLOOR、MOD金额、页数、取整计算日期时间函数DATE_FORMAT、STR_TO_DATE、DATEDIFF、NOW报表统计、时间窗口计算流程控制函数IF、IFNULL、NULLIF、CASE WHEN逻辑判断、空值兜底聚合函数COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT分组统计、行转列JSON 函数JSON_EXTRACT、JSON_ARRAYAGG处理 JSON 字段、对接接口加密函数MD5、SHA2、AES_ENCRYPT脱敏、校验、签名信息函数VERSION、DATABASE、LAST_INSERT_ID诊断、脚本开发日常开发里字符串、日期、流程控制、聚合这四类占掉了 90% 以上的使用场景必须熟练掌握JSON 和加密函数在对接外部系统、处理半结构化数据时会用到信息函数不常写但关键时刻能救命。下面几章我就按这个优先级展开。2. 字符串函数实战拼接、截取、清洗的完整思路2.1 CONCAT 与 CONCAT_WS拼接时最容易忽略的NULL问题CONCAT 大家都会用但有个坑很多人是上线之后才发现的只要有一个参数是 NULL整个结果就是 NULL。比如生成用户展示名SELECT CONCAT(first_name, ·, last_name) FROM users;如果last_name是空字符串还好如果是 NULL这段拼接结果整个变成 NULL。此时改用 CONCAT_WS 就稳妥了SELECT CONCAT_WS(·, first_name, last_name) FROM users;CONCAT_WS 里的 WS 是 With Separator 的意思第一个参数是分隔符后面是要拼接的字段。它最大的好处是会自动跳过 NULL不会因为一个 NULL 字段把整条数据搞没。在公司里做账号体系、通讯录展示这类需求我基本只用 CONCAT_WS。2.2 LENGTH 与 CHAR_LENGTH中英混排下的统计陷阱LENGTH返回的是字节数CHAR_LENGTH返回的是字符数。在 UTF-8 编码下一个汉字占 3 个字节一个英文字母占 1 个字节。所以SELECT LENGTH(你好abc); -- 93*23 SELECT CHAR_LENGTH(你好abc); -- 5面试里经常有人被问到这个很多人的第一反应是“这不都一样吗”等你把一个包含中文的用户昵称拿去SUBSTRING(name, 1, 10)截取截出来长度对不上、界面换行错乱的时候才明白区别有多大。实际业务中如果要限制用户昵称最长 20 个字符应该用CHAR_LENGTH(name) 20而不是LENGTH(name)。否则中英文混合输入时判断结果会偏离用户直觉。2.3 SUBSTRING_INDEX处理路径、编号这类半结构化字符串的利器SUBSTRING 是常规截取参数从 1 开始而且支持负数从末尾倒着数。但更实用的是 SUBSTRING_INDEX它是按分隔符截取的。举个例子业务表里存了一个文件路径/upload/2024/07/report_10086.pdf想取文件名一句话搞定SELECT SUBSTRING_INDEX(/upload/2024/07/report_10086.pdf, /, -1); -- 结果report_10086.pdf第三个参数是正数表示从左往右数到第几个分隔符取它左边的部分是负数表示从右往左数到第几个分隔符取它右边的部分。处理编号也常用它比如订单号ORD-2024-000123想取中间年份SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ORD-2024-000123, -, 2), -, -1); -- 结果2024这种嵌套写法在数据清洗时很管用比写一大段正则表达式直观得多。2.4 REPLACE 和 FIND_IN_SET清洗与集合判断的实用姿势REPLACE 在数据清洗里出场率非常高尤其是处理用户输入的空格、手机号里的横线、金额里的逗号SELECT REPLACE(138-1234-5678, -, );需要注意 REPLACE 是替换所有匹配项不是只替换第一个。如果要替换第一个得用INSERT函数实用性反而没那么高。FIND_IN_SET 也是个冷门但好用的函数它判断某个值是否在逗号分隔的字符串集合中SELECT FIND_IN_SET(mysql, redis,mysql,kafka); -- 结果2代表第二个元素我没少用这个函数处理老系统里把标签存成1,2,3这种逗号分隔字符串的脏数据。但要提醒一句FIND_IN_SET 不走索引数据量大了之后这种过滤条件性能会很难看能改表结构的尽早拆成关联表。3. 日期时间函数坑最多、也最能拉开水平差距的一块3.1 NOW() 与 SYSDATE()看似相同实际行为不同NOW() 和 SYSDATE() 都能返回当前日期时间但在一条复杂的 SQL 里它们有本质区别。NOW() 取的是语句开始执行时的时间整条 SQL 过程中保持不变SYSDATE() 是函数真正执行到的那一刻的时间。如果一条 SQL 里有子查询、关联查询跑了比较久SYSDATE() 在不同行上可能拿到不同的时间这会让统计结果变得不可复现。更麻烦的是在基于 binlog 的主从复制中SYSDATE() 的不确定性可能导致从库和主库数据不一致。官方其实也建议不要随便用 SYSDATE除非你明确知道自己在干什么。日常开发统一用 NOW() 或 CURRENT_TIMESTAMP 写默认值就够了CREATE TABLE orders ( id INT PRIMARY KEY, create_time DATETIME DEFAULT CURRENT_TIMESTAMP );3.2 DATE_FORMAT 与 STR_TO_DATE报表输出和数据清洗的搭档DATE_FORMAT 是把日期格式化成指定字符串STR_TO_DATE 是它的逆操作把字符串解析成日期。报表里最常用的几个格式符列出来格式符含义示例%Y四位年份2024%y两位年份24%m两位月份07%c月份无前导零7%d两位日01%e日无前导零1%H24小时制两位14%i分钟两位30%s秒两位59实际统计时按天分组就是GROUP BY DATE_FORMAT(create_time, %Y-%m-%d)按小时分组就是GROUP BY DATE_FORMAT(create_time, %Y-%m-%d %H:00)。反过来的场景更多出现在数据接入环节。上游给了一个字符串2024/07/01 12:30:00想直接比较大小必须先转成日期类型SELECT STR_TO_DATE(2024/07/01 12:30:00, %Y/%m/%d %H:%i:%s);格式符对不上结果就会是 NULL这个报错方式很隐蔽我在接入第三方数据时踩过不止一次。3.3 DATEDIFF 与 TIMESTAMPDIFF一字之差语义差之千里两个函数都算日期差但用法完全不一样-- DATEDIFF参数是 end, start返回相差的天数忽略时间部分 SELECT DATEDIFF(2024-07-05, 2024-07-01); -- 4 -- TIMESTAMPDIFF参数是 unit, start, end第一个参数指定单位 SELECT TIMESTAMPDIFF(DAY, 2024-07-01, 2024-07-05); -- 4 SELECT TIMESTAMPDIFF(MONTH, 2024-01-01, 2024-07-01); -- 6 SELECT TIMESTAMPDIFF(YEAR, 2000-06-01, 2024-06-01); -- 24面试时我经常问一句“算用户年龄你用什么”有人回答 DATEDIFF(now(), birth_date) / 365这样算出来的年龄会因为闰年而有偏差。正确姿势是SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) FROM users;TIMESTAMPDIFF 是严格按时间单位边界计算的统计月数、年龄、时长这类需求优先用它。3.4 UNIX_TIMESTAMP 与 FROM_UNIXTIME前后端时间协作的关键做接口联调时很多系统统一用秒级或毫秒级时间戳传输MySQL 里对应两个函数-- 日期转时间戳 SELECT UNIX_TIMESTAMP(2024-07-01 00:00:00); -- 1719763200 -- 时间戳转日期 SELECT FROM_UNIXTIME(1719763200); -- 2024-07-01 00:00:00前端传的是毫秒时间戳时要转成秒再比较不然日期会差得离谱WHERE create_time FROM_UNIXTIME(1719763200000 / 1000)还有一个实用技巧MySQL 的 NOW 函数可以带精度NOW(3)返回毫秒精度的时间SELECT NOW(3); -- 2024-07-01 12:30:00.123如果需要毫秒时间戳可以用UNIX_TIMESTAMP(NOW(3)) * 1000但结果里末尾三位是有点特殊的最好先小范围验证再上线。4. 数值与流程控制函数写 SQL 也可以像写代码4.1 ROUND 和 TRUNCATE一个四舍五入一个直接砍掉数值计算最常见的坑就是“我以为这个函数是干这个的结果它是干那个的”。ROUND 和 TRUNCATE 就是典型SELECT ROUND(2.567, 2); -- 2.57 SELECT TRUNCATE(2.567, 2); -- 2.56ROUND 四舍五入TRUNCATE 直接丢掉指定位数后面的数字。算金额、算评分时两者结果可能差一分钱做财务对账时这种差异会被无限放大所以必须先明确业务要求的是“四舍五入”还是“截断保留”。另外对金额做计算强烈建议字段用 DECIMAL 而不是 FLOAT/DOUBLE。浮点数在计算机里本身就有精度误差0.1 0.2这种经典问题在 SQL 里一样存在。把金额字段设计成DECIMAL(10, 2)再做聚合计算才能保证对得上账。4.2 IFNULL、NULLIF、COALESCENULL 处理的选择题NULL 在 SQL 里是个特殊存在和 NULL 做任何比较都不会为真所以空值兜底是写 SQL 的基本功。IFNULL 最简单两个参数第一个是 NULL 就返回第二个SELECT IFNULL(remark, 无备注) FROM orders;COALESCE 更通用可以传多个参数返回第一个非 NULL 的值SELECT COALESCE(high_priority_remark, normal_remark, 无备注) FROM orders;NULLIF 的语义容易和 IFNULL 记混它是两个参数相等时返回 NULL否则返回第一个参数。一个非常经典的用法是防止除零错误SELECT SUM(amount) / NULLIF(COUNT(*), 0) FROM orders;COUNT(*) 是 0 时NULLIF 把除数变成 NULL除法结果就是 NULL而不是直接报 “Division by 0” 错误。4.3 CASE WHEN 做统计一条 SQL 算出多个维度的营收流程控制函数里CASE WHEN 是结构化统计的利器。比如要统计订单表里各支付状态的金额不用写多条 SQL一条就够了SELECT SUM(CASE WHEN pay_status paid THEN order_amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN pay_status pending THEN order_amount ELSE 0 END) AS pending_amount, SUM(CASE WHEN pay_status refunded THEN order_amount ELSE 0 END) AS refunded_amount FROM orders WHERE create_time 2024-07-01 AND create_time 2024-08-01;这种写法比在应用层循环查数据库高效得多。IF 函数也能实现类似效果但只能处理简单的二选一多分支可读性远不如 CASE WHEN。面试中遇到“行转列”“同时统计多个条件”这类题CASE WHEN 都是标准答案。5. 聚合函数与 GROUP_CONCAT从算总数到行转列5.1 COUNT(*) 与 COUNT(字段) 的语义与性能差异COUNT 是聚合函数里最容易出错的不是因为用起来复杂而是因为很多人在面试时说不清区别。COUNT() 统计的是行的数量不管某列是不是 NULLCOUNT(1) 也统计行数在 MySQL 8.0 里和 COUNT() 性能基本没有差异但 COUNT(字段) 会忽略该字段为 NULL 的行。举个例子订单表里有 100 行其中 10 行 pay_time 为 NULL那么SELECT COUNT(*) FROM orders; -- 100 SELECT COUNT(pay_time) FROM orders; -- 90做数据对账时要格外小心统计已支付订单数应该是COUNT(pay_time)而不是COUNT(*)否则会多算。COUNT(DISTINCT 字段) 可以去重计数但它同样忽略 NULL。5.2 SUM、AVG、MAX、MIN 中 NULL 的行为差异SUM、AVG、MAX、MIN 聚合时都会忽略 NULL所以 0 和 NULL 在聚合结果里是两码事。比如 10 行数据里 9 行金额是 1001 行金额是 NULLAVG 的结果是 100 而不是 90 或 90.9。这会造成一个很隐蔽的偏差如果你在明细里把 NULL 当 0 处理但聚合函数自动忽略了它那么平均值和你预期的可能不一致。处理方式要么是清洗数据时把 NULL 统一改成 0要么在聚合前显式用 IFNULL 或 COALESCE 兜底SELECT AVG(IFNULL(order_amount, 0)) FROM orders;5.3 GROUP_CONCAT 行转列的坑1000字节截断GROUP_CONCAT 能把多行数据拼成一个字符串做行转列特别方便SELECT teacher_id, GROUP_CONCAT(student_name ORDER BY student_id SEPARATOR 、) FROM class_list GROUP BY teacher_id;但有一个非常隐蔽的坑GROUP_CONCAT 默认最大长度是 1024 字节超过这个长度结果会被静默截断不会报错。想象一下你在做一个导出功能导出的名单后面悄悄少了好几个人排查半天都没想到是这里的问题。解决方式是在执行 SQL 前调整会话变量SET SESSION group_concat_max_len 102400;如果是线上长期使用建议在配置文件 my.cnf 里加上group_concat_max_len 102400。面试时主动讲出这个坑会显得你确实踩过。5.4 窗口函数与内置函数的配合8.0 的统计新姿势MySQL 8.0 引入窗口函数后很多以前要用复杂自连接才能实现的需求变得异常简单。窗口函数不完全算内置函数但它经常和内置函数配合使用分不开。举个最常见的例子查每个用户最近的一笔订单。SELECT user_id, order_amount, create_time FROM ( SELECT user_id, order_amount, create_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1;再比如算累计销售额SELECT sale_date, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_amount FROM daily_sales;如果你还在用 5.7这类需求只能靠用户变量或自连接写法又绕又容易错。有升级条件的话8.0 的窗口函数值得好好用起来。6. JSON 与加密函数现代业务绕不开的两类函数6.1 JSON_EXTRACT 与 - 操作符不靠 ORM 也能解析 JSONMySQL 从 5.7 开始支持 JSON 类型配套的函数也越来越完善。最常见的需求是从 JSON 字段里取某个属性SELECT JSON_EXTRACT(info, $.name) FROM users WHERE id 1;JSON_EXTRACT 返回的是 JSON 类型的值如果取字符串会带双引号。-运算符和它等价而-运算符合并了 JSON_UNQUOTE直接返回不带引号的字符串SELECT info-$.name FROM users WHERE id 1;-实际使用中更顺手写接口的时候基本不用再手动去引号了。JSON 聚合函数也很实用JSON_ARRAYAGG 把多行聚合成一个 JSON 数组比 GROUP_CONCAT 拼字符串更结构化SELECT teacher_id, JSON_ARRAYAGG(student_name) FROM class_list GROUP BY teacher_id;6.2 加密函数能做什么不能做什么MD5、SHA1、SHA2 这几个函数在 MySQL 里都有但很多人对它们的定位有误解。MD5 和 SHA 系列适合做数据完整性校验、接口签名、脱敏场景。比如导数据时比对两边的 MD5 值是否一致或者给手机号脱敏存 MD5 用于精确匹配。它们不适合直接当密码存储方案来用因为不带盐彩虹表一查就能还原弱密码。SELECT MD5(hello); -- 5d41402abc4b2a76b9719d911017c592 SELECT SHA2(hello, 256); -- 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824AES_ENCRYPT / AES_DECRYPT 可以做对称加密但密钥管理是个大问题。本质上密钥放在 MySQL 函数里和放在配置里没有太大区别数据库一旦泄露密钥也保不住。涉及用户密码这种敏感信息我的建议永远是应用层使用加盐哈希算法处理完后再把摘要字符串存进数据库不要把加密逻辑放在 SQL 里。7. 对字段套函数引发的索引失效一次全表扫描的排查7.1 一个慢查询的完整排查链路回到开头那个 6 秒的慢查询完整的排查过程值得写下来因为这套思路是通用的。第一步拿到慢查询 SQL 后先 EXPLAINEXPLAIN SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, SUM(order_amount) FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-07-01 GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);EXPLAIN 结果里 type 是 ALLkey 是 NULLrows 显示 300 多万。看到这三个信息基本可以断定索引没有生效全表扫描了。第二步检查 WHERE 条件发现 create_time 字段被DATE_FORMAT包住。普通索引存储的是字段原始值而DATE_FORMAT(create_time, ...)是每行算出来的结果优化器没办法对这个计算结果直接建索引匹配只能放弃索引。第三步改写 SQL把函数从 WHERE 条件里抹掉改成基于原始字段的范围条件SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, SUM(order_amount) FROM orders WHERE create_time 2024-07-01 00:00:00 AND create_time 2024-08-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);这里有个细节WHERE 里不再套函数但 SELECT 和 GROUP BY 里保留 DATE_FORMAT 没问题因为函数是作用于已经筛选出来的结果集不影响索引扫描。改写后 EXPLAIN 的 type 变成 range查询耗时从 6 秒降到几十毫秒。类似的写法还有WHERE YEAR(create_time) 2024也应该改成create_time 2024-01-01 AND create_time 2025-01-01。7.2 隐式类型转换另一个让索引失效的隐形杀手除了显式函数包裹隐式类型转换也会让索引失效。最常见的是字符串字段和数字比较SELECT * FROM users WHERE mobile 13812345678;mobile 是 VARCHAR 类型右边是数字字面量。MySQL 会把 mobile 转成数字再比较等于对字段进行了隐式 CAST索引自然用不上。正确写法是给数字加引号SELECT * FROM users WHERE mobile 13812345678;EXPLAIN 之前往往看不出问题数据量小的时候全表扫描也不慢等数据涨到千万级才爆发。排查的时候如果发现 type 是 ALL 而条件字段明明有索引就多看一眼字段类型和值类型是否一致。字符串编码不一致也会引发类似问题跨库关联时如果两边字符集不同记得先统一成 utf8mb4。7.3 函数索引MySQL 8.0 给的补救方案如果业务确实有大量按日期格式化、按月份分组的统计需求又不想每次都用半开区间改写MySQL 8.0.13 之后提供了函数索引。在表上直接建表达式索引CREATE INDEX idx_create_month ON orders ((DATE_FORMAT(create_time, %Y-%m)));注意表达式外面必须再加一层括号这是函数索引的语法要求。它的原理相当于把表达式的计算结果隐式存成了一个生成列再对这个生成列建索引这样查询时就能直接走索引。不过我的经验是函数索引能解决“查询性能”问题解决不了“统计口径混乱”问题。业务上真正长期要用的统计维度我更建议新建一个冗余字段或者维度表把日期格式化结果在写入时就存好查询时直接等值匹配简单、可靠、通用。8. 面试和实际排错中经常被问到的几个函数问题8.1 为什么 GROUP BY 中使用函数要小心GROUP BY 里对字段套函数同样有索引失效的问题。比如按天统计时用GROUP BY DATE_FORMAT(create_time, %Y-%m-%d)MySQL 没法直接利用 create_time 上的索引来做分组扫描通常要先全表扫一遍。哄骗过优化器的写法是在子查询里先生成 date 字段再分组SELECT day, SUM(amount) FROM ( SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, amount FROM orders WHERE create_time 2024-07-01 AND create_time 2024-08-01 ) t GROUP BY day;外层分组用的是已经算好的 day相当于把计算放在索引筛选之后的子集上比直接在大表上 GROUP BY 函数要稳。8.2 WHERE 不能直接使用聚合函数只能 HAVING很多人第一次写“分组后过滤”的 SQL 都会踩这个错SELECT user_id, SUM(amount) AS total FROM orders WHERE SUM(amount) 1000 -- 这行会报错 GROUP BY user_id;WHERE 是逐行过滤发生在聚合之前而 SUM 这类聚合函数要等 GROUP BY 分组之后才能计算所以过滤聚合结果只能用 HAVINGSELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id HAVING total 1000;理解了这个执行顺序很多报错就不用死记硬背了。还有一个值得注意的细节能在 WHERE 里过滤掉的行别留到 HAVING 再过滤因为先缩小数据规模再做分组聚合性能会好很多。8.3 几个高频面试题的标准答法函数相关的高频面试题其实都能用一句话点出核心CHAR_LENGTH 和 LENGTH 的区别一个按字符数一个按字节数UTF-8 下中文占 3 字节。DATEDIFF 和 TIMESTAMPDIFF 的区别前者固定按天差算后者可以按年、月、天、时、分、秒算算年龄用后者。COUNT(*)、COUNT(1)、COUNT(字段) 的区别前两个统计行数第三个忽略 NULL。ROUND 和 TRUNCATE 的区别一个四舍五入一个截断。IFNULL 和 NULLIF 的区别IFNULL 是空值兜底NULLIF 是两值相等返回 NULL。日期格式化里 %Y 和 %y 的区别四位年份和两位年份。对索引字段使用函数后查询变慢怎么优化改写为范围条件或者用 MySQL 8.0 的函数索引。如何把多行数据拼成一个字符串GROUP_CONCAT注意设置 group_concat_max_len。这些题我平时面试候选人时基本会挑两三个问。能答上来的不少能讲清楚“业务上什么时候用”“底层为什么这样设计”的其实不多。面试官看到你能把函数背后的执行逻辑讲明白通常会高看一眼。
