在做多条件模糊查询的时候,出现了 java.sql.SQLException: Illegal mix of collations for operation 'like' 异常,后经过排查发现为数据库版本问题。
解决方法 :
编码格式不统一(只要针对中文汉字),数据库版问题,因此我们需要在like
后面添加关键字binary即可。
<select id="getAllKuCun" resultType="Map">
select * from jinxiang_repository where
<if test="condition1!=''">
code like binary concat(concat("%",#{condition1}),"%") or
name like binary concat(concat("%",#{condition1}),"%") or
specifications like binary concat(concat("%",#{condition1}),"%") or
brand like binary concat(concat("%",#{condition1}),"%") or
source like binary concat(concat("%",#{condition1}),"%") or
measurement like binary concat(concat("%",#{condition1}),"%") or
total like binary concat(concat("%",#{condition1}),"%") or
price like binary concat(concat("%",#{condition1}),"%") or
money like binary concat(concat("%",#{condition1}),"%") or
date like binary concat(concat("%",#{condition1}),"%") or
contractNumber like binary concat(concat("%",#{condition1}),"%") or
annotation like binary concat(concat("%",#{condition1}),"%") or
validity like binary concat(concat("%",#{condition1}),"%") and
</if>
<if test="condition2!=''">
type = #{condition2} and
</if>
1=1
ORDER BY id DESC
</select>