[解決済み] 操作 '=' に対する照合順序 (utf8_unicode_ci,IMPLICIT) と (utf8_general_ci,IMPLICIT) の不適当な混合

質問

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でも同じエラーが出ます。 DB全体の再作成もテストしましたが、ダメでした。

何か手助けがあれば、とても感謝します。

解決方法は？

ストアドプロシージャのパラメータに対するデフォルトの照合順序は utf8_general_ci で、照合順序を混在させることはできないので、4つのオプションがあります。

オプション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 照合順序を変更する必要はなく、ソートの速度も少し速くなります。

アップデイト utf8_general_ci は、パフォーマンスの向上がごくわずかであるため、使用しないことをお勧めします。参照 https://stackoverflow.com/a/766996/1432614