从BadSqlGrammarException到SQL优化——MyBatis动态SQL避坑指南

共计 2058 个字符，预计需要花费 6 分钟才能阅读完成。

问题现场：突如其来的 SQL 语法错误

在开发医疗管理系统时，我需要实现一个公告查询接口。测试阶段突然抛出异常：

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: 
You have an error in your SQL syntax near 'LIMIT 10' at line 11

查看 MyBatis 生成的 SQL 显示异常位置：

ORDER BY a.publish_time DESC;  LIMIT ?

这个简单的问题引发了我对 MyBatis 动态 SQL 和 SQL 规范的思考。

问题解剖：意想不到的隐藏陷阱

陷阱 1：” 多余的 ” 分号
原始的 XML 映射文件包含这样的内容：

ORDER BY a.publish_time DESC;

这里的分号在 SQL 语法层面是正确的，但在动态 SQL 处理时却成为致命的 ” 拦路虎 ”。这是因为：

1. MyBatis 自动拼接 SQL 语句时，后续可能添加分页参数
2. 不同数据库对分号的容忍度不同（如 MySQL 允许结尾分号，但 Oracle 不允许）
3. 分页插件处理时会发生预期外的语句分隔

陷阱 2：无形的条件拼接
原始代码使用自由条件拼接：

WHERE
<choose>...</choose>
<if>...</if>

这可能导致两种异常情况：

• 空 WHERE 条件（当所有条件不满足时）
• 非法条件开头（如出现 AND 开头）

陷阱 3：分页参数传递
调试日志显示 LIMIT 参数丢失：

LIMIT ?

但实际上调用时已经传递了分页参数。这说明参数处理器存在异常。

解决方案：逐层破解难题

第一层修复：语法规范化
改造前：

ORDER BY a.publish_time DESC;

改造后：

ORDER BY a.publish_time DESC

原理说明：
MyBatis 会动态拼接 LIMIT 子句，手动添加分号会导致语法结构破坏。根据不同数据库方言自动处理结尾符号更可靠。

第二层加固：安全条件构建
改造前：

WHERE
<choose>...</choose>
<if>...</if>

改造后：

<where>
    <choose>...</choose>
    <if>...</if>
</where>

改进效果：

• 自动过滤空条件，避免出现 WHERE 后无内容
• 智能处理 AND/OR 开头，规避非法语法
• 对 1=1 等伪条件自动优化

第三层优化：参数强化验证
添加参数校验注解：

public List<Announcement> selectByTargetRole(@Param("targetRole") @Nullable String targetRole,
    @Param("keyword") @NotBlank String keyword
) 

配置校验框架，避免空参数导致的异常拼接。

高级技巧：分页深度处理方案

PageHelper 的正确打开方式

Service 层实现示例：

public PageInfo<Announcement> queryAnnouncements(int pageNum, int pageSize) {PageHelper.startPage(pageNum, pageSize);
    List<Announcement> list = announcementMapper.selectByTargetRole(...);
    return new PageInfo<>(list);
}

Must-Have 配置项：

pagehelper:
  helper-dialect: mysql
  support-methods-arguments: true
  params: count=countSql

MariaDB 特别呵护策略

在 my.ini 添加：

[mysqld]
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

防御编程：构建 SQL 安全屏障

Debug 法宝：SQL 完整日志

启用 MyBatis 完整日志：

logging:
  level:
    org.mybatis: DEBUG

观察控制台输出的最终 SQL 形态：

SELECT a.* 
FROM sys_announcement a 
LEFT JOIN sys_user_announcement ua 
ON a.announcement_id = ua.announcement_id 
WHERE a.target_role = ? 
ORDER BY a.publish_time DESC 
LIMIT ?

静态代码检测 Hook

在 pre-commit 阶段加入 SQL 检测：

# 使用 sqlparse 检测语法
pip install sqlparse
find . -name "*.xml" | xargs python -m sqlparse --parse

认知升级：SQL 编写黄金法则

1. “ 去分号主义 ”：动态 SQL 要像「清水混凝土」一样纯粹
2. 防御性拼接：所有动态条件都用 <where> 包裹
3. 分页标准化：统一使用分页插件，禁用原生 LIMIT
4. 数据库方言：配置双保险（PageHelper+ 数据源配置）
5. 日志双重验证：同时观察控制台日志和 DB 查询日志