但凡写过两年SQL的人手里应该都攒过一本「MySQL函数笔记」这个函数怎么拼、那个函数返回什么、为什么同样一段SQL换个环境就报错——这些零碎问题最后几乎都能在MySQL内置函数这里碰头。MySQL内置函数是数据库提供的一组现成处理函数覆盖字符串、数值、日期时间、条件判断、聚合统计等场景用好了能让SQL变得又短又稳不用一股脑把逻辑搬到程序里一遍遍重写。这篇文章打算把内置函数按「分类体系→高频实操→性能雷区→踩坑排查」这条线完整梳理一遍。无论是刚装好MySQL 8.0、还在照着教程建表的初学者还是已经负责业务库、天天写报表SQL的开发或者是做数据同步和调优的运维都能从这里找到可直接抄走的东西。函数本身不分项目大小建订单表、做用户画像、统计活动转化、同步数据到ClickHouse处处都用得上值得认真过一遍。1. 内置函数全景先建体系再记细节别一头扎进函数堆MySQL官方文档里的函数加起来几百个如果按照「看到一个记一个」的方式去学结果大概率是边记边忘真到写SQL时还要反复查。更合理的做法是先建一个分类框架把函数按用途装进抽屉里用到哪一类就翻哪个抽屉这样记忆负担小很多应用的时候也能更快定位。我自己习惯把内置函数分成六大类字符串处理、数值计算、日期时间、流程控制、聚合统计、JSON与系统信息。前四类属于「行级函数」也就是对每一行数据单独做处理聚合函数则是把多行数据汇总成一个结果JSON和系统函数在5.7之后越来越重要尤其是8.0把JSON能力大幅增强之后几乎成了业务表设计的标配。分类之外还要搞清楚函数的出现位置。同一个函数放在SELECT里、WHERE里、GROUP BY里作用和语义可能截然不同。比如SUBSTRING(phone, 1, 3)放在SELECT里是把手机号前三位取出来展示放在WHERE里就是按前三位过滤放在GROUP BY里就是按前三位分组统计。这一点很多人初始化学习时会忽略实际写SQL时却最容易在这里犯迷糊。内置函数还有一个需要留意的点是「内置」两个字。它指的是MySQL服务端自带的函数不需要额外安装插件也不需要你自己写逻辑。和它相对的是自定义函数UDF那是需要开发者自己创建、自己维护的函数体不在本文讨论范围之内。在实际项目中能用内置函数解决的尽量不要去写自定义函数原因后面性能部分会详细说。版本差异同样不容忽视。MySQL 5.7和8.0虽然都以「MySQL」命名但函数能力差距不小。JSON函数在5.7里已经能用基础语法8.0进一步支持JSON_TABLE等高级特性窗口函数是8.0才有的5.7里只能用子查询加变量硬凑REGEXP_REPLACE这类字符串正则替换函数也只在8.0里才完整可用。所以写函数之前第一件事是确认线上版本别拿8.0的语法去5.7上跑那一定会报错。2. 字符串与数值函数日常SQL里最常用的两个大类2.1 字符串函数拼接、截取、替换、清洗一站搞定字符串函数是平时写SQL用得最多的尤其是做数据清洗和报表展示的时候。先看拼接CONCAT(str1, str2, ...)可以把多个字段或常量拼成一个字符串但有一个非常经典的坑——只要任何一个参数是NULL整个结果就会变成NULL。比如CONCAT(first_name, last_name)只要last_name为空结果就整个是空的这在用户名单拼接场景里很容易引发事故。解决办法有两种各看场景。第一种是CONCAT_WS(separator, str1, str2, ...)它会在参数之间插入分隔符并且会自动跳过NULL值不会返回NULL。第二种是用IFNULL先把NULL转成默认值再拼接。我的习惯是拼接地址、姓名这类「某个字段可能缺失但其他字段还要保留」的场景优先用CONCAT_WS需要严格控制结果格式的场景用IFNULL手动指定空值替换。截取函数里SUBSTRING(str, pos, len)是最基础的一个。注意MySQL的字符位置是从1开始数的不是从0开始这点和Java、JavaScript的字符串截取习惯完全不同刚切换过来的人很容易写串。SUBSTRING(2024-06-15, 1, 4)返回的是2024很多人第一次写成SUBSTRING(2024-06-15, 0, 4)结果会平白多出一个空字符或错位。从身份证取生日是一个很典型的综合练习CONCAT(SUBSTRING(id_card, 7, 4), -, SUBSTRING(id_card, 11, 2), -, SUBSTRING(id_card, 13, 2))一次把截取和拼接都用上了。如果只想取左侧或右侧固定长度还有LEFT(str, len)和RIGHT(str, len)两个便捷函数比如RIGHT(phone, 4)取手机号后四位做脱敏展示。查找和替换也是高频操作。LOCATE(substr, str)返回子串第一次出现的位置找不到返回0常用来做条件判断比如找出所有邮箱是QQ邮箱的用户WHERE LOCATE(qq.com, email) 0。REPLACE(str, from_str, to_str)做全量替换注意它是替换所有匹配项不是只替换第一个。清洗用户输入数据时我会连续嵌套好几个REPLACE把回车、换行、多个空格都清理掉。有一个点必须单独拎出来讲LENGTH(str)和CHAR_LENGTH(str)的区别。LENGTH返回的是字节数CHAR_LENGTH返回的是字符数。在中文字符集UTF-8下一个汉字占3个字节LENGTH(张三)的结果是6CHAR_LENGTH(张三)的结果是2。很多人在做长度校验时用错了函数结果明明限定了用户名最多10个字符中文用户却只能存3个这就是典型的函数误用。2.2 数值函数取整、取余、随机数的那些坑数值函数在报表计算中无处不在但坑也多。最典型的是取整函数的选择。MySQL里有ROUND()、FLOOR()、CEILING()、TRUNCATE()四个看起来差不多的函数实际行为完全不同。ROUND(3.14159, 2)是四舍五入到指定小数位返回3.14FLOOR(3.99)向下取整返回3CEILING(3.01)向上取整返回4TRUNCATE(3.99, 1)是直接截断不管后面是几返回3.9。它们的区别可以用一句话总结FLOOR和CEILING只处理整数方向TRUNCATE只看小数位不管舍入ROUND才会真正考虑进位。计算分页偏移量、库存分配这类场景选错取整函数会导致结果差一。MOD(a, b)取余数等价于a % b。有一个容易被忽略的行为当b为负数时MySQL中MOD的结果可能和编程语言里不一样所以习惯上用正数做模运算更安全。POWER(a, b)和SQRT()用于幂运算和平方根ABS()取绝对值这些就没太多幺蛾子直接用。随机数函数RAND()值得单独说一说。RAND()每次执行都会返回一个0到1之间的随机小数注意是「每次执行」都会变。如果在一个查询里多次调用RAND()每一行拿到的随机值都不同。想生成指定范围内的随机整数标准写法是FLOOR(RAND() * (max - min 1)) min。比如要生成1到100之间的随机整数FLOOR(RAND() * 100) 1。这个公式理解起来也不难RAND()最大接近1乘以区间长度100得到接近100的数FLOOR后得到0到99再加1就是1到100。我实际做过的一个需求是运营后台的随机抽奖要从商品表里随机抽10个商品。当时写的SQL是ORDER BY RAND() LIMIT 10功能是实现了但商品量大之后明显变慢。原因是ORDER BY RAND()需要为每一行生成随机数再排序全表扫描加文件排序数据量上了百万就会拖垮库。后来改成先SELECT id FROM table WHERE ... LIMIT 1000取出候选集在程序里随机挑10个再用主键回表速度快了一个数量级。这也是一个典型教训函数好用但不能无脑套在热点查询里。3. 日期时间函数订单统计和报表的地基工程3.1 拿到当前时间NOW、CURDATE、SYSDATE三兄弟别混用业务表里几乎都有create_time字段所以获取当前时间的函数是入门第一课。NOW()返回当前完整的日期和时间格式是YYYY-MM-DD HH:MM:SSCURDATE()只返回日期部分CURTIME()只返回时间部分。这三兄弟很简单但有个隐蔽的坑NOW()和SYSDATE()在官方文档里都表示当前时间执行结果看起来一样实际机制不同。NOW()是语句开始执行的时间一条SQL里不管调用多少次NOW()拿到的都是同一个值而SYSDATE()是函数被真正执行那一刻的时间如果一条SQL执行耗时比较长不同位置的SYSDATE()返回值可能不一样。这个差异在复制架构里尤其危险。主库执行一条用了SYSDATE()的长SQL备库在回放这条SQL时调用SYSDATE()拿到的是备库当前时间两边数据就可能不一致。生产环境处理流水、订单这类对时间一致性敏感的表我会统一用NOW()或直接让字段走DEFAULT CURRENT_TIMESTAMP不碰SYSDATE()。3.2 格式化与解析DATE_FORMAT和STR_TO_DATE是一对镜像日期格式化的核心函数是DATE_FORMAT(date, format)。format参数用一堆百分号占位符表示输出格式最常用的几个是%Y四位数年份、%m两位数月份、%d两位数日期、%H24小时制小时、%i分钟、%s秒。典型写法DATE_FORMAT(create_time, %Y-%m-%d %H:%i:%s)输出2024-06-15 14:30:00。把字符串解析成日期的函数是STR_TO_DATE(str, format)和DATE_FORMAT的格式占位符完全一致相当于镜像操作。比如前端传过来一个2024/06/15这种格式直接存进DATE字段之前要先解析STR_TO_DATE(2024/06/15, %Y/%m/%d)。如果不做解析让MySQL隐式转换很容易出现格式不识别导致报错或存储异常的情况。有一个高频报表需求按天、按月、按年分组统计。按月分组的经典写法是DATE_FORMAT(create_time, %Y-%m)作为分组键然后COUNT或SUM。但这里有一个必须提前知道的性能代价对create_time字段套DATE_FORMAT函数后这个字段上的索引就失效了大量数据时查询会慢。具体解法后面第6节专门讲这里先记住结论。3.3 时间差与日期运算DATEDIFF和TIMESTAMPDIFF的细节差异计算两个日期之间差多少天用DATEDIFF(expr1, expr2)结果是expr1减expr2的天数只看日期部分不看时间。比如DATEDIFF(2024-06-15, 2024-06-01)返回14用来算用户注册天数、优惠券剩余有效期都很顺手。TIMESTAMPDIFF(unit, start, end)则更通用unit可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR等比如计算两个时间之间差多少分钟TIMESTAMPDIFF(MINUTE, start_time, end_time)。注意参数顺序是「结束时间在前开始时间在后」很多人第一次用总写反导致结果出现负数。日期加减运算用DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)也可以用等价的ADDDATE和SUBDATE。我的习惯是统一用DATE_ADD/DATE_SUB因为INTERVAL语法更清晰。比如统计最近7天订单WHERE create_time DATE_SUB(CURDATE(), INTERVAL 7 DAY)。注意这里用CURDATE()而不是NOW()因为CURDATE()返回日期和日期字段比较时不会把当天零点之前的数据漏掉。如果你在做每月1号自动结算、每周一自动汇总这类周期性任务遵循一个原则能用日期函数直接在SQL里算出来的就不要在程序里先算好再传参。这样逻辑收敛在数据库层排查问题时只需要看SQL就能理解全部时间口径。4. 流程控制与聚合函数让SQL具备业务判断能力4.1 IF、IFNULL、NULLIF、CASE WHEN的使用边界流程控制函数让SQL不只是「查数据」还能在查询过程中做判断。最基础的是IF(expr, true_value, false_value)三目运算符的SQL版。比如把订单金额大于100的标记为「大单」IF(amount 100, 大单, 普通单)。IFNULL(expr1, expr2)专门处理NULLexpr1为NULL时返回expr2。更灵活的是COALESCE(expr1, expr2, ..., exprN)它能依次检查多个参数返回第一个非NULL值。COALESCE在多个可能为空的字段里取「第一个有效值」这个场景非常好用比如COALESCE(nickname, real_name, 匿名用户)用户的昵称没填就取真名真名也没有就显示匿名用户。NULLIF(expr1, expr2)的逻辑是当expr1等于expr2时返回NULL否则返回expr1。这个函数最常见的用法是做「除零保护」。比如统计客单价SUM(amount) / NULLIF(COUNT(*), 0)当COUNT(*)0时NULLIF返回NULL除法结果就是NULL而不是报错。这一类「不要让SQL直接除零」的细节就是日常开发里最容易体现功力的地方。CASE WHEN是流程控制里的重头戏也是我最推荐的条件判断写法。它的可读性比嵌套IF强太多特别是多个分支时IF嵌套写三层以上基本没法维护CASE WHEN一层一层列出来谁看了都明白。订单状态转中文是个经典例子SELECT order_no, CASE status WHEN 1 THEN 待付款 WHEN 2 THEN 已付款 WHEN 3 THEN 已发货 ELSE 未知状态 END AS status_text FROM orders;这里有一个新手常踩的坑CASE WHEN如果没有匹配到任何分支又没有写ELSE结果会返回NULL。状态转换场景里这会让展示层直接出现空值。我现在的规矩是所有CASE WHEN一律写ELSE兜底哪怕兜底值就是未知也要把NULL可能性堵死。4.2 聚合函数COUNT、SUM、AVG、MAX、MIN的隐藏陷阱聚合函数是把多行数据汇总成一行结果的函数通常和GROUP BY搭配使用。COUNT(*)统计行数、COUNT(字段)统计该字段非NULL的行数这两者的差异是最常见的坑。COUNT()不管字段值是不是NULL都会计数而COUNT(字段)会跳过NULL。如果写成COUNT(remark)想统计有备注的订单数而某些订单的remark是NULL结果会比COUNT()少这是完全正常的但很多人会当成Bug来排查。SUM和AVG都有一个特性计算时忽略NULL值。也就是说某一行字段是NULL不会参与SUM的累加也不会被算进AVG的分母。但如果整组数据都是NULLSUM返回NULL而不是0AVG也返回NULL。报表里把这些值直接展示出来前端可能显示成空白甚至报错。稳妥做法是外层套IFNULLIFNULL(SUM(amount), 0)让结果为0而不是NULL。MAX和MIN相对简单取一组数据的最大最小值。注意它们同样忽略NULL所以不会出现「最小值是NULL」的情况。GROUP_CONCAT是把一组数据拼成字符串的函数报表场景里很实用。比如查一个订单下的所有商品名GROUP_CONCAT(product_name SEPARATOR 、)。它有两个注意事项一是默认长度限制是1024字节超过会被静默截断需要先SET SESSION group_concat_max_len 102400调大二是排序稳定性问题如果想让拼接结果按时间顺序排列要写成GROUP_CONCAT(product_name ORDER BY create_time SEPARATOR 、)。聚合函数配合CASE WHEN可以做行转列。一个经典的例子统计各月份订单里不同支付方式的数量占比。SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS total_orders, SUM(CASE WHEN pay_type wechat THEN 1 ELSE 0 END) AS wechat_orders, SUM(CASE WHEN pay_type alipay THEN 1 ELSE 0 END) AS alipay_orders FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);CASE WHEN在里面充当了过滤器的角色满足条件返回1不满足返回0SUM之后就是计数。这个模式非常常用建议直接背下来。5. JSON函数与系统函数5.7/8.0带来的新玩法5.1 JSON字段的提取、修改与聚合从MySQL 5.7开始支持JSON类型8.0继续增强JSON函数现在已经是业务开发绕不开的一块。业务表里存JSON的场景太多了活动配置、用户扩展信息、埋点参数、第三方回调原始数据等等。如果还在用VARCHAR存JSON再靠程序解析不仅查询麻烦还没法用数据库侧的表达式索引。提取JSON字段里的值最基础的是JSON_EXTRACT(json_doc, path)第二参数是路径表达式比如$.name表示根节点下的name属性。它的简写形式是-运算符data - $.name。要注意的是JSON_EXTRACT和-返回的仍然是JSON类型如果原值是字符串张三返回结果是带引号的张三。想直接拿到纯字符串要用-运算符data - $.name。一个简单记忆方法多一个符号就多剥一层引号。SELECT user_id, profile - $.name AS user_name, profile - $.age AS user_age FROM user_profile WHERE profile - $.city 杭州;修改JSON用JSON_SET(json_doc, path, value)它会更新已有键或新增不存在的键。JSON_INSERT只新增不更新JSON_REPLACE只更新不新增三者语义不同选错会覆盖数据。实际生产里我几乎只用JSON_SET因为它的行为最符合直觉路径存在就改不存在就加。聚合生成JSON的函数也很有用。JSON_ARRAYAGG(expr)把一组值聚合成JSON数组JSON_OBJECTAGG(key, value)把一组键值对聚合成JSON对象。比如查一个商品的所有标签SELECT product_id, JSON_ARRAYAGG(tag_name) FROM product_tags GROUP BY product_id;如果你想在5.7上把多行数据拼成JSON数组基本只能靠JSON_ARRAYAGG等升到8.0配合JSON_TABLE还能把JSON拆回关系表两个方向都能走通。5.2 系统信息函数运维和开发的日常工具系统信息函数在运维脚本和后台管理页面里出场频率很高。VERSION()返回当前MySQL版本号排查环境差异时SELECT VERSION();一句就能定位。DATABASE()返回当前默认库名多库共用连接池时很有用。USER()返回当前连接的用户和主机信息CURRENT_USER()返回当前账号实际匹配的认证用户。调试权限问题时这两个函数能帮你快速确认「我到底是谁」。CONNECTION_ID()返回当前连接的线程IDKILL一个卡住的连接时先用它查到ID再配合KILL命令处理比满屏找更高效。LAST_INSERT_ID()是另一个高频函数返回最近一次INSERT操作中自增主键的值。注意它只对本会话生效不会被其他连接干扰所以可以在程序里安全地获取刚插入记录的主键避免再查一次表。UUID()用来生成全局唯一字符串。它基于时间和MAC地址生成算是一把不会重复的「随机钥匙」。如果不想让主键暴露业务量大小可以用UUID()或者它的变体UUID_SHORT()。区别在于UUID_SHORT()返回一个64位整数比UUID字符串省空间但在分布式环境下依然有可能碰撞单库场景下用问题不大。MD5()和SHA1()是信息摘要函数本质上不是加密而是生成固定长度的指纹。常见用途对手机号、身份证做脱敏前的哈希索引或者对文件内容做完整性校验。但记住它们不能用于密码存储场景密码哈希应该用专门的加密算法在应用层完成数据库函数只负责业务数据的处理不做安全边界。6. 函数的性能代价索引失效与隐式转换必须背下来6.1 WHERE列上套函数再好的索引也白搭使用内置函数最大的性能隐患就是把函数套在索引列上参与条件筛选。经典反面教材SELECT * FROM orders WHERE DATE(create_time) 2024-06-15;这条SQL的逻辑没毛病但它对create_time调用了DATE()函数。MySQL在大多数情况下无法对函数作用后的结果使用B树索引索引有序性被破坏了优化器只能放弃索引走全表扫描。数据量小的时候感觉不出来等表里几百万行一次全表扫描就能把接口拖到超时。正确做法是把函数从列上抹掉改成范围比较SELECT * FROM orders WHERE create_time 2024-06-15 00:00:00 AND create_time 2024-06-16 00:00:00;这样create_time字段保持原样可以直接走索引范围扫描比全表扫描快几个量级。这个改写思路可以推广到所有日期函数DATE()、YEAR()、MONTH()、DATE_FORMAT()等凡是套在列上的,都想办法改写成对常量做函数、对列做范围比较。如果因为业务需要必须按自然月分组统计而原列上的索引又很重要8.0以后可以用生成列加索引来兼顾。先定义month_col TINYINT GENERATED ALWAYS AS (MONTH(create_time)) STORED然后对这个生成列建索引查询时直接WHERE month_col 6。这样外表看起来还是函数逻辑实际已经转化成了普通列匹配索引也不浪费。6.2 隐式类型转换数字当字符串用字符串当数字用另一种容易让索引失效的情况是隐式类型转换。典型场景字段phone_num是VARCHAR类型但SQL里写的是数字常量。SELECT * FROM users WHERE phone_num 13800138000;MySQL会自动把字段值转换成数字再比较而一旦对列做了类型转换列上的索引就用不上了。结果就是全表扫描一查一个准。解决办法是写SQL时保持类型一致把数字常量写成字符串SELECT * FROM users WHERE phone_num 13800138000;反过来也一样如果字段是INTEGER类型条件里写字符串比较安全吗WHERE id 100这种MySQL还是会做类型转换但因为是对常量转而不是对列转索引不受影响。真正要避免的是「对列做隐式函数处理」的情况。所以核心原则是比较时等号两边类型一致或者让转换发生在常量那一侧。6.3 分组排序中的函数索引问题GROUP BY和ORDER BY里使用函数同样会导致无法高效排序或分组。ORDER BY DATE(create_time)这笔排序等于让MySQL把每行都计算一遍DATE()然后再对计算结果排序索引的有序性完全无效。GROUP BY DATE_FORMAT(create_time, %Y-%m)同样如此分组逻辑无法走索引会在临时表里完成分组。如果你的查询模式固定是「按月份分组」我更推荐的方案是业务表直接冗余一个month字段写入时由程序或默认值计算好查询时直接GROUP BY month索引照常生效。这确实有一点冗余但换来的是查询性能的确定性。用空间换时间在报表系统里是常态。7. 高频踩坑清单与排查思路遇到函数问题照着这个表走把多年来被问到最多的函数相关问题整理成一张速查表遇到异常先对照一遍比翻文档快很多。现象常见原因解决/规避方式CONCAT拼接结果全部为NULL任一参数为NULL导致整体NULL改用CONCAT_WS或对参数套IFNULLCOUNT(某字段)结果比COUNT(*)少该字段存在NULL值COUNT(字段)自动忽略明确业务上要数「非空数」还是「总行数」ROUND结果和小数预期不一致浮点数精度误差或舍入方向理解偏差金额场景用DECIMAL类型避免FLOAT/DOUBLE日期按月分组慢得离谱对索引列套DATE_FORMAT导致索引失效改范围查询或冗余月份字段/生成列GROUP_CONCAT结果不完整超过group_concat_max_len默认1024字节被截断按需调大session变量并注意拼接内容排序CASE WHEN无匹配时返回NULL没有写ELSE分支每个CASE WHEN都建议写ELSE兜底STR_TO_DATE解析报错字符串格式和format占位符不一致先确认输入字符串的固定格式再写对应的format查出来的中文字符乱码或长度不对LENGTH和CHAR_LENGTH混用导致字节/字符混淆字符数判断一律用CHAR_LENGTHMOD结果为负数参数出现负数时MySQL行为和部分语言不同模运算尽量使用正数参数两个日期相减结果和预期差很多DATEDIFF只算天数忽略时间部分需要小时/分钟差用TIMESTAMPDIFF排查函数相关问题我的固定套路是三步走。第一步把SQL拆开一段一段注释掉定位是哪个函数导致的异常第二步单独SELECT这个函数作用于测试数据上的结果确认函数本身的输出是否符合预期第三步用EXPLAIN看执行计划确认是否因为函数导致索引失效或产生了额外的文件排序、临时表。大多数函数问题走完这三步都能定位到根因剩下的基本就是业务口径没对齐不是函数的问题。提示函数本身只是工具跑得慢、报错、结果不对多半是使用姿势或者周边环境的问题。排查时先确认数据、再确认函数行为、最后确认执行计划这个顺序不要颠倒否则容易在错误的方向上反复折腾。8. 最后再分享一个实践技巧和我一样经常被「又要函数可读、又要查询够快」夹在中间的人可以试试MySQL 8.0的生成列方案。我已经在好几个项目里落地了这个思路表里原本需要一个YEAR(create_time)作为分组维度我不再让SQL每次现算而是建一个STORED生成列加索引查询直接走列。表面上多占了一点存储但换来了SQL更简洁、索引更稳、报表查询稳定可控。另外花半小时建一份自己的「函数速查表」绝对值得。我自己的表格分四列函数名、语法、行为说明、生产环境里用过的真实场景。每踩一次坑就往里补一行时间长了就是一份比官方文档更适合自己的参考手册。遇到拿不准的函数先查自己的表再查官方文档确认版本差异基本不会再被困在同一个小坑里。
但凡写过两年SQL的人手里应该都攒过一本「MySQL函数笔记」这个函数怎么拼、那个函数返回什么、为什么同样一段SQL换个环境就报错——这些零碎问题最后几乎都能在MySQL内置函数这里碰头。MySQL内置函数是数据库提供的一组现成处理函数覆盖字符串、数值、日期时间、条件判断、聚合统计等场景用好了能让SQL变得又短又稳不用一股脑把逻辑搬到程序里一遍遍重写。这篇文章打算把内置函数按「分类体系→高频实操→性能雷区→踩坑排查」这条线完整梳理一遍。无论是刚装好MySQL 8.0、还在照着教程建表的初学者还是已经负责业务库、天天写报表SQL的开发或者是做数据同步和调优的运维都能从这里找到可直接抄走的东西。函数本身不分项目大小建订单表、做用户画像、统计活动转化、同步数据到ClickHouse处处都用得上值得认真过一遍。1. 内置函数全景先建体系再记细节别一头扎进函数堆MySQL官方文档里的函数加起来几百个如果按照「看到一个记一个」的方式去学结果大概率是边记边忘真到写SQL时还要反复查。更合理的做法是先建一个分类框架把函数按用途装进抽屉里用到哪一类就翻哪个抽屉这样记忆负担小很多应用的时候也能更快定位。我自己习惯把内置函数分成六大类字符串处理、数值计算、日期时间、流程控制、聚合统计、JSON与系统信息。前四类属于「行级函数」也就是对每一行数据单独做处理聚合函数则是把多行数据汇总成一个结果JSON和系统函数在5.7之后越来越重要尤其是8.0把JSON能力大幅增强之后几乎成了业务表设计的标配。分类之外还要搞清楚函数的出现位置。同一个函数放在SELECT里、WHERE里、GROUP BY里作用和语义可能截然不同。比如SUBSTRING(phone, 1, 3)放在SELECT里是把手机号前三位取出来展示放在WHERE里就是按前三位过滤放在GROUP BY里就是按前三位分组统计。这一点很多人初始化学习时会忽略实际写SQL时却最容易在这里犯迷糊。内置函数还有一个需要留意的点是「内置」两个字。它指的是MySQL服务端自带的函数不需要额外安装插件也不需要你自己写逻辑。和它相对的是自定义函数UDF那是需要开发者自己创建、自己维护的函数体不在本文讨论范围之内。在实际项目中能用内置函数解决的尽量不要去写自定义函数原因后面性能部分会详细说。版本差异同样不容忽视。MySQL 5.7和8.0虽然都以「MySQL」命名但函数能力差距不小。JSON函数在5.7里已经能用基础语法8.0进一步支持JSON_TABLE等高级特性窗口函数是8.0才有的5.7里只能用子查询加变量硬凑REGEXP_REPLACE这类字符串正则替换函数也只在8.0里才完整可用。所以写函数之前第一件事是确认线上版本别拿8.0的语法去5.7上跑那一定会报错。2. 字符串与数值函数日常SQL里最常用的两个大类2.1 字符串函数拼接、截取、替换、清洗一站搞定字符串函数是平时写SQL用得最多的尤其是做数据清洗和报表展示的时候。先看拼接CONCAT(str1, str2, ...)可以把多个字段或常量拼成一个字符串但有一个非常经典的坑——只要任何一个参数是NULL整个结果就会变成NULL。比如CONCAT(first_name, last_name)只要last_name为空结果就整个是空的这在用户名单拼接场景里很容易引发事故。解决办法有两种各看场景。第一种是CONCAT_WS(separator, str1, str2, ...)它会在参数之间插入分隔符并且会自动跳过NULL值不会返回NULL。第二种是用IFNULL先把NULL转成默认值再拼接。我的习惯是拼接地址、姓名这类「某个字段可能缺失但其他字段还要保留」的场景优先用CONCAT_WS需要严格控制结果格式的场景用IFNULL手动指定空值替换。截取函数里SUBSTRING(str, pos, len)是最基础的一个。注意MySQL的字符位置是从1开始数的不是从0开始这点和Java、JavaScript的字符串截取习惯完全不同刚切换过来的人很容易写串。SUBSTRING(2024-06-15, 1, 4)返回的是2024很多人第一次写成SUBSTRING(2024-06-15, 0, 4)结果会平白多出一个空字符或错位。从身份证取生日是一个很典型的综合练习CONCAT(SUBSTRING(id_card, 7, 4), -, SUBSTRING(id_card, 11, 2), -, SUBSTRING(id_card, 13, 2))一次把截取和拼接都用上了。如果只想取左侧或右侧固定长度还有LEFT(str, len)和RIGHT(str, len)两个便捷函数比如RIGHT(phone, 4)取手机号后四位做脱敏展示。查找和替换也是高频操作。LOCATE(substr, str)返回子串第一次出现的位置找不到返回0常用来做条件判断比如找出所有邮箱是QQ邮箱的用户WHERE LOCATE(qq.com, email) 0。REPLACE(str, from_str, to_str)做全量替换注意它是替换所有匹配项不是只替换第一个。清洗用户输入数据时我会连续嵌套好几个REPLACE把回车、换行、多个空格都清理掉。有一个点必须单独拎出来讲LENGTH(str)和CHAR_LENGTH(str)的区别。LENGTH返回的是字节数CHAR_LENGTH返回的是字符数。在中文字符集UTF-8下一个汉字占3个字节LENGTH(张三)的结果是6CHAR_LENGTH(张三)的结果是2。很多人在做长度校验时用错了函数结果明明限定了用户名最多10个字符中文用户却只能存3个这就是典型的函数误用。2.2 数值函数取整、取余、随机数的那些坑数值函数在报表计算中无处不在但坑也多。最典型的是取整函数的选择。MySQL里有ROUND()、FLOOR()、CEILING()、TRUNCATE()四个看起来差不多的函数实际行为完全不同。ROUND(3.14159, 2)是四舍五入到指定小数位返回3.14FLOOR(3.99)向下取整返回3CEILING(3.01)向上取整返回4TRUNCATE(3.99, 1)是直接截断不管后面是几返回3.9。它们的区别可以用一句话总结FLOOR和CEILING只处理整数方向TRUNCATE只看小数位不管舍入ROUND才会真正考虑进位。计算分页偏移量、库存分配这类场景选错取整函数会导致结果差一。MOD(a, b)取余数等价于a % b。有一个容易被忽略的行为当b为负数时MySQL中MOD的结果可能和编程语言里不一样所以习惯上用正数做模运算更安全。POWER(a, b)和SQRT()用于幂运算和平方根ABS()取绝对值这些就没太多幺蛾子直接用。随机数函数RAND()值得单独说一说。RAND()每次执行都会返回一个0到1之间的随机小数注意是「每次执行」都会变。如果在一个查询里多次调用RAND()每一行拿到的随机值都不同。想生成指定范围内的随机整数标准写法是FLOOR(RAND() * (max - min 1)) min。比如要生成1到100之间的随机整数FLOOR(RAND() * 100) 1。这个公式理解起来也不难RAND()最大接近1乘以区间长度100得到接近100的数FLOOR后得到0到99再加1就是1到100。我实际做过的一个需求是运营后台的随机抽奖要从商品表里随机抽10个商品。当时写的SQL是ORDER BY RAND() LIMIT 10功能是实现了但商品量大之后明显变慢。原因是ORDER BY RAND()需要为每一行生成随机数再排序全表扫描加文件排序数据量上了百万就会拖垮库。后来改成先SELECT id FROM table WHERE ... LIMIT 1000取出候选集在程序里随机挑10个再用主键回表速度快了一个数量级。这也是一个典型教训函数好用但不能无脑套在热点查询里。3. 日期时间函数订单统计和报表的地基工程3.1 拿到当前时间NOW、CURDATE、SYSDATE三兄弟别混用业务表里几乎都有create_time字段所以获取当前时间的函数是入门第一课。NOW()返回当前完整的日期和时间格式是YYYY-MM-DD HH:MM:SSCURDATE()只返回日期部分CURTIME()只返回时间部分。这三兄弟很简单但有个隐蔽的坑NOW()和SYSDATE()在官方文档里都表示当前时间执行结果看起来一样实际机制不同。NOW()是语句开始执行的时间一条SQL里不管调用多少次NOW()拿到的都是同一个值而SYSDATE()是函数被真正执行那一刻的时间如果一条SQL执行耗时比较长不同位置的SYSDATE()返回值可能不一样。这个差异在复制架构里尤其危险。主库执行一条用了SYSDATE()的长SQL备库在回放这条SQL时调用SYSDATE()拿到的是备库当前时间两边数据就可能不一致。生产环境处理流水、订单这类对时间一致性敏感的表我会统一用NOW()或直接让字段走DEFAULT CURRENT_TIMESTAMP不碰SYSDATE()。3.2 格式化与解析DATE_FORMAT和STR_TO_DATE是一对镜像日期格式化的核心函数是DATE_FORMAT(date, format)。format参数用一堆百分号占位符表示输出格式最常用的几个是%Y四位数年份、%m两位数月份、%d两位数日期、%H24小时制小时、%i分钟、%s秒。典型写法DATE_FORMAT(create_time, %Y-%m-%d %H:%i:%s)输出2024-06-15 14:30:00。把字符串解析成日期的函数是STR_TO_DATE(str, format)和DATE_FORMAT的格式占位符完全一致相当于镜像操作。比如前端传过来一个2024/06/15这种格式直接存进DATE字段之前要先解析STR_TO_DATE(2024/06/15, %Y/%m/%d)。如果不做解析让MySQL隐式转换很容易出现格式不识别导致报错或存储异常的情况。有一个高频报表需求按天、按月、按年分组统计。按月分组的经典写法是DATE_FORMAT(create_time, %Y-%m)作为分组键然后COUNT或SUM。但这里有一个必须提前知道的性能代价对create_time字段套DATE_FORMAT函数后这个字段上的索引就失效了大量数据时查询会慢。具体解法后面第6节专门讲这里先记住结论。3.3 时间差与日期运算DATEDIFF和TIMESTAMPDIFF的细节差异计算两个日期之间差多少天用DATEDIFF(expr1, expr2)结果是expr1减expr2的天数只看日期部分不看时间。比如DATEDIFF(2024-06-15, 2024-06-01)返回14用来算用户注册天数、优惠券剩余有效期都很顺手。TIMESTAMPDIFF(unit, start, end)则更通用unit可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR等比如计算两个时间之间差多少分钟TIMESTAMPDIFF(MINUTE, start_time, end_time)。注意参数顺序是「结束时间在前开始时间在后」很多人第一次用总写反导致结果出现负数。日期加减运算用DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)也可以用等价的ADDDATE和SUBDATE。我的习惯是统一用DATE_ADD/DATE_SUB因为INTERVAL语法更清晰。比如统计最近7天订单WHERE create_time DATE_SUB(CURDATE(), INTERVAL 7 DAY)。注意这里用CURDATE()而不是NOW()因为CURDATE()返回日期和日期字段比较时不会把当天零点之前的数据漏掉。如果你在做每月1号自动结算、每周一自动汇总这类周期性任务遵循一个原则能用日期函数直接在SQL里算出来的就不要在程序里先算好再传参。这样逻辑收敛在数据库层排查问题时只需要看SQL就能理解全部时间口径。4. 流程控制与聚合函数让SQL具备业务判断能力4.1 IF、IFNULL、NULLIF、CASE WHEN的使用边界流程控制函数让SQL不只是「查数据」还能在查询过程中做判断。最基础的是IF(expr, true_value, false_value)三目运算符的SQL版。比如把订单金额大于100的标记为「大单」IF(amount 100, 大单, 普通单)。IFNULL(expr1, expr2)专门处理NULLexpr1为NULL时返回expr2。更灵活的是COALESCE(expr1, expr2, ..., exprN)它能依次检查多个参数返回第一个非NULL值。COALESCE在多个可能为空的字段里取「第一个有效值」这个场景非常好用比如COALESCE(nickname, real_name, 匿名用户)用户的昵称没填就取真名真名也没有就显示匿名用户。NULLIF(expr1, expr2)的逻辑是当expr1等于expr2时返回NULL否则返回expr1。这个函数最常见的用法是做「除零保护」。比如统计客单价SUM(amount) / NULLIF(COUNT(*), 0)当COUNT(*)0时NULLIF返回NULL除法结果就是NULL而不是报错。这一类「不要让SQL直接除零」的细节就是日常开发里最容易体现功力的地方。CASE WHEN是流程控制里的重头戏也是我最推荐的条件判断写法。它的可读性比嵌套IF强太多特别是多个分支时IF嵌套写三层以上基本没法维护CASE WHEN一层一层列出来谁看了都明白。订单状态转中文是个经典例子SELECT order_no, CASE status WHEN 1 THEN 待付款 WHEN 2 THEN 已付款 WHEN 3 THEN 已发货 ELSE 未知状态 END AS status_text FROM orders;这里有一个新手常踩的坑CASE WHEN如果没有匹配到任何分支又没有写ELSE结果会返回NULL。状态转换场景里这会让展示层直接出现空值。我现在的规矩是所有CASE WHEN一律写ELSE兜底哪怕兜底值就是未知也要把NULL可能性堵死。4.2 聚合函数COUNT、SUM、AVG、MAX、MIN的隐藏陷阱聚合函数是把多行数据汇总成一行结果的函数通常和GROUP BY搭配使用。COUNT(*)统计行数、COUNT(字段)统计该字段非NULL的行数这两者的差异是最常见的坑。COUNT()不管字段值是不是NULL都会计数而COUNT(字段)会跳过NULL。如果写成COUNT(remark)想统计有备注的订单数而某些订单的remark是NULL结果会比COUNT()少这是完全正常的但很多人会当成Bug来排查。SUM和AVG都有一个特性计算时忽略NULL值。也就是说某一行字段是NULL不会参与SUM的累加也不会被算进AVG的分母。但如果整组数据都是NULLSUM返回NULL而不是0AVG也返回NULL。报表里把这些值直接展示出来前端可能显示成空白甚至报错。稳妥做法是外层套IFNULLIFNULL(SUM(amount), 0)让结果为0而不是NULL。MAX和MIN相对简单取一组数据的最大最小值。注意它们同样忽略NULL所以不会出现「最小值是NULL」的情况。GROUP_CONCAT是把一组数据拼成字符串的函数报表场景里很实用。比如查一个订单下的所有商品名GROUP_CONCAT(product_name SEPARATOR 、)。它有两个注意事项一是默认长度限制是1024字节超过会被静默截断需要先SET SESSION group_concat_max_len 102400调大二是排序稳定性问题如果想让拼接结果按时间顺序排列要写成GROUP_CONCAT(product_name ORDER BY create_time SEPARATOR 、)。聚合函数配合CASE WHEN可以做行转列。一个经典的例子统计各月份订单里不同支付方式的数量占比。SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS total_orders, SUM(CASE WHEN pay_type wechat THEN 1 ELSE 0 END) AS wechat_orders, SUM(CASE WHEN pay_type alipay THEN 1 ELSE 0 END) AS alipay_orders FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);CASE WHEN在里面充当了过滤器的角色满足条件返回1不满足返回0SUM之后就是计数。这个模式非常常用建议直接背下来。5. JSON函数与系统函数5.7/8.0带来的新玩法5.1 JSON字段的提取、修改与聚合从MySQL 5.7开始支持JSON类型8.0继续增强JSON函数现在已经是业务开发绕不开的一块。业务表里存JSON的场景太多了活动配置、用户扩展信息、埋点参数、第三方回调原始数据等等。如果还在用VARCHAR存JSON再靠程序解析不仅查询麻烦还没法用数据库侧的表达式索引。提取JSON字段里的值最基础的是JSON_EXTRACT(json_doc, path)第二参数是路径表达式比如$.name表示根节点下的name属性。它的简写形式是-运算符data - $.name。要注意的是JSON_EXTRACT和-返回的仍然是JSON类型如果原值是字符串张三返回结果是带引号的张三。想直接拿到纯字符串要用-运算符data - $.name。一个简单记忆方法多一个符号就多剥一层引号。SELECT user_id, profile - $.name AS user_name, profile - $.age AS user_age FROM user_profile WHERE profile - $.city 杭州;修改JSON用JSON_SET(json_doc, path, value)它会更新已有键或新增不存在的键。JSON_INSERT只新增不更新JSON_REPLACE只更新不新增三者语义不同选错会覆盖数据。实际生产里我几乎只用JSON_SET因为它的行为最符合直觉路径存在就改不存在就加。聚合生成JSON的函数也很有用。JSON_ARRAYAGG(expr)把一组值聚合成JSON数组JSON_OBJECTAGG(key, value)把一组键值对聚合成JSON对象。比如查一个商品的所有标签SELECT product_id, JSON_ARRAYAGG(tag_name) FROM product_tags GROUP BY product_id;如果你想在5.7上把多行数据拼成JSON数组基本只能靠JSON_ARRAYAGG等升到8.0配合JSON_TABLE还能把JSON拆回关系表两个方向都能走通。5.2 系统信息函数运维和开发的日常工具系统信息函数在运维脚本和后台管理页面里出场频率很高。VERSION()返回当前MySQL版本号排查环境差异时SELECT VERSION();一句就能定位。DATABASE()返回当前默认库名多库共用连接池时很有用。USER()返回当前连接的用户和主机信息CURRENT_USER()返回当前账号实际匹配的认证用户。调试权限问题时这两个函数能帮你快速确认「我到底是谁」。CONNECTION_ID()返回当前连接的线程IDKILL一个卡住的连接时先用它查到ID再配合KILL命令处理比满屏找更高效。LAST_INSERT_ID()是另一个高频函数返回最近一次INSERT操作中自增主键的值。注意它只对本会话生效不会被其他连接干扰所以可以在程序里安全地获取刚插入记录的主键避免再查一次表。UUID()用来生成全局唯一字符串。它基于时间和MAC地址生成算是一把不会重复的「随机钥匙」。如果不想让主键暴露业务量大小可以用UUID()或者它的变体UUID_SHORT()。区别在于UUID_SHORT()返回一个64位整数比UUID字符串省空间但在分布式环境下依然有可能碰撞单库场景下用问题不大。MD5()和SHA1()是信息摘要函数本质上不是加密而是生成固定长度的指纹。常见用途对手机号、身份证做脱敏前的哈希索引或者对文件内容做完整性校验。但记住它们不能用于密码存储场景密码哈希应该用专门的加密算法在应用层完成数据库函数只负责业务数据的处理不做安全边界。6. 函数的性能代价索引失效与隐式转换必须背下来6.1 WHERE列上套函数再好的索引也白搭使用内置函数最大的性能隐患就是把函数套在索引列上参与条件筛选。经典反面教材SELECT * FROM orders WHERE DATE(create_time) 2024-06-15;这条SQL的逻辑没毛病但它对create_time调用了DATE()函数。MySQL在大多数情况下无法对函数作用后的结果使用B树索引索引有序性被破坏了优化器只能放弃索引走全表扫描。数据量小的时候感觉不出来等表里几百万行一次全表扫描就能把接口拖到超时。正确做法是把函数从列上抹掉改成范围比较SELECT * FROM orders WHERE create_time 2024-06-15 00:00:00 AND create_time 2024-06-16 00:00:00;这样create_time字段保持原样可以直接走索引范围扫描比全表扫描快几个量级。这个改写思路可以推广到所有日期函数DATE()、YEAR()、MONTH()、DATE_FORMAT()等凡是套在列上的,都想办法改写成对常量做函数、对列做范围比较。如果因为业务需要必须按自然月分组统计而原列上的索引又很重要8.0以后可以用生成列加索引来兼顾。先定义month_col TINYINT GENERATED ALWAYS AS (MONTH(create_time)) STORED然后对这个生成列建索引查询时直接WHERE month_col 6。这样外表看起来还是函数逻辑实际已经转化成了普通列匹配索引也不浪费。6.2 隐式类型转换数字当字符串用字符串当数字用另一种容易让索引失效的情况是隐式类型转换。典型场景字段phone_num是VARCHAR类型但SQL里写的是数字常量。SELECT * FROM users WHERE phone_num 13800138000;MySQL会自动把字段值转换成数字再比较而一旦对列做了类型转换列上的索引就用不上了。结果就是全表扫描一查一个准。解决办法是写SQL时保持类型一致把数字常量写成字符串SELECT * FROM users WHERE phone_num 13800138000;反过来也一样如果字段是INTEGER类型条件里写字符串比较安全吗WHERE id 100这种MySQL还是会做类型转换但因为是对常量转而不是对列转索引不受影响。真正要避免的是「对列做隐式函数处理」的情况。所以核心原则是比较时等号两边类型一致或者让转换发生在常量那一侧。6.3 分组排序中的函数索引问题GROUP BY和ORDER BY里使用函数同样会导致无法高效排序或分组。ORDER BY DATE(create_time)这笔排序等于让MySQL把每行都计算一遍DATE()然后再对计算结果排序索引的有序性完全无效。GROUP BY DATE_FORMAT(create_time, %Y-%m)同样如此分组逻辑无法走索引会在临时表里完成分组。如果你的查询模式固定是「按月份分组」我更推荐的方案是业务表直接冗余一个month字段写入时由程序或默认值计算好查询时直接GROUP BY month索引照常生效。这确实有一点冗余但换来的是查询性能的确定性。用空间换时间在报表系统里是常态。7. 高频踩坑清单与排查思路遇到函数问题照着这个表走把多年来被问到最多的函数相关问题整理成一张速查表遇到异常先对照一遍比翻文档快很多。现象常见原因解决/规避方式CONCAT拼接结果全部为NULL任一参数为NULL导致整体NULL改用CONCAT_WS或对参数套IFNULLCOUNT(某字段)结果比COUNT(*)少该字段存在NULL值COUNT(字段)自动忽略明确业务上要数「非空数」还是「总行数」ROUND结果和小数预期不一致浮点数精度误差或舍入方向理解偏差金额场景用DECIMAL类型避免FLOAT/DOUBLE日期按月分组慢得离谱对索引列套DATE_FORMAT导致索引失效改范围查询或冗余月份字段/生成列GROUP_CONCAT结果不完整超过group_concat_max_len默认1024字节被截断按需调大session变量并注意拼接内容排序CASE WHEN无匹配时返回NULL没有写ELSE分支每个CASE WHEN都建议写ELSE兜底STR_TO_DATE解析报错字符串格式和format占位符不一致先确认输入字符串的固定格式再写对应的format查出来的中文字符乱码或长度不对LENGTH和CHAR_LENGTH混用导致字节/字符混淆字符数判断一律用CHAR_LENGTHMOD结果为负数参数出现负数时MySQL行为和部分语言不同模运算尽量使用正数参数两个日期相减结果和预期差很多DATEDIFF只算天数忽略时间部分需要小时/分钟差用TIMESTAMPDIFF排查函数相关问题我的固定套路是三步走。第一步把SQL拆开一段一段注释掉定位是哪个函数导致的异常第二步单独SELECT这个函数作用于测试数据上的结果确认函数本身的输出是否符合预期第三步用EXPLAIN看执行计划确认是否因为函数导致索引失效或产生了额外的文件排序、临时表。大多数函数问题走完这三步都能定位到根因剩下的基本就是业务口径没对齐不是函数的问题。提示函数本身只是工具跑得慢、报错、结果不对多半是使用姿势或者周边环境的问题。排查时先确认数据、再确认函数行为、最后确认执行计划这个顺序不要颠倒否则容易在错误的方向上反复折腾。8. 最后再分享一个实践技巧和我一样经常被「又要函数可读、又要查询够快」夹在中间的人可以试试MySQL 8.0的生成列方案。我已经在好几个项目里落地了这个思路表里原本需要一个YEAR(create_time)作为分组维度我不再让SQL每次现算而是建一个STORED生成列加索引查询直接走列。表面上多占了一点存储但换来了SQL更简洁、索引更稳、报表查询稳定可控。另外花半小时建一份自己的「函数速查表」绝对值得。我自己的表格分四列函数名、语法、行为说明、生产环境里用过的真实场景。每踩一次坑就往里补一行时间长了就是一份比官方文档更适合自己的参考手册。遇到拿不准的函数先查自己的表再查官方文档确认版本差异基本不会再被困在同一个小坑里。
