MySQL高频函数全解析：六大类函数用法与避坑指南

你是不是也有这种感觉每天写 SQL来来回回用到的函数就那么几个但一到关键时候就卡壳。比如要把两个字段拼起来突然忘了 CONCAT 遇到 NULL 会不会出错想按月份做统计DATE_FORMAT 的格式符还得临时翻手册更别提 ROUND、TRUNCATE、CEILING、FLOOR 这四个取整函数每次用都要纠结一下。说实话我做了这么多年数据开发和系统维护MySQL 函数相关的坑没少踩尤其是字符集、时区和隐式类型转换这几个方向网上资料讲得分散踩一次就得浪费半天去排查。这篇博文我打算把 MySQL 里最常见、最高频的函数系统梳理一遍包括字符串、数值、日期时间、流程控制、聚合和类型转换六大类每个函数都会说明适用场景、执行逻辑和容易踩的坑。重点不是背函数清单而是搞清楚“什么时候用哪个、为什么这么用”。内容基于 MySQL 8.0 的默认行为也适合刚入门没多久的开发同学写过几年 SQL 但没系统整理过的人同样能从中找到平时忽略的细节。1. 字符串函数日常写 SQL 最先用到的工具箱字符串处理是 SQL 里最频繁的操作无论是拼接报表字段、截取编号、脱敏手机号还是做模糊条件判断都离不开这一组函数。但这一组函数也是初学者最容易写出“能跑但不对”的 SQL 的地方因为 NULL 的传播特性、字符集差异和隐式转换都会在背后捣乱。1.1 拼接、截取、大小写文本处理三板斧先说说 CONCAT。它是用来拼接多个字符串的比如CONCAT(订单号: , order_no)功能很直白但有一个非常经典的坑只要参与拼接的任何一个值是 NULL整个结果就会变成 NULL。原因很简单NULL 在 SQL 语义里代表“未知”未知和任何字符串拼接的结果依然未知。实际业务里经常有“用户没有填姓氏”这种场景一拼出来整列都是空白排查半天才发现是 NULL 的问题。更好的做法是用CONCAT_WS(separator, str1, str2, ...)它的第一个参数是分隔符并且会自动忽略 NULL 值。举例来说拼接省市区地址字段时CONCAT_WS(-, province, city, district)不会因为某个区为空就整个地址消失而是只显示已填的部分。这一点在导出报表、生成日志时特别有用。截取函数 SUBSTRING也常写 SUBSTR用得也很多语法是SUBSTRING(str, pos, len)。注意 MySQL 的字符下标从 1 开始不是从 0 开始这是很多从其他语言转过来的开发者最容易搞混的地方。它支持负索引SUBSTRING(abcdef, -3, 2)会从倒数第三个字符开始往后取两个结果是 de适合从右侧取指定长度的字符串比如取订单号后四位。大小写转换函数 UPPER 和 LOWER 没什么好说的一个是转大写一个是转小写。但有一点值得注意它们作用于非英文字符时行为取决于字符集和排序规则。在 utf8mb4 下对中文调用 UPPER 不会有什么变化这很正常。1.2 查找替换与去空格处理文本内容的核心组合查找某个子串在字符串中第一次出现的位置用到的是 LOCATE 或者 INSTR。LOCATE(substr, str)返回子串的位置找不到就返回 0INSTR(str, substr)参数顺序跟 LOCATE 是反的我见过不少人把这两个记混导致查出来的结果莫名其妙。建议只记住一个我习惯用 LOCATE因为它的参数顺序更符合自然语言“在谁里面找什么”。字符串补位函数 LPAD 和 RPAD 也值得单独拎出来说。比如生成订单流水号时要求编号统一是 8 位不足部分在左侧补零LPAD(CAST(id AS CHAR), 8, 0)。这个函数经常在报表编号、优惠券编码场景里配合 CAST 一起使用非常实用。REPLACE 是全量替换函数REPLACE(str, from_str, to_str)。它的典型场景是做脱敏比如把手机号中间四位替换成星号REPLACE(phone, SUBSTRING(phone, 4, 4), ****)。不过这里有个经验之谈脱敏最好写明确不要用这种动态截取再替换的写法因为如果手机号中间四位在某些数据里出现多次REPLACE 会把所有匹配的地方都替换掉。安全起见直接用 CONCAT 把前三后四拼出来更可控。去空格方面TRIM 去两端空格LTRIM 只去左边RTRIM 只去右边。TRIM 还能去掉指定字符比如TRIM(BOTH x FROM xxhelloxx)会返回 hello。需要注意这里默认去的是空格如果数据里有换行符\n或制表符\tTRIM 不会处理得配合 REPLACE 先把换行符替换掉再 TRIM。1.3 CHAR_LENGTH 和 LENGTH 的区别中文场景最容易翻车很多人在 utf8mb4 字符集下统计字符串长度时会踩坑问题就出在 CHAR_LENGTH 和 LENGTH 的区别上。CHAR_LENGTH 返回的是字符数也就是我们人眼看到的“有多少个字符”LENGTH 返回的是字节数。在 utf8mb4 编码下一个英文字母占 1 个字节但一个中文汉字占了 3 个字节。所以CHAR_LENGTH(你好)的结果是 2而LENGTH(你好)的结果是 6。如果你用 LENGTH 去判断一段用户评论是否超过某个长度就会把中文字数放大三倍导致明明是 50 个字却提示超长。这里要特别提醒建表时 varchar(20) 中的 20 表示的是字符数不是字节数。所以在做输入校验时应该用 CHAR_LENGTH 来对齐业务上的“字数”概念只有当你真正关心存储占用比如判断某个字段是否超过 B 树索引的字节上限时才需要看 LENGTH。我见过有人用 LENGTH 做评论字数限制上线后中文内容全被卡住就是一个非常典型的案例。1.4 实操心得字符集、排序规则与索引的那些事字符串函数用得好不好很多时候取决于你对字符集和排序规则有没有概念。MySQL 8.0 默认是 utf8mb4默认排序规则一般是 utf8mb4_0900_ai_ci其中 ai 表示口音不敏感ci 表示大小写不敏感。也就是说条件里写WHERE username Admin可能会把 admin、ADMIN 都查出来。如果业务上要求用户名严格区分大小写就要把排序规则改成 ..._bin或者在设计表时就确定好。还有一条铁律不要在索引列上直接套函数。比如WHERE UPPER(username) ADMIN写法看着没毛病但 MySQL 必须先对每一行的 username 执行 UPPER再拿结果去比较原来的 B 树索引直接失效数据量一大就是全表扫描。解决办法通常是存储时统一大小写格式或者使用 MySQL 8.0 的生成列把UPPER(username)作为单独的列并加上索引查询时直接用这个生成列。我自己在维护业务系统时遇到过不止一次因为字符串函数导致线上慢查询的案例。排查到最后几乎都是同样的套路条件字段的类型和排序规则没确认函数一上去索引就废了。所以现在写 SQL 前我都会先确认列的类型和排序规则条件里尽量不做函数包裹。2. 数值函数别在计算上栽跟头数值计算看着简单真正埋雷的地方往往不在函数本身而在数据类型。你在 SELECT 里写 0.1 0.2表面上是两个小数相加实际上 MySQL 的浮点类型在二进制层面根本没法精确表示 0.1 和 0.2所以结果会变成 0.30000000000000004。这种问题在金额计算里是不可接受的。2.1 四舍五入与取整ROUND、TRUNCATE、CEILING、FLOOR 的边界ROUND(x, d) 是四舍五入函数d 表示保留的小数位数。ROUND(2.567, 2)结果是 2.57。如果不写 d相当于四舍五入到整数ROUND(2.5)会返回 3。注意 MySQL 这里的四舍五入规则是“逢五进位”跟某些编程语言的“银行家舍入”不一样做财务计算时不要凭经验套用其他语言的行为。TRUNCATE(x, d) 是直接截断不四舍五入。TRUNCATE(2.567, 2)结果是 2.56。这两个函数的差别在会计场景里很关键如果系统里的金额显示只需要保留两位小数但实际计算又不想因为四舍五入产生累计误差那就得想清楚每一层到底用 ROUND 还是 TRUNCATE。向上取整是 CEILINGCEIL 是它的别名向下取整是 FLOOR。CEILING(2.1)返回 3FLOOR(2.9)返回 2。买票、计费、分页这类场景经常会用到 CEILING比如按 100 条一页算总页数就是CEILING(total / 100)。还有一个很多人不知道的用法ROUND 和 TRUNCATE 的位数 d 可以是负数表示在小数点左侧进行舍入。例如ROUND(1234.567, -2)的结果是 1200ROUND(1250, -2)的结果则是 1300因为它按百位四舍五入。这个技巧在做“金额以百元为单位汇总”的报表时很实用但可读性差建议在 SQL 注释里写清楚。2.2 取余、绝对值与随机数MOD、ABS、RAND 的冷知识MOD(x, y) 是取余函数等价于x % y。它的一个细节是结果的符号跟被除数一致MOD(-7, 2)在 MySQL 里返回 -1而不是 1。如果业务里需要判断一个数是奇数还是偶数注意不要被负数干扰稳妥写法是MOD(ABS(column), 2)。ABS 是绝对值函数RAND 是随机数函数。RAND() 返回 [0, 1) 区间的一个随机小数RAND(N) 则是一个固定种子同一个种子生成的随机序列是确定的。这个特性可以用来生成测试数据保证每次跑出来的数据一样方便复现问题。日常开发里常见的需求是从表中随机取 N 条记录很多人会直接ORDER BY RAND() LIMIT 10。这个写法在小表上没有感觉但在千万级的大表上会非常痛苦因为 MySQL 需要对全表每一行都生成随机数再做排序。更合理的思路是查出主键的最小值和最大值在区间内随机取一个主键值再用主键去定位记录。当然如果业务允许稍微复杂的写法用子查询随机取主键再关联原表性能也好很多。2.3 实战经验金额计算不要直接用浮点类型这个坑我必须单独拿出来说。MySQL 的 FLOAT 和 DOUBLE 是二进制浮点数无法精确表示很多十进制小数。比如SELECT 0.1 0.2;在 MySQL 8.0 里执行结果不是 0.3而是 0.30000000000000004。这个误差在单次计算里几乎看不出来但一旦累加、汇总就会积累出不可忽略的差异。订单金额、账户余额、单价这类数据设计表结构时就必须用 DECIMAL比如DECIMAL(10,2)表示最多 10 位数字保留 2 位小数。如果因为历史原因某个金额字段已经是 DOUBLE想要纠正数据时也要小心。直接 UPDATE 回写很容易因为精度问题产生新的误差建议先把数据读出来在应用层使用高精度类型处理再写回。日常写聚合 SQL 时对 DECIMAL 字段做 SUM 一般是安全的但做除法或 AVG 可能产生很多位小数最终展示前用 ROUND 控制一下位数能避免报表里出现一连串诡异数字。3. 日期时间函数时区与索引是最容易踩的坑日期时间是业务系统里最绕不开的类型但也是新手翻车重灾区。原因主要有两个一个是 MySQL 的日期时间函数数量多、格式符多另一个是时区问题让同一段时间在不同环境下表现出完全不同的结果。再加上函数套列导致的索引失效日期条件往往成为慢查询的头号元凶。3.1 获取当前时间NOW、CURRENT_TIMESTAMP、SYSDATE 的细微差别NOW() 和 CURRENT_TIMESTAMP 基本是同一个函数返回的是当前语句开始执行时的日期时间精确到秒或微秒取决于版本和参数。重点要讲的是 SYSDATE()它跟 NOW() 看起来一样实际却有一个微妙差异SYSDATE() 返回的是函数被调用那一刻的实时时间。你可以做个简单验证SELECT NOW(), SYSDATE(), SLEEP(2), NOW(), SYSDATE();这个语句里两个 NOW() 的值是一样的而两个 SYSDATE() 会相差 2 秒因为中间 SLEEP 了 2 秒。看似不起眼但在长事务、存储过程或者批量更新脚本里SYSDATE() 会导致“同一个事务里取到不同时间”的情况进而影响数据的一致性判断。所以我的建议是业务逻辑里统一用 NOW()不要随手用 SYSDATE()。配套的几个函数也顺便记一下CURDATE() 和 CURRENT_DATE() 返回当前日期CURTIME() 和 CURRENT_TIME() 返回当前时间。这些函数返回的类型分别是 DATE 和 TIME在做日期区间查询时可以直接参与比较。3.2 格式化与解析DATE_FORMAT 与 STR_TO_DATEDATE_FORMAT 是把日期转成指定格式的字符串格式符非常丰富常用的有%Y 四位年份%y 两位年份%m 两位月份%c 月份不补零%d 两位天%e 天不补零%H 24 小时制%h 12 小时制%i 分钟%s 秒%W 星期几的英文名%M 月份的英文名典型用法是DATE_FORMAT(order_time, %Y-%m-%d)把 datetime 转成 2024-03-15 格式用来按天分组统计SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS order_day, COUNT(*) AS cnt FROM orders GROUP BY DATE_FORMAT(order_time, %Y-%m-%d);STR_TO_DATE 是反向操作把字符串按指定格式解析成日期类型多用在导入外部数据。比如 CSV 里的日期列是 15/03/2024 10:30:00可以用STR_TO_DATE(15/03/2024 10:30:00, %d/%m/%Y %H:%i:%s)转成标准 DATETIME。注意格式符必须跟字符串完全对应否则会返回 NULL 或报错。这里有个经验在做报表时如果日期字段是 datetime 类型按天分组更推荐用区间判断而不是 DATE_FORMAT 函数套列。下面的 3.4 会细讲。3.3 日期运算与区间提取DATE_ADD、DATEDIFF、TIMESTAMPDIFFDATE_ADD(date, INTERVAL expr unit) 是在日期上增加一段时间DATE_SUB 则是减少。单位可以是 YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND。比如SELECT DATE_ADD(2024-03-15, INTERVAL 1 MONTH); -- 2024-04-15 SELECT DATE_SUB(2024-03-15, INTERVAL 7 DAY); -- 2024-03-08INTERVAL 后面的数值也可以是负数所以 DATE_SUB 完全可以用 DATE_ADD 加负值替代。区分两个函数的意义更多在于可读性。顺手记一个非常实用的函数 LAST_DAY(date)它返回指定日期所在月份的最后一天。LAST_DAY(2024-02-10)返回 2024-02-29做月末结算、出账单时特别方便。DATEDIFF(end_date, start_date) 返回两个日期相差的天数计算逻辑是end_date - start_date。DATEDIFF(2024-03-20, 2024-03-15)返回 5。TIMESTAMPDIFF(unit, start_date, end_date) 更灵活可以按 MONTH、HOUR、MINUTE 等粒度计算差值比如计算两个时间相差几个月、几小时。注意 TIMESTAMPDIFF 的参数顺序是“结束时间在前、开始时间在后”跟 DATEDIFF 相反很多人第一次用会搞反导致结果出现负数。日期中提取部分字段的函数也很好用YEAR(date)、MONTH(date)、DAY(date) 分别取年、月、日更通用的还有 EXTRACT(YEAR FROM date)、EXTRACT(MONTH FROM date)。统计“本年度注册用户”或“某月订单”这些需求都会用到。3.4 实操心得别在索引列上套函数日期函数导致索引失效是我排查慢查询时遇到频率最高的问题之一。最典型的写法是这样SELECT * FROM orders WHERE DATE_FORMAT(order_time, %Y-%m-%d) 2024-03-15;这个写法语义上没错但 MySQL 没办法用 order_time 上的索引因为每一行的 order_time 都必须先经过 DATE_FORMAT 转成字符串再跟目标值比较。处理上百万行数据时全表扫描的代价非常明显。更好的写法是范围查询SELECT * FROM orders WHERE order_time 2024-03-15 00:00:00 AND order_time 2024-03-16 00:00:00;这样既覆盖了 3 月 15 日全天又保留了 B 树索引的顺序匹配能力。再比如“最近 7 天订单”很多人写成WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) order_time其实没问题因为左侧的 DATE_SUB 是作用于常量 NOW()不是作用于列真正要避免的是在 order_time 上套函数。时区问题也需要警惕。如果程序写入的时间是 UTC数据库却按本地时区读取日期查询和报表统计就会凭空差出 8 小时。处理办法是让数据库、连接参数和业务代码的时区配置保持一致。MySQL 的 CONVERT_TZ 函数可以转换时区但依赖系统的时区表是否已加载实际使用中反而容易引入新的不确定性不如从源头统一。4. 条件函数与流程控制让 SQL 具备“逻辑判断”能力你有没有遇到过这样的场景一个查询要根据某个字段的不同值返回不同结果比如订单状态 1 显示为“待支付”2 显示为“已支付”。如果全靠应用层去处理写出来的代码又长又啰嗦。条件函数就是为这类需求准备的它们让 SQL 有能力做判断和分支把部分展示逻辑落到数据库里。4.1 IF、IFNULL、NULLIF三个易混但常用的条件函数IF(expr, true_value, false_value) 是三目运算符。IF(amount 100, 大额, 普通)会根据条件返回对应的值。这个函数很小巧但我更推荐在复杂逻辑里使用 CASE WHEN因为条件多的时候 IF 嵌套会让 SQL 变得非常难读。IFNULL(expr1, expr2) 用来处理 NULLexpr1 是 NULL 就返回 expr2否则返回 expr1。这个函数在查询用户昵称、备注、扩展字段时特别常用比如IFNULL(nickname, 匿名用户)。要注意的是IFNULL 在返回时可能触发隐式类型转换如果两个参数类型不一致尽量先手动做好类型转换别依赖隐式行为。NULLIF(expr1, expr2) 跟 IFNULL 不是一回事当两个参数相等时它返回 NULL否则返回第一个参数。最经典的用法是防除零。比如要计算“完成率 完成数 / 总数”当总数为 0 时直接除会报错写成完成数 / NULLIF(总数, 0)分母变成 NULL整个表达式的结果就变成 NULL不会报错展示层再统一处理 NULL 即可。4.2 CASE WHEN表达式的两种写法与适用场景CASE WHEN 有两种写法。第一种是简单 CASE 表达式语法是CASE value WHEN expected_value THEN result ELSE default END适合做等值匹配比如CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 END。第二种是搜索 CASE 表达式语法是CASE WHEN condition THEN result ELSE default END条件可以写任意比较表达式比如范围判断、LIKE、IN 等。我个人的习惯是尽量用搜索式哪怕等值匹配也写成CASE WHEN status 1 THEN ...。原因很简单可读性更好后续如果要从等值判断改成范围判断不需要重写整个表达式。另外搜索式 CASE 是标准 SQL 写法跨数据库迁移时兼容性也更好。CASE WHEN 最容易被忽略的一点是 ELSE 缺省时默认返回 NULL。如果某个分支没有匹配结果就是 NULL这在后续聚合、拼接时会产生连锁反应。如果你确定业务上“没有匹配就返回 0”一定要写 ELSE 0。4.3 COALESCE一次性处理多个字段的默认值COALESCE 的作用是返回参数列表里第一个非 NULL 的值可以传多个参数这在多字段叠加取值的场景里非常好用。比如一个用户表里有 nickname、phone、reg_source 三列想在前台展示时“昵称优先没有昵称用手机号再没有就用注册来源”一条 SQL 就能搞定SELECT COALESCE(nickname, phone, reg_source, 未知用户) AS display_name FROM users;COALESCE 是标准 SQLIFNULL 可以看成它只支持两个参数的简化版。两者效率上没有明显差别但 COALESCE 更通用建议新代码统一用它。它和 NULLIF 搭配还能解决“空字符串也要当成空值”的问题。例如COALESCE(NULLIF(TRIM(nickname), ), 匿名用户)先用 TRIM 去掉空格用 NULLIF 把空串变成 NULL再交给 COALESCE 补默认值一步到位。4.4 实战案例用条件函数做分类统计条件函数最强大的用法是放在聚合函数内部实现“按条件计数”或“按条件求和”。比如订单表里有一列 amount想统计每个业务员“大额订单金额 1000有多少笔”可以这么写SELECT sales_name, SUM(CASE WHEN amount 1000 THEN 1 ELSE 0 END) AS large_order_cnt, SUM(CASE WHEN amount 1000 THEN 1 ELSE 0 END) AS normal_order_cnt, COUNT(*) AS total_cnt FROM orders GROUP BY sales_name;这里的原理是SUM 会跳过 NULL而 CASE 表达式在条件不满足时返回 0所以 SUM(CASE ... THEN 1 ELSE 0 END) 统计出来的就是满足条件的行数。也可以用COUNT(IF(condition, 1, NULL))达到同样效果两者本质一样。但要小心COUNT 的 IF 分支里不要用 0因为 COUNT(0) 会计数只有 NULL 才会被跳过。这种写法可以避免拆分成多次查询再在应用层合并的麻烦一条 SQL 就能把多个维度的统计结果拉出来特别适合做销售看板、渠道分析这类报表。5. 聚合函数统计报表的正确姿势聚合函数是报表类 SQL 的基石COUNT、SUM、AVG、MAX、MIN 五个函数几乎覆盖了所有统计需求。但越基础的东西越容易在细节上出错。比如 COUNT(字段) 在字段有 NULL 时统计结果会偏小AVG 的分子分母也不是你直觉里那样。下面把几个最容易混淆的细节一次讲清楚。5.1 COUNT 家族COUNT(*)、COUNT(1)、COUNT(字段) 到底啥区别网上关于 COUNT() 和 COUNT(1) 性能差异的说法五花八门在 MySQL 8.0 的 InnoDB 引擎下这两者基本没有性能差别。COUNT() 是统计行数COUNT(1) 也是统计行数因为括号里的 1 只是一个常量表达式MySQL 不会去读具体列。真正容易出错的是 COUNT(字段)。COUNT(title) 这样的写法会统计 title 字段非 NULL 的行数。换句话说如果 title 是 NULL这一行不会被计入结果。这在统计“发表过文章的用户数”时是合理的但如果你只是想知道表里一共有多少行却写成 COUNT(id)一旦某行 id 为 NULL虽然主键不会但普通字段会结果就不对了。最稳妥的口诀统计行数用 COUNT(*)统计某个字段有值的行数用 COUNT(字段)。COUNT(DISTINCT column) 也很常用它统计的是某列去重后的非 NULL 数量。比如统计“有多少个不同城市下单”就可以写COUNT(DISTINCT city)。如果你的需求只是判断“是否存在满足条件的记录”用 EXISTS 通常比 COUNT(*) 更快因为 COUNT 要数完所有符合条件的行为止EXISTS 遇到第一条就会停下来。5.2 SUM、AVG 与 NULL 的爱恨纠葛SUM(字段) 会自动忽略 NULL 值这个特性大多数人都知道。但如果被统计的字段全部都是 NULLSUM 的结果不是 0而是 NULL。所以前台展示时可能会看到一个空白最好用 IFNULL 或 COALESCE 包一层把 NULL 转成 0。AVG 的细节更容易忽略。AVG(字段) 在计算时分母是“该字段非 NULL 的行数”不是表的总行数。比如你有 10 个学生其中 3 个没有交作业导致成绩列为 NULLAVG(score) 只算了 7 个人的平均分。如果你希望把缺考的人按 0 分计就不能直接用 AVG得先COALESCE(score, 0)或者用 SUM(score) / COUNT(*) 来算。MAX 和 MIN 同样会忽略 NULL 值这个行为在某些业务场景下是隐患。比如统计“最近一次登录时间”如果某用户从来没有登录过MAX(login_time) 会返回 NULL而不是默认的 1970 年之类的东西。所以习惯是聚合结果前想清楚NULL 是代表“没有数据”还是应该被当成 0 或默认值。5.3 聚合前的过滤WHERE 和 HAVING 的分工这个问题在面试里经常出现在真实场景里也经常有人写错。WHERE 是在聚合之前过滤原始行HAVING 是在聚合之后过滤分组结果。比如要查“2024 年之后每个月的销售额且销售额大于 1 万元”就得先 WHERE 过滤时间范围再 GROUP BY 月份最后 HAVING 过滤聚合结果。SELECT DATE_FORMAT(order_time, %Y-%m) AS month, SUM(amount) AS month_sales FROM orders WHERE order_time 2024-01-01 GROUP BY month HAVING SUM(amount) 10000;这里有个性能原则能用 WHERE 提前过滤的就不要留给 HAVING。因为 WHERE 在分组前就把大量无关行排除掉聚合的数据集越小执行速度越快。HAVING 里写的条件不能引用非聚合的非分组列这跟 5.4 要讲的 ONLY_FULL_GROUP_BY 有关。5.4 实操心得ONLY_FULL_GROUP_BY 模式下的写法从 MySQL 5.7 开始ONLY_FULL_GROUP_BY 默认是开启的。这个 SQL 模式要求SELECT 列表里出现的普通列必须出现在 GROUP BY 中或者在聚合函数里。否则会直接报错。这个限制的初衷是消除二义性。比如SELECT dept, name, MAX(salary) FROM employees GROUP BY dept;如果同部门有两个不同姓名的员工查询应该显示谁的名字数据库根本没有依据来选。所以 MySQL 直接拒绝执行。很多人第一次遇到这个报错第一反应是关掉 SQL 模式我强烈不建议这样做。关闭之后 SQL 能跑但结果可能包含不确定的随机行生成报表时数字对不上排查起来更痛苦。正确的做法是用子查询或窗口函数。比如要取每个部门工资最高的人可以这样SELECT dept, name, salary FROM ( SELECT dept, name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 1;窗口函数是理解这种需求的标准解法它本质上是把“分组后取组内特定行”的问题从聚合函数里解放出来。如果你还没接触过窗口函数遇到这类需求时就可以开始学习它了。6. 类型转换与实用函数避坑清单最后一章聊两类内容一类是显示类型转换函数另一类是平时容易被忽略但又很实用的其他函数。最重要的是我会整理一张速查表把前面提到的坑汇总到一起方便你贴在工作位上。6.1 CAST 与 CONVERT显式转换保平安CAST(expr AS type) 和 CONVERT(expr, type) 是 MySQL 里的两个显式类型转换函数功能基本一致。常见的目标类型包括 DATE、DATETIME、TIME、DECIMAL、SIGNED带符号整数、CHAR 等。比如SELECT CAST(2024-03-15 AS DATE); -- 转成日期类型 SELECT CAST(123 AS SIGNED); -- 转成整数 SELECT CONVERT(2024-01-01 10:00:00, DATETIME);显式转换最大的价值是避免隐式转换带来不可预知的后果。举一个我排查过的真实案例某个表的 phone 字段是 varchar条件传了一个数字WHERE phone 13800138000。这种写法 MySQL 会把字段值转成数字再跟数字比较结果有两个问题一是手机号如果前面有 0转换后 0 会丢失匹配不上二是对列做隐式转换会让索引失效造成全表扫描。正确写法是把条件里的数字写成字符串WHERE phone 13800138000。我在团队里定了条规矩所有字符串字段跟常量比较时常量必须加引号。这条规矩看似简单却避免了大量因为隐式转换导致的慢查询和错误匹配。6.2 加密与哈希函数的使用边界MySQL 提供 MD5()、SHA1()、SHA2() 等哈希函数。哈希函数的特点是单向不可逆相同输入产生相同输出适合做不需要还原的数据指纹。比如对用户手机号做脱敏生成一个固定长度的标识用于跨表关联但不直接暴露原始号码。需要特别提醒的是永远不要把用户密码直接MD5(password)存进数据库。MD5 彩虹表攻击太容易了加盐虽然能提高一些成本但现代密码存储应该使用专门的密码哈希算法如 bcrypt、argon2这些算法在应用层实现MySQL 自带的 MD5/SHA2 不是为密码存储设计的。数据库里用哈希函数更多是生成匿名标识、校验文件内容这类需求。另外SHA2(str, hash_length) 需要第二个参数指定输出位数常见的是 256表示 SHA-256。SHA2(hello, 256)会返回一个 64 位的十六进制字符串。6.3 函数使用避坑清单一张表记住所有高频坑场景容易踩的写法建议写法原因日期相等判断WHERE DATE_FORMAT(dt, %Y-%m-%d) 2024-03-15WHERE dt 2024-03-15 00:00:00 AND dt 2024-03-16 00:00:00函数套列导致索引失效字符串列与数字比较WHERE phone 13800138000WHERE phone 13800138000隐式类型转换可能让索引失效拼接含 NULLCONCAT(a, b)CONCAT_WS(, a, b)统计行数COUNT(某字段)COUNT(*)字段有 NULL 时统计结果偏小金额字段设计FLOAT / DOUBLEDECIMAL(10, 2)二进制浮点数存在精度误差空字符串也算空值IFNULL(name, 匿名)COALESCE(NULLIF(name, ), 匿名)NULLIF 把空串转成 NULLAVG 统计含缺考AVG(score)SUM(score) / COUNT(*)或先 COALESCEAVG 分母是非 NULL 行数取分组内特定行SELECT 非聚合列 GROUP BY窗口函数 ROW_NUMBER()满足 ONLY_FULL_GROUP_BY 规则防除零错误完成数 / 总数完成数 / NULLIF(总数, 0)分母为 NULL 结果返回 NULL 不报错这张表里的每一条我都对应到真实场景踩过或帮别人排查过。函数本身不难记难的是知道什么时候不该用、用了会产生什么连带问题。我在实际项目里养成的一个习惯是写完一条 SQL 先问自己三件事列的数据类型是不是匹配条件里有没有对列做函数包裹聚合或分组的结果会不会因为 NULL 产生偏差这三件事检查完SQL 的质量基本就有底了。遇到不熟悉的函数也别急着背先建一张临时表插几行边界数据包含 NULL、空串、负数、最大长度直接在测试环境里执行看结果再用 EXPLAIN 看执行计划比翻任何手写笔记都直观。函数这东西记不全很正常关键是心里有一张“哪里容易翻车”的地图。希望这篇梳理能帮你在写 SQL 时少走几步弯路把更多精力留给真正要解决的问题。