存储过程参数的默认排序规则为utf8_general_ci
并且您不能混合排序规则,因此有四个选项:
:添加COLLATE
到您的输入变量中:
SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);
:COLLATE
在WHERE
条款中添加:
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 -- COLLATE added
AND productUsers.productID = rProductID;
END
:将其添加到IN
参数定义中:
CREATE PROCEDURE updateProductUsers(
IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci, -- COLLATE added
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
:更改字段本身:
ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;
除非您需要按Unicode顺序对数据进行排序,否则我建议您更改所有表以使用
utf8_general_ci
排序规则,因为它不需要更改
代码,并且会稍微加快排序速度。
:utf8mb4 / utf8mb4_unicode_ci现在是首选的字符集/排序规则方法。建议不要使用utf8_general_ci,因为性能提升可忽略不计。参见https://stackoverflow.com/a/766996/1432614