做 Java 开发这几年我有个特别真切的感受框架可以一个接一个地学但 MySQL 函数这种东西真的是用到哪查到哪每次查完就忘换个场景又得重新翻。这段时间我决定把 Java 这条老路重走一遍第二篇就落在 MySQL 函数上。这篇内容不是把官方手册抄一遍而是站在 Java 开发者的角度把字符串函数、数值函数、日期时间函数、流程控制函数、聚合函数这几大类按真实业务里出现频率最高的用法重新整理一遍每一步都配业务案例和踩坑记录。如果你符合下面三种情况这篇会很对胃口刚学完 Java 语法准备接项目但一到 SQL 就卡壳的初学者工作一两年业务代码能写但 SQL 只会查单表碰到统计报表就头疼的初中级开发把 MySQL 函数当面试八股文背过却说不清“什么时候该用 IF 什么时候该用 CASE”的求职党。MySQL 函数不难难的是建立一张“什么场景用什么函数”的地图这篇就是帮你把地图画出来。1. 搞清 MySQL 函数的地图Java 开发才不会被 SQL 卡住1.1 为什么要把 MySQL 函数从手册里拎出来我见过不少人写 Java 业务习惯把所有数据从数据库捞出来再在 Java 里做循环统计、拼接字符串、判断状态。比如统计用户年龄段分布先 SELECT * 查出全部用户再在 service 层 for 循环数一遍。这种写法在数据量小的时候没问题一旦数据量上来JVM 内存扛不住网络传输也浪费而一条带函数的聚合 SQL 就能解决。反过来我也见过 SQL 水平不错但 Java 代码稀烂的人把大量正则、日期解析、字符串处理逻辑写在数据库函数里结果数据库 CPU 被打满应用层还很难排查。正确的心态是把 MySQL 函数当成工具能用索引解决的优先用索引能用 SQL 表达的统计语义就交给 SQL但绝不能滥用。MySQL 函数本身很简单它难在三个地方函数的返回类型容易记混、函数的 NULL 传播往往违反直觉、函数套在索引列上会导致索引失效。这三个问题我会在每个分类里反复提到因为它们在真实开发中造成的 Bug 占比非常高。1.2 函数分类总览一张表建立全局认知我第一次系统梳理 MySQL 函数是照着官方文档一条条看的结果看完全忘了。后来换了个思路按“Java 开发里什么需求最常用”把函数分成五组瞬间清晰很多。函数类别代表性函数Java 开发中最常见的场景字符串函数CONCAT、SUBSTRING、REPLACE、TRIM、LPAD拼接查询条件、拆分字段、数据清洗、编号补零数值函数ROUND、ABS、MOD、RAND、FLOOR金额四舍五入、分表路由取模、随机抽样日期时间函数NOW、DATE_FORMAT、DATEDIFF、DATE_ADD统计今日数据、计算用户年龄、超时判断流程控制函数IF、CASE WHEN、COALESCE、IFNULL状态值翻译、多条件分组统计、NULL 兜底聚合函数COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT报表统计、分组求和、列表标签拼接这张表不用背多写几次自然就熟了。关键是记住函数之间经常组合出现并不是非此即彼。比如一条订单统计 SQL可能同时用到 DATE_FORMAT 做按天格式化、CASE WHEN 做状态分类、SUM 做金额汇总、GROUP_CONCAT 做订单号拼接单看每个函数都不复杂组合起来才能真正解决业务问题。1.3 学函数前先记住三件事第一件事先想清楚函数的返回类型。字符串函数返回字符串数值函数返回数值日期函数返回日期或字符串类型错了后面的运算就会出问题。比如 FORMAT(12345.678, 2) 返回的是字符串 12,345.68如果你拿这个结果去加一个数MySQL 会把字符串转成数值逗号被去掉后变成 12345.68可能没问题但在 Java 里你拿到的就是一个带逗号的字符串前端展示和后续 BigDecimal 转换都可能踩坑。第二件事极度重视 NULL 传播。CONCAT(NULL, a) 不会返回 a而是 NULLIF(NULL, 1, 2) 返回 2SUM 聚合会忽略 NULL。这些规则长期被忽略但只要有一个字段被允许为 NULL统计结果就可能错得离谱。第三件事警惕函数套在索引列上。WHERE DATE(create_time) 2024-01-01 这种写法即使 create_time 建了索引MySQL 也无法使用必须改成范围条件。这个会在后面的排查章节展开细说但请先把它刻在脑子里对列做任何“加工”都可能让索引报废。2. 字符串与数值函数写业务绕不开的那批高频用法2.1 字符串函数拼接、截取、替换与补位CONCAT 是最基础的拼接函数坑在于只要有一个参数是 NULL结果就是 NULL。你写 CONCAT(username, -, phone)只要 phone 是 NULL整个字段查出来就是 NULL。这时候要用 CONCAT_WS它的好处是会自动跳过 NULL还能指定分隔符比如 CONCAT_WS(-, username, phone, email) 会把 NULL 值跳过而不是让整条结果变 NULL这在导出报表时非常实用。SUBSTRING 做截取时要注意位置从 1 开始不是从 0 开始。SUBSTRING(username, 2, 3) 取的是第 2 位开始的 3 个字符和 Java 的 substring(1, 4) 语义完全不同写习惯 Java 后特别容易在这翻车。SUBSTRING_INDEX 是按分隔符取段比如 SUBSTRING_INDEX(北京市/朝阳区/望京, /, 2) 返回 北京市/朝阳区想取最后一段就给负数SUBSTRING_INDEX(字段, /, -1) 返回 望京。处理层级地址、JSON 字段里的某个值时特别好用。REPLACE 做全局替换TRIM 去首尾空格LTRIM 和 RTRIM 分别去左、右空格。这里有个经验用户提交手机号或者身份证号时经常带空格和横线入库前用 REPLACE(REPLACE(phone, -, ), , ) 先清洗一遍比在 Java 层写一堆 replace 链更直接。补位函数 LPAD 和 RPAD 很常用比如工号统一补零LPAD(user_id, 8, 0) 能把 123 变成 00000123。还有一个容易忽视的细节LENGTH 返回字节数CHAR_LENGTH 返回字符数。在 utf8mb4 字符集下LENGTH(中文) 返回 6CHAR_LENGTH(中文) 返回 2。统计用户昵称长度、做输入校验时一定用 CHAR_LENGTH我见过有人用 LENGTH 校验昵称表情符号直接被算成 4 个字节导致长度判断完全不准。-- 字符串函数组合示例清洗手机号并补全展示格式 SELECT CONCAT_WS( | , username, LPAD(REPLACE(REPLACE(phone, -, ), , ), 11, 0)) FROM user WHERE CHAR_LENGTH(username) BETWEEN 2 AND 20;2.2 数值函数取整、取模、随机与格式化ROUND 做四舍五入TRUNCATE 直接截断CEIL 向上取整FLOOR 向下取整。这四个的差异在金额计算里非常敏感订单优惠金额如果按 ROUND 和按 TRUNCATE 分别计算最后的对账单可能差几分钱。Java 端如果用了 BigDecimalMySQL 端也建议统一用 ROUND 或 TRUNCATE 且明确小数点位数避免两边结果不一致。ABS 取绝对值RAND 取随机数。RAND() 配合 ORDER BY 可以做随机抽样但数据量大时 ORDER BY RAND() 性能极差因为它会对整个结果集做随机排序。通常的做法是先 COUNT 拿到行数在 Java 层算出随机偏移量再用 LIMIT offset, 1这样能避免全表排序。MOD 取模在分表路由里最常用。但注意MySQL 的 MOD 和 Java 的 % 在负数场景下结果不一致。MySQL 中 MOD(-7, 3) 返回 2而 Java 中 (-7) % 3 返回 -1。如果你在 Java 里按 MOD 的结果做分表又在 SQL 里用 MOD 做路由两边算出的表号可能不一样数据就读不到了。处理办法是统一要么两边都转成正数取模要么规定 MOD 语义并在代码里加上注释。FORMAT 可以把数字格式化为千分位字符串比如 FORMAT(1234567.891, 2) 返回 1,234,567.89。这个函数适合做报表展示但注意返回的是字符串不再是数值后续还有计算就必须先 CAST。另外Java 端如果用了 DecimalFormat两边格式也可能有差异建议展示层统一在 Java 做。-- 分表路由一致性示例Java 和 MySQL 都使用 MOD 并确认负数处理 SELECT MOD(CAST(user_id AS SIGNED), 4) AS table_no FROM user WHERE id 1001;2.3 组合实例用函数做一次数据清洗假设订单表里有“收货地址”字段格式是“省/市/区/详细地址”但历史数据里有的用“-”分隔有的带空格还有一些为 NULL。现在要统计每个省的订单数直接 GROUP BY 省份是做不到的。可以用 SUBSTRING_INDEX 先按“/”取第一段再用 REPLACE 把“-”和空格统一替换掉最后用 COALESCE 把 NULL 处理成“未知省份”。SELECT COALESCE(SUBSTRING_INDEX(REPLACE(REPLACE(address, -, /), , ), /, 1), 未知省份) AS province, COUNT(*) AS order_cnt FROM trade_order GROUP BY province ORDER BY order_cnt DESC;这个场景特别典型不是单个函数解决问题而是用“REPLACE 规范化 SUBSTRING_INDEX 提取 COALESCE 兜底 GROUP BY 聚合”一串组合拳。我在实际项目里处理过大量类似的数据质量问题经验是清洗逻辑最好在 SQL 层完成因为数据源头就是脏的Java 每读一次就洗一次会被动但清洗后如果要作为长期维度更建议做一次数据订正写入新字段而不是每条查询都现场算因为函数运算在 WHERE 和 GROUP BY 上都会影响性能。3. 日期时间函数Java 和 MySQL 的时区与格式地狱3.1 DATE_FORMAT 与 STR_TO_DATE别把格式串写反DATE_FORMAT 是把日期转成指定格式的字符串STR_TO_DATE 是反着来把字符串解析成日期。格式串里的占位符是 MySQL 特有的最常见的几个%Y 四位年%y 两位年%m 两位月%d 两位日%H 24 小时制小时%i 分钟%s 秒。对应到 Java 的 SimpleDateFormat 就是 yyyy、MM、dd、HH、mm、ss。这里有个坑Java 里分钟是 mm月份是 MM字母大小写含义完全相反MySQL 里月份是 %m分钟是 %i没有大小写之分但 %m 和 %i 必须区分。我见过有人把 STR_TO_DATE(2024-01-15 13:45:30, %Y-%m-%d %H:%i:%s) 写错成 %M结果解析出来月份变成了带字母的 January直接报错。这类问题不会出现在编译期而是运行期才爆所以写代码时建议固定模板-- 推荐固定格式模板Java 侧用 DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss) SELECT STR_TO_DATE(2024-01-15 13:45:30, %Y-%m-%d %H:%i:%s); SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s);DATE_FORMAT 在报表场景非常常用按天、按小时、按月分组统计时直接把 datetime 列格式化成对应粒度再 GROUP BY。但要注意对 datetime 列做 DATE_FORMAT 后原来的索引基本用不上如果是大表高频分组统计宁可提前冗余一个 date 类型的字段并加索引也不要每天现场 DATE_FORMAT。3.2 DATEDIFF 与 TIMESTAMPDIFF计算间隔时的顺序陷阱DATEDIFF(date1, date2) 返回 date1 减 date2 的天数结果是前减后。TIMESTAMPDIFF(unit, start, end) 返回 end 减 start 的值结果也是结束减开始但参数顺序看着像“开始在前结束在后”非常容易写反。我举个例子计算两个日期间隔天数用 DATEDIFF(2024-02-01, 2024-01-01) 得到 31TIMESTAMPDIFF(DAY, 2024-01-01, 2024-02-01) 也得到 31。区别在于 TIMESTAMPDIFF 前面需要指定单位支持 HOUR、DAY、WEEK、MONTH、YEAR 等还支持 SECOUND 的拼写容易错实际是 SECOND。实际开发中判断一个订单是否超时 24 小时如果用 TIMESTAMPDIFF(HOUR, create_time, NOW()) 24这个顺序是结束时间减去开始时间正确。如果写反结果永远是负数永远超时。我的习惯是在代码里统一注释第一个参数是单位第二个是起始时间第三个是结束时间。DATE_ADD 和 DATE_SUB 是往日期上加或减时间。DATE_ADD(NOW(), INTERVAL 7 DAY) 表示一周后。它们更适合用来构造范围查询比如查询最近 7 天订单SELECT COUNT(*) FROM trade_order WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY);这种写法对 create_time 索引友好因为函数只作用在常量上索引列本身没有被包裹。3.3 时区问题Java 侧少 8 小时的真相很多 Java 开发都遇到过数据库存的时间明明是对的用 JDBC 查出来却少了 8 小时或者反过来。原因几乎都是连接串没指定时区MySQL Connector/J 默认用了服务器时区而服务器时区与 JVM 时区不一致转换时出了问题。解决办法很直接JDBC URL 里加上 serverTimezoneAsia/Shanghai同时数据库、JVM、连接三处统一时区。另外要注意 MySQL 里 DATETIME 和 TIMESTAMP 两个类型的区别DATETIME 不做时区转换存什么取什么TIMESTAMP 在写入时从会话时区转成 UTC读取时再转回会话时区。TIMESTAMP 范围只有到 2038 年DATETIME 范围更大现在多数项目默认用 DATETIME我建议你也沿用这个约定。Java 侧接收日期字段时优先用 java.time.LocalDateTime通过 rs.getObject(create_time, LocalDateTime.class) 直接映射不要用 rs.getString 拿到字符串再做 SimpleDateFormat 解析一个是慢另一个是隐式依赖数据库返回的格式。如果必须格式化展示在 SQL 里用 DATE_FORMAT 或者在 Java 里用 DateTimeFormatter 都行但要固定一处别两边都做。# JDBC URL 固定写法避免 8 小时时区错乱 jdbc:mysql://localhost:3306/mydb?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/Shanghai4. 条件判断与流程控制在 SQL 里写 if 和 case4.1 IF、IFNULL、NULLIF、COALESCE选哪个更合适IF(expr, a, b) 类似 Java 三元表达式expr 为真返回 a否则返回 b。适合简单二选一比如 IF(amount 0, 已支付, 未支付)。不过 IF 的结果可以嵌套嵌套两层以上可读性就很差建议改用 CASE。IFNULL(a, b) 是专用 NULL 兜底如果 a 是 NULL 返回 b否则返回 a。这个是开发最高频的统计金额、拼接字段、排序都常用。注意 IFNULL 只处理两个参数多个字段兜底要嵌套比如 IFNULL(bonus, IFNULL(other, 0))这种嵌套链可读性也差不如改用 COALESCE。NULLIF(a, b) 语义是a 等于 b 时返回 NULL否则返回 a。它常用来排除特定值参与聚合。比如统计平均薪资时不想把某个测试员工的 10000 算进去可以用 AVG(NULLIF(salary, 10000))等价于把该值置 NULL 后让 AVG 忽略。这个函数知道的人不多但用对场合会很爽。COALESCE(a, b, c, ...) 返回参数列表中第一个非 NULL 的值。比 IFNULL 更适合多字段兜底比如展示电话优先手机号其次座机都没有显示“未填写”直接 COALESCE(mobile, landline, 未填写)。它在 Java 生态里对应的概念是 null 合并比一堆 if 判断链简洁得多。-- 典型应用订单列表中优惠金额为 NULL 时按 0 展示并参与排序 SELECT order_no, COALESCE(discount_amount, 0) AS discount_amount FROM trade_order ORDER BY COALESCE(discount_amount, 0) DESC;4.2 CASE WHEN 的两种写法CASE WHEN 有两种写法。简单表达式是 CASE 列 WHEN 值 THEN 结果适用于等值判断搜索表达式是 CASE 当满足条件 THEN 结果适用于范围判断、多条件复合。简单写法SELECT CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 ELSE 未知 END AS status_text FROM trade_order;搜索写法SELECT CASE WHEN amount 1000 THEN 大额订单 WHEN amount 100 THEN 普通订单 ELSE 小额订单 END AS order_level FROM trade_order;两种写法都返回表达式结果可以用在 SELECT 输出、WHERE 过滤、ORDER BY 排序、GROUP BY 分组。我建议等值翻译用简单写法范围判断用搜索写法不要混着写。CASE 表达式返回的列别名在 Java 的 mapper 映射时要成对写好比如 status_text 映射为 statusText不然 MyBatis 自动映射又可能对不上驼峰。搜索 CASE 里还可以放聚合判断比如 HAVING 里写 HAVING SUM(CASE WHEN typeBUY THEN amount ELSE 0 END) 1000这个结合场景非常多。4.3 行转列CASE 与聚合函数组合的唯一真解报表开发里最经典的“行转列”本质就是 CASE 配聚合函数。假设交易流水表里有 user_id、trade_type、amount分别是 BUY 和 SELL要统计每个用户买入总金额和卖出总金额一条 SQL 搞定SELECT user_id, SUM(CASE WHEN trade_type BUY THEN amount ELSE 0 END) AS buy_amount, SUM(CASE WHEN trade_type SELL THEN amount ELSE 0 END) AS sell_amount FROM trade_flow GROUP BY user_id;这个写法的要点CASE 在聚合之前把每一行分类SUM 再对分类后的结果求和。ELSE 0 很关键如果不写 ELSE不满足条件的行返回 NULL而 SUM 会忽略 NULL效果上恰好也是 0但语义上不如写 ELSE 0 清楚。刚开始用的时候建议把 ELSE 显式写出来读 SQL 的人一眼能懂。这种行转列在 Java 报表项目里是刚需能少写一大票 for 循环。如果还要按时间维度再分组就在 GROUP BY 里同时加 DATE_FORMAT(create_time, %Y-%m-%d)一天一行每行里同时有买卖两个金额列前端画折线图表格都舒服。5. 聚合函数与分组统计报表需求的最后一公里5.1 COUNT(*) 不等于 COUNT(列)NULL 语义决定了结果COUNT() 统计的是行数COUNT(1) 效果一样COUNT(列) 统计的是该列非 NULL 值的个数。很多人写 COUNT(1) 以为比 COUNT() 快其实 MySQL 官方早就优化过两者性能没有实质差别真正有差别的是 COUNT(列) 和 COUNT(*) 的语义。举一个实际踩过的坑统计订单支付成功人数表里有一个 pay_time 字段失败订单该字段为 NULL。如果用 COUNT(pay_time)它会自动忽略 NULL正好能统计出成功订单数但如果业务上要求“所有下单人数”和“支付成功人数”两个指标就必须写 COUNT(*) 和 COUNT(pay_time)两个结果一比失败人数就出来了。只写其中一个报表就会少一堆数。SUM 和 AVG 同样忽略 NULL。AVG 常见误解AVG(salary) 如果表里有 10 个人其中 2 人 salary 为 NULLAVG 只对 8 个非 NULL 求平均不是对 10 个人求平均。想让 NULL 参与计算先 COALESCE(salary, 0) 再 AVG否则统计口径会跟 Java 端手工算的完全不一致。这类“口径差异”最容易在面试里被问到实际项目里更是报表对不上的根源。5.2 GROUP BY 与 HAVING分组统计的正确姿势GROUP BY 会把相同分组列的行合并成一行SELECT 里能出现的列只有两类GROUP BY 里写过的列或者包在聚合函数里的列。如果 SELECT 里出现第三方列在 MySQL 5.7 以上且开启 ONLY_FULL_GROUP_BY 时会直接报错this is incompatible with sql_modeonly_full_group_by。遇到这个报错正确的改法是把 SELECT 里的非聚合列也加到 GROUP BY 里或者用 ANY_VALUE(column) 包一层取任意值也可以直接用 MIN(column) 或 MAX(column) 让语义更明确。比如查每个用户的订单数需求还要带上用户手机号简洁写法是 GROUP BY user_id, phone如果 phone 在同 user_id 下不唯一就得想清楚要显示哪一个不能稀里糊涂 ANY_VALUE。HAVING 是分组后的过滤WHERE 是分组前的过滤。统计下单超过 3 次的用户不能写 WHERE COUNT() 3必须 HAVING COUNT() 3。性能上优先用 WHERE 把大数据量提前过滤掉分组后的结果越小HAVING 压力越小。SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM trade_order WHERE status PAID GROUP BY user_id HAVING COUNT(*) 3 AND SUM(amount) 1000;5.3 GROUP_CONCAT把多行合并成一行返回给 JavaGROUP_CONCAT 是我认为 Java 开发最该掌握的聚合函数之一。它能把同一个分组下的多行某个字段拼成一个字符串省掉在 Java 里循环拼接列表的代码。最常见的业务一个用户有多个收货标签存在子表里查询时要展示成“标签A,标签B,标签C”用一条子查询加 GROUP_CONCAT 就搞定。语法支持 DISTINCT、内部 ORDER BY 和自定义分隔符SELECT user_id, GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ,) AS tag_list FROM user_tag GROUP BY user_id;使用时有三个坑。第一个结果长度默认受 group_concat_max_len 限制默认值是 1024 字节标签一多就会被截断需要在会话里 SET SESSION group_concat_max_len 1024 * 1024或写入数据库配置。第二个GROUP_CONCAT 会忽略 NULL 值如果想让 NULL 显示成占位符需要先用 COALESCE 转一下。第三个拼出来的字符串在 Java 端接收后要按分隔符 split如果标签本身包含分隔符建议 SEPARATOR 用一个不太可能出现的符号比如 \u0001或者干脆在 Java 端用 JSON 序列化。6. 函数与 Java 代码实战结合从 SQL 到 JDBC 的完整落点6.1 PreparedStatement函数参数这块也要绑定参数MySQL 函数本身不是 SQL 注入的重点函数里的参数如果由 Java 字符串拼接进 SQL才是注入点。比如想按“姓名和手机号”做联合模糊搜索有人会把 SQL 拼成 name LIKE % keyword %这种写法只要 keyword 里带一个单引号就能破坏语句结构。正确姿势是把查询关键词作为参数绑定让 SQL 保持模板。LIKE 可以写成WHERE CONCAT(name, phone) LIKE CONCAT(%, ?, %)Java 侧用 PreparedStatement 的 setString 传参数。这里的 CONCAT(%, ?, %) 相当于把通配符也参数化构造既保证索引可用性也避免注入风险。模糊搜索时要注意 CONCAT 拼接字段会把 NULL 变 NULL所以 name 和 phone 也要先经 IFNULL 处理。String sql SELECT id, name, phone FROM user WHERE CONCAT(IFNULL(name, ), IFNULL(phone, )) LIKE CONCAT(%, ?, %); try (PreparedStatement ps connection.prepareStatement(sql)) { ps.setString(1, keyword); try (ResultSet rs ps.executeQuery()) { // 处理结果 } }杀手锏是永远不要自己用字符串拼 SQL。我在代码审查里看到过太多次把用户输入直接放进 SQL 的事故很多还是老系统遗留。不管函数多复杂先写模板再绑参数这条底线不能破。6.2 动态排序字段不能直接用 ? 绑定时的白名单方案ORDER BY 后面的列名和函数名无法用 PreparedStatement 的 ? 占位符绑定这是 JDBC 规范的限制。很多系统允许用户点击表头排序传一个 sortField 和 sortOrder后端如果直接把这两个参数拼进 ORDER BY就是一个天然注入点。解决方案很简单但对安全意识要求很高排序字段走白名单映射服务端预先定义允许排序的列名集合用户传进来的字段必须先经过校验能匹配才拼接不能匹配就拒绝或使用默认排序。白名单里可以包含特定函数比如 LENGTH(name)但必须由后端定义而不是用户传过来。private static final MapString, String SORT_COLUMNS Map.of( createTime, create_time, amount, amount, userNameLen, LENGTH(username) ); public String buildSort(String sortField) { String column SORT_COLUMNS.get(sortField); if (column null) { throw new IllegalArgumentException(非法排序字段); } return column; }排序方向和排序列一样也必须只有两个候选值 DESC 或 ASC。这个方案写起来不复杂却能堵住最常见的“order by 注入”我建议所有涉及动态排序的接口都统一这么处理。6.3 存储过程、自定义函数与函数索引的实际取舍MySQL 自定义函数和存储过程在项目里有争议。好处是业务逻辑贴近数据跨应用复用方便坏处是难调试、难迁移、难做数据库扩容。我个人的原则不反对但少用。批量数据处理、复杂报表统计如果必须用也要把逻辑写得足够简单并且只用来做计算不要把外部服务调用、消息发送这种活塞进数据库。创建存储过程时必须处理 DELIMITER因为 sql 结尾默认分号会让客户端提前结束。存储过程内部声明变量用 DECLARE赋值用 SET 或者 SELECT INTO循环用 LOOP 或 WHILE。另一个常见坑是开启 binlog 后创建函数会报错 1418日志里提示 log_bin_trust_function_creators需要数据库管理员评估后开启对应参数否则只能找有 SUPER 权限的账号创建。MySQL 8.0 开始支持函数索引这是解决“函数导致索引失效”的最佳手段。原来 WHERE LOWER(username) abc 用不上 username 索引现在可以 CREATE INDEX idx_username_lower ON user ((LOWER(username)))查询就能走这个索引。这个特性很实用但它会增加写入开销只建议在高频查询的列上建不能为了每个函数都建一个。7. 常见问题与排查技巧实录7.1 常见问题速查表先从现象定位到原因这些年帮同事排查过的 SQL 问题有大半都能归到下面这张表里。遇到问题时先对照定位再看具体数据能省很多时间。现象可能原因推荐排查与解决查出来的时间比预期少 8 小时JDBC 连接未指定时区连接串加 serverTimezoneAsia/Shanghai日期过滤后查询非常慢对索引列用了 DATE() 等函数改成范围条件CONCAT 拼接结果出现 NULL拼接字段存在 NULL用 CONCAT_WS 或 IFNULL 兜底COUNT 结果与报表对不上COUNT(列) 忽略了 NULL明确口径用 COUNT(*) 或 COUNT(非空列)MOD 结果和 Java 计算不一致负数取模规则不同统一转正数后取模或统一用 MODGROUP BY 报 only_full_group_bySELECT 了非聚合列改为聚合函数或加入 GROUP BY创建自定义函数报 1418/1419binlog 与安全限制检查 log_bin_trust_function_creators中文、表情符号统计长度不准用 LENGTH 而非 CHAR_LENGTH改用 CHAR_LENGTH模糊搜索越来越慢LIKE 前置通配符评估全文检索或搜索引擎方案这张表里最频繁出现的就是“索引列被函数包裹”和“NULL 语义没理清”两类基本能覆盖 80% 的 SQL 疑难杂症。7.2 一个日期慢查询的真实排查过程线上订单统计接口每天凌晨跑一次逻辑是统计当天已支付订单数。SQL 长这样SELECT COUNT(*) FROM trade_order WHERE DATE(create_time) CURDATE() AND status PAID;查数据量只有几十万但执行时间到了四五秒。EXPLAIN 一看type 是 ALL说明 create_time 索引没走。原因就是 DATE(create_time) 把索引列包进了函数优化器无法直接使用索引。改成范围条件后不到 0.01 秒SELECT COUNT(*) FROM trade_order WHERE create_time CONCAT(CURDATE(), 00:00:00) AND create_time CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), 00:00:00) AND status PAID;这个案例几乎是我遇到过的所有“日期慢查询”的标准答案排查流程就是三步先 EXPLAIN 看 type发现 ALL 了再检查 WHERE 里的列有没有被函数包裹最后改成范围查询。养成这个习惯后很多性能问题在写 SQL 的阶段就能当场避免。7.3 容易被忽略的隐式类型转换和字符集问题隐式类型转换是个隐蔽杀手。id 列是整型查询写 WHERE id 1 时 MySQL 会把字符串转数值通常没大毛病但函数参数里如果混入不同类型优化器可能放弃索引。比如 WHERE UNIX_TIMESTAMP(create_time) 1700000000UNIX_TIMESTAMP 本身也是函数索引照样失效这个问题用范围查询同样能解决。字符集方面表字段、连接参数、JDBC URL 三处字符集如果不统一中文存进去变乱码LENGTH 统计也会错。我建议统一为 utf8mb4因为 utf8 在 MySQL 里最多支持 3 字节emoji 表情就需要 4 字节用 utf8mb4 才能完整支持。JDBC URL 里加 characterEncodingutf8mb4 或 characterEncodingutf8现在新版连接器对 utf8mb4 默认支持也比较好但表结构里要确认。排查字符集问题时不要只看 Java 代码先用客户端连数据库查一遍原始值确认库里到底存的什么。很多“乱码”问题最终发现是连接串漏了 characterEncoding而不是代码转码有问题。最后分享一个我检查 SQL 的习惯写完一条 SQL先自己在脑子里过一遍——查出来的字段类型是什么哪个地方可能有 NULLWHERE 里的条件会不会让索引失效。这个顺序帮我处理了大量慢查询和口径不一致问题。MySQL 函数本身不难难的是组合用法和养成习惯。希望你下次写 SQL 的时候能下意识想起函数的这张地图而不是再打开搜索引擎现查现看。 
长河君 · 2026/9/26 5:54:44 · 阅读 1.2k Java开发必知：MySQL函数高频用法与避坑指南
做 Java 开发这几年我有个特别真切的感受框架可以一个接一个地学但 MySQL 函数这种东西真的是用到哪查到哪每次查完就忘换个场景又得重新翻。这段时间我决定把 Java 这条老路重走一遍第二篇就落在 MySQL 函数上。这篇内容不是把官方手册抄一遍而是站在 Java 开发者的角度把字符串函数、数值函数、日期时间函数、流程控制函数、聚合函数这几大类按真实业务里出现频率最高的用法重新整理一遍每一步都配业务案例和踩坑记录。如果你符合下面三种情况这篇会很对胃口刚学完 Java 语法准备接项目但一到 SQL 就卡壳的初学者工作一两年业务代码能写但 SQL 只会查单表碰到统计报表就头疼的初中级开发把 MySQL 函数当面试八股文背过却说不清“什么时候该用 IF 什么时候该用 CASE”的求职党。MySQL 函数不难难的是建立一张“什么场景用什么函数”的地图这篇就是帮你把地图画出来。1. 搞清 MySQL 函数的地图Java 开发才不会被 SQL 卡住1.1 为什么要把 MySQL 函数从手册里拎出来我见过不少人写 Java 业务习惯把所有数据从数据库捞出来再在 Java 里做循环统计、拼接字符串、判断状态。比如统计用户年龄段分布先 SELECT * 查出全部用户再在 service 层 for 循环数一遍。这种写法在数据量小的时候没问题一旦数据量上来JVM 内存扛不住网络传输也浪费而一条带函数的聚合 SQL 就能解决。反过来我也见过 SQL 水平不错但 Java 代码稀烂的人把大量正则、日期解析、字符串处理逻辑写在数据库函数里结果数据库 CPU 被打满应用层还很难排查。正确的心态是把 MySQL 函数当成工具能用索引解决的优先用索引能用 SQL 表达的统计语义就交给 SQL但绝不能滥用。MySQL 函数本身很简单它难在三个地方函数的返回类型容易记混、函数的 NULL 传播往往违反直觉、函数套在索引列上会导致索引失效。这三个问题我会在每个分类里反复提到因为它们在真实开发中造成的 Bug 占比非常高。1.2 函数分类总览一张表建立全局认知我第一次系统梳理 MySQL 函数是照着官方文档一条条看的结果看完全忘了。后来换了个思路按“Java 开发里什么需求最常用”把函数分成五组瞬间清晰很多。函数类别代表性函数Java 开发中最常见的场景字符串函数CONCAT、SUBSTRING、REPLACE、TRIM、LPAD拼接查询条件、拆分字段、数据清洗、编号补零数值函数ROUND、ABS、MOD、RAND、FLOOR金额四舍五入、分表路由取模、随机抽样日期时间函数NOW、DATE_FORMAT、DATEDIFF、DATE_ADD统计今日数据、计算用户年龄、超时判断流程控制函数IF、CASE WHEN、COALESCE、IFNULL状态值翻译、多条件分组统计、NULL 兜底聚合函数COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT报表统计、分组求和、列表标签拼接这张表不用背多写几次自然就熟了。关键是记住函数之间经常组合出现并不是非此即彼。比如一条订单统计 SQL可能同时用到 DATE_FORMAT 做按天格式化、CASE WHEN 做状态分类、SUM 做金额汇总、GROUP_CONCAT 做订单号拼接单看每个函数都不复杂组合起来才能真正解决业务问题。1.3 学函数前先记住三件事第一件事先想清楚函数的返回类型。字符串函数返回字符串数值函数返回数值日期函数返回日期或字符串类型错了后面的运算就会出问题。比如 FORMAT(12345.678, 2) 返回的是字符串 12,345.68如果你拿这个结果去加一个数MySQL 会把字符串转成数值逗号被去掉后变成 12345.68可能没问题但在 Java 里你拿到的就是一个带逗号的字符串前端展示和后续 BigDecimal 转换都可能踩坑。第二件事极度重视 NULL 传播。CONCAT(NULL, a) 不会返回 a而是 NULLIF(NULL, 1, 2) 返回 2SUM 聚合会忽略 NULL。这些规则长期被忽略但只要有一个字段被允许为 NULL统计结果就可能错得离谱。第三件事警惕函数套在索引列上。WHERE DATE(create_time) 2024-01-01 这种写法即使 create_time 建了索引MySQL 也无法使用必须改成范围条件。这个会在后面的排查章节展开细说但请先把它刻在脑子里对列做任何“加工”都可能让索引报废。2. 字符串与数值函数写业务绕不开的那批高频用法2.1 字符串函数拼接、截取、替换与补位CONCAT 是最基础的拼接函数坑在于只要有一个参数是 NULL结果就是 NULL。你写 CONCAT(username, -, phone)只要 phone 是 NULL整个字段查出来就是 NULL。这时候要用 CONCAT_WS它的好处是会自动跳过 NULL还能指定分隔符比如 CONCAT_WS(-, username, phone, email) 会把 NULL 值跳过而不是让整条结果变 NULL这在导出报表时非常实用。SUBSTRING 做截取时要注意位置从 1 开始不是从 0 开始。SUBSTRING(username, 2, 3) 取的是第 2 位开始的 3 个字符和 Java 的 substring(1, 4) 语义完全不同写习惯 Java 后特别容易在这翻车。SUBSTRING_INDEX 是按分隔符取段比如 SUBSTRING_INDEX(北京市/朝阳区/望京, /, 2) 返回 北京市/朝阳区想取最后一段就给负数SUBSTRING_INDEX(字段, /, -1) 返回 望京。处理层级地址、JSON 字段里的某个值时特别好用。REPLACE 做全局替换TRIM 去首尾空格LTRIM 和 RTRIM 分别去左、右空格。这里有个经验用户提交手机号或者身份证号时经常带空格和横线入库前用 REPLACE(REPLACE(phone, -, ), , ) 先清洗一遍比在 Java 层写一堆 replace 链更直接。补位函数 LPAD 和 RPAD 很常用比如工号统一补零LPAD(user_id, 8, 0) 能把 123 变成 00000123。还有一个容易忽视的细节LENGTH 返回字节数CHAR_LENGTH 返回字符数。在 utf8mb4 字符集下LENGTH(中文) 返回 6CHAR_LENGTH(中文) 返回 2。统计用户昵称长度、做输入校验时一定用 CHAR_LENGTH我见过有人用 LENGTH 校验昵称表情符号直接被算成 4 个字节导致长度判断完全不准。-- 字符串函数组合示例清洗手机号并补全展示格式 SELECT CONCAT_WS( | , username, LPAD(REPLACE(REPLACE(phone, -, ), , ), 11, 0)) FROM user WHERE CHAR_LENGTH(username) BETWEEN 2 AND 20;2.2 数值函数取整、取模、随机与格式化ROUND 做四舍五入TRUNCATE 直接截断CEIL 向上取整FLOOR 向下取整。这四个的差异在金额计算里非常敏感订单优惠金额如果按 ROUND 和按 TRUNCATE 分别计算最后的对账单可能差几分钱。Java 端如果用了 BigDecimalMySQL 端也建议统一用 ROUND 或 TRUNCATE 且明确小数点位数避免两边结果不一致。ABS 取绝对值RAND 取随机数。RAND() 配合 ORDER BY 可以做随机抽样但数据量大时 ORDER BY RAND() 性能极差因为它会对整个结果集做随机排序。通常的做法是先 COUNT 拿到行数在 Java 层算出随机偏移量再用 LIMIT offset, 1这样能避免全表排序。MOD 取模在分表路由里最常用。但注意MySQL 的 MOD 和 Java 的 % 在负数场景下结果不一致。MySQL 中 MOD(-7, 3) 返回 2而 Java 中 (-7) % 3 返回 -1。如果你在 Java 里按 MOD 的结果做分表又在 SQL 里用 MOD 做路由两边算出的表号可能不一样数据就读不到了。处理办法是统一要么两边都转成正数取模要么规定 MOD 语义并在代码里加上注释。FORMAT 可以把数字格式化为千分位字符串比如 FORMAT(1234567.891, 2) 返回 1,234,567.89。这个函数适合做报表展示但注意返回的是字符串不再是数值后续还有计算就必须先 CAST。另外Java 端如果用了 DecimalFormat两边格式也可能有差异建议展示层统一在 Java 做。-- 分表路由一致性示例Java 和 MySQL 都使用 MOD 并确认负数处理 SELECT MOD(CAST(user_id AS SIGNED), 4) AS table_no FROM user WHERE id 1001;2.3 组合实例用函数做一次数据清洗假设订单表里有“收货地址”字段格式是“省/市/区/详细地址”但历史数据里有的用“-”分隔有的带空格还有一些为 NULL。现在要统计每个省的订单数直接 GROUP BY 省份是做不到的。可以用 SUBSTRING_INDEX 先按“/”取第一段再用 REPLACE 把“-”和空格统一替换掉最后用 COALESCE 把 NULL 处理成“未知省份”。SELECT COALESCE(SUBSTRING_INDEX(REPLACE(REPLACE(address, -, /), , ), /, 1), 未知省份) AS province, COUNT(*) AS order_cnt FROM trade_order GROUP BY province ORDER BY order_cnt DESC;这个场景特别典型不是单个函数解决问题而是用“REPLACE 规范化 SUBSTRING_INDEX 提取 COALESCE 兜底 GROUP BY 聚合”一串组合拳。我在实际项目里处理过大量类似的数据质量问题经验是清洗逻辑最好在 SQL 层完成因为数据源头就是脏的Java 每读一次就洗一次会被动但清洗后如果要作为长期维度更建议做一次数据订正写入新字段而不是每条查询都现场算因为函数运算在 WHERE 和 GROUP BY 上都会影响性能。3. 日期时间函数Java 和 MySQL 的时区与格式地狱3.1 DATE_FORMAT 与 STR_TO_DATE别把格式串写反DATE_FORMAT 是把日期转成指定格式的字符串STR_TO_DATE 是反着来把字符串解析成日期。格式串里的占位符是 MySQL 特有的最常见的几个%Y 四位年%y 两位年%m 两位月%d 两位日%H 24 小时制小时%i 分钟%s 秒。对应到 Java 的 SimpleDateFormat 就是 yyyy、MM、dd、HH、mm、ss。这里有个坑Java 里分钟是 mm月份是 MM字母大小写含义完全相反MySQL 里月份是 %m分钟是 %i没有大小写之分但 %m 和 %i 必须区分。我见过有人把 STR_TO_DATE(2024-01-15 13:45:30, %Y-%m-%d %H:%i:%s) 写错成 %M结果解析出来月份变成了带字母的 January直接报错。这类问题不会出现在编译期而是运行期才爆所以写代码时建议固定模板-- 推荐固定格式模板Java 侧用 DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss) SELECT STR_TO_DATE(2024-01-15 13:45:30, %Y-%m-%d %H:%i:%s); SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s);DATE_FORMAT 在报表场景非常常用按天、按小时、按月分组统计时直接把 datetime 列格式化成对应粒度再 GROUP BY。但要注意对 datetime 列做 DATE_FORMAT 后原来的索引基本用不上如果是大表高频分组统计宁可提前冗余一个 date 类型的字段并加索引也不要每天现场 DATE_FORMAT。3.2 DATEDIFF 与 TIMESTAMPDIFF计算间隔时的顺序陷阱DATEDIFF(date1, date2) 返回 date1 减 date2 的天数结果是前减后。TIMESTAMPDIFF(unit, start, end) 返回 end 减 start 的值结果也是结束减开始但参数顺序看着像“开始在前结束在后”非常容易写反。我举个例子计算两个日期间隔天数用 DATEDIFF(2024-02-01, 2024-01-01) 得到 31TIMESTAMPDIFF(DAY, 2024-01-01, 2024-02-01) 也得到 31。区别在于 TIMESTAMPDIFF 前面需要指定单位支持 HOUR、DAY、WEEK、MONTH、YEAR 等还支持 SECOUND 的拼写容易错实际是 SECOND。实际开发中判断一个订单是否超时 24 小时如果用 TIMESTAMPDIFF(HOUR, create_time, NOW()) 24这个顺序是结束时间减去开始时间正确。如果写反结果永远是负数永远超时。我的习惯是在代码里统一注释第一个参数是单位第二个是起始时间第三个是结束时间。DATE_ADD 和 DATE_SUB 是往日期上加或减时间。DATE_ADD(NOW(), INTERVAL 7 DAY) 表示一周后。它们更适合用来构造范围查询比如查询最近 7 天订单SELECT COUNT(*) FROM trade_order WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY);这种写法对 create_time 索引友好因为函数只作用在常量上索引列本身没有被包裹。3.3 时区问题Java 侧少 8 小时的真相很多 Java 开发都遇到过数据库存的时间明明是对的用 JDBC 查出来却少了 8 小时或者反过来。原因几乎都是连接串没指定时区MySQL Connector/J 默认用了服务器时区而服务器时区与 JVM 时区不一致转换时出了问题。解决办法很直接JDBC URL 里加上 serverTimezoneAsia/Shanghai同时数据库、JVM、连接三处统一时区。另外要注意 MySQL 里 DATETIME 和 TIMESTAMP 两个类型的区别DATETIME 不做时区转换存什么取什么TIMESTAMP 在写入时从会话时区转成 UTC读取时再转回会话时区。TIMESTAMP 范围只有到 2038 年DATETIME 范围更大现在多数项目默认用 DATETIME我建议你也沿用这个约定。Java 侧接收日期字段时优先用 java.time.LocalDateTime通过 rs.getObject(create_time, LocalDateTime.class) 直接映射不要用 rs.getString 拿到字符串再做 SimpleDateFormat 解析一个是慢另一个是隐式依赖数据库返回的格式。如果必须格式化展示在 SQL 里用 DATE_FORMAT 或者在 Java 里用 DateTimeFormatter 都行但要固定一处别两边都做。# JDBC URL 固定写法避免 8 小时时区错乱 jdbc:mysql://localhost:3306/mydb?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/Shanghai4. 条件判断与流程控制在 SQL 里写 if 和 case4.1 IF、IFNULL、NULLIF、COALESCE选哪个更合适IF(expr, a, b) 类似 Java 三元表达式expr 为真返回 a否则返回 b。适合简单二选一比如 IF(amount 0, 已支付, 未支付)。不过 IF 的结果可以嵌套嵌套两层以上可读性就很差建议改用 CASE。IFNULL(a, b) 是专用 NULL 兜底如果 a 是 NULL 返回 b否则返回 a。这个是开发最高频的统计金额、拼接字段、排序都常用。注意 IFNULL 只处理两个参数多个字段兜底要嵌套比如 IFNULL(bonus, IFNULL(other, 0))这种嵌套链可读性也差不如改用 COALESCE。NULLIF(a, b) 语义是a 等于 b 时返回 NULL否则返回 a。它常用来排除特定值参与聚合。比如统计平均薪资时不想把某个测试员工的 10000 算进去可以用 AVG(NULLIF(salary, 10000))等价于把该值置 NULL 后让 AVG 忽略。这个函数知道的人不多但用对场合会很爽。COALESCE(a, b, c, ...) 返回参数列表中第一个非 NULL 的值。比 IFNULL 更适合多字段兜底比如展示电话优先手机号其次座机都没有显示“未填写”直接 COALESCE(mobile, landline, 未填写)。它在 Java 生态里对应的概念是 null 合并比一堆 if 判断链简洁得多。-- 典型应用订单列表中优惠金额为 NULL 时按 0 展示并参与排序 SELECT order_no, COALESCE(discount_amount, 0) AS discount_amount FROM trade_order ORDER BY COALESCE(discount_amount, 0) DESC;4.2 CASE WHEN 的两种写法CASE WHEN 有两种写法。简单表达式是 CASE 列 WHEN 值 THEN 结果适用于等值判断搜索表达式是 CASE 当满足条件 THEN 结果适用于范围判断、多条件复合。简单写法SELECT CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 ELSE 未知 END AS status_text FROM trade_order;搜索写法SELECT CASE WHEN amount 1000 THEN 大额订单 WHEN amount 100 THEN 普通订单 ELSE 小额订单 END AS order_level FROM trade_order;两种写法都返回表达式结果可以用在 SELECT 输出、WHERE 过滤、ORDER BY 排序、GROUP BY 分组。我建议等值翻译用简单写法范围判断用搜索写法不要混着写。CASE 表达式返回的列别名在 Java 的 mapper 映射时要成对写好比如 status_text 映射为 statusText不然 MyBatis 自动映射又可能对不上驼峰。搜索 CASE 里还可以放聚合判断比如 HAVING 里写 HAVING SUM(CASE WHEN typeBUY THEN amount ELSE 0 END) 1000这个结合场景非常多。4.3 行转列CASE 与聚合函数组合的唯一真解报表开发里最经典的“行转列”本质就是 CASE 配聚合函数。假设交易流水表里有 user_id、trade_type、amount分别是 BUY 和 SELL要统计每个用户买入总金额和卖出总金额一条 SQL 搞定SELECT user_id, SUM(CASE WHEN trade_type BUY THEN amount ELSE 0 END) AS buy_amount, SUM(CASE WHEN trade_type SELL THEN amount ELSE 0 END) AS sell_amount FROM trade_flow GROUP BY user_id;这个写法的要点CASE 在聚合之前把每一行分类SUM 再对分类后的结果求和。ELSE 0 很关键如果不写 ELSE不满足条件的行返回 NULL而 SUM 会忽略 NULL效果上恰好也是 0但语义上不如写 ELSE 0 清楚。刚开始用的时候建议把 ELSE 显式写出来读 SQL 的人一眼能懂。这种行转列在 Java 报表项目里是刚需能少写一大票 for 循环。如果还要按时间维度再分组就在 GROUP BY 里同时加 DATE_FORMAT(create_time, %Y-%m-%d)一天一行每行里同时有买卖两个金额列前端画折线图表格都舒服。5. 聚合函数与分组统计报表需求的最后一公里5.1 COUNT(*) 不等于 COUNT(列)NULL 语义决定了结果COUNT() 统计的是行数COUNT(1) 效果一样COUNT(列) 统计的是该列非 NULL 值的个数。很多人写 COUNT(1) 以为比 COUNT() 快其实 MySQL 官方早就优化过两者性能没有实质差别真正有差别的是 COUNT(列) 和 COUNT(*) 的语义。举一个实际踩过的坑统计订单支付成功人数表里有一个 pay_time 字段失败订单该字段为 NULL。如果用 COUNT(pay_time)它会自动忽略 NULL正好能统计出成功订单数但如果业务上要求“所有下单人数”和“支付成功人数”两个指标就必须写 COUNT(*) 和 COUNT(pay_time)两个结果一比失败人数就出来了。只写其中一个报表就会少一堆数。SUM 和 AVG 同样忽略 NULL。AVG 常见误解AVG(salary) 如果表里有 10 个人其中 2 人 salary 为 NULLAVG 只对 8 个非 NULL 求平均不是对 10 个人求平均。想让 NULL 参与计算先 COALESCE(salary, 0) 再 AVG否则统计口径会跟 Java 端手工算的完全不一致。这类“口径差异”最容易在面试里被问到实际项目里更是报表对不上的根源。5.2 GROUP BY 与 HAVING分组统计的正确姿势GROUP BY 会把相同分组列的行合并成一行SELECT 里能出现的列只有两类GROUP BY 里写过的列或者包在聚合函数里的列。如果 SELECT 里出现第三方列在 MySQL 5.7 以上且开启 ONLY_FULL_GROUP_BY 时会直接报错this is incompatible with sql_modeonly_full_group_by。遇到这个报错正确的改法是把 SELECT 里的非聚合列也加到 GROUP BY 里或者用 ANY_VALUE(column) 包一层取任意值也可以直接用 MIN(column) 或 MAX(column) 让语义更明确。比如查每个用户的订单数需求还要带上用户手机号简洁写法是 GROUP BY user_id, phone如果 phone 在同 user_id 下不唯一就得想清楚要显示哪一个不能稀里糊涂 ANY_VALUE。HAVING 是分组后的过滤WHERE 是分组前的过滤。统计下单超过 3 次的用户不能写 WHERE COUNT() 3必须 HAVING COUNT() 3。性能上优先用 WHERE 把大数据量提前过滤掉分组后的结果越小HAVING 压力越小。SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM trade_order WHERE status PAID GROUP BY user_id HAVING COUNT(*) 3 AND SUM(amount) 1000;5.3 GROUP_CONCAT把多行合并成一行返回给 JavaGROUP_CONCAT 是我认为 Java 开发最该掌握的聚合函数之一。它能把同一个分组下的多行某个字段拼成一个字符串省掉在 Java 里循环拼接列表的代码。最常见的业务一个用户有多个收货标签存在子表里查询时要展示成“标签A,标签B,标签C”用一条子查询加 GROUP_CONCAT 就搞定。语法支持 DISTINCT、内部 ORDER BY 和自定义分隔符SELECT user_id, GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ,) AS tag_list FROM user_tag GROUP BY user_id;使用时有三个坑。第一个结果长度默认受 group_concat_max_len 限制默认值是 1024 字节标签一多就会被截断需要在会话里 SET SESSION group_concat_max_len 1024 * 1024或写入数据库配置。第二个GROUP_CONCAT 会忽略 NULL 值如果想让 NULL 显示成占位符需要先用 COALESCE 转一下。第三个拼出来的字符串在 Java 端接收后要按分隔符 split如果标签本身包含分隔符建议 SEPARATOR 用一个不太可能出现的符号比如 \u0001或者干脆在 Java 端用 JSON 序列化。6. 函数与 Java 代码实战结合从 SQL 到 JDBC 的完整落点6.1 PreparedStatement函数参数这块也要绑定参数MySQL 函数本身不是 SQL 注入的重点函数里的参数如果由 Java 字符串拼接进 SQL才是注入点。比如想按“姓名和手机号”做联合模糊搜索有人会把 SQL 拼成 name LIKE % keyword %这种写法只要 keyword 里带一个单引号就能破坏语句结构。正确姿势是把查询关键词作为参数绑定让 SQL 保持模板。LIKE 可以写成WHERE CONCAT(name, phone) LIKE CONCAT(%, ?, %)Java 侧用 PreparedStatement 的 setString 传参数。这里的 CONCAT(%, ?, %) 相当于把通配符也参数化构造既保证索引可用性也避免注入风险。模糊搜索时要注意 CONCAT 拼接字段会把 NULL 变 NULL所以 name 和 phone 也要先经 IFNULL 处理。String sql SELECT id, name, phone FROM user WHERE CONCAT(IFNULL(name, ), IFNULL(phone, )) LIKE CONCAT(%, ?, %); try (PreparedStatement ps connection.prepareStatement(sql)) { ps.setString(1, keyword); try (ResultSet rs ps.executeQuery()) { // 处理结果 } }杀手锏是永远不要自己用字符串拼 SQL。我在代码审查里看到过太多次把用户输入直接放进 SQL 的事故很多还是老系统遗留。不管函数多复杂先写模板再绑参数这条底线不能破。6.2 动态排序字段不能直接用 ? 绑定时的白名单方案ORDER BY 后面的列名和函数名无法用 PreparedStatement 的 ? 占位符绑定这是 JDBC 规范的限制。很多系统允许用户点击表头排序传一个 sortField 和 sortOrder后端如果直接把这两个参数拼进 ORDER BY就是一个天然注入点。解决方案很简单但对安全意识要求很高排序字段走白名单映射服务端预先定义允许排序的列名集合用户传进来的字段必须先经过校验能匹配才拼接不能匹配就拒绝或使用默认排序。白名单里可以包含特定函数比如 LENGTH(name)但必须由后端定义而不是用户传过来。private static final MapString, String SORT_COLUMNS Map.of( createTime, create_time, amount, amount, userNameLen, LENGTH(username) ); public String buildSort(String sortField) { String column SORT_COLUMNS.get(sortField); if (column null) { throw new IllegalArgumentException(非法排序字段); } return column; }排序方向和排序列一样也必须只有两个候选值 DESC 或 ASC。这个方案写起来不复杂却能堵住最常见的“order by 注入”我建议所有涉及动态排序的接口都统一这么处理。6.3 存储过程、自定义函数与函数索引的实际取舍MySQL 自定义函数和存储过程在项目里有争议。好处是业务逻辑贴近数据跨应用复用方便坏处是难调试、难迁移、难做数据库扩容。我个人的原则不反对但少用。批量数据处理、复杂报表统计如果必须用也要把逻辑写得足够简单并且只用来做计算不要把外部服务调用、消息发送这种活塞进数据库。创建存储过程时必须处理 DELIMITER因为 sql 结尾默认分号会让客户端提前结束。存储过程内部声明变量用 DECLARE赋值用 SET 或者 SELECT INTO循环用 LOOP 或 WHILE。另一个常见坑是开启 binlog 后创建函数会报错 1418日志里提示 log_bin_trust_function_creators需要数据库管理员评估后开启对应参数否则只能找有 SUPER 权限的账号创建。MySQL 8.0 开始支持函数索引这是解决“函数导致索引失效”的最佳手段。原来 WHERE LOWER(username) abc 用不上 username 索引现在可以 CREATE INDEX idx_username_lower ON user ((LOWER(username)))查询就能走这个索引。这个特性很实用但它会增加写入开销只建议在高频查询的列上建不能为了每个函数都建一个。7. 常见问题与排查技巧实录7.1 常见问题速查表先从现象定位到原因这些年帮同事排查过的 SQL 问题有大半都能归到下面这张表里。遇到问题时先对照定位再看具体数据能省很多时间。现象可能原因推荐排查与解决查出来的时间比预期少 8 小时JDBC 连接未指定时区连接串加 serverTimezoneAsia/Shanghai日期过滤后查询非常慢对索引列用了 DATE() 等函数改成范围条件CONCAT 拼接结果出现 NULL拼接字段存在 NULL用 CONCAT_WS 或 IFNULL 兜底COUNT 结果与报表对不上COUNT(列) 忽略了 NULL明确口径用 COUNT(*) 或 COUNT(非空列)MOD 结果和 Java 计算不一致负数取模规则不同统一转正数后取模或统一用 MODGROUP BY 报 only_full_group_bySELECT 了非聚合列改为聚合函数或加入 GROUP BY创建自定义函数报 1418/1419binlog 与安全限制检查 log_bin_trust_function_creators中文、表情符号统计长度不准用 LENGTH 而非 CHAR_LENGTH改用 CHAR_LENGTH模糊搜索越来越慢LIKE 前置通配符评估全文检索或搜索引擎方案这张表里最频繁出现的就是“索引列被函数包裹”和“NULL 语义没理清”两类基本能覆盖 80% 的 SQL 疑难杂症。7.2 一个日期慢查询的真实排查过程线上订单统计接口每天凌晨跑一次逻辑是统计当天已支付订单数。SQL 长这样SELECT COUNT(*) FROM trade_order WHERE DATE(create_time) CURDATE() AND status PAID;查数据量只有几十万但执行时间到了四五秒。EXPLAIN 一看type 是 ALL说明 create_time 索引没走。原因就是 DATE(create_time) 把索引列包进了函数优化器无法直接使用索引。改成范围条件后不到 0.01 秒SELECT COUNT(*) FROM trade_order WHERE create_time CONCAT(CURDATE(), 00:00:00) AND create_time CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), 00:00:00) AND status PAID;这个案例几乎是我遇到过的所有“日期慢查询”的标准答案排查流程就是三步先 EXPLAIN 看 type发现 ALL 了再检查 WHERE 里的列有没有被函数包裹最后改成范围查询。养成这个习惯后很多性能问题在写 SQL 的阶段就能当场避免。7.3 容易被忽略的隐式类型转换和字符集问题隐式类型转换是个隐蔽杀手。id 列是整型查询写 WHERE id 1 时 MySQL 会把字符串转数值通常没大毛病但函数参数里如果混入不同类型优化器可能放弃索引。比如 WHERE UNIX_TIMESTAMP(create_time) 1700000000UNIX_TIMESTAMP 本身也是函数索引照样失效这个问题用范围查询同样能解决。字符集方面表字段、连接参数、JDBC URL 三处字符集如果不统一中文存进去变乱码LENGTH 统计也会错。我建议统一为 utf8mb4因为 utf8 在 MySQL 里最多支持 3 字节emoji 表情就需要 4 字节用 utf8mb4 才能完整支持。JDBC URL 里加 characterEncodingutf8mb4 或 characterEncodingutf8现在新版连接器对 utf8mb4 默认支持也比较好但表结构里要确认。排查字符集问题时不要只看 Java 代码先用客户端连数据库查一遍原始值确认库里到底存的什么。很多“乱码”问题最终发现是连接串漏了 characterEncoding而不是代码转码有问题。最后分享一个我检查 SQL 的习惯写完一条 SQL先自己在脑子里过一遍——查出来的字段类型是什么哪个地方可能有 NULLWHERE 里的条件会不会让索引失效。这个顺序帮我处理了大量慢查询和口径不一致问题。MySQL 函数本身不难难的是组合用法和养成习惯。希望你下次写 SQL 的时候能下意识想起函数的这张地图而不是再打开搜索引擎现查现看。