MySQL、MyBatis——Illegal mix of collations for operation 'like'

 

当模糊查询为或，不论是“时间类型的字段”还是“字符串类型字段”，都可以查到对应列表的所有值，这样看的话，问题是不存在的~

其实有错误： Illegal mix of collations for operation 'like'

select nd.*,emp.empId as id,emp.empName from NoticeDetail nd inner join employee emp on emp.empId=nd.empId  
			where title like concat('%','李','%') or content LIKE concat('%','李','%') or noticeTime like concat( '%','李','%')
			order by CONVERT(emp.empName USING gbk) asc
			limit 0,5;

用于MyBatis的话，也会报错：

三月 09, 2019 10:11:09 上午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [SpringMVC] in context with path [/HTGlory] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: java.sql.SQLException: Illegal mix of collations for operation 'like'
### The error may exist in file [F:\ALL_Course\10Activiti\Ecli\.metadata\.plugins\org.eclipse.wst.server.core\tmp4\wtpwebapps\HTGlory\WEB-INF\classes\com\ht\qyf\mapper\NoticeDetailDAO.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select nd.*,emp.empId as id,emp.empName from NoticeDetail nd inner join employee emp on emp.empId=nd.empId    where title like concat('%',?,'%') or content LIKE concat('%',?,'%') or noticeTime like concat( '%',?,'%')    order by CONVERT(title USING gbk) desc    limit ?,?;
### Cause: java.sql.SQLException: Illegal mix of collations for operation 'like'
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1271]; Illegal mix of collations for operation 'like'; nested exception is java.sql.SQLException: Illegal mix of collations for operation 'like'] with root cause
java.sql.SQLException: Illegal mix of collations for operation 'like'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3005)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3003)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:136)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
	at com.sun.proxy.$Proxy41.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
	at com.sun.proxy.$Proxy14.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:195)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
	at com.sun.proxy.$Proxy31.listNoticeDetailPage3(Unknown Source)
	at com.ht.qyf.service.NoticeDetailService.listPageIf2(NoticeDetailService.java:40)
	at com.ht.qyf.service.NoticeDetailService$$FastClassBySpringCGLIB$$4c18c4e.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
	at com.ht.qyf.service.NoticeDetailService$$EnhancerBySpringCGLIB$$bdc92ed3.listPageIf2(<generated>)
	at com.ht.qyf.controller.NoticeDetailController.list(NoticeDetailController.java:62)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:777)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:706)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)

发现，时间不能：这样！！！ 所以删除“时间的模糊查询”（因人而异啦，对我来说，还是删除 时间的模糊查询好了！！！想实现时间的模糊查询，恐怕是在MyBatis里写if判断了。。。【反正我放弃了时间的模糊查询】）

我的MySQL是5.5版的！！！ 这样模糊查询（5.5版及以上），也是可以的！！！

select nd.*,emp.empId as id,emp.empName from NoticeDetail nd inner join employee emp on emp.empId=nd.empId  
			where title like concat('%李%') or content LIKE concat('%李%')

总之，

 like concat('%','','%')或者like concat('','%')或者like concat('%','')
 like concat('%','4（数字）','%')或者like concat('4（数字）','%')或者like concat('%','4（数字）')

（当查询条件为“空”或者“含数字的字符串”）可以给“时间类型的字段”、“字符串类型的字段”模糊查询，

like concat('%','李','%')或like concat('李','%')或like concat('%','李')
【“李”的前、后 模糊查询】或【“李”的后 模糊查询】或【“李”的前 模糊查询】

（当查询条件为“含中文的字符串”）只能给“字符串类型的字段”模糊查询 

参考：

MySQL Illegal mix of collations for operation 'like'

补充：

还是LIKE+order by查询的效率问题,关于怎么确定使用哪个索引,怎么决定是否使用全表扫描

mysql中orderby和limit同时使用的bug

解决Mysql错误提示Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

补知识：

sql 语句用substr 不用 like 可以提升查询效率

substr和like那个查询的速度快？

sql按中文数字排序

SQL数据表中按中文数字一二三四五排序