mysql에서“잘못된 조합의 데이터 정렬”오류 문제 해결

mysql에서“잘못된 조합의 데이터 정렬”오류 문제 해결

---

MySQL의 저장 프로 시저를 통해 선택하려고 할 때 아래 오류가 발생합니다.

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

여기서 무엇이 잘못 될지 알고 있습니까?

테이블의 데이터 정렬과 latin1_general_ciwhere 절의 열 데이터 정렬은 입니다 latin1_general_cs.

---

이는 일반적으로 호환되지 않는 두 데이터 정렬의 문자열을 비교하거나 다른 데이터 정렬의 데이터를 결합 된 열로 선택하려고함으로써 발생합니다.

이 절 COLLATE에서는 쿼리에 사용 된 데이터 정렬을 지정할 수 있습니다.

예를 들어 다음 WHERE절은 항상 게시 한 오류를 나타냅니다.

WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs

해결책은 쿼리 내의 두 열에 대해 공유 데이터 정렬을 지정하는 것입니다. 이 COLLATE절 을 사용하는 예는 다음과 같습니다 .

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

다른 옵션은 BINARY연산자 를 사용하는 것 입니다.

BINARY str은 CAST (str AS BINARY)의 약어입니다.

솔루션은 다음과 같습니다.

SELECT * FROM table WHERE BINARY a = BINARY b;

또는,

SELECT * FROM table ORDER BY BINARY a;

---

TL; DR

일치하도록 문자열 중 하나 (또는 ​​둘 다)의 데이터 정렬을 변경하거나 COLLATE표현식에 절을 추가 하십시오.

---

1. 어쨌든이 "콜 레이션"은 무엇입니까?

   일반적으로 문자 집합 및 데이터 정렬에 설명 된대로 :

   문자 집합 기호와 인코딩의 집합입니다. 정렬은 문자 집합의 문자를 비교하기위한 규칙의 집합입니다. 가상의 문자 집합의 예를 통해 구별을 명확하게 만들어 봅시다.

   “ A”,“ B”,“ a”,“ b”의 네 글자로 된 알파벳이 있다고 가정합니다 . 우리는 각 문자에 숫자“ A”= 0,“ B”= 1,“ a”= 2,“ b”= 3을 부여합니다. 문자“ A”는 기호이고 숫자 0은 “ ” 의 인코딩A 이며 모든 문자 의 조합 네 글자와 해당 인코딩은 문자 세트 입니다.

   두 문자열 값“ A”및“ B” 을 비교한다고 가정합니다 . 이 작업을 수행하는 가장 간단한 방법은 인코딩 보는 것입니다 : "0 A"에 대한 1 " B". 0이 1 A보다 작으므로“ B” 이“ ” 보다 작습니다 . 방금 수행 한 작업은 문자 집합에 데이터 정렬을 적용하는 것입니다. 데이터 정렬은 일련의 규칙입니다 (이 경우 하나의 규칙 만 해당).“인코딩 비교” 우리는 가능한 모든 데이터 정렬 중 가장 간단한 데이터를 이진 데이터 정렬이라고 부릅니다 .

   그러나 소문자와 대문자가 같다고 말하고 싶다면 어떻게해야합니까? (1) 소문자 문자 "치료 : 그런 다음 우리는 적어도 두 가지 규칙을 것 a"과 " b동등"을 " A"및 " B"; (2) 그런 다음 인코딩을 비교하십시오. 이를 대소 문자를 구분하지 않는 데이터 정렬이라고합니다. 이진 데이터 정렬보다 조금 더 복잡합니다.

   실제로 대부분의 문자 세트에는“ A”및“ B”뿐만 아니라 전체 알파벳, 여러 문자 또는 수천 개의 문자가 포함 된 동부 쓰기 시스템, 많은 특수 기호 및 문장 부호와 같은 많은 문자가 있습니다. 또한 실제로는 대부분의 데이터 정렬에 소문자 구분 여부뿐만 아니라 악센트 구분 여부 ( "액센트"는 독일어 " Ö"에서와 같이 문자에 첨부 된 표시 )와 여러 문자에 대한 규칙이 많이 있습니다. 매핑 (예 : 두 독일 데이터 정렬 중 하나에서 “ Ö”=“ OE” 규칙 ).

   데이터 정렬 효과의 예 아래에 추가 예가 나와 있습니다 .

2. 자, 그러나 MySQL은 주어진 식에 사용할 데이터 정렬을 어떻게 결정합니까?

   식의 데이터 정렬에 설명 된대로 :

   대부분의 문장에서 MySQL이 대조 연산을 해결하기 위해 사용하는 것은 분명합니다. 예를 들어, 다음과 같은 경우 데이터 정렬이 열의 데이터 정렬이라는 것이 분명해야합니다 charset_name.

   SELECT x FROM T ORDER BY x;
   SELECT x FROM T WHERE x = x;
   SELECT DISTINCT x FROM T;
   

   그러나 피연산자가 여러 개인 경우 모호성이있을 수 있습니다. 예를 들면 다음과 같습니다.

   SELECT x FROM T WHERE x = 'Y';
   

   비교시 열 x또는 문자열 리터럴 의 데이터 정렬을 사용해야 'Y'합니까? 모두 x와 'Y'정렬을, 그래서 어떤 조합이 우선?

   표준 SQL은 예전에는 "강제력"규칙이라고하는 것을 사용하여 이러한 질문을 해결합니다.

   [데 레시아 ]

   MySQL은 다음 규칙과 함께 강제성 값을 사용하여 모호성을 해결합니다.

   • 보자력 값이 가장 낮은 데이터 정렬을 사용하십시오.

   • 양쪽이 동일한 보자력을 갖는 경우 :

     • 양면이 유니 코드이거나 양면이 유니 코드가 아닌 경우 오류입니다.

     • 측면 중 하나에 유니 코드 문자 집합이 있고 다른쪽에 비 유니 코드 문자 집합이 있으면 유니 코드 문자 집합이있는 쪽이 이기고 자동 문자 집합 변환이 비 유니 코드쪽에 적용됩니다. 예를 들어, 다음 명령문은 오류를 리턴하지 않습니다.

       SELECT CONCAT(utf8_column, latin1_column) FROM t1;
       

       의 문자 집합과 utf8데이터 정렬을 가진 결과를 반환합니다 utf8_column. 의 값은 연결 latin1_column하기 utf8전에 자동으로 변환됩니다 .

     • 혼합 동일한 문자 집합에서 피연산자과의 작동을하지만, _bin정렬하고, _ci또는 _cs정렬을의 _bin정렬이 사용됩니다. 이는 비 이진 문자열과 이진 문자열을 혼합하는 연산이 피연산자를 이진 문자열로 평가하는 방식과 비슷하지만 데이터 유형이 아닌 데이터 정렬을위한 것입니다.

3. "부정합 한 데이터 정렬"이란 무엇입니까?

   "부정합 한 조합의 콜 레이션"은식이 서로 다른 콜 레이션이지만 동일한 보자력을 가진 두 문자열을 비교할 때 발생하며, 보자력 규칙은 충돌을 해결하는 데 도움이되지 않습니다. 위 인용문의 세 번째 글 머리표에 설명 된 상황입니다.

   질문에 주어진 특정 오류 Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='는 동일한 보자력을 가진 두 개의 비 유니 코드 문자열 사이에 동등 비교가 있음을 나타냅니다. 또한 데이터 정렬이 명령문에 명시 적으로 제공되지 않고 문자열 소스 (예 : 열 메타 데이터)에서 암시되었음을 나타냅니다.

4. 그것은 모두 잘되지만, 그러한 오류를 어떻게 해결합니까?

   위에서 인용 한 수동 추출에서 알 수 있듯이이 문제는 여러 가지 방법으로 해결할 수 있으며 그 중 두 가지가 합리적이고 권장됩니다.

   • 일치하는 문자열이 더 이상 모호하지 않도록 문자열 중 하나 (또는 ​​둘 다)의 데이터 정렬을 변경하십시오.

     How this can be done depends upon from where the string has come: Literal expressions take the collation specified in the collation_connection system variable; values from tables take the collation specified in their column metadata.

   • Force one string to not be coercible.

     I omitted the following quote from the above:

     MySQL assigns coercibility values as follows:

     • An explicit COLLATE clause has a coercibility of 0. (Not coercible at all.)

     • The concatenation of two strings with different collations has a coercibility of 1.

     • The collation of a column or a stored routine parameter or local variable has a coercibility of 2.

     • A “system constant” (the string returned by functions such as USER() or VERSION()) has a coercibility of 3.

     • The collation of a literal has a coercibility of 4.

     • NULL or an expression that is derived from NULL has a coercibility of 5.

     Thus simply adding a COLLATE clause to one of the strings used in the comparison will force use of that collation.

   Whilst the others would be terribly bad practice if they were deployed merely to resolve this error:

   • Force one (or both) of the strings to have some other coercibility value so that one takes precedence.

     Use of CONCAT() or CONCAT_WS() would result in a string with a coercibility of 1; and (if in a stored routine) use of parameters/local variables would result in strings with a coercibility of 2.

   • Change the encodings of one (or both) of the strings so that one is Unicode and the other is not.

     This could be done via transcoding with CONVERT(expr USING transcoding_name); or via changing the underlying character set of the data (e.g. modifying the column, changing character_set_connection for literal values, or sending them from the client in a different encoding and changing character_set_client / adding a character set introducer). Note that changing encoding will lead to other problems if some desired characters cannot be encoded in the new character set.

   • Change the encodings of one (or both) of the strings so that they are both the same and change one string to use the relevant _bin collation.

     Methods for changing encodings and collations have been detailed above. This approach would be of little use if one actually needs to apply more advanced collation rules than are offered by the _bin collation.

---

Adding my 2c to the discussion for future googlers.

I was investigating a similar issue where I got the following error when using custom functions that recieved a varchar parameter:

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

Using the following query:

mysql> show variables like "collation_database";
    +--------------------+-----------------+
    | Variable_name      | Value           |
    +--------------------+-----------------+
    | collation_database | utf8_general_ci |
    +--------------------+-----------------+

I was able to tell that the DB was using utf8_general_ci, while the tables were defined using utf8_unicode_ci:

mysql> show table status;
    +--------------+-----------------+
    | Name         | Collation       |
    +--------------+-----------------+
    | my_view      | NULL            |
    | my_table     | utf8_unicode_ci |
    ...

Notice that the views have NULL collation. It appears that views and functions have collation definitions even though this query shows null for one view. The collation used is the DB collation that was defined when the view/function were created.

The sad solution was to both change the db collation and recreate the views/functions to force them to use the current collation.

• Changing the db's collation:

  ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
  

I hope this will help someone.

---

Sometimes it can be dangerous to convert charsets, specially on databases with huge amounts of data. I think the best option is to use the "binary" operator:

e.g : WHERE binary table1.column1 = binary table2.column1

---

I had a similar problem, was trying to use the FIND_IN_SET procedure with a string variable.

SET @my_var = 'string1,string2';
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);

and was receiving the error

Error Code: 1267. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'

Short answer:

No need to change any collation_YYYY variables, just add the correct collation next to your variable declaration, i.e.

SET @my_var = 'string1,string2' COLLATE utf8_unicode_ci;
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);

Long answer:

I first checked the collation variables:

mysql> SHOW VARIABLES LIKE 'collation%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_general_ci |
    +----------------------+-----------------+
    | collation_database   | utf8_general_ci |
    +----------------------+-----------------+
    | collation_server     | utf8_general_ci |
    +----------------------+-----------------+

Then I checked the table collation:

mysql> SHOW CREATE TABLE my_table;

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column_name` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This means that my variable was configured with the default collation of utf8_general_ci while my table was configured as utf8_unicode_ci.

By adding the COLLATE command next to the variable declaration, the variable collation matched the collation configured for the table.

---

You can try this script, that converts all of your databases and tables to utf8.

---

Solution if literals are involved.

I am using Pentaho Data Integration and dont get to specify the sql syntax. Using a very simple DB lookup gave the error "Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='"

The generated code was "SELECT DATA_DATE AS latest_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE PSEUDO_KEY = ?"

Cutting the story short the lookup was to a view and when I issued

mysql> show full columns from hr_cc_normalised_data_date_v;
+------------+------------+-------------------+------+-----+
| Field      | Type       | Collation         | Null | Key |
+------------+------------+-------------------+------+-----+
| PSEUDO_KEY | varchar(1) | cp850_general_ci  | NO   |     |
| DATA_DATE  | varchar(8) | latin1_general_cs | YES  |     |
+------------+------------+-------------------+------+-----+

which explains where the 'cp850_general_ci' comes from.

The view was simply created with 'SELECT 'X',......' According to the manual literals like this should inherit their character set and collation from server settings which were correctly defined as 'latin1' and 'latin1_general_cs' as this clearly did not happen I forced it in the creation of the view

CREATE OR REPLACE VIEW hr_cc_normalised_data_date_v AS
SELECT convert('X' using latin1) COLLATE latin1_general_cs        AS PSEUDO_KEY
    ,  DATA_DATE
FROM HR_COSTCENTRE_NORMALISED_mV
LIMIT 1;

now it shows latin1_general_cs for both columns and the error has gone away. :)

---

MySQL really dislikes mixing collations unless it can coerce them to the same one (which clearly is not feasible in your case). Can't you just force the same collation to be used via a COLLATE clause? (or the simpler BINARY shortcut if applicable...).

---

If the columns that you are having trouble with are "hashes", then consider the following...

If the "hash" is a binary string, you should really use BINARY(...) datatype.

If the "hash" is a hex string, you do not need utf8, and should avoid such because of character checks, etc. For example, MySQL's MD5(...) yields a fixed-length 32-byte hex string. SHA1(...) gives a 40-byte hex string. This could be stored into CHAR(32) CHARACTER SET ascii (or 40 for sha1).

Or, better yet, store UNHEX(MD5(...)) into BINARY(16). This cuts in half the size of the column. (It does, however, make it rather unprintable.) SELECT HEX(hash) ... if you want it readable.

Comparing two BINARY columns has no collation issues.

---

A possible solution is to convert the entire database to UTF8 (see also this question).

---

One another source of the issue with collations is mysql.proc table. Check collations of your storage procedures and functions:

SELECT
  p.db, p.db_collation, p.type, COUNT(*) cnt
FROM mysql.proc p
GROUP BY p.db, p.db_collation, p.type;

Also pay attention to mysql.proc.collation_connection and mysql.proc.character_set_client columns.

---

If you have phpMyAdmin installed, you can follow the instructions given in the following link: https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation You have to match the collate of the database with that of all the tables, as well as the fields of the tables and then recompile all the stored procedures and functions. With that everything should work again.

---

I used ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;, but didn't work.

In this query:

Select * from table1, table2 where table1.field = date_format(table2.field,'%H');

This work for me:

Select * from table1, table2 where concat(table1.field) = date_format(table2.field,'%H');

Yes, only a concat.

---

This code needs to be put inside Run SQL query/queries on database

SQL QUERY WINDOW

ALTER TABLE `table_name` CHANGE `column_name` `column_name`   VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

Please replace table_name and column_name with appropriate name.

참고URL : https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql