在存储过程中非法混合排序

时间：2021-10-24 02:06:37

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

MySQL中我的存储过程失败了:错误:非法混合排序(utf8_general_ci，隐式)和(utf8_unicode_ci，隐式)用于操作'='。

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

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

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.

我的表中的所有列都有utf8_unicode_ci排序。数据库排序也是一样的。我甚至在' /config/database.yml中指定了排序规则。

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"]]

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.

可能最有趣的事实是，我在同一个MySQL服务器上有另一个具有相同排序的数据库(甚至查询来自Rails控制台的排序变量也会得到相同的结果)，它可以毫无问题地运行这个存储过程。

Thanks for your help.

谢谢你的帮助。

1 个解决方案

#1

17  

To quick fix,

快速修复,

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 */

Permanent fix

永久解决

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

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

#1

17  

To quick fix,

快速修复,

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 */

Permanent fix

永久解决

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

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

标签：

相关文章