1. 函数在MySQL里的定位为什么说它是查询操作的“瑞士军刀”做后端开发或者数据分析的日常工作里写SQL的时间占比相当高。你可能会发现同样的需求有人能写出一坨一百行还带各种子查询的SQL有人却用几个函数组合一下十几行就搞定了而且执行效率还更高。这就是MySQL内置函数的价值——它就是把那些经常需要用到的逻辑比如截取字符串、计算日期差、判断条件分支、统计数据汇总预先封装好了。你只需要学会怎么调用它们就能省掉大量手工处理的时间。这一节讲的“函数”指的是MySQL内置函数不是你自己在存储过程里定义的函数。内置函数覆盖面很广从字符串处理、数学计算、日期时间操作到流程控制、条件判断、加密解密基本涵盖了业务开发里能用到的绝大多数场景。适合谁来学三类人最需要吃透这个部分刚入行的后端开发写SQL还停留在SELECT * FROM table WHERE id 1的阶段遇到稍微复杂的查询就要去网上搜搜完还看不懂。数据分析师或运营经常要从数据库里提数做报表但是对SQL函数的掌握停留在COUNT、SUM这种最基础层面稍微涉及日期转换、条件统计就得求助技术。准备面试的开发者MySQL函数是面试常客尤其是字符串函数、日期函数、流程控制函数经常和索引优化放在一起考。这个内容本身没有什么门槛只需要你有一点点SQL基础就行。下面我直接用案例来拆解每一类函数都配上我实际开发中遇到的场景让你学完就能往项目里套。从实践角度看函数掌握得好不好直接决定了你是不是总在“先查出来再在代码里处理”这条弯路里打转。很多明明一条SQL就能返回的结果你非要全量查回内存再写Java/Python逻辑慢慢磨性能差且代码又臭又长。2. 字符串函数详解最常用的一类坑也最多2.1 从拼接、截取到替换字符串处理的完整工具链字符串函数应该是日常业务里用得最多的一类。“用户昵称脱敏”“订单号拼接”“商品描述截断”这些需求听起来很常规但怎么写才高效、怎么处理边界条件里面是有门道的。CONCAT与CONCAT_WSCONCAT(str1, str2, ...)是最基础的拼接函数。注意如果任何一个参数是NULL整个结果就是NULL。这个坑我早年踩过拼接地址的时候某个字段没填结果整条地址串直接变成NULL了。所以后来我习惯用CONCAT_WS(separator, str1, str2, ...)它有两个优势一是可以指定分隔符比如CONCAT_WS(-, 2024, 12, 18)直接得到2024-12-18二是CONCAT_WS会忽略NULL值不会因为某个字段为空导致整个结果崩掉。强烈推荐在做字段拼接时用这个函数替代CONCAT。CHAR_LENGTH与LENGTH这两个函数看起来都是算长度实际上有本质区别。CHAR_LENGTH统计的是字符数LENGTH统计的是字节数。在UTF-8编码下一个中文字符占3个字节所以LENGTH(数据库)返回9而CHAR_LENGTH(数据库)返回3。什么时候用哪个如果你要做“用户输入的昵称不能超过20个字符”这种校验必须用CHAR_LENGTH。如果你要估算某列数据占用多少存储空间就要用LENGTH。这个区别在面试里也经常被问到答不上来说明基础不牢。SUBSTRING / LEFT / RIGHTSUBSTRING(str, pos, len)用于从指定位置截取指定长度的子串。注意MySQL的起始位置是从1开始的不像是很多编程语言从0开始。有位同学曾经因为这个问题截取结果一直不对排查了半天才发现是“从1开始”这个细节。-- 从第2个字符开始截取取3个字符 SELECT SUBSTRING(hello world, 2, 3); -- 结果: ell -- 从右向左截位置用负数表示截取后取4位 SELECT SUBSTRING(hello world, -5, 2); -- 结果: or -- LEFT和RIGHT分别从左右两端截取 SELECT LEFT(hello world, 5); -- 结果: hello SELECT RIGHT(hello world, 5); -- 结果: world实际开发中LEFT经常配合REPEAT做脱敏处理。比如手机号中间四位打码SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone FROM user_info;REPLACE与SUBSTRING_INDEXREPLACE(str, from_str, to_str)是把字符串里所有匹配的子串替换成新内容。做数据清洗的时候要把用户输入的旧前缀替换成新前缀一条UPDATE就能搞定UPDATE product SET image_url REPLACE(image_url, http://old.cdn.com/, https://new.cdn.com/);初次看到这个用法的人通常都很惊喜因为在代码里替换还得分页查出来、循环替换、再写回去SQL一个语句就完成了。SUBSTRING_INDEX(str, delimiter, count)则是一个极其好用的拆分函数。按分隔符拆字符串count为正数表示取第N个分隔符之前的所有内容负数表示从右边数。比如有一个字段存的是“类型-编号-名称”要取中间那段编号-- 先取前两段再从结果里取第二段 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(A-1001-SKU, -, 2), -, -1); -- 结果: 1001这种嵌套写法初次看到会绕一下但一旦理解了就再也离不开了。它比在应用层用split再取下标要省事得多。2.2 字符串函数避坑指南字符集、索引和NULL地狱字符串函数的坑比想象中多尤其是和字符集、NULL打交道的时候。这几年带团队做项目见最多的线上问题基本都集中在下面几个点。第一个坑字符集不一致。当表字段的字符集和连接字符集不一致时LENGTH的结果会变得很奇怪同一个字符串在不同连接里返回不同的字节长度。更严重的情况是会触发隐式字符集转换导致字段上的索引失效。所以如果你发现某个用到了字符串函数的WHERE条件明明该走索引却走了全表扫描先查一下字符集是不是UTF-8、排序规则是不是大小写不敏感。第二个坑LIKE和字符串函数配合使用时的索引问题。LIKE abc%是可以走索引的但如果你写WHERE LEFT(name, 3) abc索引就直接失效了。这是因为函数处理后的结果无法直接匹配索引中的原始数据。所以能用LIKE前缀匹配解决的问题就不要套LEFT套SUBSTRING去硬刚。第三个坑NULL的传染性。前面提到过CONCAT遇到NULL会整个变成NULL。同理REPLACE如果原始字符串是NULL结果也是NULL。很多人在做数据清洗时没提前处理NULL导致下游数据出现大量空值。解决思路有两条要么在函数外套一层IFNULL要么在拼接前就用COALESCE把所有可能为NULL的字段统一兜底。-- 组合写法示例拼接地址避免单个字段为空导致整条数据丢失 SELECT CONCAT_WS(, , COALESCE(province, 未知省份), COALESCE(city, ), COALESCE(district, )) AS full_address FROM user_address;第四个坑不可见字符。用户输入的数据里经常混着换行符、制表符、全角空格。有时候你查一条记录怎么都查不到是因为字段值里混了个不可见字符。这时候用HEX函数把目标字符串转成十六进制看看尾部的编码就能定位问题。比如常见的是字符串尾部带了0A换行符你直接用匹配永远匹配不上。关于这一块我的建议是凡是涉及字符串比较、去重、分组的字段入库前就在应用层做好格式化别指望所有脏数据都能靠SQL函数来清洗。SQL函数是补救工具不是第一道防线。3. 数学与日期函数数值处理和时间计算的必备技能3.1 常用数学函数的计算逻辑与使用场景数学函数在业务开发里用得不算最多但一旦用上就是那种“还好我知道有这个函数”的瞬间。ROUND / TRUNCATE / CEIL / FLOORROUND(x, d)做四舍五入TRUNCATE(x, d)直接截断不四舍五入CEIL向上取整FLOOR向下取整。这四兄弟容易弄混先看一组对比SELECT ROUND(3.14159, 2); -- 3.14 SELECT TRUNCATE(3.14159, 2); -- 3.14 SELECT ROUND(3.145, 2); -- 3.15四舍五入 SELECT TRUNCATE(3.145, 2); -- 3.14直接截断 SELECT CEIL(3.1); -- 4 SELECT FLOOR(3.9); -- 3注意一个细节ROUND在某些边界值上可能会有预期之外的浮点数误差比如ROUND(2.675, 2)在某些版本里返回的是2.67而不是2.68。这是因为浮点数的二进制表示本身就存在精度问题。如果你对精度有严格要求比如在做金额计算最好不要依赖ROUND而是定量化成整数做运算或者使用DECIMAL类型的字段。CEIL和FLOOR最适合用在分页计算和库存分配这类场景。比如总共有100件商品每页显示30件计算总页数时就可以SELECT CEIL(100 / 30)得到4页比手动判断余数要简洁得多。MOD与取模运算MOD(x, y)返回余数。最经典的应用场景是分库分表时的路由计算比如用户ID取模8决定数据落在哪个分表。另外还有一个非常实用的用法判断奇数偶数——MOD(id, 2) 1表示是奇数行可以用来做简单的奇偶分流。ABS / RAND / POWER / SQRT这些函数没什么神秘感但是在某些场景里特别好用。RAND()生成0到1之间的随机数配合ORDER BY RAND()可以实现随机抽取一条记录比如“随机推荐商品”。-- 随机抽取3条记录 SELECT * FROM products ORDER BY RAND() LIMIT 3;但这种写法有个明显的性能问题ORDER BY RAND()会对全表每一行都生成随机数再排序数据量大时非常慢。如果只是随机抽一条更好的方案是先SELECT COUNT(*)得到总数然后随机取一个偏移量再用LIMIT offset, 1去查。不过若表不大用ORDER BY RAND()图个方便也没问题。3.2 日期时间函数实战从格式化到时间差计算日期时间函数可以说是在报表统计里最核心的部分。每月的活跃用户、每日的销售额、订单的响应时长全是这堆函数在撑着。NOW / CURDATE / CURTIMESELECT NOW(); -- 完整日期时间: 2025-01-15 14:32:10 SELECT CURDATE(); -- 日期: 2025-01-15 SELECT CURTIME(); -- 时间: 14:32:10这三个函数用于获取当前时刻。写代码时注意如果你的应用服务器和数据库服务器不在同一台机器上NOW()返回的是数据库服务器的本地时间而不是应用服务器的。跨时区场景下最好在数据库连接字符串里统一指定时区避免出现“时间对不上”的问题。DATE_FORMAT与STR_TO_DATE这两个函数是一对兄弟DATE_FORMAT把日期时间按指定格式转成字符串STR_TO_DATE则把字符串按指定格式解析成日期时间。它们的格式化占位符很多但最常用的就那么几个%Y四位年份、%m两位月份、%d两位日期、%H24小时制小时、%i分钟、%s秒。-- 按天统计订单量输出格式为 2025-01-15 SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) FROM orders GROUP BY day; -- 把字符串解析成日期 SELECT STR_TO_DATE(2025-01-15 14:30:00, %Y-%m-%d %H:%i:%s);报表开发里DATE_FORMAT基本每天都在用。按小时统计活跃度就改格式为%Y-%m-%d %H按月统计就改成%Y-%m非常灵活。DATEDIFF / TIMESTAMPDIFF / DATE_ADDDATEDIFF计算两个日期之间相差的天数适合做“距离上次登录多少天”“今天到活动结束还有多少天”这类计算。SELECT DATEDIFF(2025-01-20, 2025-01-15); -- 结果: 5TIMESTAMPDIFF更强大可以指定单位计算相差的小时、分钟、秒、甚至月份和年份-- 订单从下单到支付花了多少分钟 SELECT TIMESTAMPDIFF(MINUTE, order_time, pay_time) AS cost_minutes FROM orders;DATE_ADD(date, INTERVAL expr unit)用于日期加减。比如“查询最近30天内的数据”SELECT * FROM orders WHERE create_time DATE_ADD(CURDATE(), INTERVAL -30 DAY);注意这里有个性能细节如果create_time是带时分秒的DATETIME类型直接和CURDATE()比较会漏掉当天的数据。正确的做法是使用半开区间WHERE create_time DATE_ADD(CURDATE(), INTERVAL -30 DAY) AND create_time DATE_ADD(CURDATE(), INTERVAL 1 DAY);用明天的零点来框定“到今天结束”的范围避免漏数据或边界重复。这个坑在日报统计里尤其常见凌晨0点到0点之间的数据经常会少算或重算。EXTRACT与DAYOFWEEKEXTRACT(unit FROM date)可以从日期中提取独立的年、月、日、时、分等部分。这会比DATE_FORMAT再做一次隐式转换更高效。DAYOFWEEK返回1到7但要注意1代表星期日不是星期一。如果你要做“统计一周里每天的订单分布”并且想让数据从周一开始排列需要自己做一次换算WEEKDAY(date)返回的是0到60代表周一这个函数更符合国内开发习惯。日期函数这一块我强烈建议你在开发环境多打印几组结果尤其要搞清楚DATETIME、TIMESTAMP、DATE三种类型与函数交互时的边界。不同类型的日期数据在隐式转换时的行为不完全一样很多“莫名其妙”的查询结果都是因为这个造成的。4. 流程控制函数与聚合函数让SQL“活”起来的核心4.1 IF、IFNULL、CASE WHEN的应用技巧很多时候我们希望SQL按条件返回不同的结果而不是把所有数据都取出来后在代码里做if else。这时候流程控制函数就派上用场了。IF(expr, true_value, false_value)这是最简单的条件判断适合二元分支-- 库存大于0显示有货否则显示无货 SELECT product_name, IF(stock 0, 有货, 无货) AS stock_status FROM products;IFNULL(expr, default_value)专门用来处理NULL值。这个函数用得极其频繁因为业务数据里NULL总是无处不在。它和COALESCE的区别在于IFNULL只有两个参数而COALESCE可以接受多个参数返回第一个非NULL的值。-- 用户没有填写年龄时显示未知 SELECT name, IFNULL(age, 未知) AS age_display FROM users;CASE WHEN这是流程控制里最灵活、最常用的。它支持多分支判断还能配合聚合函数做条件统计。SELECT CASE WHEN score 90 THEN 优秀 WHEN score 80 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level, COUNT(*) AS cnt FROM student_scores GROUP BY level;注意这个写法先把CASE WHEN的结果起个别名level然后在GROUP BY里直接引用这个别名。MySQL是允许在GROUP BY里使用别名的但WHERE里不行因为WHERE的执行顺序在SELECT之前。这个是SQL执行顺序的问题很多人在这个细节上报错理解执行顺序之后就豁然开朗了。CASE WHEN配合SUM实现“一列转多列”的透视效果这个技巧写报表的人一定要掌握。比如统计订单表里各状态的数量传统写法可能要用四条SQL分别查。用CASE WHEN加SUM一条SQL就能搞定SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, SUM(CASE WHEN status pending THEN 1 ELSE 0 END) AS pending_cnt, SUM(CASE WHEN status paid THEN 1 ELSE 0 END) AS paid_cnt, SUM(CASE WHEN status shipped THEN 1 ELSE 0 END) AS shipped_cnt, SUM(CASE WHEN status cancelled THEN 1 ELSE 0 END) AS cancelled_cnt FROM orders GROUP BY day;这个写法背后的思路是SUM只累加条件为真的行条件为假的就加0相当于把多行不同状态的数据“摊”到了多个列里。做BI报表、数据透视表的时候这个技巧能从根源上减少多次查询。4.2 聚合函数配合GROUP BY的常见踩坑实录聚合函数就是COUNT、SUM、AVG、MAX、MIN这五个。它们本身不难难的是和GROUP BY、HAVING配合的时候。COUNT(*)和COUNT(column)的区别这是面试必问的考点。COUNT(*)统计的是所有行数包括NULL值的那一行COUNT(column)统计的是该列非NULL的行数。如果你要统计用户表里有多少人填了手机号应该写COUNT(phone)如果你要统计总用户数就写COUNT(*)或COUNT(id)。AVG自动忽略NULL值AVG(column)在计算平均数时会忽略NULL行但不会忽略0。所以如果一个字段里既有真正的0又有NULL它们的含义又不同AVG的结果就会和你想象中的“平均值”有偏差。这时候需要先用CASE WHEN把0或NULL统一处理掉再做平均。HAVING和GROUP BY的关系WHERE是分组前过滤HAVING是分组后过滤。想在分组后过滤掉不满足条件的组必须用HAVING。这个大家应该都知道但实际写的时候容易犯的一个错误是在HAVING里使用了一个不在SELECT中的聚合条件然后又和WHERE混淆导致逻辑错乱。-- 找出订单数超过100的客户 SELECT customer_id, COUNT(*) AS order_cnt FROM orders WHERE status ! cancelled GROUP BY customer_id HAVING COUNT(*) 100;这里WHERE status ! cancelled是做分组前的过滤把已取消的订单排除了HAVING COUNT(*) 100是做分组后的过滤只保留下单数大于100的客户。两者的职责完全不同理解这个边界能让你的SQL组织得更清晰。聚合函数与DISTINCT组合COUNT(DISTINCT column)用来统计某列去重后的数量日常做“多少独立用户访问”这类统计时很常用。但要注意性能和内存消耗尤其是高基数字段DISTINCT可能会占用大量临时表空间。如果表特别大建议在离线统计或专门的聚合表上做。聚合函数这块最大的经验是能用一条SQL完成的统计尽量不要拆成多条。多次往返数据库的开销远比一次复杂查询大而且多条SQL之间的数据一致性难以保证特别在写入频繁的业务表上。这也是为什么CASE WHEN透视、GROUP BY多维度聚合的技巧要熟练。5. 其他常用函数与综合案例学完就能上手5.1 加密、系统信息等辅助函数盘点除了上面几大类有几类函数虽然使用频率不那么高但在特定场景下能解决大问题。加密函数MD5(str)、SHA1(str)、SHA2(str, len)常用于敏感信息的防泄露。比如密码存储虽然现在更推荐专用的password_hash算法但在遗留系统里MD5依然很常见。注意MD5返回的是32位十六进制字符串SHA1是40位SHA2可以指定长度256或512。加密函数不适合做超大字段的加密因为计算开销明显。如果要加密身份证号这种长度不长的字段可以先MD5再存并在应用层混合一个固定盐值避免被彩虹表直接命中。压缩函数COMPRESS(str)用zlib压缩字符串UNCOMPRESS(str)解压。这个函数适合存储大文本数据比如一篇文章的全文、日志详情等。在数据量较大的业务中压缩存储可以明显减少磁盘占用和IO消耗。缺点是不能对压缩后的字段建立有效的索引所以你需要另外存一个用于检索的摘要字段或者抽出来单独建表。系统信息函数DATABASE()返回当前数据库名USER()返回当前用户VERSION()返回数据库版本号。这些函数通常在排查问题时用。比如你怀疑连到了错误的库实例直接SELECT DATABASE(), USER(), VERSION();就能确认。JSON函数这个提一下因为现在业务表里使用JSON类型的字段越来越普遍。JSON_EXTRACT(json_doc, path)用于提取JSON字段中指定路径的值JSON_UNQUOTE去掉引号JSON_CONTAINS判断是否存在某个键值对。-- 注意字段假设是JSON类型且存了 {name:某产品,price:99.9} SELECT product_name, JSON_UNQUOTE(JSON_EXTRACT(attrs, $.name)) AS attr_name FROM products;这里有个常见误区JSON_EXTRACT返回的结果如果是一个字符串会带双引号所以通常要再套一个JSON_UNQUOTE才能得到干净的文本。如果你使用MySQL 5.7以上还有一个运算符-直接去除引号写法更简洁SELECT attrs-$.name AS attr_name FROM products;5.2 综合实战一条SQL整合多种函数的完整演进基础函数单独用都挺简单难的是组合起来。下面用一个真实的业务需求来演示运营要一份“本月每个城市、每个商品分类的销售额排行”并且只在销售额超过一定阈值的商品分类里取前三名。字段有订单表订单ID、下单时间、城市、商品分类、金额、状态数据量在百万级。首先做基础统计这一步使用了DATE_FORMAT做日期过滤、CASE WHEN把取消订单剔除SELECT city, category, SUM(CASE WHEN status ! cancelled THEN amount ELSE 0 END) AS sales_amount FROM orders WHERE create_time DATE_FORMAT(CURDATE(), %Y-%m-01) AND create_time DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) GROUP BY city, category;这一版能跑通但有两个问题一是只要销售额大于0的分类都会出来数据太多不便于运营查看二是没有排名。加上HAVING过滤掉过小的分类再用窗口函数排名SELECT city, category, sales_amount, RANK() OVER (PARTITION BY city ORDER BY sales_amount DESC) AS rk FROM ( SELECT city, category, SUM(CASE WHEN status ! cancelled THEN amount ELSE 0 END) AS sales_amount FROM orders WHERE create_time DATE_FORMAT(CURDATE(), %Y-%m-01) AND create_time DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) GROUP BY city, category HAVING SUM(CASE WHEN status ! cancelled THEN amount ELSE 0 END) 10000 ) t HAVING rk 3;这个写法里有两层函数组合外层用窗口函数RANK()在分组内排名内层用聚合函数加CASE WHEN做条件汇总。单独看每个函数都好理解但组合起来就解决了实际业务里“先统计、再过滤、再排名”的完整流程。从这个例子可以看到函数不是孤立使用的。它们之间通过SQL的执行顺序形成一条完整的流水线FROM确定数据源WHERE分组前过滤GROUP BY分组HAVING分组后过滤SELECT计算最终列ORDER BY排序LIMIT截断。理解了这条流水线你在任何函数组合面前都不会慌。6. 常见报错与经验速查写给正在Debug的你6.1 常见函数报错速查表下面这个表是我在实际带人过程中整理的。遇见了可以直接对照排查。报错或异常现象常见原因解决方案CONCAT拼接后数据消失某个参数为NULL改用CONCAT_WS或外层套IFNULL、COALESCELENGTH和CHAR_LENGTH结果不一致表或字段字符集不是utf8确认字符集做长度校验时用CHAR_LENGTH日期范围查询漏掉当天数据DATETIME与DATE直接比较用半开区间 开始 AND 下一天GROUP BY后查询报错SELECT的列没在GROUP BY或聚合函数中检查only_full_group_by模式补齐分组列ORDER BY RAND()性能差大表全量排序改用随机偏移量的方案DATE_FORMAT拼错格式符%i写成了%m分钟错乱用官方格式对照表复查JSON_EXTRACT结果带双引号返回值是JSON字符串用JSON_UNQUOTE或-运算符函数导致索引失效在索引列上使用了函数改写条件或改成范围查询这个表里的第一个和第三个出现频率最高。前者是因为很多新手忽略NULL的语义后者是日期边界判断的经典错误。两条都是经验积累出来的不是看书能直接看会的。6.2 几个实用的小习惯写函数时能少踩很多坑第一个习惯写函数前先确认字段类型。你在INT字段上用SUBSTRING在DATETIME字段上用DATE_FORMATSQL都能跑但结果的语义可能完全不是你想的那样。字段类型决定了该用哪一类函数这是第一原则。第二个习惯多函数嵌套时从内往外拆开验证。嵌套三个以上的函数时出错很难一眼看出来。我的做法是先跑最内层的函数确认结果没问题再一层层往外套。写得在开发环境多调试几次总比上线后排查要舒服得多。第三个习惯用函数做统计任务时先小范围验证再全表跑。你写了一条改造SQL不要直接在生产环境全量执行。先加LIMIT 100或者限定一个短时间范围确认结果符合预期再放开限制。第四个习惯定期做函数结果抽查。有些函数的结果会受数据变化的影响比如DATE_FORMAT遇到日期越界值或者REPLACE替换后的数据引发新的冲突。设个定时脚本定期对比几次统计数据能提前发现函数使用中的逻辑漏洞。我个人在实际开发里最有体会的一点是函数是SQL的“思维能力”但别让函数成为一个大泥团。当一条SQL里函数嵌套超过四层我建议你停下来考虑拆成两步查询或在应用层处理一部分。数据库的资源很宝贵函数也不是用得越多越好。能在SQL里顺手做掉的变换就顺手做掉做不掉或者代价太大的老老实实交给代码处理这才是生产环境里可持续的写法。学完这一节建议你拿自己线上的一张表把今天提到的每一类函数都拿真实数据试一遍。不用刻意记试的时候多看看执行计划等用熟了这些函数会变成你写SQL时的直觉反应。碰到不会的函数也可以直接查对应版本的官方文档配合示例跑一遍效果记忆会深刻得多。