1.情景展示
在Navicat中,编写mysql存储过程,执行的时候,报错信息如下:
1267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
2.具体分析
mysql对字符集的支持有两方面,分别是:字符集(Character set)和排序方式(Collation)。
查询数据库字符集
SHOW VARIABLES LIKE '%character%';
校对规则是依赖字符集而存在的,在设置字符集时,可以设置当前字符集的校对规则;
如果不设置校对规则,字符集会使用默认的校对规则。
表级的字符集和校对规则是MySQL的扩展,在标准SQL中没有。
每一张表有一个表字符集和一个校对规则,为指定表字符集和校对规则,CREATE TABLE 和ALTER TABLE语句有一个可选的子句:
CREATE TABLE tblname (columnlist)
[DEFAULT CHARACTER SET charsetname [COLLATE collationname]]
ALTER TABLE tblname
[DEFAULT CHARACTER SET charsetname] [COLLATE collationname]
上面的报错原因就是:校对规则不一致的原因导致(字段的字符集排序规则不一致)。
校对规则是在字符集内用于比较字符的一套规则,可以控制 select 查询时where 条件大小写是否敏感的规则.如字段 col 在表中的值为 'abc','ABC','AbC' 在不同的校对规则下,where col='ABC'会有不同的结果。
校对规则有如下特征:
a 两个不同的字符集不能有相同的校对规则。
b 每个字符集有一个默认校对规则。例如,utf8默认校对规则是utf8generalci
c 存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名比如utf8,并且以ci(大小写不敏感)、或bin(二元)结束 ,如 utf8_bin。
MySQL 提供四种默认级别的字符集和校验规则:服务器级、数据库级、表级,和连接级,一般字段级别的不常用。
查看数据库的排序规则编码
SHOW VARIABLES WHERE variable_name LIKE 'collation%';
3.解决方案
方式一:查询的时候指定排序规则;
语法:
字段/变量 + collate +排序规则。
通过改变=号一方的排序规则,使其与另一方的排序规则保持一致。
-- 方式一
select * from base_org_info WHERE ORGNAME = '河南省' COLLATE utf8mb4_general_ci;
-- 方式二
select * from base_org_info WHERE ORGNAME COLLATE utf8mb4_unicode_ci = '河南省';
如果字符集也不一样的话,可能字符集也要改变:
-- 方式1
select * from base_org_info WHERE CONVERT(ORGNAME USING utf8mb4) COLLATE utf8mb4_0900_ai_ci = '河南省';
-- 方式2
select * from base_org_info WHERE ORGNAME = CONVERT('河南省' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
方式二:将=号两边转换成二进制,再比较;
select * from base_org_info WHERE BINARY ORGNAME = BINARY '河南省';
方式三:更改现有列的排序规则;
遵循最小变动原则(也就是说,现在哪个字段在比较的时候报错,就修改哪个字段的排序规则,使其与要比较的对象的排序规则保持一致。)
这里又有两种实现方式;
一种是:通过Navicat操作;
另一种是:通过SQL实现。
语法:
ALTER TABLE 表名 MODIFY 列名 数据类型 CHARACTER SET 字符集 COLLATE 排序规则。
ALTER TABLE base_org_info MODIFY orgcode VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
一般情况下,我们只需要修改排序规则即可,数据类型和字符集保持不变。
4.扩展
另外,如果需要更改表的字符集或者排序规则的话,需要注意的是:
只会对以后的列有影响,对已经创建的列,不会产生影响。
方式一:SQL修改;
-- 带不带=,均可
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name [COLLATE collation_name];
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name [COLLATE = collation_name];
方式二:sqlyog修改。