[mysql] ‘=’작업에 대한 데이터 정렬 (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를 다시 작성하는 것을 테스트했지만 좋지 않습니다.

도움을 주시면 감사하겠습니다.

답변

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

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

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

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

| 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 |

ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;

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

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;