记MySQL查询视图出现字符集编码不一致问题

一、背景

        由于在项目中，有每天跑批处理大量的数据的需求，报表字段的取值原来多个业务表，为防止以后有字段取值来源变更，或者业务字段的调整，不用修改业务系统的代码，因此采用视图的方式，不用发版，只修改视图就可以完成取值，确定好方案后，按此方式开发，在测试环境和准上线环境测试没问题之后上线，在上线之后跑批失败；

1、错误信息：

Caused by: org.springframework.jdbc.UncategorizedSQLException: Executing query; uncategorized SQLException for SQL [ SELECT * FROM TEMP_TAVLE WHERE 1 = 1  and 
column='0']; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

2、代码中sql写法

select * from temp_table where 1=1 and column='0';

二、排查思路

1、查看错误日志

        通过日志 nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '=' 可以看到，sql语句“=” 前后的字符集编码不同，一个是utf8mb4_0900_ai_ci，另一个是utf8mb4_general_ci，由此可以定位到，应该是字段column字段的问题，所以看看这个字段在源数据表的字符集编码，还有通过视图查询的时候显示的字符集编码是什么；

2、查看源数表字段字符集编码

select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.COLLATION_NAME 列字符集排序规则,b.TABLE_COLLATION 表字符集排序规则 from information_schema.columns a,information_schema.tables b where a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME and a.TABLE_NAME in('表名1','表名2');

3、查询结果

由此图可以看到源业务表的字段的字符集编码规则，然后去对视图里的字符集编码规则查询方式和源业务表相同；

三、解决方案

        从报错日志可以看出，主要问题是字符集编码规则问题，因此我们需要将字段的字符集编码统一，字符集编码规则设置方式可以分两种，具体情况如下：

方式一

更改源业务表字符集编码规则或者建表的时候指定字符集编码规则

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

方式二

视图指定查询字段的字符集编码规则，改成符合自己业务的字符集

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

四、验证

将字符集编码规则修改一致，重新跑批正常，数据也能正常入库；