'='작업에 대한 데이터 정렬 (utf8_unicode_ci, IMPLICIT)과 (utf8_general_ci, IMPLICIT)의 잘못된 조합

'='작업에 대한 데이터 정렬 (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를 다시 작성하는 테스트를 수행했지만 좋지 않습니다.

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

---

네 가지 옵션이 있습니다.

옵션 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매개 변수 정의에 추가하십시오 .

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;

as the default collation for stored procedure parameters is utf8_general_ci and you can't mix collations.

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

---

I spent half a day searching for answers to an identical "Illegal mix of collations" error with conflicts between utf8_unicode_ci and utf8_general_ci.

I found that some columns in my database were not specifically collated utf8_unicode_ci. It seems mysql implicitly collated these columns utf8_general_ci.

Specifically, running a 'SHOW CREATE TABLE table1' query outputted something like the following:

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

Note the line 'col1' varchar(4) CHARACTER SET utf8 NOT NULL does not have a collation specified. I then ran the following query:

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

This solved my "Illegal mix of collations" error. Hope this might help someone else out there.

---

I had a similar problem, but it occurred to me inside procedure, when my query param was set using variable e.g. SET @value='foo'.

What was causing this was mismatched collation_connection and Database collation. Changed collation_connection to match collation_database and problem went away. I think this is more elegant approach than adding COLLATE after param/value.

To sum up: all collations must match. Use SHOW VARIABLES and make sure collation_connection and collation_database match (also check table collation using SHOW TABLE STATUS [table_name]).

---

A bit similar to @bpile answer, my case was a my.cnf entry setting collation-server = utf8_general_ci. After I realized that (and after trying everything above), I forcefully switched my database to utf8_general_ci instead of utf8_unicode_ci and that was it:

ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;

---

In my own case I have the following error

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

$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);

After weeks of google searching I noticed that the two fields I am comparing consists of different collation name. The first one i.e username is of utf8_general_ci while the second one is of utf8_unicode_ci so I went back to the structure of the second table and changed the second field (matric_no) to utf8_general_ci and it worked like a charm.

---

Despite finding an enormous number of question about the same problem (1, 2, 3, 4) I have never found an answer that took performance into consideration, even here.

Although multiple working solutions has been already given I would like to do a performance consideration.

EDIT: Thanks to Manatax for pointing out that option 1 does not suffer of performance issues.

Using Option 1 and 2, aka the COLLATE cast approach, can lead to potential bottleneck, cause any index defined on the column will not be used causing a full scan.

Even though I did not try out Option 3, my hunch is that it will suffer the same consequences of option 1 and 2.

Lastly, Option 4 is the best option for very large tables when it is viable. I mean there are no other usage that rely on the original collation.

Consider this simplified query:

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

In my original example, I had many more joins. Of course, table1 and table2 have different collations. Using the collate operator to cast, it will lead to indexes not being used.

See sql explanation in the picture below.

Visual Query Explanation when using the COLLATE cast

On the other hand, option 4 can take advantages of possible index and led to fast queries.

In the picture below, you can see the same query being run after applied Option 4, aka altering the schema/table/column collation.

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

In conclusion, if performance are important and you can alter the collation of the table, go for Option 4. If you have to act on a single column, you can use something like this:

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

---

This happens where a column is explicitly set to a different collation or the default collation is different in the table queried.

if you have many tables you want to change collation on run this query:

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;

this will output the queries needed to convert all the tables to use the correct collation per column

참고URL : https://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic