QueryWrapper如何实现字段截取查询？

Question

在使用 MyBatis-Plus 的 QueryWrapper 进行数据库查询时，如何实现对数据库字段的截取查询（如按日期字段的年月匹配、字符串字段的前几位模糊匹配）？常见场景包括：根据创建时间字段的“年-月”部分查询数据，或对手机号、编号等字段的前缀进行截取比对。由于 QueryWrapper 本身未直接提供字段截取的链式方法，开发者常困惑于如何在不拼接 SQL 字符串的前提下，安全高效地使用数据库函数（如 MySQL 的 DATE_FORMAT、SUBSTR）实现字段截取条件查询。如何结合 QueryWrapper 的 lambda 表达式与 wrapper 内部函数完成此类操作，同时避免 SQL 注入风险？

Answer 1

一、背景与问题引入

在现代企业级 Java 开发中，MyBatis-Plus 作为 MyBatis 的增强工具，极大简化了数据库操作。其 QueryWrapper 提供了链式调用和 Lambda 表达式支持，使条件构造更加直观。然而，当需要对数据库字段进行截取匹配时（如按“年-月”查询时间字段、手机号前7位模糊匹配等），开发者常面临挑战。

核心痛点在于：QueryWrapper 并未提供原生的字段截取方法，若直接拼接 SQL 字符串则可能引入 SQL 注入风险，违背安全开发原则。如何在不牺牲安全性的前提下，利用数据库函数（如 MySQL 的 DATE_FORMAT、SUBSTR）实现精准截取查询，成为高级开发者必须掌握的技能。

二、常见应用场景分析

• 场景1：按创建时间的“年-月”统计订单 —— 需提取 create_time 字段的年月部分进行分组或过滤。
• 场景2：手机号归属地匹配 —— 根据手机号前7位（号段）查询运营商或地区信息。
• 场景3：编号前缀检索 —— 如工单编号以“GD2024”开头的数据查询。
• 场景4：身份证号区域码比对 —— 截取前6位匹配行政区划代码。
• 场景5：日志按小时粒度聚合 —— 使用 HOUR() 或 DATE_FORMAT(time, '%Y-%m-%d %H') 聚合访问日志。
• 场景6：邮箱域名筛选 —— 提取 @ 后的内容进行分类统计。
• 场景7：文件路径目录匹配 —— 截取路径前缀判断存储位置。
• 场景8：IP 地址网段识别 —— 通过 SUBSTR 或正则提取前缀判断归属。
• 场景9：商品编码类别筛选 —— 前三位代表产品大类。
• 场景10：会员卡号区域划分 —— 卡号第4~6位表示发卡机构。

三、技术原理剖析：QueryWrapper 的扩展机制

MyBatis-Plus 的 QueryWrapper 支持通过 apply() 方法嵌入原生 SQL 片段，同时保留预编译参数化机制，从而避免 SQL 注入。关键在于正确使用占位符 {%s} 和参数绑定。

此外，Lambda 版本的 QueryWrapper<T> 可结合 lambda() 构建类型安全的字段引用，再通过 apply 注入数据库函数表达式。

以下是常用数据库函数对照表：

需求类型	MySQL 函数	Oracle 函数	PostgreSQL 函数
日期年月提取	DATE_FORMAT(date, '%Y-%m')	TO_CHAR(date, 'YYYY-MM')	TO_CHAR(date, 'YYYY-MM')
字符串前N位	SUBSTR(str, 1, N)	SUBSTR(str, 1, N)	SUBSTRING(str FROM 1 FOR N)
后N位提取	RIGHT(str, N)	SUBSTR(str, -N)	RIGHT(str, N)
位置查找	LOCATE(sub, str)	INSTR(str, sub)	POSITION(sub IN str)
长度获取	LENGTH(str)	LENGTH(str)	LENGTH(str)

四、解决方案实践：基于 apply 与 lambda 的安全实现

以下为具体代码示例，展示如何结合 apply 和 Lambda 表达式完成字段截取查询。

// 示例1：按创建时间的“年-月”查询订单
queryWrapper.lambda()
    .eq("DATE_FORMAT(create_time, '%Y-%m')", "2024-04");

// 存在注入风险！应改用参数化方式：
queryWrapper.apply("DATE_FORMAT({0}, '%Y-%m') = {1}",
    "create_time", "2024-04");

// 更优写法：使用字段引用 + 参数绑定
queryWrapper.lambda().apply(
    "DATE_FORMAT({0}, '%Y-%m') = ?", 
    Order::getCreateTime, 
    "2024-04"
);

// 示例2：手机号前7位匹配
queryWrapper.lambda().apply(
    "SUBSTR({0}, 1, 7) = ?", 
    User::getPhone, 
    "1381234"
);

// 示例3：编号前缀模糊匹配（LIKE 截取）
queryWrapper.lambda().apply(
    "{0} LIKE CONCAT(?, '%')", 
    Order::getOrderNo, 
    "GD2024"
);

// 示例4：身份证前6位匹配行政区
queryWrapper.lambda().apply(
    "SUBSTR({0}, 1, 6) = ?", 
    User::getIdCard, 
    "440106"
);

五、进阶技巧：封装通用截取条件工具类

为提升代码复用性与可维护性，建议封装通用的条件构建工具。

public class QueryWrapperUtils {

    /**
     * 按年月格式化日期字段进行等值查询
     */
    public static <T> QueryWrapper<T> eqByYearMonth(
            QueryWrapper<T> wrapper,
            SFunction<T, ?> fieldFunc,
            String yearMonth) {
        return wrapper.apply("DATE_FORMAT({0}, '%Y-%m') = ?", fieldFunc, yearMonth);
    }

    /**
     * 字符串字段前N位精确匹配
     */
    public static <T> QueryWrapper<T> prefixMatch(
            QueryWrapper<T> wrapper,
            SFunction<T, ?> fieldFunc,
            String prefix) {
        int len = prefix.length();
        return wrapper.apply("SUBSTR({0}, 1, {1}) = ?", fieldFunc, len, prefix);
    }

    /**
     * 前缀模糊匹配（LIKE）
     */
    public static <T> QueryWrapper<T> likePrefix(
            QueryWrapper<T> wrapper,
            SFunction<T, ?> fieldFunc,
            String prefix) {
        return wrapper.apply("{0} LIKE CONCAT(?, '%')", fieldFunc, prefix);
    }
}

六、执行流程图解：QueryWrapper 截取查询逻辑流

graph TD A[开始构造 QueryWrapper] --> B{是否需字段截取?} B -- 否 --> C[使用常规 eq/like 等方法] B -- 是 --> D[选择对应数据库函数] D --> E[使用 apply 方法注入表达式] E --> F[通过 {0} 引用字段 Lambda] F --> G[绑定参数防止注入] G --> H[生成预编译SQL] H --> I[执行查询返回结果]

七、性能与安全注意事项

1. 索引失效风险：在字段上使用函数（如 DATE_FORMAT）会导致索引无法命中，建议对高频查询建立函数索引（MySQL 8.0+ 支持）。
2. 跨数据库兼容性：不同数据库函数语法差异大，若项目需多数据库支持，应抽象函数层或使用 JPA/Hibernate 替代。
3. 参数化是关键：禁止字符串拼接用户输入，始终使用 ? 占位符或 {n} 结合参数列表。
4. 避免过度嵌套：复杂表达式影响可读性，必要时可拆分为视图或在应用层处理。
5. 测试覆盖：对 apply 条件编写单元测试，验证生成 SQL 正确性。
6. 日志审计：开启 MyBatis SQL 日志，监控实际执行语句。
7. 权限最小化：数据库账号不应具备执行任意函数的权限。
8. 缓存策略调整：函数计算结果不宜缓存原始记录，需设计二级缓存键规则。