mysql:错误代码[1267]；操作 '=' 的排序规则 (latin1_general_cs,IMPLICIT) 和 (latin1_swedish_ci,IMPLICIT) 的非法混合

Question

我想让我的 User 表的 password 列在 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 字段区分大小写，我执行了以下查询:

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

这行得通并且该字段现在区分大小写。

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

存储过程::

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 代码中调用这个存储过程时，出现以下错误:

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

任何人都可以帮助解决这个问题吗？在存储过程中执行时，作为简单查询工作的东西会出错!？

Answer 1

根据 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.

您可以添加一个明确的COLLATE表达式中的子句强制其中一个操作数具有具有较低强制性值的显式排序规则:

USER_PASSWORD=ip_user_password COLLATE 'latin1_general_cs'

在这种情况下，您甚至可能想考虑 latin1_bin？

无论如何，您不应以明文形式存储密码。相反，存储用户密码的加盐哈希值，并简单地验证该哈希值是否与存储的相匹配。