Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/hellostory/article/details/7266447

问题描述:MySQL进行字符串比较时发生错误:

SELECT
	a.equ_no,
	b.fullCode
FROM
	equipment a,
	(
		SELECT
			t.*, getEquTypeFullCode (t.equType_id) AS fullCode
		FROM
			equ_type t
	) b
WHERE
	substring(a.equ_no, 1, 5) = b.fullCode
错误如下:

[Err] 1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

解决方法:将比较等式一边进行字符串转换,如改为“CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci

SELECT
	a.equ_no,
	b.fullCode
FROM
	equipment a,
	(
		SELECT
			t.*, getEquTypeFullCode (t.equType_id) AS fullCode
		FROM
			equ_type t
	) b
WHERE
	substring(a.equ_no, 1, 5) = CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci;


      

展开阅读全文

Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIB

12-05

在进行MySQL数据库查询时报错rnjava.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='rn com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)rn com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)rn com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1125)rn com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)rn com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)rn com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)rn org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)rn org.hibernate.loader.Loader.getResultSet(Loader.java:1812)rn org.hibernate.loader.Loader.doQuery(Loader.java:697)rn org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)rn org.hibernate.loader.Loader.doList(Loader.java:2232)rn org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)rn org.hibernate.loader.Loader.list(Loader.java:2124)rn org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)rn org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)rn org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)rn org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)rn org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)rn com.student.dao.StudentDaoImpl.loadstu(StudentDaoImpl.java:369)rn sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn java.lang.reflect.Method.invoke(Unknown Source)rn org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)rn org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)rn org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)rn org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)rn org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)rn org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)rn com.sun.proxy.$Proxy43.loadstu(Unknown Source)rn com.student.service.StuServiceImpl.loadstu(StuServiceImpl.java:87)rn com.student.action.StudentAction.loadstu(StudentAction.java:139)rn sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn java.lang.reflect.Method.invoke(Unknown Source)rn com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:452)rn com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:291)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:254)rn com.zFramework.action.PermissionInterceptor.intercept(PermissionInterceptor.java:31)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)rn com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:263)rn org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)rn com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:133)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207)rn com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207)rn com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:243)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:142)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:166)rn com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:190)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)rn com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)rn org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)rn org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:485)rn org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)rn org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)rn org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)rn org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)rn org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)rn org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) 论坛

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

07-20

HTTP Status 500 - rnrn--------------------------------------------------------------------------------rnrntype Exception reportrnrnmessage rnrndescription The server encountered an internal error () that prevented it from fulfilling this request.rnrnexception rnrnjavax.servlet.ServletException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='rn org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:825)rn org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:758)rn org.apache.jsp.login_jsp._jspService(login_jsp.java:417)rn org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)rn javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rn org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)rn org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)rn org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)rn javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rnrnrnroot cause rnrnjava.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='rn com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)rn com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)rn com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1675)rn com.mysql.jdbc.Connection.execSQL(Connection.java:2295)rn com.mysql.jdbc.Connection.execSQL(Connection.java:2228)rn com.mysql.jdbc.Statement.executeQuery(Statement.java:1159)rn bookshop.run.login.excute(login.java:66)rn org.apache.jsp.login_jsp._jspService(login_jsp.java:71)rn org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)rn javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rn org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)rn org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)rn org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)rn javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rnrnrnnote The full stack trace of the root cause is available in the Apache Tomcat/5.0.28 logs.rnrnrn--------------------------------------------------------------------------------rnrnApache Tomcat/5.0.28 论坛

Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

12-24

系统是:linuxrnweb服务器:tomcatrn数据库:mysql 4rnrn查询时抛出以下异常:rnrnjava.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='rn com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)rn com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)rn com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)rn com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)rn com.mysql.jdbc.Connection.execSQL(Connection.java:3176)rn com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)rn com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)rn org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)rn org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)rn org.hibernate.loader.Loader.getResultSet(Loader.java:1272)rn org.hibernate.loader.Loader.doQuery(Loader.java:391)rn org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)rn org.hibernate.loader.Loader.doList(Loader.java:1593)rn org.hibernate.loader.Loader.list(Loader.java:1577)rn org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)rn org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)rn org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)rn org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)rn org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:849)rn org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)rn org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:840)rn org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:836)rn com.kingtop.zj.dao.UserDAO.findByProperty(UserDAO.java:127)rn sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)rn sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)rn java.lang.reflect.Method.invoke(Method.java:585)rn org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)rn org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)rn org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)rn org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)rn org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)rn org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)rn $Proxy19.findByProperty(Unknown Source)rn com.kingtop.zj.action.LoginAction.execute(LoginAction.java:69)rn org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)rn org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)rn org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)rn org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)rn org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)rn javax.servlet.http.HttpServlet.service(HttpServlet.java:709)rn javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rn sun.reflect.GeneratedMethodAccessor1507.invoke(Unknown Source)rn sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)rn java.lang.reflect.Method.invoke(Method.java:585)rn org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:243)rn java.security.AccessController.doPrivileged(Native Method)rn javax.security.auth.Subject.doAsPrivileged(Subject.java:517)rn org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:275)rn org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:161)rn java.security.AccessController.doPrivileged(Native Method)rn org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)rn org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)rn sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)rn sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)rn java.lang.reflect.Method.invoke(Method.java:585)rn org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:243)rn java.security.AccessController.doPrivileged(Native Method)rn javax.security.auth.Subject.doAsPrivileged(Subject.java:517)rn org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:275)rn org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:217)rn这大致的意思我是知道的。= 号两边的编码不一致,数据库中我用的是GBKrn好像=号另一边的是latin编码。但是我在代码中已经设了request.setCharacterEncoding("gbk");rn为什么还会出现这样不匹配的情况呢。而且我在web.xml文件中也设了字符编码的过滤啊。rn找了很久,实在没法了。各们达人请帮帮忙,卡在这已经一天了,心急如火啊!!! 论坛

没有更多推荐了,返回首页