MyBatis like %#{name}%的问题及解决

问题背景

MyBatis配置文件如下：

<select id="findByUsername" resultType="User">
  SELECT * FROM User WHERE username LIKE '%#{username}%'
</select>

可以看到，配置文件里面，对SQL的处理，将%占位符与#{}占位符作为一个整体，直接拼接了起来，并且对username这个字段，进行前缀和后缀的模糊匹配。且同时在这个场景中，User表中存在有username列的索引。

这样做，会导致2个问题：

1. username列索引失效。
2. SQL语句生成异常。

索引失效

先说明下，索引失效的问题。

首先需要知道，B+树对列值的数据布局，是根据前缀来有序排序的。所以如果使用前缀模糊查找，就会导致无法根据B+树数据的有序性，从而找到索引树的入口，最终导致索引树无法被使用，即，索引失效。

解决方式：

1. 使用全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX idx_username ON users(username);
-- 使用全文索引进行查询
SELECT * FROM users WHERE MATCH(username) AGAINST('John');

SQL语句生成异常

然后说明下，SQL语句生成异常的问题。

首先需要知道，对#{}进行字符串拼接，会导致#{}无法绑定参数。

这是因为，Mybatis是通过’?‘去判断SQL语句中是否存在有需要参数替换的情况的，所以当这个’?‘的前后被其它字符串包裹时，比如‘%?’，那么对于Mybatis来说，它是无法判断这到底是个实际的参数值，还是一个需要替换的参数占位符。那么在这种情况下，Mybatis是不会对‘%?‘进行参数替换的，也就是，在最终动态生成的SQL语句中，传给数据库的SQL语句依然会是‘%?’，而这显然是不符合预期的。

举例如下：

MyBatis配置文件：

<select id="findByUsername" resultType="User">
  SELECT * FROM User WHERE username LIKE '%#{username}%'
</select>

动态生成的SQL语句：

SELECT * FROM User WHERE username LIKE '%?%'

最后传给数据库的SQL语句，也依然会是：

SELECT * FROM User WHERE username LIKE '%?%'

解决方式：

1. CONCAT函数

使用CONCAT函数，来拼接%占位符和#{}参数占位符。

<select id="findUsersByName" parameterType="String" resultType="User">
    SELECT * FROM users WHERE username LIKE CONCAT('%', #{username}, '%')
</select>

2. 手动拼接%占位符

将%占位符和参数拼接好在传入#{}参数占位符中。

Java调用接口：

public interface UserMapper {
  List<User> findByUsername(@Param("username") String username);
}
List<User> users = userMapper.findByUsername("%john%");

MyBatis配置文件：

<select id="findUsersByName" parameterType="String" resultType="User">
    SELECT * FROM users WHERE username LIKE #{username}
</select>

最终动态生成的SQL语句为：

SELECT * FROM users WHERE name LIKE '%john%'