如何修复“操作'='的排序规则(utf8_unicode_ci,隐式）和(utf8_general_ci,隐式）的非法混合”

Question

在my remove服务器上执行MySQL中的存储过程，如下所示：

CREATE OR REPLACE PROCEDURE `SetNextPage`(
    IN `inRefNo` varchar(30) COLLATE utf8_general_ci,
    IN `inStage` varchar(40) COLLATE utf8_general_ci,
    IN `inRedirectTo` varchar(50) COLLATE utf8_general_ci,
    IN `inSurveyType` varchar(14) COLLATE utf8_general_ci
)
BEGIN
    IF inSurveyType = 'preinspection' THEN
        UPDATE preinspections SET Stage = inStage, RedirectTo = inRedirectTo WHERE RefNo = inRefNo;
    ELSE
        UPDATE surveys SET Stage = inStage, RedirectTo = inRedirectTo WHERE RefNo = inRefNo;
    END IF;
END

我收到错误消息如下：

操作“=”的排序规则(utf8_unicode_ci,隐式）和(utf8_general_ci,隐式）的非法混合

SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="mydatabase" AND TABLE_TYPE="BASE TABLE";

SHOW VARIABLES WHERE Variable_name LIKE ("character%") OR Variable_name LIKE ("Collation%");

要使存储过程运行，应该进行哪些更改？

Answer 1

执行显示创建过程SetNextPage\g

您可能会发现，定义proc时有效的字符集和/或排序规则并不是您现在想要的。

或者更改连接的排序规则，或者删除进程，设置名称，然后重新创建进程。