MySQL中的常见函数是每个写SQL的人绕不开的基本功。我做数据库开发这些年发现一个规律那些看起来很复杂的数据处理任务底层往往就是几个常见函数在组合发力。字符串拼接截取、日期格式化与计算、数值精度控制、分组后聚合汇总再加条件分支和类型转换基本覆盖了日常开发中九成以上的字段加工需求。这篇分享我按函数族进行梳理每个函数都配上实际业务用法和踩坑记录适合入门后想系统梳理一遍的同学也适合写了好几年SQL还想查漏补缺的开发者。1. 函数分类与设计思路为什么要按函数族来学1.1 把函数分族记忆比背列表高效得多MySQL内置的函数有好几百个但真正在业务SQL里高频出现的数来数去就那几类。我发现很多开发者一上来就对着官方文档从头到尾看看完一遍什么也记不住写SQL的时候该查还是查。更实用的做法是先做分类建立一张“问题到函数”的映射关系表遇到需求时先判断属于哪一类问题再定位具体函数。函数族解决的典型问题代表函数字符串函数文本清洗、拼接、截取、替换、格式化CONCAT、SUBSTRING、REPLACE、TRIM日期时间函数获取当前时间、格式化、日期计算、周期判断NOW、DATE_FORMAT、DATEDIFF、DATE_ADD数值函数舍入、取整、取余、随机数、精度控制ROUND、CEIL、MOD、RAND聚合函数分组统计、去重计数、汇总归并COUNT、SUM、AVG、GROUP_CONCAT流程控制函数条件分支、空值兜底、逻辑判断IF、IFNULL、CASE WHEN、COALESCE类型转换函数显式转换数据类型、规避隐式转换陷阱CAST、CONVERT有了这张表你会发现大部分SQL问题都能归到某一类里去解决。比如运营要一份按月份统计的报表核心就是日期函数加聚合函数做数据清洗的时候优先考虑字符串函数要处理复杂的业务规则判断就交给流程控制函数。分完类再逐个攻破效率比死记硬背高得多。1.2 函数调用的通用约定与执行机制从执行机制上看MySQL函数大多数是逐行计算的。在一张10万行的表上执行SELECT UPPER(name)函数会对每一行的name字段分别计算一次。理解这一点很重要它能解释为什么函数用在WHERE条件里有时会拖慢查询函数计算本身有成本同时函数对索引字段的包装可能会导致索引失效。掌握函数不是单纯记住函数名而是要理解它的运行位置、计算时机和优化影响。调用方式本身很简单函数名加括号加参数。但有几个通用约定值得在开始就建立正确习惯。第一参数顺序必须记牢。比如SUBSTRING(str, pos, len)和LOCATE(substr, str)的参数顺序不同写混了结果天差地别。第二注意NULL的传播特性。绝大多数函数的输入只要是NULL输出就是NULL这会让最终结果出现NULL时需要回头排查源头。第三学会读报错信息。MySQL对参数个数和类型检查得很严格报错信息通常能直接告诉你是哪个参数的问题。第四函数可以嵌套但嵌套层数越多可读性越差。遇到三层以上的嵌套表达式我建议用子查询拆开或者加注释说明计算过程不然下次维护的人会很想打人。2. 字符串函数文本数据清洗与格式化的第一工具族2.1 拼接、截取、替换三个最常用的基础操作先说我使用频率最高的CONCAT。它的作用是把多个字符串首尾相连。实际工作里一个常见场景是生成新的编码比如需要按“品牌-分类-序号”的格式给一批商品重新命名可以直接SELECT CONCAT(brand, -, category, -, id) FROM products。但这里有个大坑如果这些字段中某个值是NULL整个结果会变成NULL不是跳过那一段而是全部失效。很多开发者第一次遇到时都以为是数据问题排查半天才发现是CONCAT的NULL传播特性。解决办法有两种。要么用IFNULL把可能为空的字段包起来要么改用CONCAT_WS。CONCAT_WS是带分隔符拼接第一个参数是分隔符后面是待拼接的内容它会自动跳过NULL值。两者对比我更喜欢CONCAT_WS写起来更简洁语义也更清楚。SUBSTRING是截取函数语法是SUBSTRING(str, pos, len)起点从1开始pos也可以为负数表示从末尾倒数。我经常用它从订单号中提取固定位置的日期段和区域码。这里有一个和主流编程语言不同的细节MySQL的SUBSTRING按字符数截取而不是按字节。在utf8mb4编码的表里直接截取中文字符串不会因为字节数问题导致半个字符的乱码这一点比很多程序语言的处理方式更友好。很多从其他语言转过来的开发者会下意识担心实测下来发现MySQL已经很贴心地处理了这个问题。REPLACE(str, from, to)用于替换字符串中所有匹配的子串是数据清洗的利器。比如导入的外部数据里手机号带了空格、URL带了换行符直接REPLACE(phone, , )就能去掉空格。它替换的是全部匹配项不像某些语言里的replaceFirst只替换第一处。当需要精确到某一次出现时应该先结合SUBSTRING_INDEX定位再去截取而不是硬用REPLACE。SUBSTRING_INDEX(str, delim, count)是很多人低估的一个函数。它按分隔符截取count为正数时从左往右数到第count个分隔符取分隔符之前的内容count为负数时从右往左数。举一个实际场景一张表存了用户上传的文件路径需要提取第一层目录用SUBSTRING_INDEX(path, /, 2)一次搞定。处理IP地址、URL域名解析、键值对拆分时这个函数比多次嵌套SUBSTRING干净得多。2.2 去空格、格式化与字符长度判断的细节TRIM、LTRIM、RTRIM是三个去空格函数。LTRIM去掉左侧空格RTRIM去掉右侧空格TRIM默认去掉两侧空格。用户输入的数据几乎都会有前后空格问题做账号比对、去重统计之前最好先用TRIM清洗一遍。TRIM还支持指定删除字符语法是TRIM(BOTH x FROM str)处理带固定包裹字符的字段时很实用但日常用得少重点记住默认的去空格行为就够了。LOWER和UPPER是大小写转换函数。它们看起来简单但有一个隐藏细节和数据库排序规则collation有关。在MySQL默认的utf8mb4_general_ci排序规则下字符串比较本身不区分大小写所以SELECT LOWER(ABC) abc返回1但SELECT MySQL mysql同样返回1。只有当表的列使用_bin或_cs排序规则时大小写才敏感。如果业务要求大小写敏感的匹配可以显式调用LOWER或UPPER统一口径也可以使用BINARY关键字强制字节比较。知道这个背景你就不会在排查“为什么LOWER了半天结果还一样”上浪费太多时间。LENGTH和CHAR_LENGTH经常被搞混。LENGTH返回字节数CHAR_LENGTH返回字符数。在utf8mb4编码下一个中文字符通常占3个字节一个emoji占4个字节而CHAR_LENGTH都只算1个字符。所以通过比较LENGTH(name)和CHAR_LENGTH(name)是否一致可以快速找出字段里是否含有中文或其他多字节字符。在做昵称、备注信息清洗时这个技巧能快速筛出异常数据。LPAD和RPAD是补位函数。LPAD(str, len, padstr)在字符串左侧补字符到指定长度RPAD在右侧补。生成订单号、优惠券编码时需要固定位数比如LPAD(id, 8, 0)把1变成00000001。这里要提醒一句如果补位后的目标长度小于原字符串长度MySQL会从左侧截断生成固定长度编码时这个行为符合预期。LEFT和RIGHT分别从左右两侧截取指定数量的字符配合INSTR、LOCATE查找子串位置后做方向性截取能拼出很多优雅的表达式。LOCATE(substr, str)返回子串第一次出现的位置找不到返回0在WHERE条件中写LOCATE(abc, content) 0等价于“内容中包含abc”语义很直观。3. 日期时间函数时间维度统计与周期计算的基石3.1 获取当前时间与日期格式化的常用写法先讲获取当前时间的一组函数。NOW()返回当前日期时间CURDATE()返回纯日期CURTIME()返回纯时间。很多人纠结NOW()和SYSDATE()的区别这里一次说清楚NOW()在一个查询语句内部的多个调用返回同一个值而SYSDATE()每次调用都取服务器当前时间。如果在一条长SQL里多次使用SYSDATE()可能拿到跨秒甚至跨分钟的微小不一致值。所以做“当前时间”相关的过滤和写入时我建议统一用NOW()。UNIX_TIMESTAMP()能把日期时间转成秒级时间戳FROM_UNIXTIME(ts)反向转换对接外部系统、处理前端传递的时间戳时很常用。DATE_FORMAT是最重量级的格式化函数。核心是格式字符串比如DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)返回标准的日期时间字符串。做日报、月报、周报统计时几乎必用DATE_FORMAT按时间维度分组。这里必须提醒格式符的大小写区别%Y是四位年份%y是两位年份%m是两位数字月份%M是英文月份名%d是两位数字日期%D是带英文后缀的日期。M和m差一个大小写结果完全不同。我的习惯是先在开发环境跑一遍DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)确认格式串再写进统计SQL能省不少返工时间。STR_TO_DATE是DATE_FORMAT的逆操作把字符串解析成日期。前端传过来的日期常常是2024-06-01 10:30:00这样的字符串格式匹配时STR_TO_DATE(str, %Y-%m-%d %H:%i:%s)就能得到合法的DATETIME。当源数据里混着2024/06/01和2024-06-01两种风格时统一用STR_TO_DATE转换后再比较比直接拿字符串比较可靠得多因为字符串比较的字典序和日期顺序在月份、日期的个位数上会产生错位。MySQL对标准格式字符串也存在隐式转换但显式转换的好处是格式可控、意图可读排查问题更省力。3.2 日期加减、时间差与周期判断的业务写法DATEDIFF(expr1, expr2)返回expr1减去expr2的天数且只比较日期部分时间部分被忽略。计算“从今天到月底还有几天”时直接DATEDIFF(LAST_DAY(CURDATE()), CURDATE())就能得到结果。如果需要精确到小时、分钟的时间差要换TIMESTAMPDIFF。TIMESTAMPDIFF(unit, begin, end)返回end和begin之间跨越的完整unit单位数unit支持YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND。它和DATEDIFF最核心的差异是TIMESTAMPDIFF按“完整单位”计数DATEDIFF按“自然日差值”计数。比如从5月31日到6月1日DATEDIFF返回1但TIMESTAMPDIFF(MONTH, ...)返回0因为并没有跨越一个完整的月份。计算用户年龄时TIMESTAMPDIFF(YEAR, birthdate, CURDATE())是标准做法比简单用当前年份减出生年份更严谨。做会员周期判断、合同周期计算时一定要想清楚用哪一个否则统计口径会出问题。DATE_ADD和DATE_SUB用来做日期加减语法是DATE_ADD(date, INTERVAL expr unit)DATE_SUB同理。INTERVAL的单位很丰富DAY、HOUR、MINUTE、MONTH、YEAR、QUARTER、WEEK都支持。比如DATE_SUB(NOW(), INTERVAL 30 MINUTE)找半小时前DATE_ADD(NOW(), INTERVAL 3 MONTH)推算三个月后。做“最近7天”统计时常见写法是WHERE create_time DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND create_time DATE_ADD(CURDATE(), INTERVAL 1 DAY)这种左右边界都写清楚的方式虽然啰嗦但不会漏数据也不会多数据。LAST_DAY(date)返回当月最后一天做月末结算、账单日计算时很顺手。要拿到下个月第一天的日期可以DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)。WEEK(date, mode)和YEARWEEK(date, mode)输出周编号但mode参数影响周起始日和第一周的定义。比如WEEK(date, 1)表示周一为一周的开始WEEK(date, 0)默认周日开始。直接使用DAYOFWEEK输出星期几时记住MySQL里周日是1、周一是2这和很多程序语言的习惯不同非常容易踩坑。周报统计我一般显式指定mode参数避免默认值差异导致周错位。4. 数值函数与聚合函数从字段运算到分组统计的进阶路径4.1 数学运算类函数的取舍与精度控制数值函数本身不复杂关键是掌握舍入方式和精度控制。ROUND(x, d)四舍五入保留d位小数TRUNCATE(x, d)直接截断。二者在金额计算中结果会有差异。比如ROUND(10.125, 2)在MySQL里得到10.13而TRUNCATE(10.125, 2)是10.12。业务上对尾差有严格要求时必须统一约定用哪个函数否则对账很容易对不上。存储金额时我建议用DECIMAL而不是FLOAT避免二进制浮点误差计算时再把结果用ROUND统一精度。CEIL和FLOOR分别向上取整和向下取整。分页计算总页数时总页数等于CEIL(总记录数/每页条数)这个场景很常见。MOD(a, b)取余数除了判断奇偶、按ID分片等用途还有一个实用场景是生成循环序列比如MOD(id, 12) 1可以生成1到12的周期性编号。ABS返回绝对值SIGN返回正负号POWER和SQRT做幂运算和平方根这些都是基础能力用的时候查一下参数就行。RAND()返回0到1之间的随机数这里要专门提醒一句。RAND()如果出现在WHERE条件里比如WHERE RAND() 0.1它的执行结果不稳定性能也不好因为每一行都要计算一次随机值索引也帮不上忙。做随机抽样时小表可以直接ORDER BY RAND() LIMIT n大表应该先取主键范围的随机偏移再去查详情避免全表参与排序。4.2 聚合函数与GROUP_CONCAT的使用细节和坑聚合函数COUNT、SUM、AVG、MAX、MIN是对一组行做归约操作的函数常和GROUP BY搭配。第一个容易踩的坑是COUNT的语义COUNT()统计所有行COUNT(字段)统计该字段非NULL的行。当LEFT JOIN产生一对多关联时COUNT(主表.主键)往往比COUNT()更能反映主表真实行数因为COUNT()会把从表多行一起算进去。比如主表一个订单关联了三个明细COUNT()会得到3而COUNT(订单主键)会得到1。第二个坑是SUM和AVG遇到全NULL时返回NULL而不是0页面拿到NULL会显示空白业务逻辑没兜底还会报错。报表里我习惯用IFNULL(SUM(x), 0)包一层。COUNT(DISTINCT 字段)是去重计数的常用写法但大表下去重计数性能开销不小。如果只要近似值可以考虑用MySQL 8.0的APPROX_COUNT_DISTINCT或者提前维护统计表。MAX和MIN除了数值还能用于字符串和日期比如取一组记录中的最大日期MAX(create_time)。HAVING是分组后的过滤条件和WHERE的过滤时机不同WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。找订单数超过10的客户要写GROUP BY customer_id HAVING COUNT(*) 10这个逻辑很多人会写错成WHERE。GROUP_CONCAT是MySQL特色函数把分组内的多个值拼成一个字符串。我的典型使用场景是把一张子表的多个标签拼到主表结果里或者把关联的多条记录ID收集成逗号分隔串传给前端。语法是GROUP_CONCAT(字段 ORDER BY 字段 SEPARATOR )ORDER BY子句控制拼接顺序比如按时间倒序拼接标签。最容易被忽视的是它的长度限制默认group_concat_max_len是1024字节超过会被静默截断返回的数据不完整。遇到这类问题携带前导检查输出长度可以在会话里SET SESSION group_concat_max_len 1048576。这个坑很多同事踩过输出结果短一截还不报错排查起来很费劲。5. 流程控制函数与类型转换让SQL具备分支逻辑5.1 IF、IFNULL、COALESCE和CASE WHEN的适用边界IF(expr, true_value, false_value)是最简单的条件判断适合二元分支。IFNULL(expr, null_value)是专门的NULL兜底函数比写IF(expr IS NULL, null_value, expr)简洁很多。COALESCE(expr1, expr2, ...)从左往右返回第一个非NULL值当多个字段都可能为空、需要按优先级取第一个实际值的时候COALESCE是优选。比如运营配置里有三档价格优先级依次是活动价、会员价、原价就可以写成COALESCE(activity_price, member_price, original_price)。还有一个容易被忽略的NULLIF(expr1, expr2)当两个值相等时返回NULL常用来防止除零错误比如百分比计算时用IFNULL(num / NULLIF(den, 0), 0)分母为0时转为NULL再兜底避免报错。CASE WHEN是最灵活的分支结构有两种写法。一种是CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 默认 END适合范围判断和复杂布尔表达式另一种是CASE 表达式 WHEN 值1 THEN 结果1 ELSE 默认 END适合等值匹配。实际项目中我更喜欢把CASE WHEN用在聚合统计里利用它返回0或1的特性在一条SELECT里同时统计多个维度的数量。比如同时输出今日下单用户数和今日支付用户数可以写COUNT(CASE WHEN status paid THEN user_id END)。要注意CASE WHEN分支按书写顺序逐个判断一旦某个条件成立后面的分支不再执行。写区间条件时从窄到宽排列避免条件重叠导致结果偏差测试时特意覆盖边界值。5.2 CAST和CONVERT的类型转换原则与隐式转换陷阱CAST(expr AS type)和CONVERT(expr, type)是MySQL中显式类型转换的两个函数功能基本一致。CAST是标准SQL写法CONVERT还额外支持带USING的字符集转换用法。日常使用场景主要是字符串转日期、数字转字符串、字符串转十进制数比如CAST(2024-06-01 AS DATE)、CAST(amount AS DECIMAL(12,2))。做报表导出时金额字段经常需要在SQL层统一成两位小数的字符串CAST很实用。真正让开发者头疼的是隐式转换。MySQL在比较不同数据类型的值时会自动进行类型转换这个行为虽然方便但隐藏着很多坑。最典型的是字符串字段和数字比较字段是VARCHAR类型存的是数字WHERE varchar_col 123MySQL会尝试把字符串字段转换成数字这样会导致字段上的索引失效查询性能急剧下降。更安全的写法是保持两侧类型一致写成WHERE varchar_col 123。另一个常见场景是日期字符串与DATETIME字段比较如果字符串格式不标准MySQL转换失败可能返回NULL结果为空。在查询条件里尽量让比较双方类型一致或显式转换后再比较不要依赖隐式转换。6. 常见问题与排查技巧实录6.1 函数使用中容易踩的坑与快速定位思路把工作中遇到频率最高的坑整理成速查表方便直接对照排查。问题现象常见原因快速处理办法CONCAT拼接结果为空参与拼接的参数有NULL改用CONCAT_WS或对空字段用IFNULL兜底GROUP_CONCAT返回内容不完整group_concat_max_len默认1024字节会话级调大SET SESSION group_concat_max_len日期统计结果多了一条用BETWEEN包含第二日零点改为边界 AND 下边界带索引的字段查询变慢WHERE中索引字段套了函数改为范围条件不要对索引列做函数运算SUM字段结果为NULL全组数据为空用IFNULL(SUM(x), 0)包裹字符串转日期结果异常格式串大小写写错先用NOW()验证DATE_FORMAT格式串第一个典型坑是CONCAT返回NULL。当拼接结果突然变成NULL时第一反应应该是检查参与拼接的字段有没有NULL。定位方法很简单把SELECT语句中的字段逐个用IFNULL包起来或者改用CONCAT_WS验证。第二个是GROUP_CONCAT结果被截断。如果拼接出来的列表长度固定且明显短于预期检查group_concat_max_len临时在会话里改大即可。第三个是日期格式化结果错乱一个常被忽视的原因是格式串大小写写错%M和%m、%Y和%y含义完全不同。排查时先SELECT出DATE_FORMAT的原始结果和预期比对。第四个是时间范围边界问题。统计“当日”数据时最稳妥的写法是open_time 2024-06-01 00:00:00 AND open_time 2024-06-02 00:00:00不要使用BETWEEN 2024-06-01 AND 2024-06-02。因为DATETIME字段在第二天的零点整那一行也会被包含进去统计多出一条。第五个是COUNT和SUM语义没分清COUNT()数行COUNT(字段)排除NULL行SUM全NULL返回NULL。关联查询中COUNT主表字段比COUNT()更可靠。第六个是RAND()在WHERE里的使用看似按比例抽样实际上随机值无法索引且每次执行结果不同测试结果不可复现。建议先查总数再按固定种子生成随机偏移或者小表用ORDER BY RAND() LIMIT。6.2 从性能角度给函数使用定几条长期规矩最后聊几个性能相关的经验都是长期积累下来的。最重要的规矩不要在WHERE条件的索引字段上套函数。很多人写日期范围统计时习惯写WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-06-01这个写法在数据量小的时候没问题几百万行之后会明显变慢因为函数让索引失效。把条件改写成范围比较create_time 2024-06-01 AND create_time 2024-06-02既能用上索引语义也更准确。第二条规矩不要在LIKE模式的左侧使用通配符%xxx这种写法同样会让索引失效。凡是WHERE里对索引列做运算、套函数、隐式类型转换都可能放弃索引查询前先想一想这个字段能不能走索引。第三条规矩能少嵌套就少嵌套。函数嵌套过深时SQL的可读性会迅速下降很难调试。遇到超过三层的嵌套表达式我会考虑用子查询或者在应用层算好中间值往往更清晰。第四条规矩注意函数在版本间的差异。MySQL 8.0和5.7在JSON函数、窗口函数、字符串函数实现细节上都有差异迁移升级前后应该把涉及函数的部分重新测试一遍。特别是加密函数和字符集相关函数不同版本行为变化大线上升级前必须验证。实用习惯上建议在开发环境建一个函数验证库专门用来快速验证某个函数的行为。执行SELECT SUBSTRING(MySQL函数, 3, 2)、SELECT DATE_FORMAT(NOW(), %Y-%m)这种一行语句跑一下立刻看到结果比反复翻文档快很多。我带过的开发者用这个方式排查SQL问题效率提升非常明显你也可以试试。