操作'='的排序规则(utf8_unicode_ci，IMPLICIT)和(utf8_general_ci，IMPLICIT)的非法混合 [英] Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

问题描述

关于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，并且您不能混合使用排序规则，因此有四个选项:

选项1 :将COLLATE添加到您的输入变量中:

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

选项2 :在WHERE子句中添加COLLATE:

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参数定义:

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://stackoverflow.com/a/766996/1432614

Error message on MySql:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

I have gone through several other posts and was not able to solve this problem. The part affected is something similar to this:

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;

The stored procedure I'm using is this:

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

I was testing with php, but the same error is given with SQLyog. I have also tested recreating the entire DB but to no good.

Any help will be much appreciated.

解决方案

The default collation for stored procedure parameters is utf8_general_ci and you can't mix collations, so you have four options:

Option 1: add COLLATE to your input variable:

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

Option 2: add COLLATE to the WHERE clause:

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: add it to the IN parameter definition:

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 the field itself:

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

Unless you need to sort data in Unicode order, I would suggest altering all your tables to use utf8_general_ci collation, as it requires no code changes, and will speed sorts up slightly.

UPDATE: utf8mb4/utf8mb4_unicode_ci is now the preferred character set/collation method. utf8_general_ci is advised against, as the performance improvement is negligible. See https://stackoverflow.com/a/766996/1432614

这篇关于操作'='的排序规则(utf8_unicode_ci，IMPLICIT)和(utf8_general_ci，IMPLICIT)的非法混合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！