mysql:错误代码[1267]；操作'='的排序规则(latin1_general_cs，IMPLICIT)和(latin1_swedish_ci，IMPLICIT)的非法混合 [英] mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

问题描述

我希望在mysql中将用户表的password列设置为case sensitive.

I want to make the password column of my User table to be case sensitive in mysql.

以下是对该表的描述:

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

为了使USER_PASSWORD字段区分大小写，我执行了以下查询:

To make the USER_PASSWORD field case sensitive I executed following query:

ALTER TABLE `mst_user` MODIFY `USER_PASSWORD` VARCHAR(50) COLLATE `latin1_general_cs`;

这有效，并且该字段现在区分大小写.

This worked and the field is now case sensitive.

但是我有一个存储过程，该存储过程对该表执行SELECT查询，以检查用户是否存在给定的凭据.

But I have a store procedure which executes a SELECT query on this table to check if the user exists for the given credentials.

存储过程::

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$$

当我从Java代码中调用此存储的proc时，出现以下错误:

When I call this stored proc from my java code I am getting the following error:

**error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='**

任何人都可以帮助解决这个问题吗? 在存储的proc中执行该查询时，可以用作简单查询的内容会导致错误！?

Can anyone help what is wrong with this? Something that works as a simple query gives error while executing it in a stored proc!?

推荐答案

如表达式:

MySQL分配强制性值如下:

MySQL assigns coercibility values as follows:

[ deletia ]

• 列或存储的例程参数或局部变量的排序规则的强制性为2.

[ deletia ]

MySQL使用强制性值和以下规则来解决歧义:

MySQL uses coercibility values with the following rules to resolve ambiguities:

[ deletia ]

• 如果双方的矫顽力相同，则:

• If both sides have the same coercibility, then:

• 如果双方都是Unicode，或者双方都不是Unicode，则错误.

您可以在以下位置添加显式的 COLLATE 子句您的表达式可以强制其中一个操作数具有较低的矫顽力值的显式排序规则:

You could add an explicit COLLATE clause in your expression to force one of the operands to have an explicit collation with a lower coercibility value:

USER_PASSWORD=ip_user_password COLLATE 'latin1_general_cs'

在这种情况下，您甚至可能要考虑latin1_bin?

You might even want to consider latin1_bin in this case?

无论如何，您不应以纯文本格式存储密码.而是存储用户密码的 saltted 哈希，然后简单地验证哈希是否与存储的哈希匹配.

In any event, you should not be storing passwords in plaintext. Instead, store salted hashes of your users' passwords and simply verify that the hash matches that which is stored.

这篇关于mysql:错误代码[1267]；操作'='的排序规则(latin1_general_cs，IMPLICIT)和(latin1_swedish_ci，IMPLICIT)的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！