mysql查询异常 java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation ‘=’

异常原因：关联表的字段排序索引不同，批量修改全库的字符型字段的排序规则为Unicode：

SELECT
	TABLE_SCHEMA '数据库',
	TABLE_NAME '表',
	COLUMN_NAME '字段',
	COLUMN_COMMENT '字段描述',
	CHARACTER_SET_NAME '原字符集',
	COLLATION_NAME '原排序规则',
	CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT ', '''', COLUMN_COMMENT ,''';') '修正SQL' 
FROM
	information_schema.`COLUMNS` 
WHERE 
	TABLE_SCHEMA = 'wldos' -- 数据库名称
  and ( -- 字符型字段
	instr(COLUMN_TYPE, 'text') > 0 
	or instr(COLUMN_TYPE, 'char') > 0
  and  instr(UPPER(COLLATION_NAME), UPPER('utf8mb4_unicode_ci')) = 0 
);

修正sql结果如下：

ALTER TABLE wldos.wo_account_association MODIFY COLUMN bind_account varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’第三方关联账号,手机号也属于第三方账号’;
ALTER TABLE wldos.wo_account_association MODIFY COLUMN third_domain varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’三方域（比如QQ号的域：qq.com）,相同的域下与主账号的关联是一对一的，比如两个qq号不能绑定同一个账号’;
ALTER TABLE wldos.wo_account_association MODIFY COLUMN create_ip varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT”;
ALTER TABLE wldos.wo_account_association MODIFY COLUMN update_ip varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT”;
ALTER TABLE wldos.wo_account_association MODIFY COLUMN delete_flag varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’删除状态字典值：normal正常，deleted删除’;
ALTER TABLE wldos.wo_app MODIFY COLUMN app_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’应用名称’;
ALTER TABLE wldos.wo_app MODIFY COLUMN app_secret varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’应用秘钥’;
ALTER TABLE wldos.wo_app MODIFY COLUMN app_code varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’应用编码：必须支持URL解析，最长5位，将作为请求路径基础path’;
ALTER TABLE wldos.wo_app MODIFY COLUMN is_valid char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’是否有效：0无效、1有效’;
ALTER TABLE wldos.wo_app MODIFY COLUMN create_ip varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT”;
ALTER TABLE wldos.wo_app MODIFY COLUMN update_ip varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT”;
ALTER TABLE wldos.wo_app MODIFY COLUMN delete_flag varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’删除状态字典值：normal正常，deleted删除’;
ALTER TABLE wldos.wo_architecture MODIFY COLUMN arch_code varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’体系 结构编码’;
ALTER TABLE wldos.wo_architecture MODIFY COLUMN arch_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’体系结构类型名称：组织架构、团队、群组、圈子’;

执行上面生成的sql，全库更新排序规则。

声明: 除非转自他站（如有侵权，请联系处理）外，本文采用 BY-NC-SA 协议进行授权 | 嗅谱网
转载请注明：转自《mysql查询异常 java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation ‘=’》
本文地址：http://www.xiupu.net/archives-10958.html
关注公众号：