mysql: 错误代码 [1267]; 操作 '=' 的排序规则 (latin1_general_cs,IMPLICIT) 和 (latin1_swedish_ci,IMPLICIT) 的非法混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13285207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
提问by DarkKnightFan
I want to make the passwordcolumn of my User table to be case sensitivein mysql.
我想让password我的用户表的列case sensitive在 mysql 中。
Following is the description of the table:
以下是该表的说明：
/*Table: mst_user*/
FIELD TYPE COLLATION
------------- ------------ -----------------
user_id VARCHAR(100) latin1_swedish_ci
first_name VARCHAR(25) latin1_swedish_ci
last_name VARCHAR(25) latin1_swedish_ci
USER_PASSWORD VARCHAR(50) latin1_swedish_ci
user_status INT(11) (NULL)
version_id INT(11) (NULL)
active_status INT(11) (NULL)
user_type INT(11) (NULL)
To make the USER_PASSWORDfield case sensitive I executed following query:
为了使USER_PASSWORD字段区分大小写，我执行了以下查询：
ALTER TABLE `mst_user` MODIFY `USER_PASSWORD` VARCHAR(50) COLLATE `latin1_general_cs`;
This worked and the field is now case sensitive.
这有效并且该字段现在区分大小写。
But I have a store procedure which executes a SELECTquery on this table to check if the user exists for the given credentials.
但是我有一个存储过程，它SELECT在这个表上执行一个查询来检查用户是否存在给定的凭据。
Stored Proc::
存储过程::
CREATE PROCEDURE `usp_password_verify`(ip_login_id VARCHAR(200),
ip_user_password VARCHAR(200),
INOUT success INT(1),
INOUT tbl_usr_password VARCHAR(100),
INOUT pkg_user_password VARCHAR(100))
BEGIN
SELECT COUNT(*)
INTO success
FROM mst_user
WHERE UPPER (user_id) = UPPER (ip_login_id)
AND USER_PASSWORD=ip_user_password;
SET tbl_usr_password = '';
SET pkg_user_password= '';
END$$
When I call this stored proc from my java code I am getting the following error:
当我从我的 java 代码调用这个存储过程时，我收到以下错误：
**error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='**
Can anyone help what is wrong with this? Something that works as a simple query gives error while executing it in a stored proc!?
任何人都可以帮助这有什么问题吗？在存储过程中执行时，作为简单查询的东西会出错！？
回答by eggyal
As documented under Collation of Expressions:
MySQL assigns coercibility values as follows:
[ deletia ]
- The collation of a column or a stored routine parameter or local variable has a coercibility of 2.
[ deletia ]MySQL uses coercibility values with the following rules to resolve ambiguities:
[ deletia ]
If both sides have the same coercibility, then:
- If both sides are Unicode, or both sides are not Unicode, it is an error.
MySQL 分配 coercibility 值如下：
[ deletia ]
- 列或存储的例程参数或局部变量的排序规则的可强制性为 2。
[ deletia ]MySQL 使用 coercibility 值和以下规则来解决歧义：
[ deletia ]
如果双方具有相同的强制力，则：
- 如果两边都是Unicode，或者两边都不是Unicode，那就是错误。
You couldadd an explicit COLLATEclause in your expression to force one of the operands to have an explicit collation with a lower coercibility value:
您可以COLLATE在表达式中添加一个显式子句，以强制操作数之一具有具有较低强制值的显式排序规则：
USER_PASSWORD=ip_user_password COLLATE 'latin1_general_cs'
You might even want to consider latin1_binin this case?
latin1_bin在这种情况下，您甚至可能要考虑？
In any event, you should not be storing passwords in plaintext. Instead, store saltedhashes of your users' passwords and simply verify that the hash matches that which is stored.
在任何情况下，您都不应该以纯文本形式存储密码。相反，存储用户密码的加盐散列，并简单地验证散列是否与存储的相匹配。
回答by Radacina
I know it's a little bit late but if this could save someone half a day of swearing it's still worth putting it down.
我知道这有点晚了，但如果这可以让某人节省半天的时间，那么它仍然值得放下。
So, my setup was like this: 10.1.22-MariaDB, utf8mb4_general_ci.
All good, I restored a dump of my database, all went OK.
所以，我的设置是这样的：10.1.22-MariaDB, utf8mb4_general_ci. 一切都很好，我恢复了我的数据库的转储，一切顺利。
The database was originally in utf8_general_ci, but for some reasons was restored as utf8_unicode_ci. Changed that back to utf8_general_ciand checked there were no artifacts in the database like columns or table definitions collated as utf8_unicode_ciinstead of utf8_general_ci
数据库原来是在utf8_general_ci，但由于某些原因被恢复为utf8_unicode_ci. 将其改回utf8_general_ci并检查数据库中没有诸如列或表定义之类的工件被整理为utf8_unicode_ci而不是utf8_general_ci
Trying to update a specific table resulted in an illegal mix of collation without any apparent reasons.
尝试更新特定表会导致在没有任何明显原因的情况下非法混合整理。
It boiled down to be in fact not the table itself but the associated trigger.
它归结为实际上不是表本身，而是相关的触发器。
In fact the trigger called a procedure that had no collation info in my database, but that had a utf8_unicode_cicollation in information_schema.ROUTINES.DATABASE_COLLATION.
事实上，触发器调用了一个过程，该过程在我的数据库中没有整理信息，但utf8_unicode_ci在information_schema.ROUTINES.DATABASE_COLLATION.
Recreating the procedure in the context of the new database collation solved my issue.
在新数据库整理的上下文中重新创建过程解决了我的问题。
回答by Ryan Jarvis
So after struggling with this error:
因此，在与此错误斗争之后：
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='
I managed to fix this problem by changing the import database file from:
我设法通过从以下位置更改导入数据库文件来解决此问题：
CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NO SQL
This is if you are importing functions/procedures/triggers, which my database had a ton of all of these features... I changed that to:
这是如果您要导入函数/过程/触发器，我的数据库具有大量所有这些功能......我将其更改为：
CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
I really hope this helps someone. I know the above was helpful, but it still took me a few hours to turn that into a solution. Thanks
我真的希望这可以帮助某人。我知道以上内容很有帮助，但我仍然花了几个小时才将其转化为解决方案。谢谢
