MySql 上的错误信息:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
我已经浏览了其他几个帖子,但无法解决这个问题。受影响的部分与此类似:
CREATE TABLE users ( userID INT UNSIGNED NOT NULL AUTO_INCREMENT, firstName VARCHAR(24) NOT NULL, lastName VARCHAR(24) NOT NULL, username VARCHAR(24) NOT NULL, password VARCHAR(40) NOT NULL, PRIMARY KEY (userid) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci; CREATE TABLE products ( productID INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(104) NOT NULL, picturePath VARCHAR(104) NULL, pictureThumb VARCHAR(104) NULL, creationDate DATE NOT NULL, closeDate DATE NULL, deleteDate DATE NULL, varPath VARCHAR(104) NULL, isPublic TINYINT(1) UNSIGNED NOT NULL DEFAULT '1', PRIMARY KEY (productID) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci; CREATE TABLE productUsers ( productID INT UNSIGNED NOT NULL, userID INT UNSIGNED NOT NULL, permission VARCHAR(16) NOT NULL, PRIMARY KEY (productID,userID), FOREIGN KEY (productID) REFERENCES products (productID) ON DELETE RESTRICT ON UPDATE NO ACTION, FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE RESTRICT ON UPDATE NO ACTION ) 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
我正在使用 php 进行测试,但 SQLyog 给出了相同的错误。我还测试过重新创建整个数据库,但效果不佳。
任何帮助都感激不尽。
存储过程参数的默认排序规则是utf8_general_ci并且您不能混合排序规则,因此您有四个选项:
utf8_general_ci
选项 1 :添加COLLATE到您的输入变量:
COLLATE
SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added CALL updateProductUsers(@rUsername, @rProductID, @rPerm);
选项 2 :添加COLLATE到WHERE子句:
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
选项 3 :将其添加到IN参数定义中(MySQL 5.7 之前):
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
选项 4 :更改字段本身:
ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;
除非您需要按 Unicode 顺序对数据进行排序,否则我建议您更改所有表以使用utf8_general_ci排序规则,因为它不需要更改代码,并且会稍微加快排序速度。
更新 :utf8mb4/utf8mb4_unicode_ci 现在是首选的字符集/整理方法。建议不要使用 utf8_general_ci,因为性能提升可以忽略不计。见https://codingdict.com/questions/ 19216