1、查看数据库字符集:
show variables like '%collation%';
2、查看表A、表B、表C的字符集:
show full columns from 表名;
show table status from 库名 like '表名';
表A的字符集是utf8mb4_general_ci、表B的字符集是utf8mb4_general_ci、表C的字符集是utf8mb4_unicode_ci、
3、带来的问题
表A和表B关联查询时应该字符集不统一返回失败:[HY000][1267] Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
4、解决办法
- 临时解决方案:在查询时指定字符集(不推荐,因为字符集不统一,table_c不能使用索引,如果数据量很大时,查询速度很慢)
select a.file_key, c.biz_id, c.create_time, c.status
from table_a t, table_b c ,table_c a
where t.template_id = c.template_id
and t.template_code = 't_001'
and c.customer_id ='1234323454323432'
and c.customer_id=a.customer_id collate utf8mb4_unicode_ci
and a.biz_id collate utf8mb4_unicode_ci =c.biz_id
order by c.create_time desc
limit 1;
- 永久解决,修改表字符集(推荐)
alter table 表名 convert to character set utf8mb4 collate utf8mb4_general_ci;
项目规范一定要建立,该统一的一定要统一。