[MySQL] ERROR 1267 (HY000): Illegal mix of collations 해결

가끔 데이터를 정제할 때 다른 테이블에 있는 데이터를 조회해서 정제할 테이블에 UPDATE문으로 수정해주는 경우가 있는데요 이럴 때 가끔 정제하려는 테이블과 데이터를 조회하는 테이블의 Collation이 달라서 에러가 발생하는 경우가 있습니다. 현재 포스팅의 제목이 바로 그 에러인데요 어떻게 해결하는지 알아봅시다.

 

에러 메시지

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

 

위의 에러 메시지는 보통 가장 많이 쓰는 Collation인데요 테이블을 조회할 때 조건절에서 서로 다른 Collation의 값을 비교하면 나오는 에러입니다.

 

해결 방법

방법은 2가지인데 생성할 때부터 collation을 맞춰주는것이 있고 조회 쿼리에서 collation을 변경하는 방법이 있는데 전자의 경우는 제 상황에서는 손이 많이 가서 후자의 방법으로 해결했습니다. 예를 들어 아래와 같이 쿼리를 수정했습니다.

 

WHERE T1.TABLE_NAME = T2.TABLE_NAME COLLATE utf8_unicode_ci

 

참고로 Collation은 영어로 정렬이라는 의미를 가지고 있는데요 이는 Collation에 따라 데이터가 정렬되는 기준이 달라짐을 의미합니다.

 

Charset and collation의 사전적 의미

 

보통 대표적인 Collation은 위의 에러메시지에서 보셨듯이 utf8_general_ci와 utf8_unicode_ci인데요 둘의 차이점은 utf8_general_ci는 비교적 속도가 빠르지만 정확도가 비교적 떨어지고 그에 반해 utf8_unicode_ci는 정확도가 비교적 높지만 속도는 비교적 느립니다.

 

구글링을 하면 보통 위의 설명만하고 이유는 알지못한채 찝찝한 상태로 구글링을 마치게되는데요 조금 더 깊이 들어가서 왜 저런 차이가 나는지 정리해봅니다.

 

사실 그다지 깊지는 않지만 실질적으로 두가지 collation의 내부에서 사용되는 알고리즘 자체가 다르기 때문에 한쪽이 당연히 속도가 느리거나 빠르거나 할 것입니다. 사실 그 차이는 미세하기 때문에 엄청나게 많은 데이터가 아닌 이상 거의 차이가 없다고 볼 수 있습니다. 또한 정확도도 마찬가지로 우리가 일반적으로 접하는 언어(한국어, 중국어, 영어, 일본어 등)는 정확도 또한 거의 차이가 없다고 할 수 있습니다. 하지만 아랍어, 라틴어 등 자주 접하지 못한 언어들은 정렬이 애매한 기준인데요 utf8_unicode_ci는 unicode라는 단어에서 알 수 있듯이 자주 접하지 못한 언어라 할지라도 정렬이 잘 이루어진다고 합니다. 하지만 utf8_general_ci는 약간 애매끼리한 느낌이라고 하네요.

 

결론은 일반적인 언어팩 환경이라면 둘의 차이는 거의 없다는 것입니다! 그렇기 때문에 위의 에러를 마주하기를 원하지 않으신다면 하나의 프로젝트 안에서는 한가지 collation을 선택하여 DB를 설계하시는게 좋겠네요.