问题Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)……的处理

1. 问题描述

• Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)……
• 查询的一个sql报了错，大致的意思就是sql里面的表混杂了utf8mb4_unicode_ci和utf8mb4_general_ci这两种排序字符集。
• sql里面是有join了多张表，可以通过下面的sql查看该表的排序字符集等信息
  • show table status from 数据库名 like '数据库表名'

2. 问题解决

• 我采取的措施是统一字符集。在和大佬们讨论了之后，现在没有使用mb4的需求，于是和线上的字符集统一，使用utf8_general_ci
• 参考：mysql 批量修改 表字段/表/数据库 字符集和排序规则
• 依次把表字段，表，数据库的字符集修改

(1). 改表字段

• ‘CHARACTER SET utf8 COLLATE utf8_general_ci;’ 这里是设置自己想要的字符集；"WHERE TABLE_SCHEMA = ‘数据库名’;"这里是你要过滤的条件，比如你要过滤的数据库名

  #改变字段数据
  SELECTTABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8 COLLATE utf8_general_ci;' ) '修正SQL' 
  FROMinformation_schema.`COLUMNS` 
  WHERETABLE_SCHEMA = '数据库名';
  

• 然后把“修正SQL”那一列复制出来拿去跑就行了。

(2). 改数据库表的字符集

#改变表
SELECTTABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' COLLATE=utf8_general_ci;' ) '修正SQL' 
FROMinformation_schema.`TABLES`
WHERETABLE_SCHEMA = '数据库名';

(3). 改数据库字符集

#修改数据库
SELECTSCHEMA_NAME '数据库',DEFAULT_CHARACTER_SET_NAME '原字符集',DEFAULT_COLLATION_NAME '原排序规则',CONCAT( 'ALTER DATABASE ', SCHEMA_NAME, ' CHARACTER SET utf8 COLLATE utf8_general_ci;' ) '修正SQL' 
FROMinformation_schema.`SCHEMATA` 
WHERESCHEMA_NAME = '数据库名';

3. 问题校验

• 再次执行1中的sql，观察表的字符集是否改变
• 再次执行sql，sql运行成功