Mysql中模糊匹配常被忽略的坑

我们先来看看问题现象

准备测试数据库的数据

这里会使用下面sql进行模糊匹配查询

SELECT * FROM product WHERE product_name like '%#{productName}%'

实际执行的sql

返回结果如下

可以看到这里跟我们预期的结果并不一样，我们预期应该只返回一个数据：20%第二组数据

但是这里将我们的测试数据全部都返回了。

原因是在MySQL模糊查询中，%和 _需要转义的原因在于它们是 SQL 标准中 LIKE操作符的通配符，具有特殊含义，不能直接作为普通字符进行匹配。

这个时候就需要使用转义符'\'，而转义符本身也是需要转义的。

解决这个的办法有两种。

1，使用util方法，在需要处理的地方调用方法进行处理（常见）

2，使用mysql的拦截器，拦截sql的like查询，修改实际的执行sql（方便）

这里我就只展示第二种，第一种想必大家都知道怎么做

拦截器修改sql（这里还有存量的打印sql信息的内容）

package com.luojie.config.myInterface.mybatisIntercept;import com.luojie.common.Conditions;
import com.luojie.util.TxtUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;/*** 自定义打印日志功能的拦截器*/
@Intercepts({// 拦截 Executor 接口的 query 方法，包含不同的参数组合@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),@Signature(type = Executor.class, method = "queryCursor", args = {MappedStatement.class, Object.class, RowBounds.class}),@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
@Slf4j
public class SqlPrintInterceptor implements Interceptor {private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");@Overridepublic Object intercept(Invocation invocation) throws Throwable {// 记录开始时间long startTime = System.currentTimeMillis();Object proceed = null;// 执行原始方法try {// 获取执行的SQL语句，判断是否是模糊查询MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];Object parameter = invocation.getArgs().length > 1 ? invocation.getArgs()[1] : null;BoundSql boundSql = mappedStatement.getBoundSql(parameter);String sql = boundSql.getSql();// 如果是模糊查询，在执行SQL之前修改参数if (isLikeQuery(sql)) {modifyParametersForLikeQuery(invocation);// 重新获取参数和BoundSql，因为参数可能被修改parameter = invocation.getArgs().length > 1 ? invocation.getArgs()[1] : null;boundSql = mappedStatement.getBoundSql(parameter);}proceed = invocation.proceed();} catch (Throwable t) {log.error("Error during SQL execution", t);throw t; // 重新抛出异常}// 记录结束时间long endTime = System.currentTimeMillis();long executionTime = endTime - startTime; // 计算执行时间// 转换执行时间为 "XXs.XXms" 格式String formattedExecutionTime = formatExecutionTime(executionTime);// 生成打印的 SQL 语句String printSql = generateSql(invocation);// 输出 SQL 和执行时间System.out.println(Conditions.RED + "SQL: " + printSql);System.out.println("Execution time: " + formattedExecutionTime);System.out.print(Conditions.RESET);log.info("SQL: " + printSql);log.info("Execution time: " + formattedExecutionTime);// 记录慢sql(这里我为了方便观察，所以设置界限为0，各位可以根据实际情况设置)if ((executionTime / 1000) >= 0) {writeSlowSqlToLocation(printSql, formattedExecutionTime);}return proceed; // 返回原始方法的结果}// 记录慢sqlprivate void writeSlowSqlToLocation(String sql, String executeTime) {String formattedDate = dateFormat.format(new Date());String logs = formattedDate + "  SQL: " + sql + "  执行耗时: " + executeTime;TxtUtil.writeLog(logs);}// 新增格式化执行时间的方法private String formatExecutionTime(long executionTime) {long seconds = executionTime / 1000; // 获取秒数long milliseconds = executionTime % 1000; // 获取剩余的毫秒数return String.format("%ds.%03dms", seconds, milliseconds); // 格式化为 "XXs.XXXms"}/*** 生成用于日志显示的SQL语句* 注意：此方法只用于日志显示，不影响实际执行的SQL*/private String generateSql(Invocation invocation) {// 获取 MappedStatement 对象MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];Object parameter = null;// 获取参数对象if (invocation.getArgs().length > 1) {parameter = invocation.getArgs()[1];}// 获取 MyBatis 配置Configuration configuration = mappedStatement.getConfiguration();// 获取 BoundSql 对象BoundSql boundSql = mappedStatement.getBoundSql(parameter);// 获取参数对象Object parameterObject = boundSql.getParameterObject();// 获取参数映射列表List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();// 获取执行的 SQL 语句String sql = boundSql.getSql();// 替换 SQL 中多个空格为一个空格sql = sql.replaceAll("[\\s]+", " ");// 情况1: 有参数对象且有参数映射if (!ObjectUtils.isEmpty(parameterObject) && !ObjectUtils.isEmpty(parameterMappings)) {// 如果只有一个参数，直接替换if (parameterObject instanceof String && parameterMappings.size() == 1) {return sql.replaceFirst("\\?", String.valueOf(parameterObject)); // 处理缺少值的情况}// 遍历每个参数映射for (ParameterMapping parameterMapping : parameterMappings) {String propertyName = parameterMapping.getProperty(); // 获取属性名MetaObject metaObject = configuration.newMetaObject(parameterObject); // 创建 MetaObjectObject obj = null; // 初始化参数对象// 如果参数对象有对应的 getter 方法if (metaObject.hasGetter(propertyName)) {obj = metaObject.getValue(propertyName); // 获取参数值} else if (boundSql.hasAdditionalParameter(propertyName)) {obj = boundSql.getAdditionalParameter(propertyName); // 获取附加参数}// 替换 SQL 中的占位符if (obj != null) {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));} else {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(propertyName)); // 处理缺少值的情况}}}// 情况2: 只有参数对象，没有参数映射 (通常是单个简单参数)else if (!ObjectUtils.isEmpty(parameterObject) && ObjectUtils.isEmpty(parameterMappings)) {// 其他情况直接替换sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));}// 情况3: 有参数映射但没有参数对象else if (ObjectUtils.isEmpty(parameterObject) && !ObjectUtils.isEmpty(parameterMappings)) {// 遍历每个参数映射for (ParameterMapping parameterMapping : parameterMappings) {String propertyName = parameterMapping.getProperty(); // 获取属性名// 尝试从附加参数中获取值Object obj = null;if (boundSql.hasAdditionalParameter(propertyName)) {obj = boundSql.getAdditionalParameter(propertyName); // 获取附加参数}// 替换 SQL 中的占位符if (obj != null) {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));} else {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(propertyName)); // 处理缺少值的情况}}}return sql; // 返回生成的 SQL 语句}/*** 修改模糊查询的参数值，对特殊字符进行转义* 注意：此方法会修改实际执行的SQL参数*/private void modifyParametersForLikeQuery(Invocation invocation) throws Exception {// 获取 MappedStatement 对象MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];Object parameter = null;// 获取参数对象if (invocation.getArgs().length > 1) {parameter = invocation.getArgs()[1];}// 获取 MyBatis 配置Configuration configuration = mappedStatement.getConfiguration();// 获取 BoundSql 对象BoundSql boundSql = mappedStatement.getBoundSql(parameter);// 获取参数对象Object parameterObject = boundSql.getParameterObject();// 获取参数映射列表List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();// 情况1: 有参数对象且有参数映射if (!ObjectUtils.isEmpty(parameterObject) && !ObjectUtils.isEmpty(parameterMappings)) {// 遍历每个参数映射for (ParameterMapping parameterMapping : parameterMappings) {String propertyName = parameterMapping.getProperty(); // 获取属性名MetaObject metaObject = configuration.newMetaObject(parameterObject); // 创建 MetaObject// 如果参数对象有对应的 getter 方法且是字符串类型，进行转义if (metaObject.hasGetter(propertyName)) {Object obj = metaObject.getValue(propertyName); // 获取参数值if (obj instanceof String) {String escapedValue = escapeSqlSpecialChars((String) obj);metaObject.setValue(propertyName, escapedValue);}}}}// 情况2: 只有参数对象，没有参数映射 (通常是单个简单参数)else if (!ObjectUtils.isEmpty(parameterObject) && parameterObject instanceof String) {// 直接替换参数对象String escapedValue = escapeSqlSpecialChars((String) parameterObject);invocation.getArgs()[1] = escapedValue;}// 情况3: 检查是否有附加参数if (!ObjectUtils.isEmpty(parameterMappings)) {for (ParameterMapping parameterMapping : parameterMappings) {String propertyName = parameterMapping.getProperty(); // 获取属性名if (boundSql.hasAdditionalParameter(propertyName)) {Object obj = boundSql.getAdditionalParameter(propertyName); // 获取附加参数if (obj instanceof String) {String escapedValue = escapeSqlSpecialChars((String) obj);// 注意：MyBatis的BoundSql不支持直接修改附加参数// 这里我们记录日志，但不进行实际修改log.debug("Found additional parameter {} that should be escaped, but cannot modify BoundSql additional parameters", propertyName);}}}}}private String getParameterValue(Object parameterObject) {// 如果参数对象为空，返回 "null"if (parameterObject == null) {return "null";}// 返回参数对象的字符串表示return parameterObject.toString();}/*** 转义SQL中的特殊字符* @param value 需要转义的值* @return 转义后的值*/private String escapeSqlSpecialChars(String value) {if (value == null) {return null;}// 转义 % _ 通配符和 \ 转义符return value.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_");}/*** 检查SQL是否是模糊查询语句* @param sql SQL语句* @return 是否是模糊查询*/private boolean isLikeQuery(String sql) {if (sql == null) {return false;}// 忽略大小写检查SQL中是否包含LIKE关键字return sql.toLowerCase().contains(" like ");}@Overridepublic Object plugin(Object target) {// 生成插件对象return Interceptor.super.plugin(target);}@Overridepublic void setProperties(Properties properties) {// 设置属性Interceptor.super.setProperties(properties);}
}

测试：