MySQL内置函数实战：分类、选型与索引失效避坑指南

MySQL内置函数这个概念在开发里其实没什么神秘的但恰恰是这些看似简单的函数最容易在业务里被用出问题。我自己刚接触数据库那几年也经历过照着文档抄函数、跑通就跑结果线上查询慢到报警再回头补课的阶段。后来项目做多了才慢慢意识到内置函数从来不是“背下来就行”的知识点它真正考验的是你对查询执行过程的理解以及在什么场景该用、什么场景坚决不用的判断力。这篇内容我准备直接从实战角度拆先梳理MySQL内置函数的整体分类和选型原则再讲字符串、数值、日期、条件、聚合、JSON这几类高频函数的用法和坑位最后把索引失效、隐式转换这类线上常见问题整理成一份排查清单。新手可以照着例子抄作业老手可以把它当作抽查自己知识体系的一份对照表。1. MySQL内置函数有哪些怎么分类选型MySQL官方文档把内置函数分得非常细光函数名就能列好几页。但真正落到日常业务开发里高频使用的其实就那几个大类。我习惯把它们拆成八类来理解字符串函数、数值函数、日期时间函数、条件判断函数、聚合函数、JSON函数、加密函数、类型转换函数。这个分类方式我用了很多年原因很简单它对应的是业务开发中最常见的八类需求。比如用户昵称里的非法字符要清洗这是字符串函数的事订单金额要做四舍五入和格式控制这是数值函数的事报表要按天、按月统计这是日期函数的事状态字段要翻译成中文展示这是条件判断函数的事统计订单总数和销售额这是聚合函数的事。1.1 按功能拆分的八类函数我先放一张速查表把每类函数的代表成员和应用场景列出来方便脑子里先有个地图函数分类代表函数典型应用场景字符串函数CONCAT、SUBSTRING、REPLACE、TRIM、LPAD字段拼接、脱敏、清洗、补位数值函数ROUND、CEIL、FLOOR、MOD、ABS金额精度、分页取整、奇偶判断日期时间函数NOW、DATE_FORMAT、DATEDIFF、DATE_ADD周期统计、时效判断、时间格式化条件判断函数IF、IFNULL、CASE WHEN、COALESCE状态映射、空值兜底、多条件分支聚合函数COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT分组统计、报表汇总、拼接分组明细JSON函数JSON_EXTRACT、JSON_CONTAINS、JSON_OBJECT半结构化字段查询、动态属性处理加密函数MD5、SHA2、AES_ENCRYPT数据签名、敏感信息存储类型转换函数CAST、CONVERT类型安全转换、编码转换这张表不是让大家去背而是提供一个索引。实际写SQL的时候遇到哪类需求能第一时间想到去哪一块找答案这就够了。1.2 内置函数的使用原则该下沉的下沉该上提的上提内置函数最大的价值是把计算逻辑从应用代码下沉到数据库层面。我举个特别常见的例子用户手机号脱敏。如果不用SQL函数应用层的做法是把整表手机号查出来然后在Java或者PHP里循环处理每一条记录都要做一次字符串截取和拼接。数据量小的时候无所谓但一旦列表接口返回几百条数据这些无效的字符串操作就会让应用服务器白白浪费CPU。用SQL内置函数就完全不一样SELECT user_id, CONCAT(LEFT(mobile, 3), ****, RIGHT(mobile, 4)) AS masked_mobile FROM user_tab;这种写法把转换逻辑收敛到数据库应用层拿到的已经是脱敏后的结果代码量少还不会出现因为某条数据格式异常导致应用层报错的情况。这个思路我概括成一句话能下沉到数据库的计算优先下沉。但这里必须有一个反向原则当某个函数会破坏索引使用的时候就不能无脑下沉了。最典型的就是在WHERE条件里对索引列套函数这个我在第3节和第6节会详细展开。一句话总结就是SELECT后面的函数要大胆用WHERE条件里的函数要谨慎再谨慎。2. 字符串函数与数值函数日常开发最常用的一组字符串函数和数值函数是写SQL时上手最快、出错也最多的一类。说不上难但细节特别碎尤其是NULL值处理和精度问题几乎每个人都踩过。2.1 字符串拼接、截取和脱敏的实战写法先讲拼接。CONCAT是大家最熟悉的函数但它有一个非常典型的坑只要任何一个参数为NULL整个结果就直接变NULL。SELECT CONCAT(first_name, last_name) FROM user_tab;如果last_name字段允许为空那结果是NULL而不是只显示first_name。这个行为很容易让业务方抱怨“用户名字怎么不见了”。解决方案有两个要么用IFNULL先兜底要么直接用CONCAT_WS。SELECT CONCAT_WS( , first_name, last_name) FROM user_tab;CONCAT_WS的第一个参数是分隔符它会自动忽略NULL值比CONCAT安全得多。我现在的习惯是多字段拼接一律优先CONCAT_WS不给自己留踩坑的机会。再讲截取。SUBSTRING的语法是SUBSTRING(str, pos, len)注意MySQL的字符串位置是从1开始的不是从0开始。新手最容易写错的地方就是写成SUBSTRING(name, 0, 3)结果发现返回空串。这个细节看着不起眼但一旦写错排查起来还挺浪费时间。截取最常见的场景是从固定格式的字段里抽子串。比如取出邮箱地址的前缀SELECT email, SUBSTRING(email, 1, LOCATE(, email) - 1) AS account_name FROM user_tab;LOCATE用来找符号的位置配合SUBSTRING就能取出账号名这两个函数组合起来非常顺手。然后是脱敏。除开手机号身份证号、银行卡号这类敏感信息都可以在SQL里直接处理SELECT CONCAT(LEFT(id_card, 4), ********, RIGHT(id_card, 4)) AS masked_id_card FROM user_tab;LEFT取前四位RIGHT取后四位中间用星号填充。这个写法基本是行业通用模板了简单高效。字符串函数里还有一类是清洗数据用的。REPLACE可以替换特殊字符TRIM可以去掉首尾空格LPAD和RPAD可以补位。比如订单号要求统一8位数字不够前面补零SELECT LPAD(CAST(order_no AS CHAR), 8, 0) FROM order_tab;这里先用CAST把数字转成字符串再用LPAD补零。CAST属于类型转换函数这类跨类组合在实际SQL里非常常见所以函数不是孤立学的它们经常需要配合使用。2.2 数值函数的精度陷阱与类型转换细节数值函数里ROUND用的最多但它恰恰最容易出精度问题。原因在于MySQL的浮点数存储遵循IEEE 754标准很多十进制小数在二进制里存不精确。比如SELECT ROUND(83.725, 2);如果传的是浮点类型结果可能并不是83.73而是83.72因为数据库内部存储的可能是83.724999...。这种问题在做金额计算时非常致命。解决思路有两个。第一金额字段一律用DECIMAL类型不用FLOAT和DOUBLE。第二对精度敏感的计算先CAST成DECIMAL再ROUNDSELECT ROUND(CAST(83.725 AS DECIMAL(10, 3)), 2);这样能稳定得到83.73。我在实际项目里处理价格折扣、积分兑换这类场景时都是这样操作的。宁可多写一步CAST也不要去赌浮点数的四舍五入行为。除了ROUND还有CEIL和FLOOR。CEIL向上取整FLOOR向下取整它们最典型的应用是分页时计算总页数SELECT CEIL(COUNT(*) / 20) AS total_pages FROM order_tab;这个写法在列表接口里非常常用能少写好几行应用层代码。MOD取模函数也很实用最常见的用途是做奇偶判断或者数据分片。比如想按用户ID的奇偶性把数据分成两个批次处理SELECT user_id, MOD(user_id, 2) AS tail FROM user_tab;这类函数逻辑简单但组合到业务里就能省不少事。数值函数还有一个关键点是类型转换。CAST的语法很简单但要注意转成什么类型。比如把一个字符串123转成整数可以写CAST(123 AS UNSIGNED)。不过这里我要提前提一个坑如果字符串里有非数字字符CAST会返回0而不会报错。这会导致数据静默失真所以要确保源头数据可转再上CAST。3. 日期时间函数统计类和时效类业务的标配日期时间函数在项目里的地位基本和WHERE条件一样高。做报表要按天分组做活动要查倒计时做订单要算超时时间这些都离不开时间函数。但这一块也是SQL性能问题的重灾区因为我见过太多人把日期函数用在索引列上。3.1 日期区间查询的正确姿势避开索引杀手先看一个典型的反例这种写法我几乎在每个项目里都见过SELECT * FROM order_tab WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-06-01;这条SQL的意图没问题就是想查某一天的订单。但它做了一件非常致命的事在create_time列上套了DATE_FORMAT函数。这样一来MySQL为了计算函数结果必须把每一行的create_time都做一次格式化索引自然就用不上了只能全表扫描。数据量一大这条SQL跑起来就是灾难。正确的写法是把它改成范围条件让索引能直接参与检索SELECT * FROM order_tab WHERE create_time 2025-06-01 00:00:00 AND create_time 2025-06-02 00:00:00;这个写法的核心思想是把函数从列上拿下来改成对查询常量做计算。数据库在比较的时候拿索引列和常量区间直接对比效率完全不一样。我后来排查慢SQL时只要看到WHERE条件里索引列被函数包着基本第一反应就是先换成范围查询再说。DATE_FORMAT不是不能用而是要放在SELECT展示层用。比如要输出2025年06月这种报表标题那它在SELECT里用就完全没问题SELECT DATE_FORMAT(create_time, %Y年%m月) AS month_label FROM order_tab GROUP BY DATE_FORMAT(create_time, %Y年%m月);这里同样要注意GROUP BY里如果对create_time做函数处理也是无法走索引的。但报表场景如果数据量没那么大或者已经通过WHERE条件缩到了小范围那问题就不大。关键是得明确知道自己在付出什么代价。3.2 NOW、SYSDATE和时区问题数据库时间别搞混日常开发里取当前时间最常见的两个函数是NOW()和SYSDATE()。很多人以为它们没区别其实行为差异不小。NOW()在一条SQL语句执行期间只取一次值而SYSDATE()是每次调用都取一次当前时间。这意味着在一条复杂SQL里如果多次调用SYSDATE()前后时间可能不一致。这种差异平时感受不到但在长事务或者主从复制环境里就可能出问题。为了语义一致我一般建议统一使用NOW()不要让同一个事务里出现多个时间标准。更值得注意的其实是时区。MySQL的NOW()、CURDATE()这些函数返回的是当前会话时区下的时间而会话时区通常继承自连接配置或者服务器的time_zone参数。一旦应用服务器的时区和数据库服务器的时区不一致就会出现“代码里打印的时间对不上库里的时间”这种闹心事。解决这个问题的最好办法是从源头统一应用连接数据库的URL里指定时区参数MySQL实例统一设置time_zone让应用、连接、数据库三层的时间标准完全一致。否则无论你用什么函数查出来的时间都是错位的。除了取当前时间日期计算也是高频需求。比如查最近7天的订单用DATE_SUB配合NOW()就可以SELECT * FROM order_tab WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY);对比两个日期相差多少天可以用DATEDIFF它只比较日期部分忽略时间部分。比如判断用户注册是否超过一年SELECT user_id, DATEDIFF(CURDATE(), register_time) AS registered_days FROM user_tab WHERE DATEDIFF(CURDATE(), register_time) 365;这里还是要提醒一句如果register_time加了索引那DATEDIFF(CURDATE(), register_time)这种写法同样会让索引失效。更好的方式是算出一个时间边界再比较也就是先把“365天前”这个时间点算出来再去跟列比较。日期函数这类“元年”问题本质都是同一个让列的独立性被破坏索引失效。4. 条件判断与聚合函数让SQL拥有“逻辑”条件判断函数是SQL里最接近“写逻辑”的部分。以前我还在应用层写一堆if-else去判断状态后来发现把这些判断下沉到SQL里不仅代码量少而且处理大量数据时效率高得多。4.1 IF、CASE WHEN、IFNULL怎么选IF函数适合简单二元判断比如及格与不及格、有效与无效SELECT user_name, IF(score 60, 及格, 不及格) AS exam_result FROM exam_tab;如果分支超过两个就推荐用CASE WHEN它更能表达多条件判断的意图。比如把订单金额分成几个档位SELECT order_id, CASE WHEN amount 1000 THEN 高金额 WHEN amount 100 THEN 中金额 ELSE 低金额 END AS amount_level FROM order_tab;CASE WHEN还有一个很大的优势它可以在聚合函数内部配合使用。比如我们想统计不同状态订单的数量在不用多个独立SQL的情况下可以这样写SELECT SUM(IF(status paid, 1, 0)) AS paid_cnt, SUM(IF(status cancel, 1, 0)) AS cancel_cnt FROM order_tab WHERE create_time 2025-01-01;这种“条件聚合”写法是我在报表开发里用得最多的技巧之一。本来可能要分成四五条SQL查现在一条就搞定了性能提升非常明显。IFNULL是用来处理NULL空的。它的语义很简单第一个参数是NULL就返回第二个参数的值SELECT user_name, IFNULL(phone, 未绑定) AS phone FROM user_tab;COALESCE则是IFNULL的加强版可以传多个参数返回第一个非NULL值SELECT COALESCE(phone, email, 无联系方式) FROM user_tab;这里要提醒一个容易忽视的点IFNULL和COALESCE本身不会影响索引使用但如果它们包住的是一个索引列优化器还是无法有效利用这个索引去做范围检索。也就是说它们适合放在SELECT输出层不适合包在WHERE条件的比较列上。4.2 聚合函数里的COUNT和GROUP_CONCAT值得细说聚合函数是报表统计的主力COUNT、SUM、AVG、MAX、MIN大家都熟但有几个细节经常被人忽略。COUNT()和COUNT(column)的区别是经典面试题也是实战坑。COUNT()统计的是行数不会跳过NULLCOUNT(column)统计的是该字段非NULL的行数。比如订单表里pay_time允许为空那COUNT(pay_time)统计的其实是“已支付订单数”而COUNT(*)统计的是全部订单数。如果没搞清楚这个区别很容易在统计支付转化率时算错数。GROUP_CONCAT是聚合函数里的一个特殊分子它能把分组内的多行数据拼成一行。这个函数在业务里太常用了比如把一个用户的所有购买商品名字拼在一起展示SELECT user_id, GROUP_CONCAT(goods_name SEPARATOR 、) AS goods_list FROM order_detail_tab GROUP BY user_id;它默认的分隔符是逗号用SEPARATOR可以自定义。但它有一个让人措手不及的默认限制group_concat_max_len默认只有1024字节内容一长就会被截断。我之前就踩过这个坑拼出来的商品列表莫名其妙少了一段排查了半天才发现是长度限制。解决办法是提前调大SET SESSION group_concat_max_len 10240;如果是在线上环境可以在客户端连接时设置或者写在配置里。这种“被默认参数坑到”的经历真的是用一次才能记住。聚合函数还有一个常见误区WHERE和HAVING的血统。聚合前的行过滤用WHERE聚合后的结果过滤用HAVING。比如筛选出订单数超过10个的用户SELECT user_id, COUNT(*) AS cnt FROM order_tab WHERE status paid GROUP BY user_id HAVING cnt 10;这里WHERE先过滤掉非支付订单然后再分组统计最后用HAVING过滤分组结果。顺序不能搞反否则统计结果就是错的。5. JSON函数与其它容易被忽略的内置能力现在很多业务表里都会设计JSON字段用来存一些结构灵活、不便拆表的扩展属性。MySQL对JSON的支持已经非常成熟了用好JSON函数可以省掉一大片应用层解析代码。5.1 JSON查询在关系表里处理半结构化数据假设商品表里有个extra字段里面存了品牌、产地、保修期等扩展信息结构大致如下{brand: 某品牌, origin: 江苏, warranty: 12}想直接查出某个商品的品牌不需要把整个JSON取回应用层解析SQL里就能完成SELECT product_id, extra-$.brand AS brand, extra-$.origin AS origin FROM product_tab;这里的-是MySQL 8.0提供的一个便捷运算符等同于JSON_UNQUOTE(JSON_EXTRACT(...))返回的是纯字符串非常方便。如果只想取原始JSON值用的是-两者区别就是返回的到底是有引号的JSON片段还是纯文本。JSON_CONTAINS也很实用。比如用户表有个标签字段存的是JSON数组想查所有包含“VIP”标签的用户SELECT user_id, tags FROM user_tab WHERE JSON_CONTAINS(tags, VIP);注意第二个参数必须是合法的JSON字符串所以VIP要手动加双引号写成VIP这个细节我一开始经常写错排查时才发现是JSON格式的问题。JSON_ARRAYAGG和JSON_OBJECT可以在聚合时直接生成JSON结构。比如按品牌聚合出商品ID数组SELECT brand, JSON_ARRAYAGG(product_id) AS product_ids FROM product_tab GROUP BY brand;这个函数在给前端输出树形结构、下拉选项时非常好用能把多次查询生成一次聚合结果。5.2 加密函数、正则函数和其它杂项加密函数方面MD5、SHA2、SHA1这类哈希函数在日常里用得不少。最常见的场景是对敏感字段做一致性签名比如给外部接口生成请求签名或者比对文件校验值。但这里我要特别强调一句不要用MD5这类快速哈希去存储用户密码。现在的主流做法是加盐后再做慢哈希比如使用专门的密码哈希算法做好防暴力破解的工作。SQL函数不等于安全方案工具和工具有各自的边界。MySQL 8.0还提供了正则相关函数比如REGEXP_LIKE、REGEXP_SUBSTR、REGEXP_REPLACE。例如从一段文本里提取手机号SELECT REGEXP_SUBSTR(remark, 1[3-9][0-9]{9}) AS extracted_mobile FROM customer_remark_tab;这种正则能力在文本清洗场景里非常强大。但它的性能通常比不上普通字符串函数和LIKE前缀匹配如果在超大数据集上做全文级别的正则还是要评估一下规模必要时考虑全文索引方案。CONVERT函数也有一个容易被忽略的用法字符集转换。比如从旧库导入的乱码数据可以在查询时用CONVERT转成目标编码SELECT CONVERT(name USING utf8mb4) FROM user_tab;这种写法在数据迁移和清洗时是救命稻草。不过它依然是一个处理型函数如果用在WHERE条件列上同样会带来索引问题所以能用在SELECT层就尽量放在SELECT层。6. 内置函数用错后的典型故障与排查清单函数用错了最直观的后果就是慢查询、错数据、接口超时。这一节我专门整理几类线上真实遇到的坑看完之后你至少能少走几趟弯路。6.1 索引失效的三大诱因第一类也是最常见的就是在索引列上使用函数。比如前面说的DATE_FORMAT、DATEDIFF、SUBSTRING等等只要出现在WHERE条件的列上基本就宣告索引报废。这是一个无论什么函数都无法绕过的限制索引存储的是原始列值不是函数计算后的值。第二类是隐式类型转换。我见过很多次这种场景某个字段在表里是VARCHAR类型但在SQL里拿它和数字做比较SELECT * FROM user_tab WHERE mobile 13800000000;它的执行结果可能是对的但代价很惨重MySQL判断出要比较的两端类型不一致会尝试把VARCHAR列转换成数字再比较这个转换过程会让索引彻底失去作用。正确的做法是让字面量和列的类型保持一致SELECT * FROM user_tab WHERE mobile 13800000000;只有一个引号之差执行计划完全不一样。我在排查慢SQL时看到数字和字符串直接比较会条件反射地检查一遍字段类型。第三类是前导模糊查询。LIKE %xxx这种写法倒不是函数但它是索引失效的经典诱因和函数问题是同一类症状。前导通配符使得索引无法按前缀定位优化器干脆放弃索引改全扫。如果业务确实需要模糊搜可以考虑前缀索引或者专用搜索组件而不是在SQL上硬扛。这些问题的共同规律是任何让索引列失去“原始值可比较性”的写法都很难走索引。记住这句话排查速度能快一半。6.2 故障排查速查表我把内置函数使用中常见的故障现象、原因和处理方案整理成一张表实际遇到问题可以直接对号入座故障现象可能原因处理方案查询越来越慢全表扫描WHERE条件里的索引列被函数包裹把函数改写为对常量计算改成范围条件日期统计结果和想象不一致DATE_FORMAT、DATEDIFF包住时间列索引未生效用和写区间查询字符串列和数字比较索引失效隐式类型转换列被转成数值保持字面量与列类型一致比如加引号聚合结果中出现不该有的NULL某些行该字段为NULL用COUNT(非空字段)或者IFNULL兜底GROUP_CONCAT结果被截断group_concat_max_len默认太小调大group_concat_max_lenROUND结果不符合四舍五入浮点精度问题使用DECIMAL类型先CAST再ROUND多名用户看不到手机号CONCAT遇到NULL返回NULL改用CONCAT_WS或先IFNULL这张表看着简单但每一条背后都是线上真实排查过的教训。尤其是第三条隐式转换它在执行计划里表现得非常隐蔽EXPLAIN看过去明明没写函数却走不了索引不熟悉的人很容易被绕进去。我自己每次写完带函数的SQL都会习惯性跑一次EXPLAIN确认执行计划。倒也不是什么复杂流程就看看type字段是不是range或者ref如果变成了ALL那就说明索引没吃上必须回头改。这个习惯帮我挡住了很多潜在的慢查询也推荐给你函数本身的复杂度不重要重要的是它有没有破坏索引的有效性。把这一个点想通内置函数这关基本就算过了。