在MySQL中如何解决“Illegal mix of collations”错误?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (5)
  • 关注 (0)
  • 查看 (589)

我想通过MySQL中的存储过程进行选择时,出现以下错误。

非法混合排序(latin1_general_cs,IMPLICIT)和(latin1_general_ci,IMPLICIT)进行操作'='

为什么呢?

该表的排序规则是latin1_general_ciwhere子句中的列的排序规则latin1_general_cs

提问于
用户回答回答于
用户回答回答于

有时候,转换字符集,特别是数据量巨大的数据库可能会很危险。我认为最好的选择是使用“二元”运算符:

e.g : WHERE binary table1.column1 = binary table2.column1
用户回答回答于

我正在研究一个类似的问题,在使用自定义时,我得到了以下错误:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and 
(utf8_general_ci,IMPLICIT) for operation '='

使用以下查询:

mysql> show variables like "collation_database";
    +--------------------+-----------------+
    | Variable_name      | Value           |
    +--------------------+-----------------+
    | collation_database | utf8_general_ci |
    +--------------------+-----------------+

我能看出DB正在使用utf8_general_ci,而表则使用utf8_unicode_ci:

mysql> show table status;
    +--------------+-----------------+
    | Name         | Collation       |
    +--------------+-----------------+
    | my_view      | NULL            |
    | my_table     | utf8_unicode_ci |
    ...

解决方案是

  • 更改数据库的排序规则:
ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
用户回答回答于

 今天在使用数据库临时表的游标时,发现了这个异常。

经查找资料,最终结果。这里和大家分享一下。 字符集问题还是一定要统一的才是最简单的。

create temporary table temp2(mc1 varchar(20) default '',mc2 varchar(20)default '',mc3 varchar(20)default '',mc4 varchar(20)default '',mc5 varchar(20)default '',parentid varchar(20),levelnum int); -- 生成临时表

declare mycur67 CURSOR for select concat(replace(space(levelnum-1),space(1),'------'),mc1,mc2,mc3,mc4,mc5) as mc,parentid as location from temp2;

异常信息为:

Illegal mix of collations for operation 'concat'

将concat的代码去掉后面的部分内容,运行结果为

declare mycur67 CURSOR for select concat(replace(space(levelnum-1),space(1),'------'),mc1) as mc,parentid as location from temp2;
错误:Illegal mix of collations (utf8_general_ci,COERCIBLE) and (gb2312_chinese_ci,IMPLICIT) for operation 'concat'

可见,是2个字段的编码类型不一致造成的。

原来我的数据库创建时,选择的是gb2312编码,而字段操作默认为UTF8的编码。 绝对统一使用UTF-8

create temporary table temp2(mc1 varchar(20) default '',mc2 varchar(20)default '',mc3 varchar(20)default '',mc4 varchar(20)default '',mc5 varchar(20)default '',parentid varchar(20),levelnum int)DEFAULT CHARSET=UTF8;

运行结果正常。

经查找资料,如下说的很清楚

mysql> show variables like "%character%"; show variables like "%collation%"; +--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

fix it with

set collation_database=utf8_general_ci;
set collation_connection=utf8_general_ci;
set collation_server=utf8_general_ci;
用户回答回答于

这通常是通过比较两个不兼容的归类字符串或尝试将不同归类的数据选择到组合列引起的。

子句COLLATE允许你指定查询中使用的排序规则。

例如,以下WHERE将给出你的错误:

WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs

你的解决方案是为查询中的两列指定共享排序规则。以下是该COLLATE子句的一个使用示例:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

另一种选择是使用BINARY运算符:

BINARY str是CAST(str AS BINARY)的简写。

你的解决方案可能看起来像这样:

SELECT * FROM table WHERE BINARY a = BINARY b;

要么,

SELECT * FROM table ORDER BY BINARY a;

扫码关注云+社区

领取腾讯云代金券