## 引言在数据库开发中函数是我们处理数据的“瑞士军刀”。MySQL 不仅提供了海量的内置函数涵盖数学、字符串、日期、加密、JSON 等场景还允许我们通过自定义函数UDF扩展专属业务逻辑。本文将打破传统的枯燥罗列在系统梳理用法的同时穿插原理剖析与避坑指南如为什么分组函数不能直接用在 WHERE 中为什么 NOT IN 遇上 NULL 会翻车帮助你不仅“会用”更能“用好”。第一部分内置函数系统函数内置函数是 MySQL 预定义的函数无需定义即可直接使用。我们将它们分为多行处理分组和单行处理两大类。一、分组函数多行处理函数分组函数操作一组数据返回一个单一结果常与GROUP BY搭档。函数说明注意点COUNT()计数COUNT(*)统计总行数含NULLCOUNT(字段)忽略NULLSUM()求和自动忽略 NULLAVG()平均值自动忽略 NULLMAX()/MIN()最大/最小值自动忽略 NULL-- 统计员工总数、平均薪资SELECTCOUNT(*)AStotal,AVG(salary)ASavg_salFROMemployees;⚠️ 核心易错原理为什么分组函数不能直接用在 WHERE 中这是面试和日常开发的高频错误。原因在于SQL 语句的执行顺序FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY因为WHERE是在GROUP BY分组之前执行的此时数据还未分组所以WHERE子句中不能使用分组函数如AVG、SUM。正确的做法是使用HAVING子句对分组后的结果进行过滤。-- ❌ 错误写法报错SELECTdepartment,AVG(salary)FROMemployeesWHEREAVG(salary)5000;-- ✅ 正确写法使用 HAVINGSELECTdepartment,AVG(salary)FROMemployeesGROUPBYdepartmentHAVINGAVG(salary)5000;二、单行处理函数重点大全单行函数对每一行输入返回一个输出结果是日常查询的主角。1. 字符串函数函数说明实战示例SUBSTR(str, pos, len)截取子串下标从1开始SUBSTR(Hello, 2, 3)→ellCONCAT(str1, str2)安全拼接遇NULL会变NULL可用IFNULL处理CONCAT(first_name, , last_name)CHAR_LENGTH(str)返回字符长度推荐CHAR_LENGTH(中文)→2LENGTH(str)返回字节长度注意中文编码LENGTH(中文)在UTF8下为6UPPER()/LOWER()大小写转换常用于模糊查询的规范化REPLACE(str, a, b)替换所有 a 为 bREPLACE(abcabc, a, x)→xbcxbcINSTR(str, substr)返回子串首次出现的位置INSTR(hello, l)→3TRIM()/LTRIM()/RTRIM()去除空格/指定字符TRIM( hi )→hi2. 数值与高级数学函数含进制转换除了常规的四舍五入这里补充了原版遗漏的指数、对数及进制转换。函数说明示例ROUND(x, y)四舍五入保留 y 位小数ROUND(3.14159, 2)→3.14TRUNCATE(x, y)截断保留 y 位小数不四舍五入TRUNCATE(3.14159, 2)→3.14CEIL(x)/FLOOR(x)向上/向下取整CEIL(3.2)→4,FLOOR(3.9)→3ABS(x)/MOD(x, y)绝对值 / 取模MOD(10, 3)→1POW(x, y)/SQRT(x)幂运算 / 平方根POW(2, 3)→8EXP(x)自然底数 e 的 x 次方EXP(1)→2.71828LOG(x)/LOG10(x)自然对数 / 10 为底对数常用于数据标准化计算BIN(x)返回二进制BIN(10)→1010OCT(x)返回八进制OCT(10)→12HEX(x)返回十六进制HEX(255)→FFCONV(x, f1, f2)万能进制转换从 f1 进制转为 f2 进制CONV(A, 16, 10)→10-- 生成 100~999 随机整数 转十六进制显示SELECTFLOOR(100RAND()*900)ASrandom_num,HEX(FLOOR(100RAND()*900))AShex_value;⚠️ 特别注意FORMAT()返回的是字符串很多新手会将FORMAT()当作数值处理但它在 MySQL 中返回的是带千分位逗号的字符串如果参与数值运算会触发隐式转换影响性能或产生意外结果。若仅需保留小数位数请优先使用ROUND()或CAST()。-- 谨慎使用 FORMAT 做计算SELECTFORMAT(1234567.89,2);-- 结果: 1,234,567.89 (字符串类型)3. 比较运算函数填补原版空缺函数说明实战场景GREATEST(val1, val2, ...)返回参数列表中的最大值比较多列数据取最高分LEAST(val1, val2, ...)返回参数列表中的最小值取最低库存警戒值COALESCE(val1, val2, ...)返回第一个非 NULL 的值极高频给字段设置默认备选值ISNULL(expr)判断是否为 NULL1是0否替代IS NULL的简便写法IN (set)/NOT IN (set)值是否存在于集合中子查询或枚举筛选 致命陷阱NOT IN遇上NULL会导致结果集为空这是 MySQL 中最经典的“翻车现场”。如果NOT IN的子查询结果集中包含了NULL那么整个查询将不返回任何行因为NULL与任何值比较的结果都是UNKNOWN。-- 假设子查询返回结果包含 NULLSELECT*FROMusersWHEREidNOTIN(1,2,NULL);-- 结果集为空因为 任何值 ! NULL 为 UNKNOWN解决方案在子查询中务必使用WHERE xxx IS NOT NULL过滤掉 NULL或使用NOT EXISTS代替。4. 日期时间函数完整加强版涵盖了季度、星期名等原版遗漏的高频实用函数。函数说明示例NOW()/CURDATE()/CURTIME()当前日期时间/日期/时间常用于插入创建时间YEAR()/MONTH()/DAY()提取年/月/日MONTH(2026-08-10)→8QUARTER(date)返回季度1~4QUARTER(2026-08-10)→3WEEK(date)返回一年中的第几周常用于周报统计DAYNAME(date)返回星期英文名Monday…前端展示友好EXTRACT(type FROM date)提取指定部分年月日时分秒比 YEAR/MONTH 更灵活DATE_FORMAT(date, fmt)按自定义格式展示%Y年%m月STR_TO_DATE(str, fmt)字符串解析为日期插入数据时必备DATEDIFF(date1, date2)计算相差天数计算工龄、账龄LAST_DAY(date)返回当月最后一天生成月度报表截止日-- 查询本月过生日的员工SELECTname,birthdayFROMemployeesWHEREMONTH(birthday)MONTH(CURDATE());5. 流程控制函数函数说明示例IF(cond, v1, v2)三目运算IF(score60, 及格, 不及格)IFNULL(v1, v2)空值替换精髓IFNULL(commission, 0)CASE WHEN ... THEN ... END多条件分支等价于 if-else如下方等级划分-- CASE WHEN 标准用法成绩等级划分SELECTname,score,CASEWHENscore90THENAWHENscore80THENBWHENscore60THENCELSEDENDASgradeFROMstudents;6. 类型转换与加密/系统/JSON函数分类函数示例核心注意类型转换CAST(123 AS SIGNED)、CONVERT(x, CHAR)字符串转数字时若包含字母结果为0加密/散列MD5(str)不可逆、AES_ENCRYPT(str, key)可逆密码存储推荐SHA2(str, 256)加盐系统信息VERSION()、DATABASE()、USER()、LAST_INSERT_ID()LAST_INSERT_ID()仅返回本次会话最后的自增IDJSONJSON_OBJECT()、JSON_EXTRACT()、JSON_SET()处理半结构化数据神器第二部分自定义函数UDF—— 核心修正与深化当内置函数不够用时我们可以自己造轮子。但关于函数体内部能否写INSERT/UPDATE/DELETE网上很多教程存在误导这里郑重修正。一、自定义函数的定义与语法DELIMITER$$-- 修改结束符避免函数体内的分号被截断CREATEFUNCTION函数名([参数名 数据类型,...])RETURNS返回值类型BEGIN-- 函数体可声明变量 DECLARERETURN返回值;END$$DELIMITER;-- 恢复结束符二、实战示例无参 / 有参 / 带变量示例1无参获取当前季度DELIMITER$$CREATEFUNCTIONget_quarter()RETURNSINTBEGINRETURNQUARTER(CURDATE());END$$DELIMITER;示例2含税薪资计算DELIMITER$$CREATEFUNCTIONtax_salary(baseDECIMAL(10,2),rateDECIMAL(5,2))RETURNSDECIMAL(10,2)BEGINRETURNbase*(1rate);END$$DELIMITER;三、⚠️ 关于函数中执行 DML数据修改的权威纠正错误认知“自定义函数中不能执行 INSERT/UPDATE/DELETE。”正确结论MySQL 在语法上允许在函数中执行 DML 操作但强烈不推荐为什么不推荐破坏复制安全性如果binlog_format为STATEMENT函数的非确定性行为如依赖随机数或当前时间会导致主从数据不一致。违反函数纯粹性函数通常用于计算并返回值混入数据修改会产生“副作用”使代码难以调试和维护。黄金准则如果需要修改数据请使用存储过程Stored Procedure如果需要获取计算结果请使用自定义函数。职责分离四、查看与删除SHOWFUNCTIONSTATUS;-- 查看所有自定义函数SHOWCREATEFUNCTION函数名;-- 查看创建语句DROPFUNCTIONIFEXISTS函数名;-- 删除第三部分函数使用最佳实践与核心心法✅ 内置函数优先常规数据转换、字符串处理、日期加减、加密校验直接用内置函数性能好且稳定。✅ 自定义函数的适用场景复杂的业务公式复用如根据多字段计算绩效系数。简化极度复杂的嵌套查询提高 SQL 可读性。封装固定的业务规则如根据地区金额计算运费。❌ 绝对要避开的坑防坑指南不要将函数用于大数据量的行级计算在WHERE条件中对字段使用函数如WHERE YEAR(date) 2026会导致索引失效全表扫描。应改为范围查询WHERE date BETWEEN 2026-01-01 AND 2026-12-31。警惕NOT INNULL上文已述这是导致数据莫名其貌丢失的头号杀手建议使用NOT EXISTS替代。注意隐式类型转换字段类型是VARCHAR但传入数字123MySQL 会隐式转换可能导致索引失效。总结MySQL 的函数体系极其强大。掌握内置函数能极大提升日常增删改查的效率而合理使用自定义函数则能让复杂业务逻辑在数据库层优雅落地。本文不仅为你补齐了“比较函数”、“进制转换”、“高级数学”等原厂完整功能更通过“WHERE执行顺序”、“NOT IN NULL陷阱”、“函数DML真相”三个硬核原理剖析帮你拨开了MySQL函数学习中最常见的迷雾。希望这份修订版指南能成为你手边最可靠的参考手册。如果有任何疑问欢迎在评论区交流探讨。扩展阅读MySQL 8.0 官方函数文档https://dev.mysql.com/doc/refman/8.0/en/functions.htmlMySQL 执行计划与索引失效场景详解本文基于 MySQL 8.0 编写已在多处标注易错点建议收藏细读。