精华内容
下载资源
问答
  • Collations
    2019-10-02 04:52:46

    3. COLLATIONS

    COLLATIONS提供有关每个字符集排序规则的信息。下表中SHOW Name对应SHOW COLLATION

    INFORMATION_SCHEMA NameSHOW NameRemarks
    COLLATION_NAMECollation
    CHARACTER_SET_NAMECharsetMySQL extension
    IDIdMySQL extension
    IS_DEFAULTDefaultMySQL extension
    IS_COMPILEDCompiledMySQL extension
    SORTLENSortlenMySQL extension

    COLLATIONS有以下列:

    • COLLATION_NAME:排序规则名称
    • CHARACTER_SET_NAME:与排序规则关联的字符集的名称。
    • ID:排序规则ID
    • IS_DEFAULT:排序规则是否为其字符集的默认值
    • IS_COMPILED:字符集是否编译到服务器中。
    • SORTLEN:这与对字符集中表示的字符串进行排序所需的内存量有关。

    示例

    mysql> SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME LIKE 'utf8mb4%';
    +------------------------+--------------------+-----+------------+-------------+---------+
    | COLLATION_NAME         | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
    +------------------------+--------------------+-----+------------+-------------+---------+
    | utf8mb4_general_ci     | utf8mb4            |  45 | Yes        | Yes         |       1 |
    | utf8mb4_bin            | utf8mb4            |  46 |            | Yes         |       1 |
    | utf8mb4_unicode_ci     | utf8mb4            | 224 |            | Yes         |       8 |
    | utf8mb4_icelandic_ci   | utf8mb4            | 225 |            | Yes         |       8 |
    | utf8mb4_latvian_ci     | utf8mb4            | 226 |            | Yes         |       8 |
    | utf8mb4_romanian_ci    | utf8mb4            | 227 |            | Yes         |       8 |
    | utf8mb4_slovenian_ci   | utf8mb4            | 228 |            | Yes         |       8 |
    | utf8mb4_polish_ci      | utf8mb4            | 229 |            | Yes         |       8 |
    | utf8mb4_estonian_ci    | utf8mb4            | 230 |            | Yes         |       8 |
    | utf8mb4_spanish_ci     | utf8mb4            | 231 |            | Yes         |       8 |
    | utf8mb4_swedish_ci     | utf8mb4            | 232 |            | Yes         |       8 |
    | utf8mb4_turkish_ci     | utf8mb4            | 233 |            | Yes         |       8 |
    | utf8mb4_czech_ci       | utf8mb4            | 234 |            | Yes         |       8 |
    | utf8mb4_danish_ci      | utf8mb4            | 235 |            | Yes         |       8 |
    | utf8mb4_lithuanian_ci  | utf8mb4            | 236 |            | Yes         |       8 |
    | utf8mb4_slovak_ci      | utf8mb4            | 237 |            | Yes         |       8 |
    | utf8mb4_spanish2_ci    | utf8mb4            | 238 |            | Yes         |       8 |
    | utf8mb4_roman_ci       | utf8mb4            | 239 |            | Yes         |       8 |
    | utf8mb4_persian_ci     | utf8mb4            | 240 |            | Yes         |       8 |
    | utf8mb4_esperanto_ci   | utf8mb4            | 241 |            | Yes         |       8 |
    | utf8mb4_hungarian_ci   | utf8mb4            | 242 |            | Yes         |       8 |
    | utf8mb4_sinhala_ci     | utf8mb4            | 243 |            | Yes         |       8 |
    | utf8mb4_german2_ci     | utf8mb4            | 244 |            | Yes         |       8 |
    | utf8mb4_croatian_ci    | utf8mb4            | 245 |            | Yes         |       8 |
    | utf8mb4_unicode_520_ci | utf8mb4            | 246 |            | Yes         |       8 |
    | utf8mb4_vietnamese_ci  | utf8mb4            | 247 |            | Yes         |       8 |
    +------------------------+--------------------+-----+------------+-------------+---------+
    26 rows in set (0.00 sec)
    
    
    mysql> show collation like 'utf8mb4%';
    +------------------------+---------+-----+---------+----------+---------+
    | Collation              | Charset | Id  | Default | Compiled | Sortlen |
    +------------------------+---------+-----+---------+----------+---------+
    | utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
    | utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
    | utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
    | utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
    | utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
    | utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
    | utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
    | utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
    | utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
    | utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
    | utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
    | utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
    | utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
    | utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
    | utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
    | utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
    | utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
    | utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
    | utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
    | utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
    | utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
    | utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
    | utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
    | utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
    | utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
    | utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
    +------------------------+---------+-----+---------+----------+---------+
    26 rows in set (0.00 sec)
    

    转载于:https://www.cnblogs.com/wanbin/p/9514647.html

    更多相关内容
  • mysql数据库和中文支持很不友好,经常见到“Illegal mix of collations for operation”错误,该如何解决呢?下面小编给大家带来了mysql数据库中涉及到哪些字符集及彻底解决中文乱码的解决方案,非常不错,一起看看...
  • Illegal mix of collations

    2021-05-27 19:08:42
    1、背景 在做关联查询时候出现问题 ...Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.md 2、分析 由上面字面意思就是 utf8mb4_unicode_

    1、背景

    • 在做关联查询时候出现问题

    • select * from A where ref_b_id = (select id from b where user_id='11');
      
    • 将会提示你如下错误

    • Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.md
      

    2、分析

    1. 由上面字面意思就是 utf8mb4_unicode_ci 和 utf8mb4_general_ci 混合使用,同时也说明不能混合使用

    2. collations关键字是排序规则,a和A同时存在,顺序应该是aA还是Aa, 或者说a==A? 不区分大小吗?

      • 1、为啥需要存在collations? 它解决什么问题?
        
        		第一点世界上有很多种语言,语言差异很大,比如中文和日元有相似地方,但是不一样,
        		所有不同字符集去编码对应语言,比如现在已经编码好保存到数据库了,我们现在需要查询,
        		数据库怎么知道那条数据算第一条数据,数据库肯定是对所有数据进行比较,
        		最常见就是ASCII的字符代表数字大小,还有一些情况我们就是认为区分大小(默认是不区分)a A是不一样,
        		这些字符的比较工作需要有评判规则,而这些规则就是collations所做的事情。
        
        
    3. 不同字符比较规则,自然就不能关联比较了,也就不能用等号,日语的某些汉字和中文汉字虽然展示是一样但是实际编码规则是不一样

    3、解决方法

    • 解决方式:让两个表统一一下collations

    3.1、怎么统一collations

    1. 如何查询表使用哪个collations

      SHOW FULL COLUMNS FROM 数据库名.表名
      
      • image-20210527150548543
      • 可以看出目前是collation的是utf8mb4_general_ci
    2. 如何查看当前数据库schema的collation?

      • SHOW VARIABLES LIKE 'collation_database';
        
    3. 如何修改当前数据库默认collation?例如修改为 utf8mb4_unicode_ci

      • ALTER DATABASE COLLATE utf8mb4_unicode_ci;
        
    4. 如何修改当前表默认collation? 例如修改为 utf8mb4_unicode_ci

      • ALTER TABLE t_application_ocr_result  COLLATE utf8mb4_unicode_ci;
        
    5. 如何修改某个表的字段默认collation 例如修改user_id为 utf8mb4_unicode_ci

      • ALTER TABLE t_application_ocr_result  MODIFY  user_id varchar(28) COLLATE utf8mb4_unicode_ci;
        
        
    6. 查看数据库表死锁记录

      • SHOW ENGINE INNODB STATUS; 
        SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
        

    3、参考

    1. 如何使得mysql不区分大小写?:How can I make SQL case sensitive string comparison on MySQL?
    2. Mysql的collation是什么?https://www.quora.com/What-is-collation-in-MySQL
    3. MySQL查看、修改字符集及Collation
    展开全文
  • @[TOC](java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’)报错原因: 字符集不统一导致 解决办法: 1:修改其中的一个字符集...

    @[TOC](java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’)

    报错原因:
    字符集不统一导致
    解决办法:
    1:修改其中的一个字符集,对于表结构进行更改。
    2:在其中的一个列后面加上COLLATE utf8mb4 unicode ci/utfa8mb4 general ci进行指定。
    注意:这种情况是不应该出现的,在进行表结构设计更改时,字符集应该统一。

    org.springframework.jdbc.UncategorizedSQLException: 
    ### Error querying database.  Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
    ### The error may exist in class path resource [mapper/DjswInvoiceMapDao.xml]
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: SELECT count(0) FROM (SELECT a.invoice_id, purchaser, credit_code, invoice_type, price_and_tax, invoice_code, invoice_no, billing_time, invoice_month, goods_code, goods_name, valid_flag, create_by, create_date, modify_by, modify_date, city_code, status, network_main_body_id, network_main_body_name, apply_invoice_no, waybill_no FROM djsw_invoice_map a LEFT JOIN djsw_invoice_map_detail b ON b.invoice_id = a.invoice_id WHERE billing_time >= ? AND billing_time <= ? GROUP BY a.invoice_id) table_count
    ### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
    ; uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    	at com.sun.proxy.$Proxy140.selectList(Unknown Source) ~[?:?]
    	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at com.sun.proxy.$Proxy192.queryPage(Unknown Source) ~[?:?]
    	at com.isoftstone.hig.tax.service.impl.DjswInvoiceMapServiceImpl.getList(DjswInvoiceMapServiceImpl.java:197) ~[classes!/:1.0]
    	at com.isoftstone.hig.tax.service.impl.DjswInvoiceMapServiceImpl$$FastClassBySpringCGLIB$$8fb882c1.invoke(<generated>) ~[classes!/:1.0]
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) ~[spring-tx-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at com.isoftstone.hig.tax.service.impl.DjswInvoiceMapServiceImpl$$EnhancerBySpringCGLIB$$8dbf3133.getList(<generated>) ~[classes!/:1.0]
    	at com.isoftstone.hig.tax.service.controller.DjswInvoiceMapController.queryInvoiceMapPageInfo(DjswInvoiceMapController.java:130) [classes!/:1.0]
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_342]
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_342]
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_342]
    	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_342]
    	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:665) [javax.servlet-api-4.0.1.jar!/:4.0.1]
    	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) [spring-webmvc-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) [javax.servlet-api-4.0.1.jar!/:4.0.1]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-embed-websocket-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123) [druid-1.1.9.jar!/:1.1.9]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:88) [spring-boot-actuator-2.1.8.RELEASE.jar!/:2.1.8.RELEASE]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114) [spring-boot-actuator-2.1.8.RELEASE.jar!/:2.1.8.RELEASE]
    	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104) [spring-boot-actuator-2.1.8.RELEASE.jar!/:2.1.8.RELEASE]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118) [spring-web-5.1.9.RELEASE.jar!/:5.1.9.RELEASE]
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_342]
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_342]
    	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.24.jar!/:9.0.24]
    	at java.lang.Thread.run(Thread.java:750) [?:1.8.0_342]
    Caused by: java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.17.jar!/:8.0.17]
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar!/:8.0.17]
    	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.17.jar!/:8.0.17]
    	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.17.jar!/:8.0.17]
    	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.17.jar!/:8.0.17]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051) ~[druid-1.1.9.jar!/:1.1.9]
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.9.jar!/:1.1.9]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) ~[druid-1.1.9.jar!/:1.1.9]
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.9.jar!/:1.1.9]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) ~[druid-1.1.9.jar!/:1.1.9]
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) ~[druid-1.1.9.jar!/:1.1.9]
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~[druid-1.1.9.jar!/:1.1.9]
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_342]
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_342]
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_342]
    	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_342]
    	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at com.sun.proxy.$Proxy245.execute(Unknown Source) ~[?:?]
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:138) ~[pagehelper-5.1.6.jar!/:?]
    	at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:148) ~[pagehelper-5.1.6.jar!/:?]
    	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:97) ~[pagehelper-5.1.6.jar!/:?]
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at com.sun.proxy.$Proxy243.query(Unknown Source) ~[?:?]
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.6.jar!/:3.4.6]
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_342]
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_342]
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_342]
    	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_342]
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
    
    展开全文
  • Illegal mix of collations for operation ‘UNION‘ 从字面意思看,说什么非法混合,没说什么原因。我考察了一下我那条SQL语句,是从不同的表中读取,然后将结果用“union all”进行合并。字段有数值型也有字符型...

    字符集不同报错,使用union汇总结果集的两个表,字符集需要一致。如果表中已有记录,那么调整字符集,现有记录不会产生效果,这时需要重新插入记录,或改变字段长度,迫使内容重写。

    最近在mysql的sql中使用union,总是报错,提示

    Error Code: 1271. Illegal mix of collations for operation ‘UNION‘
    

    从字面意思看,说什么非法混合,没说什么原因。我考察了一下我那条SQL语句,是从不同的表中读取,然后将结果用“union all”进行合并。字段有数值型也有字符型。如果将字符型全部去掉,可正常执行,否则出错。

            select * from (
                  select
                      a.userid as id, a.userid as user_id,a.loginname as login_name, 1 as type, a.unitid as owner_id, 1 as status ,b.unitname as owner_name
                  from org_user a inner join org_unit b on a.unitid=b.unitid and a.userstatus=0
                  union all
                  select
                      a.id, a.user_id,a.login_name, a.type, a.owner_id, a.status,b.name as owner_name
                  from work_user a inner join work_club b on a.owner_id=b.id and a.type=0 and a.status=1
                  union all
                  select
                      a.id, a.user_id, a.login_name,a.type, a.owner_id, a.status,b.club_name as owner_name
                  from work_user a inner join work_register_apply b on a.login_name=b.login_name and a.type=0 and a.status=0
              ) u where user_id=1000 limit 1
    

    猜测应该是字符集的问题。mysql比较奇怪,每个表可以单独指定字符集(也许是每个字段都能单独指定字符集?试了一下,还真是)。
    在这里插入图片描述
    于是修改相应的字符集

    ALTER TABLE `beian`.`tmp_work_club` 
    CHANGE COLUMN `NAME` `NAME` VARCHAR(120) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_as_ci' NOT NULL COMMENT '俱乐部名称' ;    
    

    结果还是报错。真难伺候。世人爱mysql爱得那么狂热,没理由。

    想起被调整的表已存在记录,估计是表字符集虽然变了,但现有的值的字符集并没有修改。于是将字段的长度调了一下,目的是迫使重写表数据。最后问题解决。

    调整字段长度可能不是一个好的方法。也许将表移到临时表,然后再重新插入更好一些。但自增的字段比较麻烦。

    展开全文
  • select company_name from upload_record_detail where ...[HY000][1270] Illegal mix of collations (utf8_general_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE), (utf8mb4_0900_ai_ci,COERCIBLE) for operati
  • Illegal mix of collations,pymysql.err.OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")
  • mysql联查报错 Illegal mix of collations
  • select * from ...Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' 查询语句中,,如上异常为两个表的编码格式不同,导致同为varc
  • 问题:Mysql执行CONCAT函数报错(Illegal mix of collations for operation 'concat') 原因:CONCAT中个别字段字符集不统一 解决:找到是哪几个字段导致的,然后是使用字段类型转换函数convert(xxx字段 USING utf...
  • 报错:Illegal mix of collations

    千次阅读 2021-04-26 11:52:10
    服务端报错:Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 原因 这个错误直白的翻译过来就是:2个字段的编码...
  • 在使用union语句时,出现错误 Illegal mix of collations for operation 'UNION' 原因是union语句会将两集合进行排序,此时每个字段的排序规则应该一样。 如果不一样如下: 两个排序规则不一样就会报错。
  • molindo-mysql-collat​​ions-lib JNI桥接到libmysqlclient(MySQL的C客户端API)提供MySQL基于归类的比较功能。 见上了解详情。... <artifactId>molindo-mysql-collations-lib <version>0.1.0 </dependency>
  • Mybatis Illegal mix of collations for operation “UNION”错误
  • 排序规则(Collations)是一组用于比较字符集中字符的规则。 MySQL使用字符集存储字符串,使用排序规则比较字符串。字符集和排序规则可以指定到列上,同一个表的各个列可以是不同的字符集和排序规则。 字符集和排序...
  • 问题: 报错:1267- illegal mix of collations for operation ‘=’ 原因: MySQL 8.0之后,默认collation不再像之前版本⼀样是是utf8mb4_general_ci,⽽是统⼀更新成了utf8mb4_0900_ai_ci。 因为联查两个表的字段...
  • 在利用DVWA平台复现sql注入时。 sql语句: 1' union select 1,group_concat(table_name) from information_schema.tables where table_schema=database()# union报错,在该种情形下,在PHPStudy中安装phpmyadmin...
  • 在项目迁移之后一个视图的访问出现了问题,报了mysql的1267错误,具体报错信息就是SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,...
  • mybatis使用INSTR的时候报错(数据库为MySQL)—— Illegal mix of collations (XXX) and (XXX) for operation 'XXX’ – Cause: java.sql.SQLException: Illegal mix of collations (utf8_bin,NONE) and (utf8mb4_...
  • Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' 原因 utf8mb4_unicode_ci和utf8mb4_general_ci列不能混合查询 排查 $ show full columns from tb_...
  • 使用了union注入时报错如下:Illegal mix of collations for operation ‘UNION payload:http://127.0.0.1/sqli-labs/Less-1/?id=-1' union select 1,2,group_concat(table_name) from information_schema.tables ...
  • 在练习DVWA中的sql注入时,在注入1’ union select table_name,table_schema from information_schema.tables where table_schema=‘dvwa’#时,遇到了 llegal mix of collations for operation 'UNION’报错的问题...
  • 当模糊查询为或,不论是“时间类型的字段”还是“字符串类型字段...其实有错误:Illegal mix of collations for operation 'like' select nd.*,emp.empId as id,emp.empName from NoticeDetail nd inner join e...
  • mysql版本的原因,sql中的日期格式有问题,5.7版本以下会报Illegal mix of collations for operation ‘>=’ 修改下日期的格式即可。
  • fetch_array()的错误,结果发现关键点还在与Illegal mix of collations for operation 'UNION' 这是由于新创建的数据库和information_schema数据库编码不同造成的 information_schema的编码是utf8_general_ci 而我们...
  • mysql> show variables like "%char%";+--------------------------+-------------------------------------+| Variable_name | Value |+--------------------------+-...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,953
精华内容 1,981
关键字:

Collations