illegal mix of collations — 콜레이션 불일치 조인 에러 해결

두 테이블을 조인했을 뿐인데 Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' 같은 에러가 나는 경우가 있습니다. 쿼리 문법은 멀쩡한데, 비교하려는 두 문자열 컬럼의 콜레이션이 다르기 때문입니다. MySQL은 문자열을 비교할 때 어떤 정렬·비교 규칙을 쓸지 하나로 정해야 하는데, 서로 호환되지 않는 콜레이션이 충돌하면 비교 자체를 거부합니다.

왜 생기는가 — 문자셋과 콜레이션

문자셋(charset)은 문자를 저장하는 인코딩이고, 콜레이션(collation)은 그 문자를 비교·정렬하는 규칙입니다. 같은 utf8mb4라도 콜레이션이 다르면 충돌합니다.

가장 흔한 시나리오는 마이그레이션 이력입니다. 옛 테이블은 utf8_general_ci로 만들어졌고, 새로 만든 테이블은 utf8mb4_unicode_ci라서 두 user_id를 조인하는 순간 터집니다.

진단 — 어느 컬럼이 다른지 본다

먼저 조인에 쓰이는 컬럼의 실제 콜레이션을 확인합니다.

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'shop'
  AND column_name IN ('user_id', 'email')
  AND collation_name IS NOT NULL;

두 컬럼의 collation_name이 다르면 그게 원인입니다.

해결 — 임시 vs 근본

① 즉시 우회 (쿼리에서 강제 변환)

핫픽스가 필요할 때는 조인 조건에서 한쪽 콜레이션을 명시적으로 맞춥니다.

SELECT *
FROM orders o
JOIN users u
  ON o.user_id = u.user_id COLLATE utf8mb4_unicode_ci;

단, COLLATE를 씌운 컬럼은 인덱스를 못 타게 되어 느려질 수 있으니 임시 조치로만 씁니다.

② 근본 해결 (컬럼 콜레이션 통일)

스키마를 한쪽 기준으로 맞추는 것이 정답입니다.

ALTER TABLE orders
  MODIFY user_id VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

테이블 전체를 한 번에 정리하려면 ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;를 씁니다. 대용량 테이블에서는 잠금·시간이 크니 온라인 DDL 도구(gh-ost, pt-online-schema-change)를 고려합니다.

③ 재발 방지

DB·테이블 기본 콜레이션을 하나로 표준화하고(utf8mb4_0900_ai_ci 또는 utf8mb4_unicode_ci), 새 테이블 생성 시 항상 명시합니다.

요점 정리

• 에러의 본질은 문법이 아니라 비교 규칙(콜레이션) 충돌입니다.
• information_schema.columns로 양쪽 컬럼 콜레이션을 먼저 대조합니다.
• 쿼리 COLLATE는 임시 우회(인덱스 손실), ALTER로 통일하는 게 근본책입니다.

문자셋·콜레이션 설계와 마이그레이션을 실습으로 다뤄보려면 데이터베이스 트랙에서 회원가입 없이 무료로 시작할 수 있습니다.