MySQL SQL 오류 (1267): Illegal mix of collations

각각 다른 테이블의 컬럼값을 비교하는 SQL 구문에서 아래와 같이 collation 관련 에러가 발생할 수 있습니다.

SELECT u.userid,
       u.positioncode
FROM   user u
WHERE  u.positioncode NOT IN (
           SELECT p.positioncode
           FROM   position p
       );

SQL 오류 (1267): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

이는 비교한 두 테이블의 데이터 값이 서로 다른 collation으로 구성되어 있을 때 발생합니다.

예를 들어, user 테이블의 positioncode와 position 테이블의 positioncode 를 비교하는 상황에서 user 테이블의 positioncode의 collation은 utf8_unicode_ci 인데 position 테이블의 positioncode의 collation은 utf8_general_ci 인 경우 입니다.

이걸 쿼리 내에서 해결하려면 collation을 직접 지정하여 두 컬럼의 collation을 일치시켜주면 됩니다.

SELECT u.userid,
       u.positioncode
FROM   user u
WHERE  u.positioncode COLLATE utf8_general_ci NOT IN (
           SELECT p.positioncode COLLATE utf8_general_ci
           FROM   position p
       );