做后端开发这些年我发现自己每天写SQL时翻来覆去用到的其实就那几十个函数。MySQL常见函数这个问题隔三差五就有同事或者群里的人问起——有人问截取字符串怎么老是多一位有人问两个日期相差几天为什么算出来是负的还有人为了一个排名需求大半夜翻窗口函数文档翻到怀疑人生。所以我干脆把日常积累的这些东西整理成一份笔记从实际业务出发讲讲这些函数的用法、背后的原理、容易踩的坑以及和性能相关的注意事项。这篇文章适合刚学SQL不久的同学系统入门也适合写过一阵子但没时间深挖细节的朋友查漏补缺。1. 拆解MySQL函数家族先搞清楚有哪些函数值得学1.1 官方分类与实际高频函数MySQL官方文档把内置函数分成了很多类数值函数、字符串函数、日期时间函数、流程控制函数、聚合函数、窗口函数、类型转换函数、JSON函数、加密函数、信息系统函数等等。但说实话日常业务开发中用得最频繁的基本集中在六类字符串处理、数值计算、日期时间、条件逻辑、聚合统计、窗口分析。我见过不少新手一开始就抱着官方文档啃结果被几十个JSON函数和加密函数吓退觉得MySQL函数太多记不住。其实这是一个典型的范围焦虑。不需要全记关键是要给这些函数建立一个“分类索引”用的时候知道去哪一类里找这才是我觉得更有效的学习方式。JSON函数虽然从5.7版本开始就很强大了但大多数业务场景下JSON字段往往只做存储和简单读取真正在SQL层面做JSON解析和搜索的并不多。加密函数更是如此一般只在特殊业务里才会用到比如做密码哈希校验。所以这篇文章不会面面俱到只挑业务实战中真正高频、真正决定SQL质量的那些函数来展开。1.2 建议的学习优先级如果让我给一个学习路线我会建议按这样的优先级来第一梯队字符串函数、日期时间函数、聚合函数。这三类几乎每个SQL都会碰到尤其在做报表统计的时候聚合函数几乎是报表的半壁江山。第二梯队条件逻辑函数、数值函数、类型转换函数。这类函数写业务规则时经常要配合使用CASE WHEN在复杂查询里出场率极高。第三梯队窗口函数。MySQL 8.0之后才完整支持用来解决排名、累计、分组TopN这类问题非常优雅值得投入时间。记住一个原则MySQL函数不是背出来的是用出来的。只要你手上有一个真实的数据需求把它拆解成若干个函数操作理解每个函数的输入输出学起来比对着文档死记硬背快得多。我的习惯是每学一个函数就把它和已经学会的函数对比一次。比如学了SUBSTRING就想想它和LEFT、RIGHT有什么区别学了DATEDIFF就对比一下TIMESTAMPDIFF。这样知识是网状的而不是一个个孤立的点记起来就更牢。2. 字符串函数写业务SQL最常用的那一批2.1 拼接、截取与大小写细节决定成败字符串函数里CONCAT应该是最先要掌握的。它把两个或多个字符串首尾相连比如CONCAT(张, 三)结果就是“张三”。但很多人在拼接时会碰到一个经典问题如果拼接的字段里存在NULL整个结果会变成NULL因为字符串和NULL拼在一起结果定义就是NULL。这往往是报表里出现空白列的原因。解决办法是用CONCAT_WS或者IFNULL先把可能的NULL值转成空字符串比如CONCAT_WS(-, first_name, last_name)。截取字符串的函数有三个容易混淆LEFT、RIGHT、SUBSTRING。LEFT从左边开始取RIGHT从右边取SUBSTRING可以从任意位置取还能指定长度。这里我必须强调一个几乎所有从其他语言转过来的开发都会踩的坑——MySQL的字符串下标是从1开始的。SUBSTRING(abcdef, 1, 3)的结果是“abc”不是“bcd”。如果你把它当成从0开始就会截错数据而且代码还不报错排查起来很费劲。大小写转换的UPPER和LOWER没什么好说的但要注意它们在排序和比较时的影响。如果字段本身是utf8mb4_general_ci的排序规则大小写是不敏感的直接比较就好。如果你在WHERE条件里对字段套个UPPER函数比如WHERE UPPER(name) ZHANG数据库会放弃索引导致全表扫描。这种“函数套在索引列上”的模式会引发性能问题后面的规范章节我会专门展开。2.2 查找、替换与填充一张表看明白字符串处理中还有一批函数单看名字很好理解组合起来就能实现很复杂的效果。我把常用的列成一张表函数作用典型返回值示例LOCATE(substr, str)返回子串首次出现的位置找不到返回0LOCATE(b, abc) 返回2INSTR(str, substr)和LOCATE类似只是参数顺序反过来INSTR(abc, b) 返回2REPLACE(str, from, to)把字符串中的指定子串替换为新串REPLACE(a-b-c, -, ) 返回 abcTRIM(str)去掉字符串首尾空格TRIM( abc ) 返回 abcLTRIM / RTRIM只去掉左边或右边空格RTRIM(abc ) 返回 abcLPAD(str, len, pad)在左边填充字符到指定长度LPAD(7, 3, 0) 返回 007RPAD(str, len, pad)在右边填充字符到指定长度RPAD(7, 3, 0) 返回 700CHAR_LENGTH(str)返回字符个数中文按1个字符算CHAR_LENGTH(数据库) 返回3LENGTH(str)返回字节数utf8mb4下中文占3字节LENGTH(数据库) 返回9这个表里我最想提醒的是LENGTH和CHAR_LENGTH的区别。很多人在统计字符串长度时用错了函数导致对中文长度的判断出问题。如果你的业务里有“用户昵称不能超过20个字符”这类需求一定要用CHAR_LENGTH而不是LENGTH否则在中文场景下明明只写了10个汉字LENGTH算出来却是30直接误判。REPLACE函数在数据清洗场景非常好用比如把字符串里的换行符替换掉、把手机号中间四位替换成星号做脱敏。但要注意REPLACE是全局替换不是只替换第一个匹配项。如果你只想替换第一次出现的内容MySQL没有内置的直接函数通常要配合LOCATE和SUBSTRING来实现或者干脆在应用层处理。2.3 实战身份证脱敏与手机号格式化字符串函数最好的学习方式就是放到真实场景里考虑。这里我拿两个需求举例。第一个是手机号脱敏。原始手机号是13812345678要显示成138****5678。这个需求用CONCAT搭配SUBSTRING就能完成SELECT CONCAT( LEFT(phone, 3), ****, RIGHT(phone, 4) ) AS masked_phone FROM user;LEFT取前3位RIGHT取后4位中间用星号填充。逻辑非常清晰而且一眼能看懂。也有些人会选择CONCAT(SUBSTRING(phone, 1, 3), ****, SUBSTRING(phone, 8, 4))效果一样但LEFT和RIGHT语义更直观。第二个是从身份证号里提取出生日期。18位身份证的第7到14位是出生日期比如110101199001011234中19900101就是出生日期。用SUBSTRING就能取出来SELECT SUBSTRING(id_card, 7, 8) AS birth_date FROM user;如果需要标准的日期格式再用STR_TO_DATE转一下。这个场景的关键就是下标从1开始、长度是8不要写成6位或9位。我见过一个实习生算错位数把1990当成了年份结果所有用户出生年份都变成了“1990”排查了半天才发现是下标位置写错了。3. 数值函数与聚合函数别让小数和NULL影响结果3.1 数值计算与舍入规则数值函数里最常用的是ROUND、TRUNCATE、FLOOR、CEILING、ABS和MOD。ROUND做四舍五入FLOOR向下取整CEILING向上取整TRUNCATE是直接截断不四舍五入。这里有个细节很多人不知道MySQL的ROUND函数对于小数部分恰好是0.5的情况是“远离零”的舍入方式而不是“四舍五入”或者“银行家舍入”。比如ROUND(2.5)得到3ROUND(-2.5)得到-3。但ROUND(2.4)是2ROUND(2.6)是3。在大部分业务里这个规则够用但如果你的场景涉及金融计算、金额分摊就不能依赖MySQL的ROUND了建议把精度控制放在应用层或者直接用DECIMAL类型配合精确计算。MOD取模函数在分库分表、数据切分、循环分配等场景非常有用。比如给一批用户按ID取模分桶MOD(user_id, 10)就能把用户分成10个桶这在做灰度发布或者抽样分析的时候经常用到。用它做奇偶判断也很方便MOD(id, 2) 1表示奇数。数值函数看起来简单但有一个底层原则要记住涉及金额、百分比等精度敏感数据时字段类型设计优先选择DECIMAL而不是FLOAT或DOUBLE。FLOAT和DOUBLE是浮点数存储的是近似值等你算出总额再去对比就会看到那串令人抓狂的0.30000000000000004。这个坑不是函数的问题是类型选型的问题但很多人都是到了用SUM统计金额的时候才暴露出来。3.2 聚合函数与GROUP BY的配合聚合函数是报表查询的核心AVG平均、SUM求和、MAX最大、MIN最小、COUNT计数。但这些函数离开GROUP BY往往只是对全表做一次汇总。真正复杂的报表逻辑几乎都是“先分组再聚合”的套路。举个典型场景统计每个分类下的订单数和销售额。SELECT category_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM orders GROUP BY category_id;GROUP BY执行时MySQL会把相同category_id的行合并成一组然后对每一组做聚合操作。如果你SELECT的字段既不在聚合函数里也不在GROUP BY里比如SELECT category_id, product_name, SUM(amount)那product_name就不会出现在每一条具体的组里这种写法在MySQL的ONLY_FULL_GROUP_BY模式下会直接报错。在5.7之前的版本里MySQL会“宽容地”给你选一个随机值这也是旧版本里一个著名的坑。HAVING和WHERE也经常有人分不清。WHERE在分组之前过滤数据HAVING在分组之后过滤分组。如果你要过滤“订单数超过100的分类”那么COUNT(*) 100这个条件必须放在HAVING里因为WHERE执行的时候聚合函数根本还没算出来。记住这个顺序链WHERE→GROUP BY→HAVING→ORDER BY一旦想清楚了写复杂查询的时候就不容易晕。3.3 COUNT系列区别和NULL陷阱COUNT可能是最常用也最容易被轻视的函数。我把COUNT(*)、COUNT(1)、COUNT(字段)放在一起说。COUNT(*)统计的是符合条件的行数不管这一行里的列是不是NULL都算。COUNT(1)效果上基本等价于COUNT(*)只是它的含义是“统计一个常量表达式出现的次数”引擎可能会做等价优化两者性能差别可以忽略。真正要小心的是COUNT(字段)。它只统计该字段不为NULL的行数如果某一行这个字段是NULL就不会被计进去。这个细微差别在统计“回访用户数”这类场景里特别容易出错。假设用户表里有个字段叫last_login_at允许为NULL如果你用COUNT(last_login_at)统计“登录过的用户”那么从未登录过的用户天然被排除掉了。如果你的本意是统计用户总数并求出登录率那COUNT(*)加AVG(last_login_at IS NOT NULL)才是正确的组合。另外和NULL相关的规则必须刻在脑子里任何值参与算术运算时只要有一个操作数是NULL结果就是NULL。所以SUM(amount)在amount全为NULL时会返回NULL而不是0。很多报表里出现空白的汇总数字不是没有数据而是NULL在作怪。稳妥的做法是包一层IFNULL(SUM(amount), 0)。4. 日期时间函数格式化、计算与时区的三道坎4.1 NOW、CURDATE和SYSDATE三个容易混淆的时间MySQL里获取当前时间的函数有NOW、CURDATE、CURTIME、SYSDATE。CURDATE返回当前日期CURTIME返回当前时间都比较好理解。容易混淆的是NOW和SYSDATE。NOW()是语句开始执行的时间同一个语句中无论调用多少次NOW()返回的都是同一个时间点。SYSDATE()是函数真正被执行的时刻。在一条普通的查询里两者几乎看不出来区别但如果你在存储过程或长事务里循环查询SYSDATE每次返回值可能不同而NOW保持一致。这就带来一个隐藏风险如果你在一个需要“生成数据变更时间”的场景里用了SYSDATE比如在一条更新语句中同时更新多行SYSDATE会在不同行上产生微小的时刻差异导致时间戳不一致。而NOW则能保证同一语句的所有行更新为同一个时间。所以从生产环境稳定性考虑我更推荐用NOW()而不是SYSDATE()。CURRENT_TIMESTAMP其实是NOW的一个别名功能一样。日期时间函数还有一个让人摸不着头脑的点如果查询里用了WHERE create_time 2024-01-01MySQL会尝试把字符串转成日期进行比较这是隐式类型转换通常能正常工作。但你最好不要依赖这种隐式转换而是显式地用STR_TO_DATE或者DATE函数减少出错的概率。4.2 DATE_FORMAT与STR_TO_DATE格式符是重点DATE_FORMAT把日期转成指定格式的字符串STR_TO_DATE把字符串按指定格式解析成日期。两者互为逆操作非常像“序列化”和“反序列化”。DATE_FORMAT的格式符很多平时常用的也就这几个格式符含义示例%Y四位年份2024%y两位年份24%m两位月份01%d两位日09%H24小时制小时23%i分钟59%s秒58%W星期名称Monday%w星期数字0为周日1一个常见需求是按天统计订单量希望输出 “2024-05-01”这样的格式SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM orders GROUP BY DATE_FORMAT(order_time, %Y-%m-%d);注意GROUP BY里也要用DATE_FORMAT因为order_time本身是带时分秒的你不可能用原始字段去分组那会把每一秒都分成一组。同理按小时统计就格式化到%Y-%m-%d %H。STR_TO_DATE的典型场景是把外部导入的字符串转成日期。比如导入的数据里日期是2024/05/01 12:30:00你想存进DATETIME字段可以这样写SELECT STR_TO_DATE(2024/05/01 12:30:00, %Y/%m/%d %H:%i:%s);这里容易出现的错误是格式符和字符串不对应比如字符串是2024-05-01格式符却写成了%Y/%m/%d结果会得到NULL而且不会报错。排查这种问题的时候最有效的办法就是先用SELECT把STR_TO_DATE的结果单独查出来看看是不是NULL再决定是不是格式匹配的问题。4.3 日期加减与间隔计算日期计算经常用在“最近30天”“上周”“本月月初”这类需求上。DATE_ADD和DATE_SUB用来做日期的加减INTERVAL关键字用来指定间隔。比如-- 当前时间加7天 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 当前时间减3个月 SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH); -- 明天 SELECT NOW() INTERVAL 1 DAY;DATEDIFF计算两个日期相差的天数只比较日期部分不比较时间部分。注意参数顺序DATEDIFF(expr1, expr2)返回的是expr1减expr2的结果。如果expr1小于expr2结果就是负数。很多人第一次算“距离截止日期还剩几天”的时候总是把两个参数调换导致结果是负数还以为程序出错了。TIMESTAMPDIFF更灵活可以指定单位用来计算两个日期在秒、分钟、小时、天、月、年上的差值。比如计算年龄SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM user;TIMESTAMPDIFF是按边界来算的比如2020-01-01到2024-01-01算4年但如果到的日期是2024-05-01那还是4年差一个月都不到5年。这个逻辑其实就是“满一年才算一岁”比较贴近期望。TIMESTAMPDIFF和DATEDIFF的区别在于TIMESTAMPDIFF在计算月份或者年的时候更精准而DATEDIFF单纯按天算。4.4 时区问题与最佳实践日期时间函数还有个坑在时区。MySQL的NOW()、CURDATE()这些函数的返回值取决于数据库连接的时区设置。如果连接参数里time_zone是08:00NOW返回的是东八区时间如果服务器时区被改过或者你连的是跨时区的数据库实例就很容易出现“代码里打印的时间”和“数据库里的时间”对不上。我的建议是几条硬性规则第一数据库和应用服务器使用同一时区尽量统一用UTC存储、展示层转换的架构这种架构在国际化业务里很常见。第二不要在SQL里频繁依赖NOW和CURDATE去生成业务时间业务时间最好在应用层统一生成后传入。第三如果你必须依赖数据库时间要用CURRENT_TIMESTAMP并且确认连接参数中的时区。排查这类问题的时候先执行SELECT NOW();看看数据库当前认为的“现在”是什么时刻再跟应用服务器时间对比就能判断是否是时区偏差。5. 条件逻辑函数把业务规则翻译成SQL5.1 IF、IFNULL、NULLIF和COALESCE怎么选条件逻辑函数是SQL里用来表达业务规则的部分。IF函数是三元表达式的SQL版本IF(expr, true_value, false_value)。比如判断订单是否大额SELECT IF(amount 1000, 大额订单, 普通订单) AS order_level FROM orders;IF函数适合只有一个分支判断的场景。一旦判定的条件多了嵌套两三个IF代码可读性就会直线下降。这时候应该考虑CASE WHEN。IFNULL(expr1, expr2)用来处理NULL值如果expr1是NULL返回expr2否则返回expr1。它本质上是一个专一用途的函数。NULLIF(expr1, expr2)则相反如果两个表达式相等返回NULL如果不相等返回expr1。NULLIF的一个妙用是“防除零”a / NULLIF(b, 0)当b为0时分母变成NULL结果也变成NULL而不是让SQL报一个除零错误。这在计算比率报表时非常实用。COALESCE(expr1, expr2, ...)是NULLIF系列里的“老大哥”它从左到右依次判断返回第一个非NULL的值。如果所有参数都是NULL返回NULL。比如用户表里有main_phone和backup_phone两个字段想优先取main_phone取不到再取backup_phoneSELECT COALESCE(main_phone, backup_phone) AS contact_phone FROM user;COALESCE比嵌套多个IFNULL要简洁得多而且参数可以有很多个。这种函数唯一的注意点是参数列表里不要一个非NULL都没有的场景否则返回NULL下游代码要处理。5.2 CASE WHEN的两种写法与执行顺序CASE WHEN有两种语法。一种是简单CASE表达式拿一个字段和多个值做相等比较另一种是搜索CASE表达式可以写任意条件。我在实际工作中几乎只用搜索CASE因为它的表达能力更强。-- 简单CASE CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 ELSE 已取消 END -- 搜索CASE CASE WHEN status 1 THEN 待支付 WHEN amount 1000 THEN 大额订单 ELSE 普通订单 END搜索CASE非常像编程语言里的if-else if-else链。它从上往下逐个判断命中一个分支后就返回对应的值后面的分支不会再执行。这意味着条件的先后顺序很重要。如果有两条WHEN条件可能同时对同一行成立MySQL只会取先命中的那一条。比如上面这个例子里status1的订单同时amount1000它会被归类为“待支付”而不是“大额订单”。写SQL的时候把更具体的、更优先的规则放在前面是避免逻辑错误的关键。很多人在做报表的时候会把“翻译状态枚举”这件事放在SELECT里用CASE WHEN完成这是最自然的用法。但如果CASE WHEN的嵌套层级超过三层或者里面还套了聚合函数查询的可读性会迅速下降。这时候我建议把部分逻辑拆到子查询或者应用层去处理SQL的职责是拿到数据不要让单条SQL承担过于复杂的业务判断。5.3 实战订单金额区间打标签用一个综合场景把条件逻辑串起来。假设要对订单打标签规则是金额大于5000的为“VIP订单”1000到5000的为“重点订单”100到1000的为“普通订单”小于100的为“低价订单”金额为NULL的时候显示“异常”。SELECT order_id, amount, CASE WHEN amount IS NULL THEN 异常 WHEN amount 5000 THEN VIP订单 WHEN amount 1000 THEN 重点订单 WHEN amount 100 THEN 普通订单 ELSE 低价订单 END AS order_tag FROM orders;注意NULL的判断要放在最前面。如果把amount 5000放在第一位而amount是NULL那么这个判断的结果是NULL而不是TRUE或者FALSECASE WHEN会把它当作不命中继续往下走。最终NULL的订单会落入ELSE分支被标记成“低价订单”这就错了。通过这个例子能看到CASE WHEN和NULL交互的微妙之处。6. 类型转换函数隐式转换的坑往往藏得深6.1 CAST和CONVERT的用法MySQL里显式做类型转换的函数有两个CAST和CONVERT。CAST的语法是CAST(expr AS type)type可以是CHAR、DATE、DATETIME、DECIMAL、SIGNED、UNSIGNED等。CONVERT有两种用法一种和CAST类似CONVERT(expr, type)另一种是用来转字符集CONVERT(expr USING utf8mb4)。SELECT CAST(123 AS SIGNED) 1; -- 124 SELECT CONVERT(2024-05-01, DATE); -- 2024-05-01 SELECT CONVERT(数据 USING utf8mb4); -- 字符集转换把字符串转成数字时MySQL会尽量解析开头的数字部分。比如CAST(123abc AS SIGNED)得到123CAST(abc123 AS SIGNED)得到0。这个行为看起来很“宽容”但在数据清洗时可能掩盖问题。如果上游数据里有格式不规范的字段转换结果会和预期相差很大而且完全没有报错。6.2 隐式类型转换的真实案例比显式转换更危险的是隐式转换——你什么都没写MySQL自己悄悄做了类型转换结果查询结果和性能都变得不可控。最经典的场景是字符串字段和数字常量比较。假设phone字段是VARCHAR类型里面存的是手机号。有人会写出这样的查询SELECT * FROM user WHERE phone 13800001111;看起来没什么问题但左侧phone是字符串右侧是数字。MySQL在比较时会把字符串转成数字这就导致phone字段上发生隐式转换。转换之后索引无法正常使用因为索引是基于字符串值构建的现在拿的是数字值去匹配优化器没有办法直接走索引只能全表扫描。更隐蔽的错误是当手机号以0开头时转换成数字会丢掉前导零当字符串不是数字开头时转换结果是0。假设表里一条phone是135abc另一条是135xyz它们在数字比较中可能都等于135查询结果里就会出现你根本不想看到的行。所以字段类型是VARCHAR就老老实实给常量加引号WHERE phone 13800001111。这不仅能让查询结果符合预期还能帮助优化器正确使用索引。CAST和CONVERT在解决这类问题时可以帮你明确语义如果你需要一个字段是DATE那就直接在定义表结构时用DATE类型而不是在查询里频繁地对VARCHAR字段做CAST。数据库设计阶段的类型选型永远比查询阶段反复转换要靠谱得多。7. 窗口函数解决排名、TopN这类“麻烦”需求7.1 ROW_NUMBER / RANK / DENSE_RANK的区别窗口函数是MySQL 8.0引入的解决“分组内排名”“累计计算”这类需求非常顺手。在8.0之前这类需求往往靠用户变量或复杂的自连接来模拟写起来又绕又容易错。窗口函数可以保留所有明细行同时在每一行上算出与“窗口”有关的计算结果这点和GROUP BY完全不同。排名函数有三个ROW_NUMBER、RANK、DENSE_RANK。它们都是给每一行分配一个序号区别在于处理重复值的方式。举个例子假设有三位同学的成绩分别是90、90、85ROW_NUMBER按顺序从1递增成绩相同也强行分出先后结果是1、2、3。RANK成绩相同时序号相同而且后续序号会跳跃结果是1、1、3。DENSE_RANK成绩相同时序号相同后续序号不跳跃结果是1、1、2。用表来对比最直观成绩ROW_NUMBERRANKDENSE_RANK901119021185332在业务上如果你要“只取前两名且不能有并列”用ROW_NUMBER如果要“比赛排名”用RANK如果要“展示排名且名次连续”用DENSE_RANK。这个选择直接决定结果是否符合业务预期值得提前想清楚。7.2 实战分组内TopN查询窗口函数最经典的应用是分组TopN。比如要查询每个部门里工资最高的前三名员工SELECT department_id, employee_name, salary FROM ( SELECT department_id, employee_name, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employee ) t WHERE rn 3;内层查询先给每个员工在各自部门内按工资排序生成一个序号rn。外层查询再筛选rn 3。PARTITION BY负责分组ORDER BY负责组内排序这就是窗口函数的基本语法。这里要注意的是窗口函数不能直接写在WHERE条件里比如WHERE ROW_NUMBER() OVER (...) 3这种写法是不成立的。你需要先通过子查询把序号计算出来再在外部过滤。记住这个顺序能省掉很多调试时间。如果要求工资相同时也算并列第一就需要用DENSE_RANK或者RANK替代ROW_NUMBER具体语义根据业务规则来。在做这类查询时EXPLAIN看到的结果往往是先对表进行一次排序数据量大时对排序缓冲区的压力很大这一点要综合考虑。7.3 窗口聚合与移动累计除了排名窗口函数还能做“累计”“移动平均”这类分析。比如统计每个用户从注册到当前每个月的累计消费金额SELECT user_id, order_month, SUM(month_amount) OVER ( PARTITION BY user_id ORDER BY order_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM monthly_spending;这里的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分区里的第一行一直累加到当前行。如果不写这个框定默认情况下以ORDER BY时也是从这个范围开始累计但是把这个框定写出来语义更清楚也便于以后修改成“最近三个月”这类滑动窗口。窗口聚合可以替代很多之前用变量才能实现的功能而且可读性强很多。不过需要留意的是如果数据量很大窗口函数会导致一次额外的排序或缓冲区使用性能上不一定比GROUP BY好需要实际测试。对于大报表我通常会在子查询中先压缩数据量再用窗口函数去计算避免窗口函数在大结果集上处理过多无效行。8. 函数使用规范让SQL既跑得快又活得久8.1 不要在索引列上套函数这是我在工作里反复强调的一条性能铁律索引列被函数包裹后索引基本就失效了。原因是数据库的索引结构是按照原始列的值构建的一旦你用函数改变比较值优化器就无法直接根据索引查找只能把每一行的列值都算一遍函数再和条件比较。典型例子是按时间范围查订单时为了“方便”直接在order_time上套DATE-- 会导致索引失效数据量大时全表扫描 SELECT * FROM orders WHERE DATE(order_time) 2024-05-01;order_time是DATETIME类型这条语句的本意是查这一天内的所有订单。从结果上看没错但因为DATE函数包住了索引列MySQL无法使用order_time上的索引。正确的写法是改成范围查询SELECT * FROM orders WHERE order_time 2024-05-01 00:00:00 AND order_time 2024-05-02 00:00:00;范围查询既能走索引逻辑也更符合“一天是一个区间”的本质。类似的情况还有很多对字符串列套LOWER、对数字列套ABS、对日期列套DATE_FORMAT等能避免就尽量避免。判断一条SQL有没有走索引方法很简单EXPLAIN一下看type列。如果看到的是ALL就是全表扫描如果看到的是range、ref或const说明索引用上了。养成写完SQL就跑一遍EXPLAIN的习惯能帮你发现很多隐藏问题。8.2 隐式转换导致索引失效前面提过的字符串字段和数字常量比较是隐式转换导致索引失效的经典案例。做查询时保持字段类型和常量类型一致是最基本的防护措施。VARCHAR字段就用引号把常量包起来DATE字段就传日期字符串不要依赖MySQL自己去猜。数字和字符串以外的隐式转换同样值得注意。比如DATETIME字段和DATE字符串比较在绝大多数情况下MySQL可以正确处理并用到索引但如果你在复杂度高的联合查询里或者排序规则不一致的字段之间做比较优化器不一定每次都能正确推导。与其赌这些边界不如在任何比较场景都保持类型统一来的稳。8.3 函数嵌套与可读性函数是工具不是装饰写SQL和写程序代码一样最终要过“维护”这一关。一段SQL今天写出来你可能看得懂三个月之后再来改就要靠它自身的可读性了。如果一个字段上套了三层函数而且每层还有不同用途调试的时候你很难一眼看出原始数据是什么。我见过最夸张的写法是把手机号脱敏、时间格式化、状态判断全部塞进一个超长SELECT函数套函数缩进乱成一团。这种SQL不是说不能运行而是后期维护成本极高。更好的做法是分层处理复杂逻辑先用子查询或CTE拆成中间步骤每一步的函数调用都保持单一职责最后再基于中间结果查询。功能相同的写法可读性和可维护性完全不同。函数的另一个使用原则是“能用明确写法就不用隐式写法”。比如WHERE create_time 2024-01-01能跑但最好显式说明WHERE create_time STR_TO_DATE(2024-01-01, %Y-%m-%d)这样看代码的人不会对类型产生疑惑。在团队协作时这类显式表达能减少很多不必要的沟通成本。最后我自己的习惯是把常用函数的例子沉淀成团队的SQL片段库。每次有人问起“这个怎么写”直接从片段库里调出一个经过验证的版本改改字段名就能用。这样既保证了正确性又避免每个人搜出各种五花八门的写法。函数本身不复杂复杂的是在合适的位置、用合适的方式调用它们这一点需要靠实践慢慢积累。
做后端开发这些年我发现自己每天写SQL时翻来覆去用到的其实就那几十个函数。MySQL常见函数这个问题隔三差五就有同事或者群里的人问起——有人问截取字符串怎么老是多一位有人问两个日期相差几天为什么算出来是负的还有人为了一个排名需求大半夜翻窗口函数文档翻到怀疑人生。所以我干脆把日常积累的这些东西整理成一份笔记从实际业务出发讲讲这些函数的用法、背后的原理、容易踩的坑以及和性能相关的注意事项。这篇文章适合刚学SQL不久的同学系统入门也适合写过一阵子但没时间深挖细节的朋友查漏补缺。1. 拆解MySQL函数家族先搞清楚有哪些函数值得学1.1 官方分类与实际高频函数MySQL官方文档把内置函数分成了很多类数值函数、字符串函数、日期时间函数、流程控制函数、聚合函数、窗口函数、类型转换函数、JSON函数、加密函数、信息系统函数等等。但说实话日常业务开发中用得最频繁的基本集中在六类字符串处理、数值计算、日期时间、条件逻辑、聚合统计、窗口分析。我见过不少新手一开始就抱着官方文档啃结果被几十个JSON函数和加密函数吓退觉得MySQL函数太多记不住。其实这是一个典型的范围焦虑。不需要全记关键是要给这些函数建立一个“分类索引”用的时候知道去哪一类里找这才是我觉得更有效的学习方式。JSON函数虽然从5.7版本开始就很强大了但大多数业务场景下JSON字段往往只做存储和简单读取真正在SQL层面做JSON解析和搜索的并不多。加密函数更是如此一般只在特殊业务里才会用到比如做密码哈希校验。所以这篇文章不会面面俱到只挑业务实战中真正高频、真正决定SQL质量的那些函数来展开。1.2 建议的学习优先级如果让我给一个学习路线我会建议按这样的优先级来第一梯队字符串函数、日期时间函数、聚合函数。这三类几乎每个SQL都会碰到尤其在做报表统计的时候聚合函数几乎是报表的半壁江山。第二梯队条件逻辑函数、数值函数、类型转换函数。这类函数写业务规则时经常要配合使用CASE WHEN在复杂查询里出场率极高。第三梯队窗口函数。MySQL 8.0之后才完整支持用来解决排名、累计、分组TopN这类问题非常优雅值得投入时间。记住一个原则MySQL函数不是背出来的是用出来的。只要你手上有一个真实的数据需求把它拆解成若干个函数操作理解每个函数的输入输出学起来比对着文档死记硬背快得多。我的习惯是每学一个函数就把它和已经学会的函数对比一次。比如学了SUBSTRING就想想它和LEFT、RIGHT有什么区别学了DATEDIFF就对比一下TIMESTAMPDIFF。这样知识是网状的而不是一个个孤立的点记起来就更牢。2. 字符串函数写业务SQL最常用的那一批2.1 拼接、截取与大小写细节决定成败字符串函数里CONCAT应该是最先要掌握的。它把两个或多个字符串首尾相连比如CONCAT(张, 三)结果就是“张三”。但很多人在拼接时会碰到一个经典问题如果拼接的字段里存在NULL整个结果会变成NULL因为字符串和NULL拼在一起结果定义就是NULL。这往往是报表里出现空白列的原因。解决办法是用CONCAT_WS或者IFNULL先把可能的NULL值转成空字符串比如CONCAT_WS(-, first_name, last_name)。截取字符串的函数有三个容易混淆LEFT、RIGHT、SUBSTRING。LEFT从左边开始取RIGHT从右边取SUBSTRING可以从任意位置取还能指定长度。这里我必须强调一个几乎所有从其他语言转过来的开发都会踩的坑——MySQL的字符串下标是从1开始的。SUBSTRING(abcdef, 1, 3)的结果是“abc”不是“bcd”。如果你把它当成从0开始就会截错数据而且代码还不报错排查起来很费劲。大小写转换的UPPER和LOWER没什么好说的但要注意它们在排序和比较时的影响。如果字段本身是utf8mb4_general_ci的排序规则大小写是不敏感的直接比较就好。如果你在WHERE条件里对字段套个UPPER函数比如WHERE UPPER(name) ZHANG数据库会放弃索引导致全表扫描。这种“函数套在索引列上”的模式会引发性能问题后面的规范章节我会专门展开。2.2 查找、替换与填充一张表看明白字符串处理中还有一批函数单看名字很好理解组合起来就能实现很复杂的效果。我把常用的列成一张表函数作用典型返回值示例LOCATE(substr, str)返回子串首次出现的位置找不到返回0LOCATE(b, abc) 返回2INSTR(str, substr)和LOCATE类似只是参数顺序反过来INSTR(abc, b) 返回2REPLACE(str, from, to)把字符串中的指定子串替换为新串REPLACE(a-b-c, -, ) 返回 abcTRIM(str)去掉字符串首尾空格TRIM( abc ) 返回 abcLTRIM / RTRIM只去掉左边或右边空格RTRIM(abc ) 返回 abcLPAD(str, len, pad)在左边填充字符到指定长度LPAD(7, 3, 0) 返回 007RPAD(str, len, pad)在右边填充字符到指定长度RPAD(7, 3, 0) 返回 700CHAR_LENGTH(str)返回字符个数中文按1个字符算CHAR_LENGTH(数据库) 返回3LENGTH(str)返回字节数utf8mb4下中文占3字节LENGTH(数据库) 返回9这个表里我最想提醒的是LENGTH和CHAR_LENGTH的区别。很多人在统计字符串长度时用错了函数导致对中文长度的判断出问题。如果你的业务里有“用户昵称不能超过20个字符”这类需求一定要用CHAR_LENGTH而不是LENGTH否则在中文场景下明明只写了10个汉字LENGTH算出来却是30直接误判。REPLACE函数在数据清洗场景非常好用比如把字符串里的换行符替换掉、把手机号中间四位替换成星号做脱敏。但要注意REPLACE是全局替换不是只替换第一个匹配项。如果你只想替换第一次出现的内容MySQL没有内置的直接函数通常要配合LOCATE和SUBSTRING来实现或者干脆在应用层处理。2.3 实战身份证脱敏与手机号格式化字符串函数最好的学习方式就是放到真实场景里考虑。这里我拿两个需求举例。第一个是手机号脱敏。原始手机号是13812345678要显示成138****5678。这个需求用CONCAT搭配SUBSTRING就能完成SELECT CONCAT( LEFT(phone, 3), ****, RIGHT(phone, 4) ) AS masked_phone FROM user;LEFT取前3位RIGHT取后4位中间用星号填充。逻辑非常清晰而且一眼能看懂。也有些人会选择CONCAT(SUBSTRING(phone, 1, 3), ****, SUBSTRING(phone, 8, 4))效果一样但LEFT和RIGHT语义更直观。第二个是从身份证号里提取出生日期。18位身份证的第7到14位是出生日期比如110101199001011234中19900101就是出生日期。用SUBSTRING就能取出来SELECT SUBSTRING(id_card, 7, 8) AS birth_date FROM user;如果需要标准的日期格式再用STR_TO_DATE转一下。这个场景的关键就是下标从1开始、长度是8不要写成6位或9位。我见过一个实习生算错位数把1990当成了年份结果所有用户出生年份都变成了“1990”排查了半天才发现是下标位置写错了。3. 数值函数与聚合函数别让小数和NULL影响结果3.1 数值计算与舍入规则数值函数里最常用的是ROUND、TRUNCATE、FLOOR、CEILING、ABS和MOD。ROUND做四舍五入FLOOR向下取整CEILING向上取整TRUNCATE是直接截断不四舍五入。这里有个细节很多人不知道MySQL的ROUND函数对于小数部分恰好是0.5的情况是“远离零”的舍入方式而不是“四舍五入”或者“银行家舍入”。比如ROUND(2.5)得到3ROUND(-2.5)得到-3。但ROUND(2.4)是2ROUND(2.6)是3。在大部分业务里这个规则够用但如果你的场景涉及金融计算、金额分摊就不能依赖MySQL的ROUND了建议把精度控制放在应用层或者直接用DECIMAL类型配合精确计算。MOD取模函数在分库分表、数据切分、循环分配等场景非常有用。比如给一批用户按ID取模分桶MOD(user_id, 10)就能把用户分成10个桶这在做灰度发布或者抽样分析的时候经常用到。用它做奇偶判断也很方便MOD(id, 2) 1表示奇数。数值函数看起来简单但有一个底层原则要记住涉及金额、百分比等精度敏感数据时字段类型设计优先选择DECIMAL而不是FLOAT或DOUBLE。FLOAT和DOUBLE是浮点数存储的是近似值等你算出总额再去对比就会看到那串令人抓狂的0.30000000000000004。这个坑不是函数的问题是类型选型的问题但很多人都是到了用SUM统计金额的时候才暴露出来。3.2 聚合函数与GROUP BY的配合聚合函数是报表查询的核心AVG平均、SUM求和、MAX最大、MIN最小、COUNT计数。但这些函数离开GROUP BY往往只是对全表做一次汇总。真正复杂的报表逻辑几乎都是“先分组再聚合”的套路。举个典型场景统计每个分类下的订单数和销售额。SELECT category_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM orders GROUP BY category_id;GROUP BY执行时MySQL会把相同category_id的行合并成一组然后对每一组做聚合操作。如果你SELECT的字段既不在聚合函数里也不在GROUP BY里比如SELECT category_id, product_name, SUM(amount)那product_name就不会出现在每一条具体的组里这种写法在MySQL的ONLY_FULL_GROUP_BY模式下会直接报错。在5.7之前的版本里MySQL会“宽容地”给你选一个随机值这也是旧版本里一个著名的坑。HAVING和WHERE也经常有人分不清。WHERE在分组之前过滤数据HAVING在分组之后过滤分组。如果你要过滤“订单数超过100的分类”那么COUNT(*) 100这个条件必须放在HAVING里因为WHERE执行的时候聚合函数根本还没算出来。记住这个顺序链WHERE→GROUP BY→HAVING→ORDER BY一旦想清楚了写复杂查询的时候就不容易晕。3.3 COUNT系列区别和NULL陷阱COUNT可能是最常用也最容易被轻视的函数。我把COUNT(*)、COUNT(1)、COUNT(字段)放在一起说。COUNT(*)统计的是符合条件的行数不管这一行里的列是不是NULL都算。COUNT(1)效果上基本等价于COUNT(*)只是它的含义是“统计一个常量表达式出现的次数”引擎可能会做等价优化两者性能差别可以忽略。真正要小心的是COUNT(字段)。它只统计该字段不为NULL的行数如果某一行这个字段是NULL就不会被计进去。这个细微差别在统计“回访用户数”这类场景里特别容易出错。假设用户表里有个字段叫last_login_at允许为NULL如果你用COUNT(last_login_at)统计“登录过的用户”那么从未登录过的用户天然被排除掉了。如果你的本意是统计用户总数并求出登录率那COUNT(*)加AVG(last_login_at IS NOT NULL)才是正确的组合。另外和NULL相关的规则必须刻在脑子里任何值参与算术运算时只要有一个操作数是NULL结果就是NULL。所以SUM(amount)在amount全为NULL时会返回NULL而不是0。很多报表里出现空白的汇总数字不是没有数据而是NULL在作怪。稳妥的做法是包一层IFNULL(SUM(amount), 0)。4. 日期时间函数格式化、计算与时区的三道坎4.1 NOW、CURDATE和SYSDATE三个容易混淆的时间MySQL里获取当前时间的函数有NOW、CURDATE、CURTIME、SYSDATE。CURDATE返回当前日期CURTIME返回当前时间都比较好理解。容易混淆的是NOW和SYSDATE。NOW()是语句开始执行的时间同一个语句中无论调用多少次NOW()返回的都是同一个时间点。SYSDATE()是函数真正被执行的时刻。在一条普通的查询里两者几乎看不出来区别但如果你在存储过程或长事务里循环查询SYSDATE每次返回值可能不同而NOW保持一致。这就带来一个隐藏风险如果你在一个需要“生成数据变更时间”的场景里用了SYSDATE比如在一条更新语句中同时更新多行SYSDATE会在不同行上产生微小的时刻差异导致时间戳不一致。而NOW则能保证同一语句的所有行更新为同一个时间。所以从生产环境稳定性考虑我更推荐用NOW()而不是SYSDATE()。CURRENT_TIMESTAMP其实是NOW的一个别名功能一样。日期时间函数还有一个让人摸不着头脑的点如果查询里用了WHERE create_time 2024-01-01MySQL会尝试把字符串转成日期进行比较这是隐式类型转换通常能正常工作。但你最好不要依赖这种隐式转换而是显式地用STR_TO_DATE或者DATE函数减少出错的概率。4.2 DATE_FORMAT与STR_TO_DATE格式符是重点DATE_FORMAT把日期转成指定格式的字符串STR_TO_DATE把字符串按指定格式解析成日期。两者互为逆操作非常像“序列化”和“反序列化”。DATE_FORMAT的格式符很多平时常用的也就这几个格式符含义示例%Y四位年份2024%y两位年份24%m两位月份01%d两位日09%H24小时制小时23%i分钟59%s秒58%W星期名称Monday%w星期数字0为周日1一个常见需求是按天统计订单量希望输出 “2024-05-01”这样的格式SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM orders GROUP BY DATE_FORMAT(order_time, %Y-%m-%d);注意GROUP BY里也要用DATE_FORMAT因为order_time本身是带时分秒的你不可能用原始字段去分组那会把每一秒都分成一组。同理按小时统计就格式化到%Y-%m-%d %H。STR_TO_DATE的典型场景是把外部导入的字符串转成日期。比如导入的数据里日期是2024/05/01 12:30:00你想存进DATETIME字段可以这样写SELECT STR_TO_DATE(2024/05/01 12:30:00, %Y/%m/%d %H:%i:%s);这里容易出现的错误是格式符和字符串不对应比如字符串是2024-05-01格式符却写成了%Y/%m/%d结果会得到NULL而且不会报错。排查这种问题的时候最有效的办法就是先用SELECT把STR_TO_DATE的结果单独查出来看看是不是NULL再决定是不是格式匹配的问题。4.3 日期加减与间隔计算日期计算经常用在“最近30天”“上周”“本月月初”这类需求上。DATE_ADD和DATE_SUB用来做日期的加减INTERVAL关键字用来指定间隔。比如-- 当前时间加7天 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 当前时间减3个月 SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH); -- 明天 SELECT NOW() INTERVAL 1 DAY;DATEDIFF计算两个日期相差的天数只比较日期部分不比较时间部分。注意参数顺序DATEDIFF(expr1, expr2)返回的是expr1减expr2的结果。如果expr1小于expr2结果就是负数。很多人第一次算“距离截止日期还剩几天”的时候总是把两个参数调换导致结果是负数还以为程序出错了。TIMESTAMPDIFF更灵活可以指定单位用来计算两个日期在秒、分钟、小时、天、月、年上的差值。比如计算年龄SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM user;TIMESTAMPDIFF是按边界来算的比如2020-01-01到2024-01-01算4年但如果到的日期是2024-05-01那还是4年差一个月都不到5年。这个逻辑其实就是“满一年才算一岁”比较贴近期望。TIMESTAMPDIFF和DATEDIFF的区别在于TIMESTAMPDIFF在计算月份或者年的时候更精准而DATEDIFF单纯按天算。4.4 时区问题与最佳实践日期时间函数还有个坑在时区。MySQL的NOW()、CURDATE()这些函数的返回值取决于数据库连接的时区设置。如果连接参数里time_zone是08:00NOW返回的是东八区时间如果服务器时区被改过或者你连的是跨时区的数据库实例就很容易出现“代码里打印的时间”和“数据库里的时间”对不上。我的建议是几条硬性规则第一数据库和应用服务器使用同一时区尽量统一用UTC存储、展示层转换的架构这种架构在国际化业务里很常见。第二不要在SQL里频繁依赖NOW和CURDATE去生成业务时间业务时间最好在应用层统一生成后传入。第三如果你必须依赖数据库时间要用CURRENT_TIMESTAMP并且确认连接参数中的时区。排查这类问题的时候先执行SELECT NOW();看看数据库当前认为的“现在”是什么时刻再跟应用服务器时间对比就能判断是否是时区偏差。5. 条件逻辑函数把业务规则翻译成SQL5.1 IF、IFNULL、NULLIF和COALESCE怎么选条件逻辑函数是SQL里用来表达业务规则的部分。IF函数是三元表达式的SQL版本IF(expr, true_value, false_value)。比如判断订单是否大额SELECT IF(amount 1000, 大额订单, 普通订单) AS order_level FROM orders;IF函数适合只有一个分支判断的场景。一旦判定的条件多了嵌套两三个IF代码可读性就会直线下降。这时候应该考虑CASE WHEN。IFNULL(expr1, expr2)用来处理NULL值如果expr1是NULL返回expr2否则返回expr1。它本质上是一个专一用途的函数。NULLIF(expr1, expr2)则相反如果两个表达式相等返回NULL如果不相等返回expr1。NULLIF的一个妙用是“防除零”a / NULLIF(b, 0)当b为0时分母变成NULL结果也变成NULL而不是让SQL报一个除零错误。这在计算比率报表时非常实用。COALESCE(expr1, expr2, ...)是NULLIF系列里的“老大哥”它从左到右依次判断返回第一个非NULL的值。如果所有参数都是NULL返回NULL。比如用户表里有main_phone和backup_phone两个字段想优先取main_phone取不到再取backup_phoneSELECT COALESCE(main_phone, backup_phone) AS contact_phone FROM user;COALESCE比嵌套多个IFNULL要简洁得多而且参数可以有很多个。这种函数唯一的注意点是参数列表里不要一个非NULL都没有的场景否则返回NULL下游代码要处理。5.2 CASE WHEN的两种写法与执行顺序CASE WHEN有两种语法。一种是简单CASE表达式拿一个字段和多个值做相等比较另一种是搜索CASE表达式可以写任意条件。我在实际工作中几乎只用搜索CASE因为它的表达能力更强。-- 简单CASE CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 ELSE 已取消 END -- 搜索CASE CASE WHEN status 1 THEN 待支付 WHEN amount 1000 THEN 大额订单 ELSE 普通订单 END搜索CASE非常像编程语言里的if-else if-else链。它从上往下逐个判断命中一个分支后就返回对应的值后面的分支不会再执行。这意味着条件的先后顺序很重要。如果有两条WHEN条件可能同时对同一行成立MySQL只会取先命中的那一条。比如上面这个例子里status1的订单同时amount1000它会被归类为“待支付”而不是“大额订单”。写SQL的时候把更具体的、更优先的规则放在前面是避免逻辑错误的关键。很多人在做报表的时候会把“翻译状态枚举”这件事放在SELECT里用CASE WHEN完成这是最自然的用法。但如果CASE WHEN的嵌套层级超过三层或者里面还套了聚合函数查询的可读性会迅速下降。这时候我建议把部分逻辑拆到子查询或者应用层去处理SQL的职责是拿到数据不要让单条SQL承担过于复杂的业务判断。5.3 实战订单金额区间打标签用一个综合场景把条件逻辑串起来。假设要对订单打标签规则是金额大于5000的为“VIP订单”1000到5000的为“重点订单”100到1000的为“普通订单”小于100的为“低价订单”金额为NULL的时候显示“异常”。SELECT order_id, amount, CASE WHEN amount IS NULL THEN 异常 WHEN amount 5000 THEN VIP订单 WHEN amount 1000 THEN 重点订单 WHEN amount 100 THEN 普通订单 ELSE 低价订单 END AS order_tag FROM orders;注意NULL的判断要放在最前面。如果把amount 5000放在第一位而amount是NULL那么这个判断的结果是NULL而不是TRUE或者FALSECASE WHEN会把它当作不命中继续往下走。最终NULL的订单会落入ELSE分支被标记成“低价订单”这就错了。通过这个例子能看到CASE WHEN和NULL交互的微妙之处。6. 类型转换函数隐式转换的坑往往藏得深6.1 CAST和CONVERT的用法MySQL里显式做类型转换的函数有两个CAST和CONVERT。CAST的语法是CAST(expr AS type)type可以是CHAR、DATE、DATETIME、DECIMAL、SIGNED、UNSIGNED等。CONVERT有两种用法一种和CAST类似CONVERT(expr, type)另一种是用来转字符集CONVERT(expr USING utf8mb4)。SELECT CAST(123 AS SIGNED) 1; -- 124 SELECT CONVERT(2024-05-01, DATE); -- 2024-05-01 SELECT CONVERT(数据 USING utf8mb4); -- 字符集转换把字符串转成数字时MySQL会尽量解析开头的数字部分。比如CAST(123abc AS SIGNED)得到123CAST(abc123 AS SIGNED)得到0。这个行为看起来很“宽容”但在数据清洗时可能掩盖问题。如果上游数据里有格式不规范的字段转换结果会和预期相差很大而且完全没有报错。6.2 隐式类型转换的真实案例比显式转换更危险的是隐式转换——你什么都没写MySQL自己悄悄做了类型转换结果查询结果和性能都变得不可控。最经典的场景是字符串字段和数字常量比较。假设phone字段是VARCHAR类型里面存的是手机号。有人会写出这样的查询SELECT * FROM user WHERE phone 13800001111;看起来没什么问题但左侧phone是字符串右侧是数字。MySQL在比较时会把字符串转成数字这就导致phone字段上发生隐式转换。转换之后索引无法正常使用因为索引是基于字符串值构建的现在拿的是数字值去匹配优化器没有办法直接走索引只能全表扫描。更隐蔽的错误是当手机号以0开头时转换成数字会丢掉前导零当字符串不是数字开头时转换结果是0。假设表里一条phone是135abc另一条是135xyz它们在数字比较中可能都等于135查询结果里就会出现你根本不想看到的行。所以字段类型是VARCHAR就老老实实给常量加引号WHERE phone 13800001111。这不仅能让查询结果符合预期还能帮助优化器正确使用索引。CAST和CONVERT在解决这类问题时可以帮你明确语义如果你需要一个字段是DATE那就直接在定义表结构时用DATE类型而不是在查询里频繁地对VARCHAR字段做CAST。数据库设计阶段的类型选型永远比查询阶段反复转换要靠谱得多。7. 窗口函数解决排名、TopN这类“麻烦”需求7.1 ROW_NUMBER / RANK / DENSE_RANK的区别窗口函数是MySQL 8.0引入的解决“分组内排名”“累计计算”这类需求非常顺手。在8.0之前这类需求往往靠用户变量或复杂的自连接来模拟写起来又绕又容易错。窗口函数可以保留所有明细行同时在每一行上算出与“窗口”有关的计算结果这点和GROUP BY完全不同。排名函数有三个ROW_NUMBER、RANK、DENSE_RANK。它们都是给每一行分配一个序号区别在于处理重复值的方式。举个例子假设有三位同学的成绩分别是90、90、85ROW_NUMBER按顺序从1递增成绩相同也强行分出先后结果是1、2、3。RANK成绩相同时序号相同而且后续序号会跳跃结果是1、1、3。DENSE_RANK成绩相同时序号相同后续序号不跳跃结果是1、1、2。用表来对比最直观成绩ROW_NUMBERRANKDENSE_RANK901119021185332在业务上如果你要“只取前两名且不能有并列”用ROW_NUMBER如果要“比赛排名”用RANK如果要“展示排名且名次连续”用DENSE_RANK。这个选择直接决定结果是否符合业务预期值得提前想清楚。7.2 实战分组内TopN查询窗口函数最经典的应用是分组TopN。比如要查询每个部门里工资最高的前三名员工SELECT department_id, employee_name, salary FROM ( SELECT department_id, employee_name, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employee ) t WHERE rn 3;内层查询先给每个员工在各自部门内按工资排序生成一个序号rn。外层查询再筛选rn 3。PARTITION BY负责分组ORDER BY负责组内排序这就是窗口函数的基本语法。这里要注意的是窗口函数不能直接写在WHERE条件里比如WHERE ROW_NUMBER() OVER (...) 3这种写法是不成立的。你需要先通过子查询把序号计算出来再在外部过滤。记住这个顺序能省掉很多调试时间。如果要求工资相同时也算并列第一就需要用DENSE_RANK或者RANK替代ROW_NUMBER具体语义根据业务规则来。在做这类查询时EXPLAIN看到的结果往往是先对表进行一次排序数据量大时对排序缓冲区的压力很大这一点要综合考虑。7.3 窗口聚合与移动累计除了排名窗口函数还能做“累计”“移动平均”这类分析。比如统计每个用户从注册到当前每个月的累计消费金额SELECT user_id, order_month, SUM(month_amount) OVER ( PARTITION BY user_id ORDER BY order_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM monthly_spending;这里的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分区里的第一行一直累加到当前行。如果不写这个框定默认情况下以ORDER BY时也是从这个范围开始累计但是把这个框定写出来语义更清楚也便于以后修改成“最近三个月”这类滑动窗口。窗口聚合可以替代很多之前用变量才能实现的功能而且可读性强很多。不过需要留意的是如果数据量很大窗口函数会导致一次额外的排序或缓冲区使用性能上不一定比GROUP BY好需要实际测试。对于大报表我通常会在子查询中先压缩数据量再用窗口函数去计算避免窗口函数在大结果集上处理过多无效行。8. 函数使用规范让SQL既跑得快又活得久8.1 不要在索引列上套函数这是我在工作里反复强调的一条性能铁律索引列被函数包裹后索引基本就失效了。原因是数据库的索引结构是按照原始列的值构建的一旦你用函数改变比较值优化器就无法直接根据索引查找只能把每一行的列值都算一遍函数再和条件比较。典型例子是按时间范围查订单时为了“方便”直接在order_time上套DATE-- 会导致索引失效数据量大时全表扫描 SELECT * FROM orders WHERE DATE(order_time) 2024-05-01;order_time是DATETIME类型这条语句的本意是查这一天内的所有订单。从结果上看没错但因为DATE函数包住了索引列MySQL无法使用order_time上的索引。正确的写法是改成范围查询SELECT * FROM orders WHERE order_time 2024-05-01 00:00:00 AND order_time 2024-05-02 00:00:00;范围查询既能走索引逻辑也更符合“一天是一个区间”的本质。类似的情况还有很多对字符串列套LOWER、对数字列套ABS、对日期列套DATE_FORMAT等能避免就尽量避免。判断一条SQL有没有走索引方法很简单EXPLAIN一下看type列。如果看到的是ALL就是全表扫描如果看到的是range、ref或const说明索引用上了。养成写完SQL就跑一遍EXPLAIN的习惯能帮你发现很多隐藏问题。8.2 隐式转换导致索引失效前面提过的字符串字段和数字常量比较是隐式转换导致索引失效的经典案例。做查询时保持字段类型和常量类型一致是最基本的防护措施。VARCHAR字段就用引号把常量包起来DATE字段就传日期字符串不要依赖MySQL自己去猜。数字和字符串以外的隐式转换同样值得注意。比如DATETIME字段和DATE字符串比较在绝大多数情况下MySQL可以正确处理并用到索引但如果你在复杂度高的联合查询里或者排序规则不一致的字段之间做比较优化器不一定每次都能正确推导。与其赌这些边界不如在任何比较场景都保持类型统一来的稳。8.3 函数嵌套与可读性函数是工具不是装饰写SQL和写程序代码一样最终要过“维护”这一关。一段SQL今天写出来你可能看得懂三个月之后再来改就要靠它自身的可读性了。如果一个字段上套了三层函数而且每层还有不同用途调试的时候你很难一眼看出原始数据是什么。我见过最夸张的写法是把手机号脱敏、时间格式化、状态判断全部塞进一个超长SELECT函数套函数缩进乱成一团。这种SQL不是说不能运行而是后期维护成本极高。更好的做法是分层处理复杂逻辑先用子查询或CTE拆成中间步骤每一步的函数调用都保持单一职责最后再基于中间结果查询。功能相同的写法可读性和可维护性完全不同。函数的另一个使用原则是“能用明确写法就不用隐式写法”。比如WHERE create_time 2024-01-01能跑但最好显式说明WHERE create_time STR_TO_DATE(2024-01-01, %Y-%m-%d)这样看代码的人不会对类型产生疑惑。在团队协作时这类显式表达能减少很多不必要的沟通成本。最后我自己的习惯是把常用函数的例子沉淀成团队的SQL片段库。每次有人问起“这个怎么写”直接从片段库里调出一个经过验证的版本改改字段名就能用。这样既保证了正确性又避免每个人搜出各种五花八门的写法。函数本身不复杂复杂的是在合适的位置、用合适的方式调用它们这一点需要靠实践慢慢积累。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
