Mysql 视图查询 编码格式异常 ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci

一、问题背景

1、线上库存在一张表原始表table_A ，查询A是正常的

select * from table_A limit 10;

2、创建视图表table_B，创建正常

CREATE OR REPLACE VIEW table_B
as
select 
id
,businiss_dt
,if(user_number=0,NULL,user_number) as user_number
from table_A
where businiss_dt > date_format(DATE_ADD(CURDATE(), INTERVAL -1 MONTH),'%Y%m%d');

3、查询视图表B，报错

select * from table_B limit 10;


ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '>'

二、问题排查

1、查看原始表编码格式

show create table table_A;

CREATE TABLE `table_A` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `businiss_dt` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '19700101' COMMENT '日期',
  `user_number` bigint(20) DEFAULT '0' COMMENT '用户编号',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT =1 DEFAULT CHARSET = utf8mb4  COMMENT = '数据表'

发现原始表缺少编码

2、改变表的编码格式，同时也需要变更数据编码格式

alter table table_A default character set utf8mb4 collate=utf8mb4_general_ci;