前几天有个同事跑过来问我一件事他们一个报表查询每天要跑十几秒明明加了索引还是没用。我过去一看SQLWHERE条件里写着DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01。这个写法本身没错但它让create_time上的索引彻底失效了全表扫描当然快不了。类似的问题我见过太多次根子往往就是对MySQL中常见函数的使用只知其一、不知其二。MySQL里的函数就像工具箱里的一把把顺手小工具绝大多数日常SQL都离不开。不管是字符串拼接、日期计算、条件判断还是聚合统计函数用得好SQL能短一半、快一倍用不好轻则结果不对重则把慢查询带上生产环境。这篇内容我按真实项目里最常见的用法从字符串、数值、日期、条件、聚合几个大类出发把高频函数逐个讲透把容易踩的坑逐一标出来。适合刚入门写SQL的新手也适合写了不少业务查询但没系统梳理过的同学看完可以直接在项目里对照着用。1. 先给MySQL函数分个类心里才有底很多初学者拿到函数列表就蒙了MySQL官方文档里列了上百个函数光字符串函数就有三四十个死记硬背肯定不现实。我的建议是别急着背先建立一个整体框架。函数本质上就是输入值、做处理、给结果按处理对象和用途可以粗略分成六大类字符串处理、数值计算、日期时间、条件逻辑、聚合统计、类型转换。日常开发里高频使用的其实也就三四十个把每类的代表函数吃透剩下的用到再查就行。为什么要按场景去记而不是按字母表去背因为实际写SQL的时候你脑子里想的不会是有个函数叫SUBSTRING_INDEX而是我要把这个IP地址分成四段取第三段该用哪个。从业务问题出发去匹配函数学习效率高得多记忆也牢固得多。我早期学的时候拿小本子抄了一遍函数名过两周忘掉大半后来改成每个分类配一个业务场景比如统计每个用户注册月份对应DATE_FORMAT把手机号打码对应CONCAT和LEFT/RIGHT一下就记住了。还有一个容易被忽略的点函数不光用在SELECT后面还能用在WHERE、ORDER BY、GROUP BY、HAVING里。但不同位置使用函数代价完全不一样。SELECT里对结果做函数处理通常只是多算一列的事WHERE里对索引列套函数就可能直接让索引失效。这一点在后面第6节我会详细展开先有这个意识就行。分类思路建立之后接下来每一类我都会按有哪些高频函数、怎么用、有哪些坑这个顺序来讲。内容不追求覆盖全部函数但凡是高频的、面试常问的、生产环境容易出问题的我都会尽量讲到。2. 字符串函数字符和字节的区别坑了多少人字符串函数是日常用得最多的一类也是隐藏坑最多的一类。先把我认为最常用的一组函数列出来函数作用典型场景CONCAT(a, b, ...)拼接多个字符串拼接姓名、地址CONCAT_WS(sep, a, b, ...)用分隔符拼接拼接带逗号的列表SUBSTRING(str, pos, len)截取子串截取身份证号出生日期SUBSTRING_INDEX(str, sep, n)按分隔符截取拆分IP、域名、路径CHAR_LENGTH(str)返回字符数判断中文字符串长度LENGTH(str)返回字节数判断存储大小UPPER(str)/LOWER(str)转大小写忽略大小写比较REPLACE(str, from, to)替换数据脱敏、内容清洗TRIM(str)/LTRIM/RTRIM去空格清理用户输入LEFT(str, n)/RIGHT(str, n)取左/右侧字符手机号打码、取区号LPAD(str, len, pad)/RPAD字符串填充订单号补零先说CONCAT这个函数有一个非常容易踩的坑只要有一个参数是NULL整个拼接结果就是NULL。比如用户表里有姓氏和名字两个字段其中一个没填用CONCAT(last_name, first_name)会得到NULL而不是想要的单边内容。稳妥做法是用CONCAT_WS它在遇到NULL时会自动跳过或者用IFNULL先把可能的空值处理掉。字符和字节的差异是我最想强调的一个点。CHAR_LENGTH按字符数计算abc和三个字都返回3LENGTH按字节数计算在utf8mb4字符集下三个字返回9。大多数应用场景下你要判断的是字符数而不是字节数比如限制用户昵称不超过20个字符。如果用了LENGTH一个中英文混合的昵称长度就会算错导致明明没超限却被拦截。我见过不止一次线上数据校验出问题最后查下来就是这里。SUBSTRING家族需要重点讲。SUBSTRING(str, pos)从第pos位截到末尾SUBSTRING(str, pos, len)截取指定长度。注意pos可以是负数表示从末尾倒数。SUBSTRING_INDEX(str, sep, n)是按分隔符截取n为正数时取第n个分隔符之前的全部内容为负数时从右侧往左取。举个例子一条用户留言北京-朝阳-望京要取城市级别SUBSTRING_INDEX(msg, -, 2)得到北京-朝阳再配合SUBSTRING_INDEX嵌套一层即可拿到第二段。这种嵌套写法非常常用我处理域名拆分、地址解析时多次用到值得记住。字符串替换REPLACE看着简单实际有个细节它是全局替换不是替换第一个匹配项。如果要只替换第一个MySQL原生没有直接函数需要绕一下或者用正则表达式函数REGEXP_REPLACE。做内容敏感词过滤时这块要注意搞不清需求是把所有出现的地方全替换还是只替换首个结果会差不少。最后提醒一个和生产性能直接相关的点在WHERE里对索引列用字符串函数比如WHERE UPPER(name) ZHANGSAN会让索引失效。这个我放在第6节详细说这里先记住结论能用常量条件解决的不要在索引列上套函数。3. 数值与日期时间函数算账和查时间的正确姿势数值函数相对简单但四舍五入到底怎么舍这个细节非常容易踩坑。ROUND(x, d)默认采用四舍五入模式但受浮点数精度影响某些特殊值有可能不按预期舍入比如ROUND(2.675, 2)在某些环境下会得到2.67而不是2.68。虽然大版本迭代后这种情况已经很少出现但做金额计算时我仍然建议用DECIMAL类型存储而不是浮点从源头上规避精度问题。其他常用数值函数基本一看就会函数作用注意事项CEIL(x)/CEILING(x)向上取整算分页页数很常用FLOOR(x)向下取整注意负数的取整方向TRUNCATE(x, d)直接截断不四舍五入和ROUND对比理解MOD(a, b)取模即a%b分库分表取余数ABS(x)绝对值—RAND()随机数0~1随机取记录时用FLOOR取整方向和直觉容易反拿FLOOR(-1.5)来说结果是-2不是-1。因为在数学定义里向下取整是取不大于原数的最大整数。-1.5不大于它的最大整数是-2。做分页、分组逻辑时负数场景出现概率不高但一旦出现就要小心。日期时间函数是重头戏也是犯错率最高的区域。先把高频函数全列出来函数作用NOW()当前日期时间CURDATE()当前日期CURTIME()当前时间DATE_FORMAT(date, fmt)格式化日期DATE_ADD(date, INTERVAL n unit)日期加DATE_SUB(date, INTERVAL n unit)日期减DATEDIFF(d1, d2)两个日期相差天数TIMESTAMPDIFF(unit, d1, d2)两个日期相差指定单位YEAR()/MONTH()/DAY()提取年/月/日STR_TO_DATE(str, fmt)字符串转日期NOW()和SYSDATE()有个小区别很多老手都可能忽略NOW()在一个SQL语句里取到的是这条语句执行开始的时间点整个语句内保持一致SYSDATE()是调用瞬间的时间。对于长SQL或者存储过程两者可能有细微差异。大部分业务场景下用NOW()就够了需要考虑严格一致性时再去用SYSDATE()。DATE_FORMAT是整个日期函数里使用频率最高的。它有个特殊符号细节格式化里%Y是四位年份%y是两位年份%m是两位月份%c是不补零的月份。有人把%m写成了%M结果返回的是英文月份名January。做按月统计时最标准写法是DATE_FORMAT(create_time, %Y-%m)得到2025-01这种格式直接配合GROUP BY做月度分组。计算日期间隔我建议优先用TIMESTAMPDIFF而不是DATEDIFF。DATEDIFF只算天数差TIMESTAMPDIFF可以指定单位TIMESTAMPDIFF(YEAR, birth, CURDATE())直接算年龄TIMESTAMPDIFF(MINUTE, start, end)算耗时灵活很多。需要特别注意它的参数顺序第二个参数减第一个参数也就是TIMESTAMPDIFF(unit, start, end)end要在后面。顺序写反结果就是负数这个错误我见过不少次。日期比较的坑要单独拎出来说。有人习惯把日期字段转成字符串再比比如WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01。这个写法有两个问题第一索引失效第二逻辑上也不严谨。正确做法是用范围查询WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。这个改动带来的性能提升非常明显典型的改一行SQL查询从秒级降到毫秒级。4. 条件判断函数把业务规则直接写进SQL条件函数解决的是根据某个条件返回不同结果的问题。它相当于在SQL里写一层简单的if-else逻辑做报表、清洗数据时特别顺手。常用的就这几个IF、IFNULL、COALESCE、NULLIF、CASE WHEN。IF(expr, true_value, false_value)适合简单二分支判断。比如统计订单是否超时IF(status COMPLETED, 已完成, 未完成)。需要注意IF三个参数中false_value不能省略省略会报语法错误。IFNULL(a, b)是IF的一种特化当a为NULL时返回b否则返回a。它适合处理空值兜底比如展示用户昵称时IFNULL(nickname, 未设置)。COALESCE(a, b, c, ...)是更推荐的兜底方案。它依次判断每个参数返回第一个非NULL的值。参数顺序有讲究越靠前的优先级越高。典型场景是三个联系方式取一个可用的COALESCE(phone, email, address)。它和IFNULL的区别在于可以传多个参数一个COALESCE顶无数个嵌套IFNULL。NULLIF(a, b)逻辑相反a等于b时返回NULL否则返回a。常见用途是防止除零错误a / NULLIF(b, 0)当b为0时结果变成NULL而不是报错。CASE WHEN是条件函数里最灵活、最能打的。它有两种写法一种是CASE expr WHEN value THEN result ...的简单匹配形式只做等值判断另一种是CASE WHEN condition THEN result ...的搜索形式可以做范围、模糊、复合条件判断。实际项目中我几乎只用第二种因为用它写区间判断特别清晰。比如给订单金额打标签SELECT order_id, amount, CASE WHEN amount 100 THEN 小额 WHEN amount 500 THEN 中额 ELSE 大额 END AS amount_level FROM orders;这个写法有几个小细节值得说。CASE WHEN是从前往后匹配命中一个就不再往下判断所以写区间条件时要注意顺序先写小范围再写大范围。ELSE不是必须的但如果不写ELSE且所有条件都没命中结果就是NULL做报表时往往不希望出现空值所以建议显式写上ELSE兜底。条件函数的一个典型综合应用是行列统计。我曾做过一个订单统计需要在一行里算出支付成功、未支付、已退款三个数量。做法是用条件函数配合聚合SUM(CASE WHEN status PAID THEN 1 ELSE 0 END)这样一条SQL就能完成多列统计比用三条SQL去查再拼结果高效得多。这种写法在报表开发里非常常见算是条件函数的进阶用法。还有一点经验条件函数在WHERE里用IF(cond, a, b)这类写法优化器不一定能充分利用索引甚至可能带来全表扫描。能改写为普通AND/OR条件的尽量改写确实不能改写的在数据量大时要重点关注执行计划。5. 聚合函数报表统计的基石聚合函数是把多行数据压缩成一行结果的函数使用频率最高的是COUNT、SUM、AVG、MAX、MIN以及把多行合并成字符串的GROUP_CONCAT。它们通常配合GROUP BY使用也会和HAVING一起做分组后的过滤。先说COUNT这是坑最多的聚合函数。COUNT(*)统计的是所有行数包括某列为NULL的行COUNT(1)效果等同于COUNT(*)也是统计全部行而COUNT(column)统计的是该列**非NULL**的行数。这个差异非常重要。比如统计用户表里填了邮箱的人数COUNT(email)和COUNT(*)结果可能完全不同。面试里也经常拿这个来问原理就是COUNT(字段)需要判断是否空值。SUM对NULL值的处理也常被忽略。SUM(NULL)结果是NULL而不是0。如果你在一堆数据里求和某行恰好是NULL最终聚合结果可能变成NULL导致后续计算全部出错。稳妥做法是SUM(IFNULL(amount, 0))或者保证源数据没有NULL。AVG也是同样的道理它本质是SUM除以COUNT同样不统计NULL行。MAX和MIN使用上相对简单但有一个细节对字符串列取MAX/MIN时按字母排序不是按长度。比如MAX(apple, banana)的结果是banana因为b排在a后面。对日期字符串比较时如果格式是YYYY-MM-DD字符串排序和日期排序是一致的可以放心用但如果格式是MM-DD-YYYY之类就不能直接比较最好转成日期类型再取最大最小。GROUP_CONCAT是一个很容易被低估的函数它能把分组内的多行数据拼成一个字符串。比如把某个部门的所有员工姓名拼起来GROUP_CONCAT(name SEPARATOR 、)。它有个默认长度限制默认值是1024字节超过会被截断。生产里拼接内容较长时需要先通过SET SESSION group_concat_max_len 1048576;这类方式调大。我实际遇到过报表里姓名列表莫名变短排查半天才发现是这个限制。WHERE和HAVING的分工也是个高频考点。WHERE在分组前过滤行HAVING在分组后过滤分组结果。比如统计订单金额大于5000的客户只看上海的客户先WHERE city 上海把城市过滤掉再GROUP BY customer_id最后HAVING SUM(amount) 5000。这个顺序错误会导致两个问题一是把不该参与聚合的数据算进去了二是WHERE里不能直接使用聚合函数。SQL语义上SELECT的WHERE阶段根本还没有聚合结果所以WHERE SUM(amount) 5000会直接报错必须放在HAVING。聚合函数配合GROUP BY还有一个小坑查询列表里除了聚合函数外出现的列必须都出现在GROUP BY里否则在不同的SQL模式下可能结果不确定或者直接报错。比如SELECT department, name, COUNT(*) FROM employee GROUP BY department这里的name不在GROUP BY里没开ONLY_FULL_GROUP_BY时可能会随机取一行开了会报错。这个规则很多新手会踩记住SELECT后出现哪些非聚合列GROUP BY就要跟着写哪些。6. 函数用不对索引白搭性能问题与排查实录函数用得对不对最终还是要看数据库愿不愿意走索引。以下三种情况是我在工作里见到过最多、导致索引失效的典型场景。**函数套在索引列上。**这是最普遍的一类。索引本质上是按照原始列值建立的有序结构一旦把列包进函数比如WHERE UPPER(name) TOM、WHERE YEAR(create_time) 2025、WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01优化器在绝大多数情况下无法直接把函数结果和索引范围对应起来只能放弃索引改走全表扫描。解决思路不是硬调SQL而是改写法。比如YEAR(create_time) 2025改成create_time 2025-01-01 AND create_time 2026-01-01既保留了业务语义又能命中索引。**隐式类型转换。**这种情况不容易一眼看出来。典型例子是字段类型是VARCHAR但条件里写成数字WHERE phone 13800138000。MySQL会把字符串列先转成数字再比较相当于对列做了隐式函数处理索引自然失效。更隐蔽的是字符串和日期做比较比如WHERE date_str 2025-01-01而date_str存的是2025-01-01 12:00:00比较时也会发生类型转换。发现这个问题最直接的办法就是看EXPLAIN输出里key字段是否是NULL如果是说明没走索引再进一步检查两个比较值的类型是否一致。**在WHERE里做计算而不是传常量。**比如WHERE order_price * 2 1000其实可以转换成WHERE order_price 500。虽然结果一样但前者让优化器没法直接利用索引去框定范围后者则可以。这条规则说白了就是把计算放在条件值的这一侧不要放在列那一侧。接下来给一个简单的排查思路。当怀疑某个SQL慢是因为函数使用不当时先跑EXPLAIN SELECT ...重点看type和keytype是ALL表示全表扫描key是NULL表示没用到索引。然后尝试把WHERE条件里的函数改写掉再跑一次EXPLAIN对比通常能看到type从ALL变成range或ref这就是优化生效的信号。有一种特殊情况值得单独讲业务上确实需要在某个字段上做函数处理又希望高效查询怎么办比如经常要按月份查订单数据字段是create_time。这时候可以考虑加一个冗余的月份字段比如month_col写数据时直接算好存进去再在这列上建索引。MySQL也从5.7开始支持生成列ALTER TABLE orders ADD COLUMN month_col VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time, %Y-%m)) STORED, ADD INDEX idx_month (month_col);这样既满足了按月份查询的需求索引也能被正常使用。SQL里的写法可以保持WHERE month_col 2025-01不需要再套函数。这种方式牺牲了一点存储空间换来了稳定的查询性能在报表场景里很实用。以下是我做SQL优化时整理的一个速查表遇到类似问题可以直接按表排查症状常见原因处理建议查询很慢EXPLAIN显示全表扫描WHERE列上套了函数改写为范围条件或加冗余生成列字符串条件查不到数据字段和条件类型不一致统一类型避免隐式转换聚合结果和自己手算的不一样忽略NULL或COUNT字段行为明确COUNT(*)/COUNT(字段)语义拼接结果突然变短GROUP_CONCAT长度超限调大group_concat_max_len日期范围查到了多余数据用字符串比较替代范围比较改成和闭开区间写法这套排查方法我每次做SQL审查都会用按这个顺序走一遍绝大多数函数使用不当导致的慢查询都能在几分钟内定位出来。函数本身不背锅关键是用在什么位置、怎么用。我个人在实际操作中的体会是MySQL函数并不难难的是建立函数结果是否影响索引使用这个意识。绝大多数网上教程只讲函数怎么用不太会讲函数放哪里会导致慢查询。所以我建议你每学一个函数都顺手想一遍如果这个函数用在WHERE的索引列上会发生什么有没有替代写法带着这个问题去写SQL时间长了就会养成肌肉记忆。另外写聚合统计时养成顺手用IFNULL兜底的习惯也能避免不少线上数据异常。最后再分享一个小技巧把你自己踩过的函数坑整理成一个固定SQL片段库下次遇到相似需求直接调比临时翻文档靠谱得多。
前几天有个同事跑过来问我一件事他们一个报表查询每天要跑十几秒明明加了索引还是没用。我过去一看SQLWHERE条件里写着DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01。这个写法本身没错但它让create_time上的索引彻底失效了全表扫描当然快不了。类似的问题我见过太多次根子往往就是对MySQL中常见函数的使用只知其一、不知其二。MySQL里的函数就像工具箱里的一把把顺手小工具绝大多数日常SQL都离不开。不管是字符串拼接、日期计算、条件判断还是聚合统计函数用得好SQL能短一半、快一倍用不好轻则结果不对重则把慢查询带上生产环境。这篇内容我按真实项目里最常见的用法从字符串、数值、日期、条件、聚合几个大类出发把高频函数逐个讲透把容易踩的坑逐一标出来。适合刚入门写SQL的新手也适合写了不少业务查询但没系统梳理过的同学看完可以直接在项目里对照着用。1. 先给MySQL函数分个类心里才有底很多初学者拿到函数列表就蒙了MySQL官方文档里列了上百个函数光字符串函数就有三四十个死记硬背肯定不现实。我的建议是别急着背先建立一个整体框架。函数本质上就是输入值、做处理、给结果按处理对象和用途可以粗略分成六大类字符串处理、数值计算、日期时间、条件逻辑、聚合统计、类型转换。日常开发里高频使用的其实也就三四十个把每类的代表函数吃透剩下的用到再查就行。为什么要按场景去记而不是按字母表去背因为实际写SQL的时候你脑子里想的不会是有个函数叫SUBSTRING_INDEX而是我要把这个IP地址分成四段取第三段该用哪个。从业务问题出发去匹配函数学习效率高得多记忆也牢固得多。我早期学的时候拿小本子抄了一遍函数名过两周忘掉大半后来改成每个分类配一个业务场景比如统计每个用户注册月份对应DATE_FORMAT把手机号打码对应CONCAT和LEFT/RIGHT一下就记住了。还有一个容易被忽略的点函数不光用在SELECT后面还能用在WHERE、ORDER BY、GROUP BY、HAVING里。但不同位置使用函数代价完全不一样。SELECT里对结果做函数处理通常只是多算一列的事WHERE里对索引列套函数就可能直接让索引失效。这一点在后面第6节我会详细展开先有这个意识就行。分类思路建立之后接下来每一类我都会按有哪些高频函数、怎么用、有哪些坑这个顺序来讲。内容不追求覆盖全部函数但凡是高频的、面试常问的、生产环境容易出问题的我都会尽量讲到。2. 字符串函数字符和字节的区别坑了多少人字符串函数是日常用得最多的一类也是隐藏坑最多的一类。先把我认为最常用的一组函数列出来函数作用典型场景CONCAT(a, b, ...)拼接多个字符串拼接姓名、地址CONCAT_WS(sep, a, b, ...)用分隔符拼接拼接带逗号的列表SUBSTRING(str, pos, len)截取子串截取身份证号出生日期SUBSTRING_INDEX(str, sep, n)按分隔符截取拆分IP、域名、路径CHAR_LENGTH(str)返回字符数判断中文字符串长度LENGTH(str)返回字节数判断存储大小UPPER(str)/LOWER(str)转大小写忽略大小写比较REPLACE(str, from, to)替换数据脱敏、内容清洗TRIM(str)/LTRIM/RTRIM去空格清理用户输入LEFT(str, n)/RIGHT(str, n)取左/右侧字符手机号打码、取区号LPAD(str, len, pad)/RPAD字符串填充订单号补零先说CONCAT这个函数有一个非常容易踩的坑只要有一个参数是NULL整个拼接结果就是NULL。比如用户表里有姓氏和名字两个字段其中一个没填用CONCAT(last_name, first_name)会得到NULL而不是想要的单边内容。稳妥做法是用CONCAT_WS它在遇到NULL时会自动跳过或者用IFNULL先把可能的空值处理掉。字符和字节的差异是我最想强调的一个点。CHAR_LENGTH按字符数计算abc和三个字都返回3LENGTH按字节数计算在utf8mb4字符集下三个字返回9。大多数应用场景下你要判断的是字符数而不是字节数比如限制用户昵称不超过20个字符。如果用了LENGTH一个中英文混合的昵称长度就会算错导致明明没超限却被拦截。我见过不止一次线上数据校验出问题最后查下来就是这里。SUBSTRING家族需要重点讲。SUBSTRING(str, pos)从第pos位截到末尾SUBSTRING(str, pos, len)截取指定长度。注意pos可以是负数表示从末尾倒数。SUBSTRING_INDEX(str, sep, n)是按分隔符截取n为正数时取第n个分隔符之前的全部内容为负数时从右侧往左取。举个例子一条用户留言北京-朝阳-望京要取城市级别SUBSTRING_INDEX(msg, -, 2)得到北京-朝阳再配合SUBSTRING_INDEX嵌套一层即可拿到第二段。这种嵌套写法非常常用我处理域名拆分、地址解析时多次用到值得记住。字符串替换REPLACE看着简单实际有个细节它是全局替换不是替换第一个匹配项。如果要只替换第一个MySQL原生没有直接函数需要绕一下或者用正则表达式函数REGEXP_REPLACE。做内容敏感词过滤时这块要注意搞不清需求是把所有出现的地方全替换还是只替换首个结果会差不少。最后提醒一个和生产性能直接相关的点在WHERE里对索引列用字符串函数比如WHERE UPPER(name) ZHANGSAN会让索引失效。这个我放在第6节详细说这里先记住结论能用常量条件解决的不要在索引列上套函数。3. 数值与日期时间函数算账和查时间的正确姿势数值函数相对简单但四舍五入到底怎么舍这个细节非常容易踩坑。ROUND(x, d)默认采用四舍五入模式但受浮点数精度影响某些特殊值有可能不按预期舍入比如ROUND(2.675, 2)在某些环境下会得到2.67而不是2.68。虽然大版本迭代后这种情况已经很少出现但做金额计算时我仍然建议用DECIMAL类型存储而不是浮点从源头上规避精度问题。其他常用数值函数基本一看就会函数作用注意事项CEIL(x)/CEILING(x)向上取整算分页页数很常用FLOOR(x)向下取整注意负数的取整方向TRUNCATE(x, d)直接截断不四舍五入和ROUND对比理解MOD(a, b)取模即a%b分库分表取余数ABS(x)绝对值—RAND()随机数0~1随机取记录时用FLOOR取整方向和直觉容易反拿FLOOR(-1.5)来说结果是-2不是-1。因为在数学定义里向下取整是取不大于原数的最大整数。-1.5不大于它的最大整数是-2。做分页、分组逻辑时负数场景出现概率不高但一旦出现就要小心。日期时间函数是重头戏也是犯错率最高的区域。先把高频函数全列出来函数作用NOW()当前日期时间CURDATE()当前日期CURTIME()当前时间DATE_FORMAT(date, fmt)格式化日期DATE_ADD(date, INTERVAL n unit)日期加DATE_SUB(date, INTERVAL n unit)日期减DATEDIFF(d1, d2)两个日期相差天数TIMESTAMPDIFF(unit, d1, d2)两个日期相差指定单位YEAR()/MONTH()/DAY()提取年/月/日STR_TO_DATE(str, fmt)字符串转日期NOW()和SYSDATE()有个小区别很多老手都可能忽略NOW()在一个SQL语句里取到的是这条语句执行开始的时间点整个语句内保持一致SYSDATE()是调用瞬间的时间。对于长SQL或者存储过程两者可能有细微差异。大部分业务场景下用NOW()就够了需要考虑严格一致性时再去用SYSDATE()。DATE_FORMAT是整个日期函数里使用频率最高的。它有个特殊符号细节格式化里%Y是四位年份%y是两位年份%m是两位月份%c是不补零的月份。有人把%m写成了%M结果返回的是英文月份名January。做按月统计时最标准写法是DATE_FORMAT(create_time, %Y-%m)得到2025-01这种格式直接配合GROUP BY做月度分组。计算日期间隔我建议优先用TIMESTAMPDIFF而不是DATEDIFF。DATEDIFF只算天数差TIMESTAMPDIFF可以指定单位TIMESTAMPDIFF(YEAR, birth, CURDATE())直接算年龄TIMESTAMPDIFF(MINUTE, start, end)算耗时灵活很多。需要特别注意它的参数顺序第二个参数减第一个参数也就是TIMESTAMPDIFF(unit, start, end)end要在后面。顺序写反结果就是负数这个错误我见过不少次。日期比较的坑要单独拎出来说。有人习惯把日期字段转成字符串再比比如WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01。这个写法有两个问题第一索引失效第二逻辑上也不严谨。正确做法是用范围查询WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。这个改动带来的性能提升非常明显典型的改一行SQL查询从秒级降到毫秒级。4. 条件判断函数把业务规则直接写进SQL条件函数解决的是根据某个条件返回不同结果的问题。它相当于在SQL里写一层简单的if-else逻辑做报表、清洗数据时特别顺手。常用的就这几个IF、IFNULL、COALESCE、NULLIF、CASE WHEN。IF(expr, true_value, false_value)适合简单二分支判断。比如统计订单是否超时IF(status COMPLETED, 已完成, 未完成)。需要注意IF三个参数中false_value不能省略省略会报语法错误。IFNULL(a, b)是IF的一种特化当a为NULL时返回b否则返回a。它适合处理空值兜底比如展示用户昵称时IFNULL(nickname, 未设置)。COALESCE(a, b, c, ...)是更推荐的兜底方案。它依次判断每个参数返回第一个非NULL的值。参数顺序有讲究越靠前的优先级越高。典型场景是三个联系方式取一个可用的COALESCE(phone, email, address)。它和IFNULL的区别在于可以传多个参数一个COALESCE顶无数个嵌套IFNULL。NULLIF(a, b)逻辑相反a等于b时返回NULL否则返回a。常见用途是防止除零错误a / NULLIF(b, 0)当b为0时结果变成NULL而不是报错。CASE WHEN是条件函数里最灵活、最能打的。它有两种写法一种是CASE expr WHEN value THEN result ...的简单匹配形式只做等值判断另一种是CASE WHEN condition THEN result ...的搜索形式可以做范围、模糊、复合条件判断。实际项目中我几乎只用第二种因为用它写区间判断特别清晰。比如给订单金额打标签SELECT order_id, amount, CASE WHEN amount 100 THEN 小额 WHEN amount 500 THEN 中额 ELSE 大额 END AS amount_level FROM orders;这个写法有几个小细节值得说。CASE WHEN是从前往后匹配命中一个就不再往下判断所以写区间条件时要注意顺序先写小范围再写大范围。ELSE不是必须的但如果不写ELSE且所有条件都没命中结果就是NULL做报表时往往不希望出现空值所以建议显式写上ELSE兜底。条件函数的一个典型综合应用是行列统计。我曾做过一个订单统计需要在一行里算出支付成功、未支付、已退款三个数量。做法是用条件函数配合聚合SUM(CASE WHEN status PAID THEN 1 ELSE 0 END)这样一条SQL就能完成多列统计比用三条SQL去查再拼结果高效得多。这种写法在报表开发里非常常见算是条件函数的进阶用法。还有一点经验条件函数在WHERE里用IF(cond, a, b)这类写法优化器不一定能充分利用索引甚至可能带来全表扫描。能改写为普通AND/OR条件的尽量改写确实不能改写的在数据量大时要重点关注执行计划。5. 聚合函数报表统计的基石聚合函数是把多行数据压缩成一行结果的函数使用频率最高的是COUNT、SUM、AVG、MAX、MIN以及把多行合并成字符串的GROUP_CONCAT。它们通常配合GROUP BY使用也会和HAVING一起做分组后的过滤。先说COUNT这是坑最多的聚合函数。COUNT(*)统计的是所有行数包括某列为NULL的行COUNT(1)效果等同于COUNT(*)也是统计全部行而COUNT(column)统计的是该列**非NULL**的行数。这个差异非常重要。比如统计用户表里填了邮箱的人数COUNT(email)和COUNT(*)结果可能完全不同。面试里也经常拿这个来问原理就是COUNT(字段)需要判断是否空值。SUM对NULL值的处理也常被忽略。SUM(NULL)结果是NULL而不是0。如果你在一堆数据里求和某行恰好是NULL最终聚合结果可能变成NULL导致后续计算全部出错。稳妥做法是SUM(IFNULL(amount, 0))或者保证源数据没有NULL。AVG也是同样的道理它本质是SUM除以COUNT同样不统计NULL行。MAX和MIN使用上相对简单但有一个细节对字符串列取MAX/MIN时按字母排序不是按长度。比如MAX(apple, banana)的结果是banana因为b排在a后面。对日期字符串比较时如果格式是YYYY-MM-DD字符串排序和日期排序是一致的可以放心用但如果格式是MM-DD-YYYY之类就不能直接比较最好转成日期类型再取最大最小。GROUP_CONCAT是一个很容易被低估的函数它能把分组内的多行数据拼成一个字符串。比如把某个部门的所有员工姓名拼起来GROUP_CONCAT(name SEPARATOR 、)。它有个默认长度限制默认值是1024字节超过会被截断。生产里拼接内容较长时需要先通过SET SESSION group_concat_max_len 1048576;这类方式调大。我实际遇到过报表里姓名列表莫名变短排查半天才发现是这个限制。WHERE和HAVING的分工也是个高频考点。WHERE在分组前过滤行HAVING在分组后过滤分组结果。比如统计订单金额大于5000的客户只看上海的客户先WHERE city 上海把城市过滤掉再GROUP BY customer_id最后HAVING SUM(amount) 5000。这个顺序错误会导致两个问题一是把不该参与聚合的数据算进去了二是WHERE里不能直接使用聚合函数。SQL语义上SELECT的WHERE阶段根本还没有聚合结果所以WHERE SUM(amount) 5000会直接报错必须放在HAVING。聚合函数配合GROUP BY还有一个小坑查询列表里除了聚合函数外出现的列必须都出现在GROUP BY里否则在不同的SQL模式下可能结果不确定或者直接报错。比如SELECT department, name, COUNT(*) FROM employee GROUP BY department这里的name不在GROUP BY里没开ONLY_FULL_GROUP_BY时可能会随机取一行开了会报错。这个规则很多新手会踩记住SELECT后出现哪些非聚合列GROUP BY就要跟着写哪些。6. 函数用不对索引白搭性能问题与排查实录函数用得对不对最终还是要看数据库愿不愿意走索引。以下三种情况是我在工作里见到过最多、导致索引失效的典型场景。**函数套在索引列上。**这是最普遍的一类。索引本质上是按照原始列值建立的有序结构一旦把列包进函数比如WHERE UPPER(name) TOM、WHERE YEAR(create_time) 2025、WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-01优化器在绝大多数情况下无法直接把函数结果和索引范围对应起来只能放弃索引改走全表扫描。解决思路不是硬调SQL而是改写法。比如YEAR(create_time) 2025改成create_time 2025-01-01 AND create_time 2026-01-01既保留了业务语义又能命中索引。**隐式类型转换。**这种情况不容易一眼看出来。典型例子是字段类型是VARCHAR但条件里写成数字WHERE phone 13800138000。MySQL会把字符串列先转成数字再比较相当于对列做了隐式函数处理索引自然失效。更隐蔽的是字符串和日期做比较比如WHERE date_str 2025-01-01而date_str存的是2025-01-01 12:00:00比较时也会发生类型转换。发现这个问题最直接的办法就是看EXPLAIN输出里key字段是否是NULL如果是说明没走索引再进一步检查两个比较值的类型是否一致。**在WHERE里做计算而不是传常量。**比如WHERE order_price * 2 1000其实可以转换成WHERE order_price 500。虽然结果一样但前者让优化器没法直接利用索引去框定范围后者则可以。这条规则说白了就是把计算放在条件值的这一侧不要放在列那一侧。接下来给一个简单的排查思路。当怀疑某个SQL慢是因为函数使用不当时先跑EXPLAIN SELECT ...重点看type和keytype是ALL表示全表扫描key是NULL表示没用到索引。然后尝试把WHERE条件里的函数改写掉再跑一次EXPLAIN对比通常能看到type从ALL变成range或ref这就是优化生效的信号。有一种特殊情况值得单独讲业务上确实需要在某个字段上做函数处理又希望高效查询怎么办比如经常要按月份查订单数据字段是create_time。这时候可以考虑加一个冗余的月份字段比如month_col写数据时直接算好存进去再在这列上建索引。MySQL也从5.7开始支持生成列ALTER TABLE orders ADD COLUMN month_col VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time, %Y-%m)) STORED, ADD INDEX idx_month (month_col);这样既满足了按月份查询的需求索引也能被正常使用。SQL里的写法可以保持WHERE month_col 2025-01不需要再套函数。这种方式牺牲了一点存储空间换来了稳定的查询性能在报表场景里很实用。以下是我做SQL优化时整理的一个速查表遇到类似问题可以直接按表排查症状常见原因处理建议查询很慢EXPLAIN显示全表扫描WHERE列上套了函数改写为范围条件或加冗余生成列字符串条件查不到数据字段和条件类型不一致统一类型避免隐式转换聚合结果和自己手算的不一样忽略NULL或COUNT字段行为明确COUNT(*)/COUNT(字段)语义拼接结果突然变短GROUP_CONCAT长度超限调大group_concat_max_len日期范围查到了多余数据用字符串比较替代范围比较改成和闭开区间写法这套排查方法我每次做SQL审查都会用按这个顺序走一遍绝大多数函数使用不当导致的慢查询都能在几分钟内定位出来。函数本身不背锅关键是用在什么位置、怎么用。我个人在实际操作中的体会是MySQL函数并不难难的是建立函数结果是否影响索引使用这个意识。绝大多数网上教程只讲函数怎么用不太会讲函数放哪里会导致慢查询。所以我建议你每学一个函数都顺手想一遍如果这个函数用在WHERE的索引列上会发生什么有没有替代写法带着这个问题去写SQL时间长了就会养成肌肉记忆。另外写聚合统计时养成顺手用IFNULL兜底的习惯也能避免不少线上数据异常。最后再分享一个小技巧把你自己踩过的函数坑整理成一个固定SQL片段库下次遇到相似需求直接调比临时翻文档靠谱得多。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
