存储过程中排序规则的非法混合 [英] Illegal mix of collations in stored procedure

问题描述

我在MySQL中的存储过程失败，并显示Mysql::Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='.

my stored procedure in MySQL fails with Mysql::Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='.

该过程在SELECT子句中尝试将VARCHAR列与传递给此过程的VARCHAR参数进行比较时失败.

The procedure fails when in SELECT clause it tries to compare a VARCHAR column with VARCHAR parameter passed to this procedure.

我表中的所有列都具有utf8_unicode_ci排序规则.数据库排序规则是相同的.我什至在`/config/database.yml中指定了排序规则.

All the columns in my tables have utf8_unicode_ci collation. Database collation is the same. I have even specified collation in `/config/database.yml.

但是，当我运行/script/console时，设置了以下变量:

However, when I run /script/console I have following variables set:

>> ActiveRecord::Base.connection.select_rows "show variables like '%colla%'"
=> [["collation_connection", "utf8_general_ci"], ["collation_database", "utf8_unicode_ci"], ["collation_server", "utf8_general_ci"]]

可能最有趣的事实是，我在同一MySQL服务器上有另一个数据库，它们具有相同的排序规则(即使从Rails控制台查询排序规则变量也会得到相同的结果)，该数据库可以毫无问题地运行此存储过程.

And possibly the most interesting fact is that I have another database on the same MySQL server with same collations (even querying for collation variables from Rails console gives same results) which runs this stored procedure without any problem.

感谢您的帮助.

推荐答案

要快速修复，

SELECT * FROM YOUR_TABLE 
WHERE YOUR_COL=@YOUR_VARIABLES COLLATE utf8_general_ci;

OR

SELECT * FROM YOUR_TABLE 
WHERE YOUR_COL=@YOUR_VARIABLES COLLATE unicode_ci;
/* depends on the collation for YOUR_COL */

永久修复

• http://dev.mysql. com/doc/refman/5.1/en/server-system-variables.html#sysvar_collat​​ion_server
• http://dev.mysql. com/doc/refman/5.1/en/server-system-variables.html#sysvar_collat​​ion_database

• http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_collation_server
• http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_collation_database

您可能需要使用正确/相同的排序规则来重新创建数据库

You probably would need to re-create your database using the right/same collation

这篇关于存储过程中排序规则的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！