操作的非法混合（utf8_unicode_ci，IMPLICIT）和（utf8_general

121

MySql上的错误消息：操作'='的非法混合排序（utf8_unicode_ci，IMPLICIT）和（utf8_general_ci，IMPLICIT）

我已经经历了其他几个帖子，但无法解决这个问题 . 受影响的部分与此类似：

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也给出了同样的错误 . 我也测试了重新创建整个数据库，但没有好处 .

任何帮助都感激不尽 .

7 回答

有四种选择：

Option 1 ：将 COLLATE 添加到输入变量中：

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

Option 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

Option 3 ：将其添加到 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

Option 4 ：改变字段本身：

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

由于存储过程参数的默认排序规则是 utf8_general_ci ，因此您无法混合排序规则 .

除非您需要按Unicode顺序对数据进行排序，否则我建议您更改所有表以使用utf8_general_ci排序规则，因为它不需要更改代码，并且会稍微加快排序速度 .

UPDATE ：utf8mb4 / utf8mb4_unicode_ci现在是首选的字符集/归类方法 . 建议不要使用utf8_general_ci，因为性能提升可以忽略不计 . 见https://stackoverflow.com/a/766996/1432614

回复于 2024-05-19T17:45:56+08:00

0
我花了半天的时间在utf8_unicode_ci和utf8_general_ci之间的冲突中寻找相同的“非法混合排序”错误的答案 .

我发现我的数据库中的某些列没有专门整理utf8_unicode_ci . 似乎mysql隐式整理了这些列utf8_general_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）CHARACTER SET utf8 NOT NULL没有指定排序规则 . 然后我运行以下查询：

ALTER TABLE table1 CHANGE col1 col1 VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

这解决了我的“非法混合排序”错误 . 希望这可以帮助其他人 .
回复于 2024-05-19T17:45:56+08:00
3

我遇到了类似的问题，但是当我的查询参数使用变量设置时，我在内部程序中遇到了问题 . SET @value='foo' .

造成这种情况的原因是 collation_connection 与数据库整理不匹配 . 更改 collation_connection 以匹配 collation_database 并且问题消失了 . 我认为这比在param / value之后添加COLLATE更优雅 .

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

回复于 2024-05-19T17:45:56+08:00
168
有点类似于@bpile的答案，我的案例是my.cnf条目设置 collation-server = utf8_general_ci . 在我意识到这一点之后（以及在尝试了上述所有内容之后），我强行将我的数据库切换为utf8_general_ci而不是utf8_unicode_ci，就是这样：
```
ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;
```
回复于 2024-05-19T17:45:56+08:00
4

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

操作'='的非法混合排序（utf8_general_ci，IMPLICIT）和（utf8_unicode_ci，IMPLICIT）

$ this-> db-> select（“users.username as matric_no，CONCAT（users.surname，''，users.first_name，''，users.last_name）as fullname”） - > join（'users'，' users.username = classroom_students.matric_no'，'left'） - > where（'classroom_students.session_id'，$ session） - > where（'classroom_students.level_id'，$ level） - > where（'classroom_students.dept_id'，$ DEPT）;

经过数周的谷歌搜索后，我注意到我所比较的两个字段包含不同的排序规则名称 . 第一个用户名是utf8_general_ci而第二个是utf8_unicode_ci所以我回到了第二个表的结构，并将第二个字段（matric_no）更改为utf8_general_ci，它就像一个魅力 .

回复于 2024-05-19T17:45:56+08:00
0
尽管发现了大量关于同一问题的问题（1，2，3，4），我从未找到过将性能考虑在内的答案，即使在这里也是如此 .

虽然已经提供了多种工作解决方案，但我想进行性能考虑 .

编辑：感谢Manatax指出选项1没有性能问题 .

使用选项1和2（也就是 COLLATE 强制转换方法）可能会导致潜在的瓶颈，因为不会使用列上定义的任何索引导致 full scan .

即使我没有尝试选项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')
;
```
在我最初的例子中，我有更多的连接 . 当然，table1和table2有不同的排序规则 . 使用 collate 运算符进行强制转换将导致索引未被使用 .

请参见下图中的sql说明 .

Visual Query Explanation when using the COLLATE cast

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

在下图中，您可以看到应用选项4后运行的同一查询，即更改架构/表/列排序规则 .

Visual Query Explanation after the collation has been changed, and therefore without the collate cast

总之，如果性能很重要并且您可以更改表的排序规则，请转到选项4.如果您必须对单个列执行操作，则可以使用以下内容：
```
ALTER TABLE schema1.table1 MODIFY `field` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
回复于 2024-05-19T17:45:56+08:00
19
如果将列显式设置为其他排序规则，或者在查询的表中默认排序规则不同，则会发生这种情况 .

如果您有许多表，则要在运行此查询时更改排序规则：
```
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;
```
这将输出转换所有表所需的查询以使用每列正确的排序规则
回复于 2024-05-19T17:45:56+08:00

操作的非法混合（utf8_unicode_ci，IMPLICIT）和（utf8_general_ci，IMPLICIT）'='

7 回答

相关问题