做 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'，而是 NULL；IF(NULL, 1, 2) 返回 2；SUM 聚合会忽略 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('中文') 返回 6，CHAR_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 端如果用了 BigDecimal，MySQL 端也建议统一用 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') 得到 31；TIMESTAMPDIFF(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 里加上 serverTimezone=Asia/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?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai4. 条件判断与流程控制：在 SQL 里写 if 和 case
4.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 type='BUY' 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 为 NULL，AVG 只对 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_mode=only_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：把多行合并成一行返回给 Java
GROUP_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 Map<String, 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 连接未指定时区 | 连接串加 serverTimezone=Asia/Shanghai |
| 日期过滤后查询非常慢 | 对索引列用了 DATE() 等函数 | 改成范围条件 |
| CONCAT 拼接结果出现 NULL | 拼接字段存在 NULL | 用 CONCAT_WS 或 IFNULL 兜底 |
| COUNT 结果与报表对不上 | COUNT(列) 忽略了 NULL | 明确口径，用 COUNT(*) 或 COUNT(非空列) |
| MOD 结果和 Java 计算不一致 | 负数取模规则不同 | 统一转正数后取模，或统一用 MOD |
| GROUP BY 报 only_full_group_by | SELECT 了非聚合列 | 改为聚合函数或加入 GROUP BY |
| 创建自定义函数报 1418/1419 | binlog 与安全限制 | 检查 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) > 1700000000，UNIX_TIMESTAMP 本身也是函数，索引照样失效，这个问题用范围查询同样能解决。
字符集方面，表字段、连接参数、JDBC URL 三处字符集如果不统一，中文存进去变乱码，LENGTH 统计也会错。我建议统一为 utf8mb4，因为 utf8 在 MySQL 里最多支持 3 字节，emoji 表情就需要 4 字节，用 utf8mb4 才能完整支持。JDBC URL 里加 characterEncoding=utf8mb4 或 characterEncoding=utf8，现在新版连接器对 utf8mb4 默认支持也比较好，但表结构里要确认。
排查字符集问题时不要只看 Java 代码，先用客户端连数据库查一遍原始值，确认库里到底存的什么。很多“乱码”问题最终发现是连接串漏了 characterEncoding，而不是代码转码有问题。
最后分享一个我检查 SQL 的习惯：写完一条 SQL，先自己在脑子里过一遍——查出来的字段类型是什么，哪个地方可能有 NULL，WHERE 里的条件会不会让索引失效。这个顺序帮我处理了大量慢查询和口径不一致问题。MySQL 函数本身不难，难的是组合用法和养成习惯。希望你下次写 SQL 的时候，能下意识想起函数的这张地图，而不是再打开搜索引擎现查现看。
