Spring Data JPA使用collate binary_ci时LIKE多映射参数查询报错

构建接收2个入参、使用collate binary_ci排序规则的Spring Data JPA自定义查询，需求规则如下：

• 一个入参通过=运算符做字符串等值匹配
• 另一个入参通过LIKE运算符做字符串模糊匹配

未添加collate binary_ci时，可正常运行的查询示例如下：

SELECT * FROM MYTABLE WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) ORDER BY ...

上述查询中LIKE对应的命名参数:in被复用到多个条件位置，但添加collate binary_ci后查询始终无法正常执行。尝试过的错误写法如下：

-- 写法1
WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) collate binary_ci ORDER BY ...
-- 写法2
WHERE ID = :id AND (MODEL LIKE %:in% OR DESCR LIKE %:in%) collate binary_ci ORDER BY ...
-- 写法3
WHERE ID = :id AND ((MODEL LIKE %:in%) collate binary_ci OR (DESCR LIKE %:in%) collate binary_ci) ORDER BY

运行上述写法的查询时，对应抛出的异常分别为：

• 写法1：Could not locate named parameter [in], expecting one of [in%, id]
• 写法2：sql statement was not ended properly
• 写法3：missing right parentheses

• Spring Boot版本：v2.4.3
• SQL Developer相关版本信息：

org.openide.specification.version   6.2
org.osgi.framework.os.version   10.0.0
org.osgi.framework.version  1.7.0
os.version  10.0
osgi.framework.version  3.9.1.v20140110-1610

1. SQL语法错误：COLLATE关键字只能作用于字符串类型的列/值表达式，不能直接作用于括号包裹的布尔判断结果，直接给整个LIKE判断组加collate binary_ci不符合SQL语法规范，会触发语句未结束、括号缺失类的解析错误。
2. JPA参数解析冲突：当%:in%占位符后紧接其他SQL关键字时，Spring Data JPA的命名参数解析器会错误截断参数名，把in%识别为合法参数名，最终抛出找不到命名参数in的异常。

方案1：业务层拼接通配符（兼容性最好） #

将COLLATE直接挂载到需要做大小写不敏感匹配的列后，移除查询语句中占位符两侧的通配符，在业务代码层拼接%通配符，完全规避JPA参数解析问题。
Repository层查询写法：

SELECT * FROM MYTABLE 
WHERE ID = :id 
  AND (
    MODEL COLLATE binary_ci LIKE :in 
    OR DESCR COLLATE binary_ci LIKE :in
  ) 
ORDER BY ...

业务层调用示例：

// 给模糊查询参数拼接前后通配符
String searchKey = "%" + inputSearchValue + "%";
List<MyEntity> result = repository.queryByIdAndSearchKey(id, searchKey);

方案2：用CONCAT函数拼接通配符（无需修改业务传参） #

如果不想调整业务层传参逻辑，可以在SQL中用CONCAT函数拼接通配符和入参，让参数占位符前后不直接拼接其他字符，保证JPA能正确识别参数，同时COLLATE作用位置符合SQL规范。
Repository层查询写法：

SELECT * FROM MYTABLE 
WHERE ID = :id 
  AND (
    MODEL COLLATE binary_ci LIKE CONCAT('%', :in, '%')
    OR DESCR COLLATE binary_ci LIKE CONCAT('%', :in, '%')
  ) 
ORDER BY ...

这种写法直接传入原始的模糊查询关键词即可，不需要额外拼接通配符。

注：如果ID字段的等值匹配也需要大小写不敏感，可以给ID字段也加上COLLATE binary_ci修饰，写法为ID COLLATE binary_ci = :id

内容的提问来源于stack exchange，提问作者chocalaca