테이블 두 개를 조인했더니 Illegal mix of collations로 실패한 이유

문제 발생

예전에 만든 테이블과 최근 테이블을 조인하자 쿼리가 실패했습니다.

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

각 테이블을 따로 조회하면 아무 문제가 없었습니다.

원인 분석

문자열 비교는 어떤 콜레이션으로 비교할지 정해져야 성립합니다. MySQL은 표현식마다 coercibility(강제 변환 가능성) 값을 매겨 우선순위를 정합니다. 문서의 표에 따르면 명시적 COLLATE가 0으로 가장 강하고, 컬럼은 2, 문자열 리터럴은 4, 숫자·시간 값은 5, NULL은 6입니다.

값이 낮은 쪽 콜레이션이 이깁니다. 그래서 WHERE x = 'Y'는 문제가 없습니다 — 컬럼(2)이 리터럴(4)을 이깁니다.

문제는 양쪽이 같은 우선순위일 때입니다. 조인 조건의 양변이 모두 컬럼(2)인데 콜레이션이 다르면 고를 근거가 없습니다. 문서의 해소 규칙대로 둘 다 유니코드이거나 둘 다 비유니코드이면 오류가 되고, 한쪽만 유니코드면 유니코드로 자동 변환됩니다.

즉 이건 데이터 문제가 아니라 스키마 이력 문제입니다. MySQL 8.0에서 기본 콜레이션이 utf8mb4_general_ci에서 utf8mb4_0900_ai_ci로 바뀌었기 때문에, 만들어진 시점이 다른 테이블이 섞이면 이 오류를 만납니다.

해결 방안

1. 원인을 먼저 확인합니다.

SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE() AND collation_name IS NOT NULL;

2. 스키마를 통일하는 것이 정답입니다. 쿼리마다 우회하면 새 쿼리를 쓸 때마다 같은 오류를 만납니다.

ALTER TABLE legacy_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

큰 테이블에서는 잠금과 소요 시간을 미리 확인하고, 마이그레이션 파일로 남깁니다.

3. 당장 급하면 COLLATE로 명시합니다. 문서가 안내하는 해소 방법이고, 명시적 COLLATE는 coercibility 0이라 항상 이깁니다.

... ON a.email = b.email COLLATE utf8mb4_0900_ai_ci

4. 다만 이 우회에는 대가가 있습니다. 컬럼 쪽에 변환을 씌우면 그 컬럼의 인덱스를 쓸 수 없습니다. 조인 성능이 함께 무너지므로 임시 조치로만 씁니다.

5. 새 테이블은 처음부터 맞춥니다. Drizzle 스키마와 마이그레이션에서 문자셋·콜레이션을 명시해두면 이 문제가 재발하지 않습니다.

6. _ci와 _bin의 차이도 함께 정합니다. 대소문자 구분 여부는 검색 결과와 unique 제약의 의미를 바꿉니다 — 이메일 컬럼에서 특히 중요합니다.

공식 문서

• MySQL: Collation Coercibility in Expressions
• MySQL: Character Set and Collation Conversion