★ FEATURED ARTICLE 做后端开发这几年我发现自己跟“动态SQL”的交集几乎贯穿了整个职业生涯。刚入行时觉得它不过是MyBatis里的几个标签后来维护过一个查询接口从三五个条件一路膨胀到二十多个筛选参数的内部系统才真正明白这个话题背后的分量。动态SQL简单说就是根据运行时参数动态拼接或构造SQL语句的技术它解决的是“查询条件不确定”这类普遍需求但同时也是SQL注入、性能退化、代码腐化的高发地带。这篇文章想把我在真实项目里踩过的坑、总结的套路、以及最终沉淀下来的一套可复用的写法完整分享出来给正在跟复杂查询条件较劲的朋友一个参考。1. 动态SQL为什么容易翻车先搞清楚它解决的真实问题1.1 需求场景筛选条件永远是可选的大多数中后台系统的列表页长这样顶部一排筛选框可能有时间范围、状态下拉、关键词输入、组织机构树选择甚至还有一批高级筛选折叠在“更多条件”里。用户不会每次都把所有条件填满于是后端接口拿到的参数天然是可空的。我在某公司维护过一个内部订单查询系统最初查询接口只有3个条件订单号、状态、时间范围。后来产品不断加需求客户名称模糊搜索、金额区间、支付渠道、商品类目、发货仓库、是否异常订单……最后接口的查询参数对象里躺着18个字段底层SQL从一次性写死的语句变成了一个字符串拼接的“怪物”。这种场景不是某个行业的特例。只要是面向人的查询功能条件可选就是刚需。把可选条件用SQL写死要么查不出来条件太严要么失去筛选意义条件缺失。动态SQL因此成为几乎所有业务系统的标配。1.2 静态SQL做不到的事以及动态SQL的本质静态SQL是预先写死、结构固定的语句。对固定查询来说它性能最好、语义最清晰。但一旦条件变成可选静态SQL只有两条路为每种组合写一条SQL或者用 WHERE 11 加上一堆 AND/OR 硬凑——前一种在条件组合超过个位数时立刻爆炸2的n次方级别的排列组合没人维护得起后一种写出来基本没法看效果也等于动态。动态SQL的本质是把“条件判断”从SQL文本层面提前到代码逻辑层面。运行时先根据参数决定要不要拼某段条件再生成最终SQL。这个“先判断再拼装”的过程是理解动态SQL所有坑的钥匙。为什么易错因为你在同时处理两个维度的复杂度业务条件维度的判断逻辑和SQL语法维度的字符串拼接。任何一个维度的失误都会让最终语句出错。1.3 为什么它容易“翻车”三个典型风险点第一个风险点是字符串处理。少了空格、多了逗号、括号不配对、引号转义出错这类问题在动态拼接时极其常见。第二个风险点是条件逻辑的边界。参数到底算不算“空”空字符串算不算有值空集合应该生成什么边界没定义清楚SQL就会在边界输入下产生错误行为。第三个风险点是安全隐患。用户传入的参数一旦直接拼进SQL注入风险随之而来。这三个风险点几乎覆盖了动态SQL的所有“翻车”场景。后文会逐一展开并给出对应的规避方案。简单说动态SQL不是写出来就完事而是要在一开始就想清楚它的语义边界和运行边界。2. 四类主流动态SQL实现方案的对比与选型思考2.1 字符串拼接入门最直接也最危险很多人在职业生涯初期都用过这种写法String sql SELECT * FROM orders WHERE 11; if (StringUtils.hasText(orderNo)) { sql AND order_no orderNo ; } if (status ! null) { sql AND status status; } if (startTime ! null) { sql AND create_time startTime ; }优点只有一个直接不依赖任何框架。缺点几乎全是致命的参数一旦可控注入风险立刻拉满SQL和Java代码混在一起可读性极差条件多了以后维护者根本不敢动中间的某一段因为前后字符串拼接的状态很难追踪。我自己见过一个很离谱的版本某老项目里有个上千行的Service方法核心业务就一百多行剩下全是if判断后面挂SQL片段。那种代码不是不能运行而是运行得让人害怕。每一个新需求进来都是在脆弱的字符串上再叠一层判断直到某天一个引号错误把整个查询打成SQL语法异常。2.2 MyBatis XML动态标签Java项目的主流选择Java后端里MyBatis的XML动态SQL是事实标准。它用XML标签表达条件逻辑把SQL片段和Java业务代码分开维护select idqueryOrderPage resultTypecom.example.OrderDO SELECT id, order_no, customer_name, status, amount, create_time FROM orders where if testorderNo ! null and orderNo ! AND order_no #{orderNo} /if if teststatus ! null AND status #{status} /if if teststartTime ! null AND create_time gt; #{startTime} /if if testendTime ! null AND create_time lt; #{endTime} /if /where ORDER BY create_time DESC /select这里where标签会在没有任何条件时自动去掉WHERE关键字有条件下自动去掉第一个AND/OR配合if、choose、set、foreach等标签基本覆盖了绝大多数查询、更新、批量插入场景。参数通过#{}绑定天然走PreparedStatement参数化规避了大部分注入风险。2.3 类型安全的动态查询JPA Criteria 与 QueryDSL如果项目用的是JPA还有一条更“类型安全”的路Criteria API 或 QueryDSL。这类方案不直接操作SQL字符串而是通过Java类型方法构造查询条件编译期就能发现字段名写错的低级错误。JPAQueryOrderDO query new JPAQuery(entityManager); QOrderDO order QOrderDO.orderDO; BooleanBuilder builder new BooleanBuilder(); if (StringUtils.hasText(orderNo)) { builder.and(order.orderNo.eq(orderNo)); } if (status ! null) { builder.and(order.status.eq(status)); } ListOrderDO list query.from(order) .where(builder) .orderBy(order.createTime.desc()) .fetch();它的优点是类型安全、重构友好缺点是学习成本稍高团队不熟的时候很容易写出抽象过度的代码。我的建议是项目里已经深度使用JPA就用它否则不必为了动态SQL单独引入整套方案。2.4 关键能力对比与选型建议表我从可读性、安全性、学习成本、复杂条件支持、团队适配五个维度做了个对比维度字符串拼接MyBatis XMLCriteria/QueryDSL可读性差好SQL集中中依赖API熟练度注入安全差易拼接好#{}参数化好参数绑定学习成本低中较高复杂动态条件勉强支持支持良好支持较好团队适配所有团队Java后端主流JPA项目选型上没有银弹。核心判断标准是团队维护这个代码的人更熟悉哪种表达方式。我见过强上QueryDSL最后因为没人会写而退回MyBatis的项目也见过用字符串拼接撑到条件爆炸才重构的团队。早一点想清楚比晚一点后悔划算得多。3. 一段真实业务中的动态查询改造从拼接地狱到可维护方案这是一个真实场景的脱敏还原字段已简化。某订单查询接口最初由一位老同事用字符串拼接实现我接手时它已经维护了两年。3.1 原始状态一串让人窒息的拼接代码public ListOrderDO queryOrders(String orderNo, Integer status, String customerName, BigDecimal minAmount, BigDecimal maxAmount, Date startTime, Date endTime, String channel, Integer pageNo, Integer pageSize) { String sql SELECT * FROM orders WHERE 11 ; if (orderNo ! null !orderNo.isEmpty()) { sql AND order_no LIKE % orderNo %; } if (status ! null) { sql AND status status; } if (customerName ! null !customerName.isEmpty()) { sql AND customer_name LIKE % customerName %; } if (minAmount ! null) { sql AND amount minAmount; } // 后面还有 maxAmount、startTime、endTime、channel 等一长串 sql ORDER BY create_time DESC LIMIT pageSize OFFSET pageNo * pageSize; return jdbcTemplate.query(sql, rowMapper); }这段代码的问题不只是丑。用户如果把 orderNo 传成% OR 11 --这条SQL会直接变成全表查询这是实打实的注入面。更隐蔽的问题是所有动态条件挤在一个方法里后续每次加筛选都要改这个方法体方法行数持续膨胀测试覆盖成本越来越高。3.2 诊断结果动态SQL散落且条件边界模糊我接手后的第一件事不是改代码而是把现有条件全部列了一遍发现几个核心问题条件判断标准不统一有的用! null有的用! null !isEmpty()空字符串和null语义混淆金额区间同时为空或者只传一头没有处理分页参数没有上限控制传超大会拖垮数据库。这些问题单独看都不致命但组合在一起就是动态SQL腐化的典型路径判断含糊 - 拼接出错 - 打补丁 - 更含糊。如果不重构下一个接手的人只能在错误的基础上继续堆代码。我当时的判断是这个接口已经不适合继续打补丁必须把动态SQL的“条件决策”和“SQL文本”彻底拆开。3.3 改造落地参数对象 条件构造器我把方法签名改成参数对象同时把动态条件抽取到独立方法SQL改用MyBatis XML维护彻底放弃字符串拼接。改造后的查询入口public ListOrderDO queryOrders(OrderQuery query) { return orderMapper.queryOrderPage(query); }查询参数对象只关心“用户想筛什么”不再关心“SQL怎么拼”public class OrderQuery { private String orderNo; private String customerName; private Integer status; private BigDecimal minAmount; private BigDecimal maxAmount; private Date startTime; private Date endTime; private String channel; private Integer pageNo; private Integer pageSize; }对应的XML把所有边界处理统一交给if和where每个条件只保留一层判断页面传空串进来会被统一过滤掉。调用方不再需要关心SQL语法只需要关注业务语义想筛订单号就set订单号没筛就不set判断逻辑全部放在SQL片段里。3.4 改造之后的变化改造后的好处非常明显接口方法从几十行缩到一行所有SQL集中到Mapper XML业务逻辑和SQL彻底分离条件判断规则统一空字符串和null都不生效注入风险基本消除。更重要的是后续加筛选条件时改动点收敛为两处参数对象加一个字段XML加一个if再也不会牵一发动全身。这样的重构并不需要高深技巧但它依赖一个前提对动态SQL的边界和风险有足够清晰的认识。这也是我坚持先讲原理再给方案的原因。很多人拿到需求第一反应就是“加一个if条件”但真正成熟的做法是先退一步看看这组动态条件能不能被统一抽象。4. 动态SQL的安全底线SQL注入、空值判断与逻辑边界4.1 为什么注入风险总是跟着拼接走SQL注入的本质是用户输入的内容被当成了SQL代码执行。字符串拼接动态SQL时传入 orderNo 如果直接拼进语句 OR 11这类值就会改变SQL语义。参数化查询PreparedStatement / MyBatis#{}会把参数值和SQL结构分离传递数据库只把参数当数据从而在结构上消灭注入。这里要特别分清 MyBatis 的#{}和${}#{}是参数占位符安全${}是字符串替换直接拼SQL有注入风险只应在排序字段、表名等内部可控的少数场景使用并且必须做白名单校验。任何一丁点外部输入进入${}都是事故。我在Review时只要看到${}接外部参数基本都会打回要求改成白名单映射。4.2 判空的完整边界null、空串、空集合动态SQL里最烦人的不是复杂语法而是“到底什么算空”。我的经验是字符串条件要同时判断null和空字符串通常业务里两者等价数值和时间条件通常只判断null集合条件要判断是否为null以及是否为空列表。MyBatis 里写testorderNo ! null and orderNo ! 就是这个道理。还有一种常被忽略的边界时间范围的开始时间与结束时间只传了一个。应用层应该在组装参数时就校验两者成对出现或者在SQL里分别做和判断而不是寄希望于调用方一定传完整。参数对象的校验逻辑放在Service层做比散落在SQL片段里更容易测试和维护。4.3 where 11 与where标签的本质区别很多从字符串拼接转过来的老手习惯在MyBatis里也写WHERE 11加AND。说实话它能工作但 where 11 是反直觉的数学恒等式阅读代码的人需要额外思考“为什么一直为真”。where标签会在有条件时输出WHERE并自动去掉第一个多余的AND/OR没有条件时什么都不输出。语义清晰也能避免WHERE AND这种低级错误。举个例子where里写了某个if当传入条件都为空时最终SQL直接就是SELECT ... FROM orders ORDER BY create_time DESC而如果用 WHERE 11拼出来的就是SELECT ... FROM orders WHERE 11 ORDER BY create_time DESC。后者虽然也能跑但多了一个永远无意义的条件也在不断训练后来者继续使用这个坏习惯。能用标签语义表达的就别留手工拼接的小聪明。4.4 数据权限条件动态SQL里最不能丢的一环在内部系统里动态SQL经常还要叠加数据权限当前用户只能看到自己部门/自己负责的数据。这种条件必须放在所有动态条件的最后追加并且在代码审查时单独检查。我踩过一个很深刻的坑某个导出接口复用了查询SQL片段但片段里忘了带权限条件结果用户通过构造参数越权拉到了全部门数据。后来所有涉及权限过滤的查询都在Mapper层统一拼一个不可被覆盖的权限片段并通过Review清单强制检查。安全底线总结成一句话参数永远走占位符条件永远有明确空值语义权限条件永远在动态拼接的最外层兜底。这三条做到了动态SQL的安全性就有基本保障。5. 动态SQL与数据库性能的博弈索引、执行计划与参数化5.1 动态条件怎样悄悄弄丢索引动态SQL容易在不知不觉中让查询走不上索引。典型场景包括对索引字段使用函数WHERE DATE(create_time) 2024-01-01会让create_time索引失效正确写法是create_time 2024-01-01 AND create_time 2024-01-02模糊查询前缀带通配符LIKE %xxx无法利用索引OR条件连接多个字段时数据库优化器可能选择全表扫描。我遇到过一个筛选逻辑用户输入订单号时希望模糊匹配于是直接写了LIKE % #{orderNo} %。线上订单量小时没问题数据量突破千万后这个条件直接把查询拖慢了一个数量级。合理的方案是订单号这种高频精确条件用等值匹配需要模糊的字段单独建全文索引或者用ES这类检索组件处理而不是让SQL硬扛全表模糊。5.2 执行计划缓存拼接SQL的另一笔隐形账数据库对SQL语句会尝试缓存执行计划。完全拼接出来的SQL每个参数值不同都会生成一个不同的SQL文本缓存立即失效每次都要重新解析、生成执行计划。参数化SQL占位符则无论参数怎么变SQL文本都不变执行计划可以复用。这解释了为什么同样一个查询字符串拼接方式在并发高的时候数据库CPU占用会异常偏高。即使MyBatis XML看起来像“拼接”它通过#{}传给数据库的仍然是占位符参数最终SQL文本稳定执行计划可复用。所以从“拼接字符串”改为“参数化动态SQL”不只是安全收益还有性能收益这一点常被忽略。很多团队只关心功能跑通却不知道同一套查询在两种写法下的数据库压力差异有多大。5.3 分页总数统计与排序字段白名单动态查询几乎总是配分页。分页包含两条SQLCount统计和列表查询。如果列表SQL复杂度高Count查询最好单独写一个轻量版本只统计主表记录数避免把大字段和复杂JOIN带进去。MyBatis的分页插件能在底层自动生成Count SQL但有时它生成的 Count 语句不够优化这时可以在Mapper里写独立的 countByCondition 方法用更简单的SQL完成统计。排序字段是另一个容易被忽视的注入风险点。排序通常走ORDER BY #{sortField}但占位符不能用于排序字段于是有人改用${sortField}直接把前端传来的字段名拼进去。正确做法是白名单在代码里把允许排序的字段名映射到固定枚举再拼进SQL。前端传 create_time 没问题传create_time; DROP TABLE orders这种值只会被映射拒绝。5.4 一次真实慢查询的排查链路某次线上告警订单列表接口在晚高峰P99延迟升到3秒多。排查过程先抓慢查询日志发现慢SQL集中在两个动态条件同时出现的场景用EXPLAIN看执行计划发现 OR 条件下两个索引字段无法同时使用优化器走了全表扫描。修复方案是把 OR 改写成两个查询结果合并或者在应用层拆成多个条件分支组合。改完后P99降到400毫秒以内。这次排查给我最大的收获是动态SQL的每个条件分支都可能在特定组合下生成一条完全不同的执行计划。测试阶段跑通只是一方面还必须主动构建“多条件组合”的压力测试提前发现组合导致的索引失效。性能问题在动态SQL里从来不是单一条件的错而是条件组合之间的相互作用。6. 动态SQL的调试与测试没人教但必须会的技能6.1 日志配置把最终执行的SQL和参数打印出来动态SQL的难点在于“看不见最终SQL”。配好日志是首要任务。MyBatis里开启日志输出后控制台会打印类似 Preparing: SELECT ... WHERE order_no ?和 Parameters: ABC123(String)这样的内容。拿到这两行你才能确认动态条件按预期生效。我见过不少同事在业务代码里打断点看参数却不知道SQL长什么样排查效率极低。日志级别建议开发环境开DEBUG生产环境开INFO或关闭避免SQL文本和参数值大量落盘引发日志膨胀和敏感信息泄露。尤其是包含用户手机号、身份证号的查询参数一旦打到日志里就是数据安全事件。6.2 用例设计覆盖条件组合而不是一个happy path动态SQL最大的测试风险是“只测了最常用的路径”。一个列表查询如果有8个可选条件理论上有几百种组合不可能全测但至少要覆盖全部条件为空验证WHERE语义正确、全部条件填满验证条件能同时生效、单条件逐个生效验证每个if分支独立正确、边界值空字符串、0、超大分页。这些用例不一定要自动化人工回归时按清单走一遍也很有价值。条件组合测试最好通过接口层做而不只是看SQL生成是否正确。因为动态SQL的最终目的是“用正确的数据做正确的事”SQL对了但参数校验漏掉的边界依然会导致业务出错。接口层测试能同时覆盖两者投入产出比很高。6.3 代码评审时的快速检查清单Review动态SQL相关的代码我会重点盯这几个位置所有外部输入是否都走#{}而不是${}排序字段是否白名单每个if的条件是否同时判了null和空串where、set、trim是否被正确使用有没有 WHERE 11 之类的历史遗留分页Count是否简洁权限过滤条件有没有在最后兜底批量的foreach有没有控制最大条数防止一次插入几万条拖垮数据库。这份清单不复杂但每次都能在Review里捞到问题。动态SQL的坑大多不是智商问题而是规范和意识问题。把这些检查养成习惯之后动态SQL就不再是每天提心吊胆的代码段而是一个可预测、可维护的常规技术点。我在实际使用中发现凡是提前把边界想清楚、把条件集中管理的项目后面上线都很少因为动态SQL出线上事故反而是那些“先写出来再说”的代码往往要花好几倍的时间在排障和修补上。所以如果你刚开始接触动态SQL不妨从参数对象的拆分和SQL片段的集中维护做起这个习惯会帮你避开大部分后续的麻烦。
阅读完成 · 觉得有帮助？
