操作“=”非法混合了排序规则（UTF8_Unicode_CI，隐式）和（UTF8_常规_CI，隐式）

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也出现了同样的错误。 我还测试了重新创建整个数据库，但效果不佳。

任何帮助都将不胜感激。

326838 次浏览
小开
最佳答案

存储过程参数的默认排序规则是utf8_general_ci，并且不能混合排序规则，因此您有四种选择：

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

SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);

选择2：将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

备选方案3：将其添加到IN参数定义（MySQL 5.7之前）：

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_常规_CI，因为性能提升微不足道。请参阅https://stackoverflow.com/a/766996/1432614

小开

我花了半天时间搜索相同的“非法混合排序规则”错误的答案，该错误在UTF8_Unicode_CI和UTF8_通用_CI之间存在冲突。

我发现数据库中的某些列没有专门UTF8_Unicode_CI进行整理。似乎MySQL隐式整理了这些列，UTF8_通用_CI。

具体来说，运行“ Show Create Table Table1 ”查询将输出如下内容：

| table1 | CREATE TABLE `table1` (
`id` int(11) NOT NULL,
`col1` varchar(4) CHARACTER SET utf8 NOT NULL,
`col2` int(11) NOT NULL,
PRIMARY KEY (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

注意：' col1 ' VARCHAR（4）字符集UTF8不为空的行未指定排序规则。然后我运行了以下查询：

ALTER TABLE TABLE1 CHANGE COL1 COL1 VARCHAR（4）字符集UTF8 整理UTF8_Unicode_CI不为空；

这解决了我的“非法混合排序规则”错误。希望这能帮助其他人。

小开

我有一个类似的问题，但它发生在我的过程中，当我的查询参数设置使用变量，例如SET @value='foo'。

导致此问题的原因是collation_connection与数据库排序规则不匹配。已将collation_connection更改为与collation_database匹配，问题消失。我认为这是一种比在param/value后面添加collate更优雅的方法。

总结一下：所有排序规则必须匹配。使用SHOW VARIABLES，并确保collation_connection和collation_database匹配（同时使用SHOW TABLE STATUS [table_name]检查表排序规则）。

小开

有点类似于@bpile的答案，我的例子是一个my.CNF条目设置collation-server = utf8_general_ci。在我意识到这一点之后（并且在尝试了上面的所有方法之后），我强行将数据库切换为UTF8_通用_CI，而不是UTF8_Unicode_CI，就是这样：

ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;
小开

在我自己的情况下，我有以下错误

操作“=”非法混合了排序规则（UTF8_常规_CI，隐式）和（UTF8_Unicode_CI，隐式）

$this->db->select（users.username as matric_no，concat（users.surname， ' ', USERS.FIRST_名称，' '，USERS.LAST_名称）作为FullName） ->join（' users '，' users.username=教室_students.matric_否'，' left '） ->where（'教室_students.session_ID '，$session） ->where（'教室_students.level_ID '，$level） ->where（'教室_students.Dept_ID '，$Dept）；

经过几周的谷歌搜索，我注意到我比较的两个字段包含不同的排序规则名称。第一个，即用户名是UTF8_通用_CI，而第二个是UTF8_Unicode_CI，所以我回到第二个表的结构，并将第二个字段（矩阵_NO）更改为UTF8_通用_CI，它工作得非常好。

小开

尽管找到了大量关于同一问题的问题（1，2，3，4），但我从未找到考虑性能的答案，即使在这里也是如此。

虽然已经给出了多个工作解决方案，但我想考虑一下性能。

编辑：感谢Manatax指出选项1不存在性能问题。

使用备选方案1和2（也称为校勘转换方法）可能会导致潜在的瓶颈，因为在列上定义的任何索引都不会使用，从而导致全扫描。

尽管我没有尝试备选方案3，但我的直觉是，它将遭受与选项1和2相同的后果。

最后，在可行的情况下，选择4是大型表的最佳选择。我的意思是没有其他用法依赖于原始校对。

考虑这个简化的查询：

SELECT
*
FROM
schema1.table1 AS T1
LEFT JOIN
schema2.table2 AS T2 ON T2.CUI = T1.CUI
WHERE
T1.cui IN ('C0271662' , 'C2919021')
;

在我最初的例子中，我有更多的连接。 当然，表1和表2具有不同的排序规则。 使用校勘运算符进行类型转换，将导致索引无法使用。

请参阅下图中的SQL说明。

使用排序转换时的可视化查询说明

另一方面，选择4可以利用可能的索引，并导致快速查询。

在下面的图片中，您可以看到在应用选择4之后运行相同的查询，即更改模式/表/列排序规则。

更改排序规则后的可视化查询解释，因此不需要强制转换排序规则

总之，如果性能很重要，并且您可以更改表的排序规则，请选择选择4。 如果必须对单个列执行操作，则可以使用以下内容：

ALTER TABLE schema1.table1 MODIFY `field` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
小开

如果列被显式设置为不同的排序规则，或者查询的表中的默认排序规则不同，则会发生这种情况。

如果您有许多要更改排序规则的表，请运行以下查询：

select concat('ALTER TABLE ', t.table_name , ' CONVERT TO CHARACTER
SET utf8 COLLATE utf8_unicode_ci;') from (SELECT table_name FROM
information_schema.tables where table_schema='SCHRMA') t;

这将输出将所有表转换为每列使用正确排序规则所需的查询

小开

答案是添加到@sebas的答案-设置我的本地环境的排序规则。不要在生产中尝试此操作。

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;


ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

此_的来源ABC_0

小开

我还遇到了一个问题，当上传Excel文件在MySQL数据库形式拉雷维尔。在Excel文件中，某些地址包含类似_ABC的字符，_错误为3

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select count(*) as aggregate from `.....` where `e....` = kas@email.com and `first_name` = Gill and `surname` = Harries and `address` = 6 St.PeterÕs Close,,,Woodbridge,Suffolk,IP12 4EJ and `status` = 1 and `client`.`deleted_at` is null)

我尝试了上面提到的许多解决方案，但不幸的是，没有人为我工作。所以我发布了适合我的解决方案。所以在某些情况下，它可能不会起作用。例外可以不同。所以你必须在此基础上找到解决方案。如果有人认为这是有用的，请投票。

我尝试了两种解决方案。

  1. 我将异常代码放入尝试{}catch（）块中进行处理。因为在执行选择（）查询时出现错误。所以我用try{}来做这个。使用存储在数据库中的上传记录。但是，当字符集问题出现在它所在的位置时？该示例图像如下所示。

enter image description here

  1. 您可以通过此代码检测操作时出现问题的字符集并更改MySQL配置
 \Config::set('database.connections.mysql.charset', 'latin1');
\Config::set('database.connections.mysql.collation', 'latin1_bin');
\DB::purge('mysql');

两种解决方案都适合我。


提交答案