mysql - 归类的非法混合 (utf8_general_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT)

Question

谁能帮我解决这个错误

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'locate'

我已经在 server1 上执行了我的存储过程并且它工作正常，但是它会在 server2 上抛出一个错误。我已经检查了两台服务器上的所有表和存储过程，它们都是一样的。

谁能帮我解决这个问题是什么以及解决方法。

这是存储过程。

DELIMITER $$

DROP PROCEDURE IF EXISTS `UPDATEPASSWORD` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UPDATEPASSWORD`(IN empn CHAR(9), IN oldpassword VARCHAR(255), IN newpassword VARCHAR(255))
main:BEGIN
DECLARE diction INT;
DECLARE prevpass INT;
DECLARE len INT;
DROP TABLE IF EXISTS  passwordstatus;
CREATE TEMPORARY TABLE passwordstatus
(
    `passwordstatus` VARCHAR(127)
);

PREPARE stmt1 FROM "select count(seqid) into @diction 
                    from dictionary where instr(?,word)>0 or instr(REVERSE(?),REVERSE(word))>0 ";
SET @a = newpassword;
SET @b = newpassword;
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
IF @diction > 0 THEN
    START TRANSACTION;
    PREPARE stmt1 FROM "INSERT INTO passwordstatus VALUES (?)";
    SET @a = "PASSWORD CONTAINS COMMONLY USED WORDS";
    EXECUTE stmt1 USING @a;
    DEALLOCATE PREPARE stmt1;
    COMMIT; 
    SELECT * FROM passwordstatus;
    LEAVE main;
END IF;

 PREPARE stmt1 FROM "select count(seqid) into @prevpass 
                    from prevpass where (instr(sha1(?),passwd)>0 or instr(REVERSE(sha1(?)),REVERSE(passwd))>0)and empno = ?";
SET @a = newpassword;
SET @b =empn;
EXECUTE stmt1 USING @a, @a, @b;
DEALLOCATE PREPARE stmt1;
IF @prevpass > 0 THEN
    START TRANSACTION;
    PREPARE stmt1 FROM "INSERT INTO passwordstatus VALUES (?)";
    SET @a = "PASSWORD CONTAINS PREVIOUSLY USED PASSWORDS";
    EXECUTE stmt1 USING @a;
    DEALLOCATE PREPARE stmt1;
    COMMIT;      
    SELECT * FROM passwordstatus;
    LEAVE main;
END IF;

PREPARE STMT1 FROM " SELECT COUNT(seqid) INTO @prevpass FROM prevpass where empno = ? ";
SET @a = empn;
EXECUTE STMT1 USING @a;
DEALLOCATE PREPARE STMT1;
START TRANSACTION;
IF @prevpass = 6 THEN
    PREPARE STMT1 FROM "DELETE FROM prevpass WHERE EMPNO = ? ORDER BY seqid LIMIT 1";
    SET @a = empn;
    EXECUTE STMT1 USING @a;
    DEALLOCATE PREPARE STMT1;
END IF;
PREPARE STMT FROM " UPDATE emppass SET passwd = sha1(?),lastupdate = now() WHERE empno = ? ";
SET @a = newpassword;
SET @b = empn;
EXECUTE STMT USING @a,@b;
DEALLOCATE PREPARE STMT;
PREPARE STMT1 FROM " INSERT INTO prevpass (empno,passwd,createdate) VALUES (?,sha1(?),now())";
SET @a = empn;
SET @b = newpassword;
EXECUTE STMT1 USING @a,@b;
DEALLOCATE PREPARE STMT1;
PREPARE stmt1 FROM "INSERT INTO passwordstatus VALUES (?)";
    SET @a = "PASSWORD UPDATED SUCCESSFULLY";
    EXECUTE stmt1 USING @a;
    DEALLOCATE PREPARE stmt1;
COMMIT;
SELECT * FROM passwordstatus;

END $$

DELIMITER ;

Answer 1

尝试在每个参数之前有一个 BINARY 命令。

SHA1(BINARY ?)
BINARY passwd