1. 从“自动挡”到“手动挡”：为什么需要自定义SQL

在Java后端开发里，Mybatis-Plus（简称MP）几乎成了操作数据库的“标配”。它那套强大的Wrapper条件构造器，配合上IService接口里琳琅满目的save、update、list、page方法，让我们处理单表的CRUD（增删改查）时，体验堪比开自动挡汽车——踩油门就走，省心省力。我见过不少项目，靠着MP的自动生成和条件拼接，就能完成80%以上的数据操作，开发效率确实高。

但就像自动挡车遇到复杂路况或追求极致操控时，老司机还是会切到手动模式一样，我们在数据库操作中，也总会遇到那些MP的“自动挡”覆盖不了的场景。这时候，自定义SQL就成了我们必须掌握的“手动挡”技能。我最初接触自定义SQL，就是因为一个多表关联的复杂统计需求。MP的Wrapper再强大，面对那种需要嵌套子查询、多重JOIN、甚至用到数据库特有函数（比如GROUP_CONCAT、窗口函数）的SQL时，就显得力不从心了。强行用Java代码在内存里拼凑和计算，不仅代码臃肿，性能更是灾难。

所以，自定义SQL的核心价值，就在于填补MP自动化能力的“空白区”，让我们在享受MP便捷的同时，又不失对复杂查询的掌控力。它不是为了替代MP，而是作为其能力的重要补充。接下来，我会结合几种最常见的场景，带你看看具体哪些情况我们必须请出“手动挡”。

2. 自定义SQL的四大典型应用场景

2.1 场景一：复杂多表关联查询

这是自定义SQL最经典的应用场景。比如，你需要查询订单信息，同时关联出用户详情、商品列表（每个订单可能对应多个商品），并且还要按商品类目进行筛选。这种“一对多再对多”的关联，用MP的@TableField注解做简单的一对一或一对多映射还行，但复杂的多对多和多重筛选，写起来会非常别扭。

一个反例：我曾见过有同事试图用MP的QueryWrapper，在主表查询后，再循环调用其他Service的list方法去填充数据。代码里充满了循环和临时集合，一次列表查询可能触发几十次甚至上百次数据库请求（N+1问题），页面打开慢得像蜗牛。正确的做法，就是写一条清晰的LEFT JOIN ... ON ...SQL，一次查询将所有关联数据按需组装好。

2.2 场景二：使用数据库特定函数或语法

不同的数据库（MySQL, PostgreSQL, Oracle等）都有自己特有的函数和语法糖。比如，MySQL的DATE_FORMAT()进行日期格式化、FIND_IN_SET()处理逗号分隔的字符串；PostgreSQL的JSONB类型操作符@>；或者像WITH RECURSIVE这样的递归查询语法。MP的通用API为了保持兼容性，无法直接生成这些数据库特有的语句。这时，自定义SQL是唯一的选择。

2.3 场景三：高性能的批量更新或删除

虽然MP提供了updateBatchById这样的批量方法，但其底层通常是遍历集合，逐条生成UPDATE语句执行（取决于配置）。对于需要根据复杂条件更新海量数据的场景，比如“将过去30天未登录的用户的status字段改为‘冻结’”，一条基于WHERE条件的UPDATE语句在性能上远超在Java中分批调用。同理，基于复杂条件的批量删除也是如此。这种操作，必须通过自定义SQL来实现。

2.4 场景四：复杂的统计报表与聚合计算

做报表时，我们经常需要写包含GROUP BY多个字段、HAVING过滤、SUM/COUNT/AVG聚合，甚至多层嵌套子查询的SQL。MP的groupBy和聚合方法虽然能处理简单分组，但对于字段别名、多层聚合、子查询别名引用等复杂情况，其链式调用的代码可读性会急剧下降，远不如一条精心编排的原生SQL直观和易于维护。

明确了为什么需要以及何时需要之后，我们来看看在Mybatis-Plus的体系下，具体有哪几种“手动挡”的挂挡方式。

3. 核心方法一：在Mapper接口中直接使用@Select/@Update等注解

这是最直接、最轻量的一种方式，适合SQL语句相对固定、不太复杂的场景。Mybatis-Plus完全兼容MyBatis的原生注解。

具体做法： 在你的XxxMapper接口（需继承MP的BaseMapper）中，直接定义方法，并使用@Select、@Update、@Insert、@Delete注解来编写SQL。

public interface UserMapper extends BaseMapper<User> { // 场景：查询某个部门下所有用户，并关联查询部门名称 @Select("SELECT u.*, d.name as dept_name " + "FROM user u " + "LEFT JOIN department d ON u.dept_id = d.id " + "WHERE u.dept_id = #{deptId}") List<UserDeptVO> selectUsersWithDept(@Param("deptId") Long deptId); // 场景：使用数据库函数进行复杂查询 @Select("SELECT * FROM user WHERE DATE_FORMAT(create_time, '%Y-%m') = #{month}") List<User> selectUsersByCreateMonth(@Param("month") String month); // 场景：执行批量更新（例如，根据条件激活用户） @Update("UPDATE user SET status = 1 WHERE last_login_time < #{thresholdDate} AND status = 0") int activateInactiveUsers(@Param("thresholdDate") Date thresholdDate); }

关键点与避坑经验：

1. 参数绑定：务必使用@Param注解明确指定参数名，并在SQL中使用#{参数名}进行引用。这是防止参数绑定错误的最基本也最重要的习惯。
2. 结果映射：如果查询返回的字段与实体类User不完全对应（比如上面多了个dept_name），你需要：
   • 定义一个结果对象UserDeptVO（或者DTO），包含所有返回字段。
   • 或者，使用MyBatis的@Results注解进行手动映射（稍显繁琐，不推荐复杂场景使用）。
3. SQL注入：注解中的SQL是静态字符串，使用#{}语法是安全的预编译方式，可以防止SQL注入。绝对不要用字符串拼接‘${}’来传递条件值，除非你非常清楚它在做字符串替换而非预编译，且参数绝对安全。
4. 可维护性：当SQL很长时，写在注解里会破坏代码格式，可读性变差。可以考虑使用<script>标签（虽然写在注解里有点怪），或者当SQL非常复杂时，转向我们后面要讲的XML方式。

注意：@Select注解等返回的是List<T>，如果你需要MP的IPage分页对象，单纯用注解是不够的。虽然可以手动计算LIMIT，但会失去MP分页插件自动处理总数等便利。这时需要结合分页插件和XML，或者使用@Select注解的方法，其参数接受一个IPage对象，但SQL里需要包含${ew.customSqlSegment}（不推荐，易出错）。更规范的分页做法在XML部分讲解。

4. 核心方法二：结合XML映射文件，处理复杂动态SQL

当SQL非常复杂，或者需要动态拼接条件（即IF、WHERE、FOREACH等标签）时，将SQL写在独立的XML映射文件中是行业内的最佳实践。这种方式分离了SQL和Java代码，结构清晰，尤其利于维护长而复杂的SQL语句。

第一步：创建XML文件并配置路径

1. 在resources目录下，创建与Mapper接口包名相同的目录结构。例如，接口com.example.mapper.UserMapper，则XML文件应放在resources/com/example/mapper/UserMapper.xml。
2. 在application.yml中确保MyBatis的XML映射路径被正确扫描：

   mybatis-plus: mapper-locations: classpath*:/mapper/**/*.xml

第二步：编写XML映射文件

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.mapper.UserMapper"> <!-- 通用查询结果映射，可复用 --> <resultMap id="UserDeptResultMap" type="com.example.vo.UserDeptVO"> <id property="id" column="id"/> <result property="username" column="username"/> <!-- ... 映射其他User字段 ... --> <result property="deptName" column="dept_name"/> </resultMap> <!-- 场景：动态多条件分页查询用户列表 --> <select id="selectUserPage" resultType="com.example.entity.User"> SELECT * FROM user <where> <if test="query.username != null and query.username != ''"> AND username LIKE CONCAT('%', #{query.username}, '%') </if> <if test="query.status != null"> AND status = #{query.status} </if> <if test="query.deptId != null"> AND dept_id = #{query.deptId} </if> <if test="query.createTimeStart != null"> AND create_time >= #{query.createTimeStart} </if> <if test="query.createTimeEnd != null"> AND create_time <= #{query.createTimeEnd} </if> </where> ORDER BY create_time DESC </select> <!-- 场景：使用foreach进行批量插入 (MySQL语法示例) --> <insert id="batchInsert" parameterType="java.util.List"> INSERT INTO user (username, email, dept_id) VALUES <foreach collection="list" item="item" separator=","> (#{item.username}, #{item.email}, #{item.deptId}) </foreach> </insert> <!-- 场景：复杂的统计报表SQL --> <select id="selectDeptUserStats" resultType="com.example.vo.DeptStatVO"> SELECT d.id as deptId, d.name as deptName, COUNT(u.id) as userCount, SUM(CASE WHEN u.status = 1 THEN 1 ELSE 0 END) as activeUserCount, AVG(u.some_score) as avgScore FROM department d LEFT JOIN user u ON d.id = u.dept_id WHERE d.parent_id = #{rootDeptId} GROUP BY d.id, d.name HAVING COUNT(u.id) > #{minUserCount} ORDER BY userCount DESC </select> </mapper>

第三步：在Mapper接口中定义对应方法

public interface UserMapper extends BaseMapper<User> { // 对应XML中的 selectUserPage IPage<User> selectUserPage(IPage<User> page, @Param("query") UserQueryDTO query); // 对应XML中的 batchInsert int batchInsert(@Param("list") List<User> userList); // 对应XML中的 selectDeptUserStats List<DeptStatVO> selectDeptUserStats(@Param("rootDeptId") Long rootDeptId, @Param("minUserCount") Integer minUserCount); }

关键点与避坑经验：

1. namespace必须对应：XML文件顶部的namespace属性值，必须是Mapper接口的全限定名（包名+类名），一个字符都不能错，这是MyBatis将它们关联起来的唯一依据。
2. 分页插件的无缝集成：这是XML方式的一大优势。如上例selectUserPage，方法参数中直接传入IPage对象，Mybatis-Plus的分页插件（PaginationInnerInterceptor）会自动工作。它会在执行查询时，自动生成并执行一条COUNT(*)语句获取总数，同时为原SQL加上LIMIT分页子句。你完全不用在XML里写LIMIT，插件帮你搞定。返回的也是包含分页信息（总条数、每页大小、当前页数据列表）的IPage对象。
3. 动态SQL标签：<where>、<if>、<foreach>、<choose>、<set>等标签是MyBatis的核心功能，它们能根据传入参数动态生成SQL片段，完美解决条件不确定的问题。注意<where>标签会智能地处理AND/OR开头的问题。
4. 参数传递：在XML中，通过@Param注解定义的参数名（如query）来引用。对于对象中的属性，使用#{query.username}这样的点号语法。
5. 结果映射：resultType直接指定返回的实体类或VO类。对于字段名和属性名不一致的情况，可以使用resultMap进行详细映射，如上例中的UserDeptResultMap。

5. 核心方法三：使用Wrapper条件构造器拼接自定义SQL片段

这是Mybatis-Plus提供的一种“混合动力”模式。它允许你在自定义SQL的WHERE部分，仍然使用MP强大的Wrapper来构造条件，从而将自定义的SELECT ... FROM ...部分与动态条件生成部分解耦，兼具灵活与便捷。

使用场景：当你需要自定义SELECT的字段或者FROM的表（比如复杂的JOIN），但WHERE条件又是动态的、且希望用MP的Wrapper来优雅构造时。

具体做法： 在XML文件中，在WHERE位置使用${ew.customSqlSegment}来嵌入Wrapper生成的条件。

<!-- UserMapper.xml --> <select id="selectCustomWithWrapper" resultType="com.example.vo.UserDetailVO"> SELECT u.id, u.username, d.name as department_name, r.role_name FROM user u LEFT JOIN department d ON u.dept_id = d.id LEFT JOIN user_role ur ON u.id = ur.user_id LEFT JOIN role r ON ur.role_id = r.id ${ew.customSqlSegment} <!-- 关键：此处注入Wrapper生成的条件 --> </select>

在Mapper接口和Service中的调用：

// Mapper接口 public interface UserMapper extends BaseMapper<User> { List<UserDetailVO> selectCustomWithWrapper(@Param("ew") Wrapper<User> wrapper); } // Service或Controller中使用 @Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService { public List<UserDetailVO> getUsersByComplexCondition(UserQueryDTO query) { // 1. 构建QueryWrapper QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like(StringUtils.isNotBlank(query.getUsername()), "u.username", query.getUsername()) .eq(query.getStatus() != null, "u.status", query.getStatus()) .ge(query.getStartTime() != null, "u.create_time", query.getStartTime()) .le(query.getEndTime() != null, "u.create_time", query.getEndTime()) .orderByDesc("u.create_time"); // 2. 注意！Wrapper中的列名需要与自定义SQL中的表别名对应 // 这里用的是 `u.username`， `u.status`， 而不是`username` // 3. 调用自定义方法，传入wrapper return this.baseMapper.selectCustomWithWrapper(wrapper); } }

关键点与避坑经验：

1. 表别名一致性：这是最容易踩的坑！在自定义SQL的SELECT和FROM部分，你给表起了别名（如u、d）。那么，在构造QueryWrapper时，所有涉及列名的条件（eq,like,ge等），其列名参数必须带上相同的表别名前缀（如"u.username"）。否则，SQL会报错“列名不明确”或找不到列。
2. ${ew.customSqlSegment}的含义：ew是@Param("ew")定义的参数名，customSqlSegment是Wrapper对象的一个属性，它包含了Wrapper生成的WHERE之后的SQL片段（不包括WHERE关键字本身）。使用${}进行字符串替换，这意味着它是直接拼接进SQL的，因此要确保Wrapper的构造是安全的，避免SQL注入。只要不将用户输入直接用于列名等结构部分，通过Wrapper的eq、like等方法设置的值都是预编译安全的。
3. 排序和分页：你可以在Wrapper中使用orderByAsc/orderByDesc来添加排序，它也会被包含在customSqlSegment中。对于分页，你依然可以像XML方式一样，在Mapper方法参数中添加IPage对象，分页插件会正常生效。
4. 灵活性：这种方法非常适合后台管理系统中那些“过滤条件复杂但查询主体固定”的列表页。前端传回各种过滤参数，后端用Wrapper轻松构建条件，而查询主体（包含哪些字段、关联哪些表）则在XML中一次定义好。

6. 实战演练：一个完整的多表分页查询案例

假设我们有一个博客系统，需要实现一个后台文章管理列表，需求如下：

• 查询文章列表，需关联显示文章分类名称和作者昵称。
• 支持根据文章标题（模糊）、分类ID、发布状态、发布时间范围进行筛选。
• 结果需要分页，并按发布时间倒序排列。

步骤1：定义查询参数DTO、返回结果VO

// 查询参数 @Data public class ArticleQueryDTO { private String title; private Long categoryId; private Integer status; // 0-草稿，1-已发布 private LocalDateTime publishTimeStart; private LocalDateTime publishTimeEnd; } // 返回结果视图对象 @Data public class ArticleAdminVO { private Long id; private String title; private String summary; private Integer status; private LocalDateTime publishTime; // 关联字段 private String categoryName; private String authorNickname; }

步骤2：在ArticleMapper接口中定义方法

public interface ArticleMapper extends BaseMapper<Article> { /** * 后台文章分页查询 * @param page 分页参数，由分页插件自动处理 * @param query 查询条件 * @return 分页结果 */ IPage<ArticleAdminVO> selectAdminArticlePage(IPage<Article> page, @Param("query") ArticleQueryDTO query); }

步骤3：编写ArticleMapper.xml

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.blog.mapper.ArticleMapper"> <select id="selectAdminArticlePage" resultType="com.example.blog.vo.ArticleAdminVO"> SELECT a.id, a.title, a.summary, a.status, a.publish_time, c.name as category_name, u.nickname as author_nickname FROM article a LEFT JOIN category c ON a.category_id = c.id LEFT JOIN user u ON a.author_id = u.id <where> <if test="query.title != null and query.title != ''"> AND a.title LIKE CONCAT('%', #{query.title}, '%') </if> <if test="query.categoryId != null"> AND a.category_id = #{query.categoryId} </if> <if test="query.status != null"> AND a.status = #{query.status} </if> <if test="query.publishTimeStart != null"> AND a.publish_time >= #{query.publishTimeStart} </if> <if test="query.publishTimeEnd != null"> AND a.publish_time <= #{query.publishTimeEnd} </if> </where> ORDER BY a.publish_time DESC </select> </mapper>

步骤4：在Service中调用

@Service public class ArticleAdminServiceImpl extends ServiceImpl<ArticleMapper, Article> implements ArticleAdminService { @Override public PageResult<ArticleAdminVO> getAdminArticlePage(ArticleQueryDTO queryDTO, PageParam pageParam) { // 1. 构建Mybatis-Plus的分页对象 Page<Article> page = new Page<>(pageParam.getPageNum(), pageParam.getPageSize()); // 2. 调用自定义的Mapper方法 IPage<ArticleAdminVO> resultPage = this.baseMapper.selectAdminArticlePage(page, queryDTO); // 3. 将IPage转换为自定义的PageResult（可选，根据项目规范） PageResult<ArticleAdminVO> pageResult = new PageResult<>(); pageResult.setList(resultPage.getRecords()); pageResult.setTotal(resultPage.getTotal()); pageResult.setPageNum((int) resultPage.getCurrent()); pageResult.setPageSize((int) resultPage.getSize()); pageResult.setPages((int) resultPage.getPages()); return pageResult; } }

案例要点分析：

1. 分页：在Service层创建Page对象并传入，Mapper方法返回IPage<VO>。分页插件自动拦截，执行两条SQL：COUNT(*)和 添加了LIMIT的原SQL。我们无需手动计算分页参数。
2. 动态WHERE：XML中的<where>和<if>标签根据queryDTO中字段是否为null来动态拼接条件，完美支持前端可选过滤。
3. 结果映射：resultType="ArticleAdminVO"，MyBatis会自动将查询结果的列名（通过AS别名指定，如category_name）映射到VO对象的属性上（categoryName），遵循下划线转驼峰的默认映射规则（需在配置中开启map-underscore-to-camel-case: true）。
4. 性能：一条SQL完成多表关联和过滤，避免了N+1查询问题，效率远高于在Java中循环查询关联数据。

7. 高级技巧与深度避坑指南

掌握了基本方法后，在实际项目中还会遇到一些更细致的问题。这里分享几个我踩过坑才总结出来的高级技巧和注意事项。

7.1 如何优雅地返回Map或自定义DTO/VO？

有时我们不需要完整的实体对象，只需要几个字段。除了定义VO，还可以直接返回Map<String, Object>或List<Map>。

在Mapper接口中：

@Select("SELECT id, username FROM user WHERE status = #{status}") List<Map<String, Object>> selectSimpleUserMap(@Param("status") Integer status);

这种方式非常灵活，但牺牲了类型安全。在XML中，使用resultType="java.util.Map"即可。

更推荐的做法：对于固定的字段组合，显式地定义DTO或VO类。这保证了类型安全、良好的代码提示和可维护性。不要因为偷懒而滥用Map。

7.2 使用<script>标签处理“动态SELECT字段”或“动态表名”

极少数情况下，我们可能需要动态决定查询哪些字段，或者根据参数切换查询的表。这可以在XML中使用<script>标签内嵌逻辑实现。

<select id="selectDynamic" resultType="com.example.entity.User"> SELECT <choose> <when test="fields != null and fields.size() > 0"> <foreach collection="fields" item="field" separator=","> ${field} <!-- 注意这里是${}，因为field是列名/SQL片段 --> </foreach> </when> <otherwise> * </otherwise> </choose> FROM user WHERE id = #{id} </select>

警告：${field}是直接的字符串替换，存在SQL注入风险！必须确保fields集合内的值（如"username","email"）来自可信的代码逻辑，而非用户直接输入。动态表名同理，需极度谨慎。

7.3 分页插件与自定义SQL的“坑”与“解”

坑1：自定义COUNT语句当你的自定义SQL非常复杂（例如带有大量GROUP BY或DISTINCT），MP分页插件自动生成的COUNT(*)语句可能会很慢甚至语义错误。这时，你需要为这个查询单独指定一个COUNT语句。

解决方案：在XML中，为同一个查询ID定义一个后缀为_COUNT的查询。

<select id="selectComplexReport" resultType="..."> SELECT ... FROM ... [复杂的JOIN和GROUP BY] </select> <select id="selectComplexReport_COUNT" resultType="java.lang.Long"> SELECT COUNT(DISTINCT main.id) FROM (...) main <!-- 一个更高效的COUNT写法 --> </select>

分页插件会优先寻找{你的方法名}_COUNT的语句来执行计数。

坑2：ORDER BY在分页时丢失在某些数据库（如某些版本的MySQL）和复杂SQL下，分页插件自动添加的LIMIT可能会与你的ORDER BY子句产生冲突，导致排序失效。确保你的ORDER BY子句在SQL中是明确的，并且是最后的部分。

7.4 事务管理：自定义更新/删除操作

对于在Mapper中使用@Update、@Delete或XML中定义的INSERT/UPDATE/DELETE语句，默认情况下，它们会参与Spring管理的事务。如果你的Service方法上标注了@Transactional，那么这些自定义的DML操作也会在同一个事务中。

重要实践：确保你的自定义更新操作是幂等的，特别是在重试或并发场景下。对于复杂的批量更新，建议在Service层方法上添加@Transactional(rollbackFor = Exception.class)，以保证数据一致性。

7.5 性能考量：大数据量下的自定义查询

• 避免SELECT *：在自定义SQL中，尤其是关联查询，务必只SELECT你需要的字段。这能显著减少网络传输和结果集映射的开销。
• 善用索引：自定义SQL让你对最终执行的SQL有完全的控制权，也意味着你需要自己考虑查询性能。确保WHERE、JOIN ON、ORDER BY子句中的字段有合适的索引。
• 分批处理：对于需要处理大量数据的自定义更新或查询，考虑在Service层进行分批操作，避免单次操作耗时过长或占用过多内存。

自定义SQL是Mybatis-Plus这把利器开刃的关键。它让我们从MP的“舒适区”走向更广阔的数据库操作领域，处理那些自动化框架无法覆盖的复杂场景。核心在于理解三种主要方式的应用边界：简单固定查询用注解，复杂动态SQL用XML，想复用Wrapper条件就用${ew.customSqlSegment}混合模式。记住，能力越大责任越大，直接编写SQL意味着你需要对性能和安全（SQL注入）有更高的意识。多写、多调试、多查看实际执行的SQL，你就能在这“手动挡”的模式下游刃有余，真正掌控数据层的每一行代码。