1. 从“函数”这个词聊起为什么MySQL函数是开发者的瑞士军刀最近在帮团队新人排查一个数据报表的Bug问题出在一个日期计算上。他们写了一大段复杂的应用层代码来处理业务逻辑日期结果不仅性能堪忧还因为时区问题导致数据对不上。我看了看其实核心需求就一句话“找出上个月最后一天下单的所有用户”。在应用层这可能需要几十行代码来处理月份边界、闰年等情况但在MySQL里一个LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))函数调用就搞定了。这件事让我再次感慨很多开发者尤其是刚入行的朋友对数据库的认知还停留在“增删改查”的层面却忽略了数据库本身就是一个强大的计算引擎而函数就是这个引擎上最趁手的工具。所谓MySQL函数你可以把它理解为数据库内置的“小程序”或“工具”。你给它输入参数它按照既定规则处理然后给你输出返回值。比如UPPER(‘hello’)返回 ‘HELLO’ABS(-10)返回 10。这些函数能直接在SQL语句里使用把数据计算、转换、判断的活儿从应用程序“下推”到数据库层去执行。这么做的核心价值有三点一是简化代码让SQL语句更清晰、更声明式避免在应用层写一堆啰嗦的处理逻辑二是提升性能在数据库内部处理数据减少了网络传输和应用层与数据库层之间的上下文切换开销三是保证一致性使用数据库内置函数处理如日期、字符串能确保所有客户端应用得到统一、准确的结果避免了不同编程语言库可能存在的细微差异。所以无论你是写业务SQL的后端开发还是做数据查询分析的数据分析师甚至是需要直接操作数据库的运维同学熟练掌握一批高频、实用的MySQL函数都能让你的工作效率和代码质量提升一个档次。这篇文章我就结合自己这些年踩过的坑和总结的经验把MySQL里那些真正“常用”且“好用”的函数分门别类地梳理一遍不仅告诉你怎么用更会重点说说什么时候用以及用的时候要注意什么。2. 字符串处理函数数据清洗与格式化的第一道关卡我们打交道的数据尤其是从外部导入或用户输入的数据很少是完美整洁的。字符串首尾可能有空格大小写不规范需要从复杂文本中提取关键部分或者将多个字段拼接成一个完整的展示信息。这些场景就是字符串函数大显身手的地方。2.1 基础修剪与填充让杂乱数据变规整TRIM()、LTRIM()、RTRIM()这三个函数是数据清洗的“先锋官”。用户在前端输入时无意中在开头或结尾敲入空格是常有的事。直接存储或用于条件匹配就会导致‘apple’和‘apple ‘被认为是两个不同的值。-- 移除字符串首尾的空格 SELECT TRIM( Hello World ); -- 返回 Hello World -- 仅移除左侧空格 SELECT LTRIM( Hello World); -- 返回 Hello World -- 仅移除右侧空格 SELECT RTRIM(Hello World ); -- 返回 Hello World注意TRIM()函数不仅可以去掉空格还可以指定去掉其他字符。例如TRIM(LEADING ‘0’ FROM ‘000123’)会返回 ‘123’这在处理某些定长编码或去除填充字符时非常有用。LPAD()和RPAD()则用于填充确保字符串达到固定长度这在生成固定格式的编号如工号‘EMP001’时必不可少。-- 将字符串‘7’左侧用‘0’填充至5位长度 SELECT LPAD(7, 5, 0); -- 返回 00007 -- 将字符串‘Hi’右侧用‘*’填充至5位长度 SELECT RPAD(Hi, 5, *); -- 返回 Hi***实操心得在查询条件中使用TRIM()要特别小心性能。例如WHERE TRIM(username) ‘john’会导致无法使用username字段上的索引因为索引存储的是原始值。对于这类需要频繁清洗后查询的字段更优的做法是在数据入库时就用TRIM()处理好保证存储的就是规整数据或者为该字段建立一个函数索引如果MySQL版本支持。2.2 子串提取与定位精准抓取文本关键信息当你的数据是像‘订单号ORD-20231001-001’或‘姓名张三技术部’这样的复合字符串时SUBSTRING()和LOCATE()或POSITION()、INSTR()的组合技就派上用场了。SUBSTRING(str, pos, len)是从字符串str的第pos个字符开始截取len个字符。这里有个易错点MySQL中字符串的起始位置是1不是0。很多从编程语言如Python、JavaScript转过来的开发者容易在这里栽跟头。SELECT SUBSTRING(MySQL Function, 7, 8); -- 从第7个字符(F)开始取8位返回 FunctionLOCATE(substr, str)返回子串substr在字符串str中第一次出现的位置如果没找到则返回0。SELECT LOCATE(World, Hello World); -- 返回 7结合使用可以动态截取。例如从上述订单号中提取日期部分SELECT SUBSTRING(订单号ORD-20231001-001, LOCATE(-, 订单号ORD-20231001-001) 1, 8); -- 先定位第一个‘-’的位置然后从它后面一位开始截取8位返回 20231001更强大的替代方案对于这种有固定分隔符的字符串如‘ORD-20231001-001’使用SUBSTRING_INDEX()函数往往更直观。SUBSTRING_INDEX(str, delim, count)按分隔符delim分割字符串返回第count个出现之前或之后的部分count为正取前为负取后。-- 获取第一个‘-’之前的部分 SELECT SUBSTRING_INDEX(ORD-20231001-001, -, 1); -- 返回 ORD -- 获取最后一个‘-’之后的部分 SELECT SUBSTRING_INDEX(ORD-20231001-001, -, -1); -- 返回 001 -- 获取中间部分第二个‘-’之前且去掉第一个‘-’之前的部分 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ORD-20231001-001, -, 2), -, -1); -- 返回 202310012.3 替换、拼接与大小写转换格式化输出利器REPLACE(str, from_str, to_str)是全局替换简单暴力。比如清理掉文本中所有不必要的占位符。CONCAT(str1, str2, ...)用于拼接字符串。这里有个重要技巧当拼接的字段中可能有NULL值时整个CONCAT的结果会变成NULL。这常常是导致输出结果意外为空的原因。解决方案是使用CONCAT_WS(separator, str1, str2, ...)函数它会忽略NULL值并用指定的分隔符连接非NULL值。或者先用IFNULL()函数将NULL转换为空字符串。SELECT CONCAT(Hello, NULL, World); -- 返回 NULL SELECT CONCAT_WS( , Hello, NULL, World); -- 返回 Hello World SELECT CONCAT(IFNULL(first_name, ), , IFNULL(last_name, )) AS full_name FROM users;UPPER()和LOWER()用于大小写转换在进行不区分大小写的比较或统一存储格式时常用。但请注意其效果受数据库字符集和校对规则Collation影响。例如某些校对规则本身就不区分大小写这时再使用UPPER()就是画蛇添足了。3. 数值计算与聚合函数不仅仅是加减乘除数值函数处理数字但绝不只是简单的四则运算。从基础计算、四舍五入到生成随机数它们为数据分析和业务计算提供了基础数学能力。3.1 基础运算与舍入保证计算精度ROUND(x, d)是最常用的舍入函数将x四舍五入到d位小数。d为0时取整为负数时向整数位左侧舍入。SELECT ROUND(123.4567, 2); -- 123.46 SELECT ROUND(123.4567, -1); -- 120 (向十位舍入)这里有个商业计算中常见的坑标准的“四舍五入”在金融场景下可能不符合“公平性”原则因为“入”的概率略大于“舍”。有些系统会要求使用“四舍六入五成双”的银行家舍入法或者直接向下取整。MySQL的ROUND()就是标准四舍五入。如果需要其他舍入方式要使用FLOOR()向下取整或CEIL()/CEILING()向上取整。SELECT FLOOR(123.7); -- 123 SELECT CEILING(123.1); -- 124FORMAT(x, d)函数在舍入的同时还会返回一个格式化为千位分隔符的字符串非常适合在报表中直接展示金额。SELECT FORMAT(1234567.456, 2); -- 返回 1,234,567.463.2 聚合函数透视数据的宏观视角聚合函数是SQL的灵魂之一它们对一组值执行计算并返回单个值。最核心的几个是COUNT()统计行数。COUNT(*)统计所有行COUNT(column)统计该列非NULL的行数。这是性能优化中常被关注的点在无WHERE条件时COUNT(主键)或COUNT(1)通常比COUNT(*)稍快但现代优化器对COUNT(*)的处理已经很好可读性优先。SUM()求和。只对数值列有效自动忽略NULL。AVG()求平均值。同样忽略NULL。重要提示AVG()的计算是SUM(column) / COUNT(column)这里的COUNT(column)不包括NULL。如果你想在分母中包含NULL将其视为0需要手动处理SUM(column) / COUNT(*)。MAX()/MIN()求最大/最小值。可用于数值、日期、字符串类型。进阶用法与坑点GROUP BY与聚合函数配合是数据分析的基石。但有一个经典错误在SELECT子句中出现了非聚合列且没有包含在GROUP BY中。在严格模式下这会导致错误。-- 错误示例在ONLY_FULL_GROUP_BY模式下 SELECT department, employee_name, AVG(salary) FROM employees GROUP BY department; -- employee_name 没有在GROUP BY中对于每个部门有多条employee_name数据库不知道选哪个显示。 -- 正确做法要么把employee_name也加入GROUP BY要么使用聚合函数处理它 SELECT department, GROUP_CONCAT(employee_name), AVG(salary) FROM employees GROUP BY department;GROUP_CONCAT()是一个非常有用的函数它将一个分组内的多个字符串值连接成一个字符串。这在需要将子行信息合并到主行展示时如“查询每个订单的所有商品名称”非常方便。SELECT order_id, GROUP_CONCAT(product_name SEPARATOR , ) AS products FROM order_items GROUP BY order_id;4. 日期与时间函数处理一切与时间相关的逻辑日期和时间是业务系统中出错率最高的部分之一时区、闰秒、月份天数差异都是潜在的坑。MySQL的日期时间函数库相当丰富能帮你规避很多麻烦。4.1 获取当前时间与时间戳操作NOW()、CURDATE()、CURTIME()分别返回当前的日期时间、日期、时间。NOW()返回的是语句开始执行的时间在整个SQL语句中值是固定的。而SYSDATE()返回的是函数执行时刻的时间如果一条语句中多次调用SYSDATE()可能会得到不同的值。在大多数业务场景下使用NOW()更为一致和安全。UNIX_TIMESTAMP()和FROM_UNIXTIME()是一对好搭档用于在Unix时间戳秒数和标准日期时间格式之间转换。这在处理来自系统日志或某些API的数据时非常常用。SELECT UNIX_TIMESTAMP(2023-10-01 12:00:00); -- 返回 1696155200 SELECT FROM_UNIXTIME(1696155200); -- 返回 2023-10-01 12:00:004.2 日期时间的提取与计算DATE()、TIME()、YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()等函数用于从日期时间值中提取特定部分。日期计算是核心需求主要使用DATE_ADD()和DATE_SUB()或者更简洁的INTERVAL表达式。-- 三天后 SELECT DATE_ADD(NOW(), INTERVAL 3 DAY); -- 等价于 SELECT NOW() INTERVAL 3 DAY; -- 两个月前 SELECT DATE_SUB(NOW(), INTERVAL 2 MONTH); -- 等价于 SELECT NOW() - INTERVAL 2 MONTH;DATEDIFF(date1, date2)返回两个日期相差的天数date1 - date2。TIMESTAMPDIFF(unit, datetime1, datetime2)更强大可以返回相差的年、月、日、小时、分钟等。SELECT DATEDIFF(2023-12-31, 2023-01-01); -- 返回 364 SELECT TIMESTAMPDIFF(YEAR, 2000-01-01, 2023-10-01); -- 返回 23 SELECT TIMESTAMPDIFF(MONTH, 2023-01-15, 2023-10-01); -- 返回 8一个真实踩坑案例计算年龄。用TIMESTAMPDIFF(YEAR, birth_date, NOW())计算的是“年份差”并非精确年龄。比如生日是2000-12-31在2023-01-01时年份差是23但实际年龄是22岁。精确计算年龄需要更复杂的逻辑比如判断今年生日是否已过。4.3 日期格式化与解析DATE_FORMAT(date, format)是将日期格式化成字符串的终极武器。STR_TO_DATE(str, format)则是其逆过程。SELECT DATE_FORMAT(NOW(), %Y年%m月%d日 %H:%i:%s); -- 返回 2023年10月01日 14:30:25 SELECT STR_TO_DATE(01,10,2023, %d,%m,%Y); -- 返回 2023-10-01格式符是大小写敏感的%Y是四位年份%y是两位年份%M是月份英文名%m是数字月份。用错了会导致解析失败或结果错误。5. 流程控制与信息函数让SQL拥有逻辑判断能力SQL不仅仅是声明“要什么数据”通过流程控制函数它也能表达一定的业务逻辑这有时能避免数据在数据库和应用层之间不必要的往返。5.1 条件判断IF与CASE WHENIF(expr, true_value, false_value)是最简单的三元判断。如果表达式expr为真非0且非NULL返回true_value否则返回false_value。SELECT name, salary, IF(salary 10000, 高收入, 普通收入) AS income_level FROM employees;CASE WHEN则提供了更强大的多分支逻辑有两种形式简单CASE表达式将某个值与一系列值比较。SELECT name, CASE department_id WHEN 1 THEN 技术部 WHEN 2 THEN 市场部 WHEN 3 THEN 销售部 ELSE 其他部门 END AS department_name FROM employees;搜索CASE表达式可以表达更复杂的条件。SELECT name, score, CASE WHEN score 90 THEN 优秀 WHEN score 80 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS grade FROM students;经验之谈复杂的、多层嵌套的CASE WHEN虽然强大但会显著降低SQL的可读性和可维护性。当逻辑过于复杂时应考虑是否更适合在应用层处理或者通过设计新的状态字段来简化查询逻辑。5.2 空值处理IFNULL与COALESCEIFNULL(expr1, expr2)是IF的特例专门处理NULL。如果expr1不是NULL返回expr1否则返回expr2。COALESCE(value1, value2, ..., valueN)则更通用它返回参数列表中第一个非NULL的值。如果所有值都是NULL则返回NULL。SELECT name, COALESCE(phone, mobile, 暂无联系方式) AS contact FROM customers;在这个例子里会优先取phone如果phone为NULL则取mobile如果两者都为NULL则返回‘暂无联系方式’。COALESCE比嵌套的IFNULL更清晰。5.3 信息函数用于调试与动态SQL这类函数不直接处理业务数据但对于编写、调试SQL非常有帮助。LAST_INSERT_ID()返回最后一条INSERT语句为AUTO_INCREMENT列生成的值。在插入数据后需要立即获取自增ID时如用于插入关联子表这个函数是关键。注意它基于当前连接会话不会受到其他客户端插入操作的影响。ROW_COUNT()返回前一个SQL语句影响的行数INSERT、UPDATE、DELETE等。在存储过程或需要确认操作是否成功的脚本中很有用。VERSION()返回MySQL服务器版本。在需要编写兼容不同版本SQL的脚本时可以先判断版本再执行特定语句。DATABASE()、USER()返回当前数据库名和用户名。用于动态构造SQL或记录日志。掌握这些函数你的SQL语句就从简单的数据搬运工升级为具备一定智能的数据处理单元。很多原本需要在代码里写的if-else逻辑其实可以优雅地放在SQL里完成让数据库做它最擅长的事。