Illegal mix of collations

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 数据库名.表名
   

   • 
   • 可以看出目前是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