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

TL; DR

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

1. 어쨌든이 “콜 레이션”항목은 무엇입니까?

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

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

   “ A”,“ B”,“ a”,“ b”의 네 글자로 된 알파벳이 있다고 가정합니다 . 각 문자에는 숫자 “” A“,” 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. 그것은 모두 잘되지만, 그러한 오류를 어떻게 해결합니까?

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

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

     이 작업을 수행하는 방법은 문자열의 위치에 따라 다릅니다. 리터럴 표현식은 collation_connection시스템 변수에 지정된 데이터 정렬을 사용합니다 . 테이블의 값은 열 메타 데이터에 지정된 데이터 정렬을 사용합니다.

   • 하나의 문자열을 강제로 적용하지 않도록합니다.

     위의 인용문을 생략했습니다.

     MySQL은 다음과 같이 보자력 값을 지정합니다.

     • 명시 적 COLLATE절의 보자력은 0입니다. (강제하지는 않습니다.)

     • 데이터 정렬이 다른 두 문자열의 연결은 보자력이 1입니다.

     • 열 또는 저장된 루틴 매개 변수 또는 로컬 변수의 데이터 정렬의 보자력은 2입니다.

     • “시스템 상수”( USER()또는 과 같은 함수에 의해 반환되는 문자열 VERSION())는 3의 보자력을 갖습니다.

     • 리터럴의 데이터 정렬의 보자력은 4입니다.

     • NULL또는 유래 된 발현은 NULL보자력이 5이다.

     따라서 COLLATE비교에 사용 된 문자열 중 하나에 절을 추가하면 해당 데이터 정렬을 사용해야합니다.

   다른 사람들은이 오류를 해결하기 위해 배포 된 경우 매우 나쁜 습관이 될 수 있습니다.

   • 문자열 중 하나 (또는 ​​둘 다)가 다른 강제 변환 값을 가지도록하여 하나가 우선합니다.

     보자력이 1 인 문자열을 사용 CONCAT()하거나 CONCAT_WS()결과로 만들 수 있습니다. (저장된 루틴에있는 경우) 매개 변수 / 지역 변수를 사용하면 2의 보자력을 갖는 문자열이 생성됩니다.

   • 하나는 유니 코드이고 다른 하나는 그렇지 않도록 문자열 중 하나 (또는 ​​둘 다)의 인코딩을 변경하십시오.

     이것은 ;로 트랜스 코딩을 통해 수행 될 수 있습니다 . 또는 데이터의 기본 문자 집합 변경 (예 : 열 수정, 리터럴 값 변경 또는 다른 인코딩으로 클라이언트에서 전송 및 문자 집합 소개 자 변경 / 추가)을 통해. 원하는 문자를 새 문자 세트로 인코딩 할 수없는 경우 인코딩을 변경하면 다른 문제가 발생할 수 있습니다.CONVERT(expr USING transcoding_name)character_set_connectioncharacter_set_client

   • 문자열 중 하나 (또는 ​​둘 다)의 인코딩을 동일하게 변경하고 관련 _bin데이터 정렬 을 사용하도록 하나의 문자열을 변경하십시오 .

     인코딩 및 데이터 정렬을 변경하는 방법은 위에 자세히 설명되어 있습니다. 데이터 정렬에서 제공하는 것보다 더 고급 데이터 정렬 규칙을 실제로 적용해야하는 경우이 방법은 거의 _bin쓸모가 없습니다.

답변

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

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

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

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

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

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

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

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;

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

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;