如何解决MySQL中的'非法混合排序规则”错误？

解决 MySQL 中非法混合排序规则错误

在执行涉及比较或操作文本数据的数据库操作时，确保一致性至关重要在字符排序规则中。在 MySQL 中，错误消息“非法混合排序规则”通常表示操作中操作数使用的排序规则之间不兼容。

在提供的代码中，问题是由操作中使用的排序规则之间的冲突引起的。下表和存储过程：

CREATE TABLE users ( ... ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE products ( ... ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE productUsers ( ... ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE PROCEDURE updateProductUsers (IN rUsername VARCHAR(24),IN rProductID INT UNSIGNED,IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

存储过程的输入参数和用户表的用户名列使用默认排序规则声明utf8_general_ci，而 ProductUsers 表的权限列和 rPerm 参数是使用 utf8_unicode_ci 排序规则声明的。

要解决此问题，您有多种选择：

选项 1：将 COLLATE 添加到输入变量

附加 COLLATE 子句到存储过程调用中的输入变量以显式指定 utf8_unicode_ci 排序规则。

$rUsername = 'aname' COLLATE utf8_unicode_ci;
$call = "CALL updateProductUsers(@rUsername, @rProductID, @rPerm);";

选项 2：将 COLLATE 添加到 WHERE 子句

将 COLLATE 子句添加到存储过程定义中的 WHERE 子句为 users.username 指定 utf8_unicode_ci 排序规则

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24),
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername COLLATE utf8_unicode_ci
        AND productUsers.productID = rProductID;
END

选项 3：将 COLLATE 添加到 IN 参数定义

对于 5.7 之前的 MySQL 版本，可以将 COLLATE 子句添加到 IN 参数定义在存储过程本身中。

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci,
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

选项 4：更改表字段

更改 users 表中的 username 列以使用 utf8_unicode_ci 排序规则。

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_unicode_ci;

虽然 utf8_general_ci 通常数据排序速度更快，但建议使用 utf8mb4/utf8mb4_unicode_ci因为它支持更广泛的 Unicode 字符。

以上是如何解决MySQL中的'非法混合排序规则”错误？的详细内容。更多信息请关注PHP中文网其他相关文章！