字符集转换问题

问题现象

业务部分表表结构字段指定字符集定义是 utf8mb4_general_ci，部分表是 utf8mb4_unicode_ci，业务 SQL 跑多个表字符集不一样的表做查询关联表报错，找出相关表修改字符集类型作 alter table modify，目前不支持 alter table modify 去修改所以报错。

mysql> CREATE TABLE `table1` (
       `pk_id` bigint(20) ,
       `notes` varchar(128) COLLATE utf8mb4_general_ci,
       PRIMARY KEY (`pk_id`)
     );
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE `table2` (
       `pk_id` bigint(20) ,
       `notes` varchar(128) COLLATE utf8mb4_unicode_ci,
       PRIMARY KEY (`pk_id`)
     );
Query OK, 0 rows affected (0.21 sec)

mysql> select * from table1 as t1 JOIN table2 as t2 on t1.notes= t2.notes;
ERROR 1267 (HY000): Illegal mix of collations
mysql> alter table table1 modify  notes varchar(128) character set utf8mb4 collate utf8mb4_unicode_ci;
ERROR 1235 (0A000): Alter charset or collation type not supported

最小化用例复现。

关键诊断信息

触发条件

多个表的字符集不同。

问题原因

多个表的字符集不同导致做查询关联表和修改字符集类型报错。

问题的风险及影响

业务查询报错和无法修改字符集类型。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

影响版本

OceanBase 数据库企业版 V3.2.3 GA（oceanbase-3.2.3.0-20220418212020）及之后版本。

解决方法及规避方式

1. 连接条件字段前指定 COLLATE utf8mb4_unicode_ci，如果是子查询用 COLLATE utf8mb4_unicode_ci = (SELECT * FROM table)。

   mysql> select * from table1 as t1 JOIN table2 as t2 on t1.notes= t2.notes;
   ERROR 1267 (HY000): Illegal mix of collations
   mysql> select * from table1 as t1 JOIN table2 as t2 on t1.notes COLLATE utf8mb4_unicode_ci = t2.notes;
   Empty set (0.06 sec)
   

2. 建新表指定和其他表相同字符集，然后 insert into select 数据到新表。