1. 内置函数全貌先搞清楚MySQL口袋里装了哪些工具1.1 为什么我们绕不开内置函数工作里写SQL绕不开的一件事就是内置函数。打交道久了你会发现很多报表需求、统计逻辑、数据清洗的活靠的其实都是MySQL那一批内置函数。有人觉得这是“死记硬背”的东西我不太同意——函数背后是MySQL帮你封装好的通用逻辑理解透了不仅能少写一大半代码还能避免很多应用层与数据库层之间的“翻译”成本。举个最直白的例子业务方要一份“上个月每天的订单量”你可以在Java里用SimpleDateFormat去解析时间字符串再分组统计但直接在SQL里用DATE_FORMAT(order_time, %Y-%m-%d)配合GROUP BY一条语句就出结果。哪个更省事写过的人都知道。而且把计算放到数据库里能保证所有报表、接口、临时查询用的是同一套逻辑不会出现“这边取数格式是yyyy-MM-dd那边又变成yyyy/MM/dd”的经典对不上问题。内置函数解决的痛点很明确减少重复造轮子、保证逻辑一致、提升查询可读性。不管你是刚入门的新手还是写了好几年SQL的老手系统性过一遍MySQL内置函数都能有收获。新手是学语法学用法老手是借Function库理清自己平时零散的经验顺便看看有没有漏掉什么好用的“冷门”函数。1.2 五类高频工具箱MySQL内置函数数量很多官方文档列出来能有几十上百个但实际工作中高频使用的其实集中在几大类。按我的习惯会把这些函数分成五个抽屉函数分类典型场景代表函数字符串函数清洗、截取、拼接、格式化文本CONCAT、SUBSTRING_INDEX、REPLACE、TRIM数值函数计算、取整、精度控制、随机数ROUND、FLOOR、CEIL、MOD、RAND日期时间函数时间格式化、区间计算、增减日期NOW、DATE_FORMAT、DATEDIFF、DATE_ADD流程控制函数在SQL里写分支逻辑、处理空值IF、CASE WHEN、IFNULL、NULLIF聚合统计函数分组汇总、去重统计、拼接分组值COUNT、SUM、AVG、GROUP_CONCAT先记这张分类地图再往抽屉里填细节比直接背函数名高效得多。下面我会按这个框架逐个拆每个函数都带上“什么时候用、怎么写、容易踩什么坑”。2. 高频函数逐个拆解从语法到实战2.1 字符串函数拼接、截取、替换的日常操作字符串处理是写SQL时最频繁的需求我先把最常用的几个拉出来讲透。拼接CONCAT与CONCAT_WSCONCAT(str1, str2, ...)负责把多个字符串拼成一个。比如生成“用户名-手机号-会员等级”这样的复合字段SELECT CONCAT(name, -, phone, -, level) AS user_info FROM users;第一个坑马上就来了CONCAT里只要有一个参数是NULL整个结果就是NULL。比如上一条SQL里phone为空那user_info整个就成了NULL前端拿到的是一坨空。解决办法有几种最省心的就是用CONCAT_WSWS是With Separator带分隔符拼接SELECT CONCAT_WS(-, name, phone, level) AS user_info FROM users;CONCAT_WS在拼接时会自动跳过NULL值只在非NULL值之间插入分隔符非常适合做“多字段拼一段话”的场景比如导出地址、生成展示列。注意它是跳过NULL不是把NULL变成空字符串两者有区别。截取SUBSTRING与SUBSTRING_INDEXSUBSTRING(str, pos, len)从指定位置截取指定长度的字符位置从1开始数。想截取“2024-06-15”里的“06”SELECT SUBSTRING(2024-06-15, 6, 2); -- 结果为 06真正业务中用得更多的是SUBSTRING_INDEX(str, delimiter, count)它按分隔符截取count为正数从左往右取负数从右往左取。“split后取某一段”这个需求上一句话就能完成-- 取出IP地址的三段比如 192.168.1.100 SELECT SUBSTRING_INDEX(192.168.1.100, ., 3); -- 结果 192.168.1 SELECT SUBSTRING_INDEX(192.168.1.100, ., -1); -- 结果 100这个函数在清洗日志、解析路径类字符串时特别好用相当于把“按分隔符拆数组再取元素”的操作压成了一行。长度LENGTH与CHAR_LENGTH的区别新手最容易踩的坑就在这。LENGTH()返回的是字节数CHAR_LENGTH()返回的是字符数。对英文字母和数字没区别但一遇到中文就变了在UTF-8字符集下一个中文字符占3字节。SELECT LENGTH(数据); -- 结果 6三个字节一个字 SELECT CHAR_LENGTH(数据); -- 结果 2做长度校验、截断判断时一定要想清楚你要的是字符个数还是字节数。比如昵称长度限制“最多10个字符”就该用CHAR_LENGTH如果你是在计算存储空间那用LENGTH。替换与去空格REPLACE、TRIM、LTRIM、RTRIMSELECT REPLACE(MySQL内置函数手册, 手册, 大全); -- MySQL内置函数大全 SELECT TRIM( abc ); -- abc去两边 SELECT LTRIM( abc ); -- abc去左边 SELECT RTRIM(abc ); -- abc去右边TRIM还有一个进阶用法去掉指定字符TRIM(BOTH - FROM --abc--)虽然用得少但遇到“去掉字符串两端固定符号”的需求时很顺手。-- 查找字符串包含子串的位置 SELECT LOCATE(函数, MySQL内置函数); -- 结果 7LOCATE和INSTR都是找子串位置只是参数顺序相反。它们和LIKE搭配使用在模糊搜索场景下很有用不过注意在WHERE里用函数套列名会让该列的索引失效这个后面专门讲。2.2 数值函数取整、四舍五入与精度控制的坑数值函数本身不难难点在于“你以为的结果”和“实际结果”之间的误差。ROUND的四舍五入陷阱SELECT ROUND(2.675, 2); -- 你可能以为结果是 2.68实际是 2.67惊不惊喜根源在浮点数精度2.675在计算机里存的其实是约等于2.6749999……四舍五入后就掉了半档。处理金额计算时也遇到过类似问题解决方案一般是用DECIMAL类型存储金额或者结合CAST转换降低误差。另外ROUND有另一种用法ROUND(x, -1)四舍五入到十位ROUND(x, -2)到百位做粗略统计时能用上。FLOOR、CEIL与业务边界FLOOR向下取整CEIL向上取整注意负数方向会反直觉SELECT FLOOR(-3.14); -- -4不是-3 SELECT CEIL(-3.14); -- -3分页算法里有个经典写法(pageNo - 1) * pageSize有人喜欢用CEIL/ 1来凑整其实直接(pageNo - 1) * pageSize就行别绕弯路。MOD、ABS、RAND与格式化SELECT MOD(17, 5); -- 取模结果 2 SELECT ABS(-12); -- 绝对值 SELECT RAND(); -- 0到1之间的随机数 SELECT ROUND(RAND() * 10, 0); -- 0到10的随机整数RAND()配合ORDER BY可以做抽奖、随机推荐比如查5条随机记录ORDER BY RAND() LIMIT 5。但这玩意儿在数据量大时性能很差因为要把所有行打乱几十万行可能还能忍上千万就别玩了。格式化数字FORMATFORMAT(1234567.891, 2)结果1,234,567.89并且结果是字符串类型带千分位逗号。报表导出时很好用但要注意它可能会改变数据类型如果后续还要拿结果做计算别直接引用先转回数值。2.3 日期时间函数格式化、区间计算与周期判断日期时间是内置函数中使用频率最稳、坑也最多的一类几乎所有业务表都带时间字段。当前时间NOW()、CURDATE()、CURTIME()、SYSDATE()SELECT NOW(); -- 当前日期时间2024-07-20 15:30:00 SELECT CURDATE(); -- 当前日期2024-07-20 SELECT CURTIME(); -- 当前时间15:30:00 SELECT SYSDATE(); -- 也是当前时间但一般在语句执行时取这里有个细节NOW()是语句开始执行的时刻SYSDATE()是调用它的时刻。长查询里两者可能会有时间差涉及日志、流水记录时要注意。日期格式化DATE_FORMAT与STR_TO_DATEDATE_FORMAT是查询分析里最常用的函数没有之一。它的格式串很多记住高频的几个就够SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2024-07-20 15:30:00 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 2024年07月20日 SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i); -- 2024-07-20 15:30反过来把字符串转成日期用STR_TO_DATESELECT STR_TO_DATE(2024年7月20日, %Y年%c月%d日);要注意%c表示“数字月份前面没0补0”而%m则要求两位月份格式串和字符串必须严格对应才能解析成功。接口接入端经常会传各种格式的时间字符串STR_TO_DATE就是你的翻译器。日期加减DATE_ADD与DATE_SUBSELECT DATE_ADD(2024-07-20, INTERVAL 1 DAY); -- 2024-07-21 SELECT DATE_ADD(2024-07-20, INTERVAL 1 MONTH); -- 2024-08-20 SELECT DATE_SUB(2024-07-20, INTERVAL 1 WEEK); -- 2024-07-13 SELECT DATE_ADD(2024-07-20 10:30:00, INTERVAL 90 MINUTE); -- 2024-07-20 12:00:00这个函数在“计算到期时间”“回溯统计窗口”时几乎是必用的。比如算“过去7天活跃用户”起点就是DATE_SUB(CURDATE(), INTERVAL 7 DAY)。注意INTERVAL后面可以跟DAY、HOUR、MINUTE、MONTH、YEAR等甚至可以组合INTERVAL 1:30 HOUR_MINUTE但组合写法读起来绕不如分开算。区间计算DATEDIFF与TIMESTAMPDIFFDATEDIFF(date1, date2)返回两个日期相差的天数顺序很重要是date1 - date2SELECT DATEDIFF(2024-07-20, 2024-07-01); -- 19算“年龄”“工龄”这类需要精确到年或月的场景用TIMESTAMPDIFF更合适它的单位可以指定SELECT TIMESTAMPDIFF(YEAR, 1990-05-15, CURDATE()); -- 年龄 SELECT TIMESTAMPDIFF(MONTH, 2024-01-01, 2024-07-01); -- 6提取年月日YEAR()、MONTH()、DAY()等SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), DAYOFWEEK(NOW());这些函数的价值在于分组统计近12个月的销量按月分组GROUP BY YEAR(create_time), MONTH(create_time)妥妥的报表神器。但注意DAYOFWEEK返回的是1到7的数值1是周日和一般图表工具里的Monday起始不一样排序时记得注意对齐。3. 流程控制与聚合函数真正体现SQL水平的部分3.1 IF与CASE WHEN在SQL里写分支逻辑很多开发同学一遇到复杂判断就习惯把数据全查出来然后到Java里写if else。其实SQL本身就能干这事而且干得更好。IF(expr, v1, v2)简单的二元判断SELECT name, IF(age 18, 成年, 未成年) AS age_group FROM users;注意IF的三个参数expr为TRUE时返回v1否则返回v2。它可以嵌套但嵌套层数一多可读性就崩了这时候就要请出CASE WHEN。CASE WHEN多条件分支的正规军SELECT name, CASE WHEN score 90 THEN 优秀 WHEN score 80 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM exam_scores;这个结构清晰、可读性强复杂业务里还能配合AND写组合条件SELECT order_no, CASE WHEN status 1 AND pay_time IS NOT NULL THEN 已支付 WHEN status 1 AND pay_time IS NULL THEN 未支付异常 WHEN status 2 THEN 已取消 ELSE 未知状态 END AS order_status_desc FROM orders;处理NULL的三个函数IFNULL、COALESCE、NULLIFIFNULL(expr1, expr2)把NULL替换成指定值COALESCE(expr1, expr2, ...)返回第一个非NULL值可以跟多个参数NULLIF(expr1, expr2)则反过来——两个值相等时返回NULL。SELECT IFNULL(phone, 未知); SELECT COALESCE(phone, email, 无联系方式); SELECT NULLIF(100, 100); -- NULLNULLIF有个妙用防止除零。比如计算“环比增长”时除数可能为0可以写NULLIF(prev_value, 0)除数为0时结果变成NULL再配合IFNULL或COALESCE兜底比在应用层判断安全得多。3.2 聚合函数与GROUP_CONCAT分组统计的进阶姿势聚合函数几乎都是配合GROUP BY用的。一个容易忽略的点COUNT(col)、COUNT(*)、COUNT(1)的区别。COUNT(*)统计行数包括NULL行COUNT(1)等价于COUNT(*)COUNT(col)只统计该列非NULL的行数。想统计“有手机号的用户数”COUNT(phone)就是正确写法什么都不加就说数的话容易多算。SUM、AVG则是典型的“NULL忽略型”函数——列里有NULL时不参与计算。但要注意SUM的结果如果全都是NULL它返回NULL而不会是0展示报表时会显示一个空最好用IFNULL(SUM(x), 0)兜底。GROUP_CONCAT是我个人非常偏爱的函数它能把分组内多行数据拼成一行听起来简单但很有用SELECT dept_id, GROUP_CONCAT(user_name ORDER BY user_id SEPARATOR 、) AS names FROM employees GROUP BY dept_id;第一个默认踩坑点SEPARATOR默认为逗号想用顿号、竖线要显式指定。第二个坑更隐蔽GROUP_CONCAT默认有长度限制默认值是1024字节拼接字符串一长结果会被截断。处理大批量拼接时需要在会话级或全局设置group_concat_max_lenSET SESSION group_concat_max_len 102400;第三个坑是排序问题GROUP_CONCAT内部的子排序需要用ORDER BY不能依赖外层ORDER BY来排拼接结果的顺序。因为外层排的是分组组内的排列用内部ORDER BY才能保证。聚合函数遇到“分组后再筛选”的情况注意HAVING和WHERE的分工WHERE在分组前筛原始行HAVING在分组后筛分组结果。SELECT category_id, COUNT(*) AS cnt FROM products WHERE status 1 -- 先过滤只统计上架商品 GROUP BY category_id HAVING COUNT(*) 5; -- 后过滤只要商品数5的分类4. 内置函数进阶技巧与业务场景实战函数单个看着都很简单真正考验是组合使用。下面分享几个我实际写过的业务场景把函数串起来能发挥更大的作用。4.1 场景一订单统计报表需求是“按月份汇总各订单状态的订单数量和总金额”其中订单状态用数字表示1待支付、2已支付、3已发货、4已完成、5已取消时间按下单时间走。SELECT DATE_FORMAT(create_time, %Y-%m) AS month, CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 WHEN 4 THEN 已完成 WHEN 5 THEN 已取消 END AS status_desc, COUNT(*) AS order_cnt, IFNULL(SUM(amount), 0) AS total_amount FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY DATE_FORMAT(create_time, %Y-%m), CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 WHEN 4 THEN 已完成 WHEN 5 THEN 已取消 END ORDER BY month DESC, status ASC;这里有个实用心得GROUP BY后面用了表达式DATE_FORMAT(...)和CASE WHEN...MySQL 8.0允许直接这样写但为了兼容老版本和代码可读性我通常在SELECT里给这个表达式起别名然后GROUP BY别名比重复一大段表达式的写法看得舒服SELECT DATE_FORMAT(create_time, %Y-%m) AS month, CASE ... END AS status_desc, COUNT(*) AS order_cnt FROM orders GROUP BY month, status_desc ORDER BY month DESC, status_desc ASC;4.2 场景二用户活跃与留存分析做用户分析时经常要算“本月有多少用户连续登录了3天”或“新用户注册后第7天是否活跃”。这个场景最能体现日期函数组合的威力。来看一个“计算用户注册后7天内是否有下单行为”的示例SELECT u.user_id, u.register_time, CASE WHEN COUNT(o.order_id) 0 THEN 7日内已下单 ELSE 7日内未下单 END AS status_7d FROM users u LEFT JOIN orders o ON o.user_id u.user_id AND o.create_time BETWEEN u.register_time AND DATE_ADD(u.register_time, INTERVAL 7 DAY) GROUP BY u.user_id, u.register_time;关键点在LEFT JOIN的ON条件里加时间的比较范围。如果不加这个条件就得先全表关联再用WHERE过滤7天内的单子性能差不止一个量级。再举个“按日活跃用户数DAU”的例子SELECT DATE_FORMAT(login_date, %Y-%m-%d) AS day, COUNT(DISTINCT user_id) AS dau FROM login_logs WHERE login_date DATE_SUB(CURDATE(), INTERVAL 14 DAY) GROUP BY DATE_FORMAT(login_date, %Y-%m-%d) ORDER BY day ASC;COUNT(DISTINCT user_id)是去重统计的好伙伴算UV、PV、去重人数全靠它。注意它和DISTINCT在COUNT里的写法位置COUNT(DISTINCT col)是合法的去重计数方式。4.3 场景三数据清洗与格式化接收外部系统数据时常有脏数据要清洗手机号多了空格、描述字段里混入了HTML标签、金额格式不统一、时间字符串解析不了……这些用内置函数也能“一条龙处理”。SELECT -- 去除手机号空格、短横线和括号 REPLACE(REPLACE(REPLACE(TRIM(phone), -, ), ), ), (, ) AS clean_phone, -- 金额字符串转成两位小数数值 CAST(1,234.567 AS DECIMAL(10,2)) AS clean_amount, -- 截断描述文本到50个字符并加省略号 IF(CHAR_LENGTH(description) 50, CONCAT(SUBSTRING(description, 1, 50), ...), description) AS short_desc, -- 统一时间格式 DATE_FORMAT(STR_TO_DATE(2024/07/20 15:30, %Y/%m/%d %H:%i), %Y-%m-%d %H:%i:%s) AS unified_time FROM external_data;数据清洗有个原则值得记住能入库前清洗的不要留到查询时清洗。查询时每次都要跑一遍替换、截取索引也没法用好长期维护下来成本很高。以上示例更合适的场景是写一个定时的ETL脚本或存储过程把清洗逻辑固化下来业务表里直接存干净数据。5. 内置函数使用中的常见坑与排查清单5.1 函数套列名导致索引失效这是我在代码评审时发现频率最高的问题。很多同学写“某天订单数”时是这样的-- 大错特错的做法对列做函数运算 SELECT COUNT(*) FROM orders WHERE DATE(create_time) 2024-07-20;WHERE DATE(create_time) ...是对create_time列应用了DATE()函数这意味着MySQL必须把表中每一行的create_time都先算一遍DATE()才能比较索引直接失效。数据量大一点这SQL就跑不动了。正确的写法是用区间查询让索引能正常工作-- 推荐写法利用B树索引范围扫描 SELECT COUNT(*) FROM orders WHERE create_time 2024-07-20 00:00:00 AND create_time 2024-07-21 00:00:00;同理WHERE YEAR(create_time) 2024应该写成create_time 2024-01-01 AND create_time 2025-01-01。你用函数的代价就是“全表扫”。5.2 隐式类型转换看起来是字符串实际在比数字MySQL为了实现“宽松”会在比较时自动做类型转换。比如SELECT * FROM user WHERE phone 13800138000; -- phone是varchar类型看起来没问题但MySQL会把phone列按数值类型转换后和数字比较结果就是phone列的索引同样失效可能还会出现“误匹配”的情况。排查思路是检查字段类型凡是字符串字段请赏它一对引号SELECT * FROM user WHERE phone 13800138000;5.3 字符集与排序规则带来的大小写问题MySQL中字符串比较是否区分大小写由排序规则Collation决定。常见排序规则utf8mb4_general_ci中的ci就是case-insensitive不区分大小写如果想区分要用utf8mb4_bin或utf8mb4_0900_as_cs这样的规则。这意味着WHERE name mysql在ci规则下能查到MySQL但换库换规则后结果可能不一样跨环境迁移时容易“灵异现象”。5.4 内置函数常见误区速查表误区表现根本原因正确做法CONCAT拼接结果有时为NULL任一参数为NULL导致整体NULL用CONCAT_WS替换或先用IFNULL包装COUNT(phone)报错“XXX is not null”想统计总人数计数对象选错统计行数用COUNT(*)统计非空字段用COUNT(col)ROUND(2.675, 2)结果不对浮点数精度存储问题敏感金额用DECIMAL类型存储别用自带函数硬扛DATE_FORMAT()拼出的月份缺前导零%c和%m区分不清固定两位数格式用%m不固定用%cGROUP_CONCAT结果不完整被截断默认长度限制1024字节SET SESSION group_concat_max_len 102400中文用LENGTH统计长度异常返回的是字节数统计字符个数用CHAR_LENGTH字符串和数字比较时索引失效隐式类型转换字符串字段始终加引号比较WHERE DATE(create_time)...查得慢列上函数导致索引失效写范围查询区间这个表基本覆盖了我在工作中最常见的函数使用误区。如果你在写SQL时有任何一条中招现在改还不晚。5.5 排查建议先看执行计划别只盯函数本身遇到函数相关性能问题我一般会先用EXPLAIN看执行计划确认type是不是ALL全表扫描key有没有用到索引。如果看到Using where且typeALL优先考虑是不是函数套列名或隐式类型转换引起的。再一个环境差异也要注意开发库数据量小全表扫也能秒出上了生产才发现慢得没法看。写SQL时脑子里就要有“这语句在千万级数据下能不能跑”这根弦。另外把函数逻辑拆开排查也是个高效方法。比如一条复杂语句结果不对先去掉GROUP BY看明细再逐层加CASE WHEN最后加聚合能很快定位是哪个函数出了问题。很多所谓的“灵异查询”其实就是某个函数对NULL处理不当或格式串写错导致的。最后再分享一个实战习惯我在写函数式SQL时永远随手记“输入类型”和“输出类型”。FORMAT输出是字符串、DATEDIFF输出是整数、DATE_FORMAT输出是字符串、STR_TO_DATE输出才是日期类型。类型一乱后续做运算、比较、拼接时很容易出莫名其妙的错。内置函数用久了你会发现真正难的不是记语法而是搞清楚“你给它的数据长什么样它还给你的数据又变成了什么样”。把这个想明白MySQL内置函数这关就算真正过了。