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 = '' -- 数据库名称
  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 .wo_account_association MODIFY COLUMN bind_account varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’第三方关联账号,手机号也属于第三方账号’;
ALTER TABLE .wo_account_association MODIFY COLUMN third_domain varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT’三方域(比如QQ号的域:qq.com),相同的域下与主账号的关联是一对一的,比如两个qq号不能绑定同一个账号’;
ALTER TABLE .wo_account_association MODIFY COLUMN create_ip varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT”;
ALTER TABLE .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,全库更新排序规则。

转载请注明:转自《mysql查询异常 java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation ‘=’
本文地址:https://www..com/archives-10958.html

赞赏

微信赞赏支付宝赞赏

上一篇

相关文章

在线留言

你必须 登录后 才能留言!

在线客服
在线客服 X

售前: 点击这里给我发消息
售后: 点击这里给我发消息

电话:15665730355