MySQL 오류: Illegal mix of collations for operation 'like'

Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation 'like'

 

 

Illegal mix of collations for operation 오류는

MySQL에서 문자열 비교 연산(LIKE, =, IN)을 수행할 때, 비교에 사용되는 문자열들의 collation이 서로 다를 경우 발생한다.

 

그중에서 'LIKE' 오류는 

LIKE 비교 연산에서 좌변과 우변의 collation이 일치하지 않거나, 우변 내부의 구성 요소들끼리 collation이 달라 발생한다.

 

---

 

✅ Character Set과 Collation 개념 정리

1. Character Set (문자 집합)

• 데이터를 어떤 문자 체계로 저장할지를 정의함
• 예시: utf8mb4, latin1, euckr 등
• utf8mb4는 이모지까지 지원하는 UTF-8의 확장형

2. Collation (정렬 규칙)

• 같은 문자셋(Character Set) 내에서 문자열 비교/정렬 방식을 정의
• 예시:
  • utf8mb4_bin: 바이너리 기반 비교 (대소문자 구분, 정렬 X)
  • utf8mb4_unicode_ci: 유니코드 표준 기반 정렬 (대소문자 구분 X, 언어 특성 반영)
  • utf8mb4_0900_ai_ci: MySQL 8.0 기본값 (악센트/대소문자 무시)

3. COERCIBLE (강제 타입 변환 우선순위)

• 리터럴이나 파라미터는 명시적 collation이 없을 경우 세션의 기본값(collation_connection)을 따름
• 이로 인해 문자열 비교 시 오류가 발생할 수 있음

4. Collation 관련 주의사항

• 세션마다 collation_connection 값이 달라질 수 있음
  • 워크벤치, IntelliJ, API 클라이언트 접속 도구마다 다르게 설정될 수 있음
  • 따라서 같은 쿼리라도 실행 환경에 따라 결과가 달라질 수 있음
• 같은 문자셋(utf8mb4)을 사용하더라도 collation이 다르면 문자열 비교 충돌 발생 가능
  • 예: '가나다' = '가나다'인데도 collation 차이로 false 처리

💡 MySQL 8.0부터 기본 문자셋은 utf8mb4, 기본 정렬 방식(collation)은 utf8mb4_0900_ai_ci 로 바뀌었다.

 

 

✅ collation 설정 확인 쿼리

-- 세션 및 서버 확인
SHOW VARIABLES LIKE 'collation%';
SHOW VARIABLES LIKE 'character_set%';

-- 데이터베이스 확인
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'your_database_name';

-- 테이블 확인
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'table_name';

-- 컬럼 확인
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'table_name';

 

 

✅ LIKE 연산 시 collation 충돌 요소

아래는 오류가 발생한 쿼리 예시이다. (mybatis)

SELECT *
FROM member
WHERE teamCode IN (
  SELECT teamCode 
  FROM team 
  WHERE teamName LIKE CONCAT('%', #{keyword}, '%')
)

 

 

1. 테이블 및 컬럼 collation → utf8mb4_bin

• 위 조회 쿼리로 테이블, 컬럼의 collation 을 확인해보니 모두 utf8mb4_bin 로 동일했다.

 

2. #{keyword} : JDBC 파라미터 → utf8mb4_unicode_ci

• Spring, MyBatis, JPA 등에서 SQL에 바인딩되는 파라미터는 JDBC 드라이버가 처리한다.
• 별도로 COLLATE를 지정하지 않으면, 해당 문자열을 DB 세션의 기본 설정 또는 드라이버의 default 설정에 따라 전달한다.
• 대체로 MySQL JDBC 드라이버는 문자열 파라미터를 utf8mb4_unicode_ci로 해석한다.

직접 확인은 어려우나 추론 방법:

1. 쿼리에 다음처럼 바인딩 파라미터를 직접 리터럴로 교체해보고 오류 메시지 확인:

LIKE CONCAT('%', '테스트', '%')

 

2. 또는 쿼리 로그에서 JDBC가 실제로 넘긴 SQL을 출력해보고 '문자열' 리터럴과 비교되는 collation이 어떤 것인지 확인

 

3. 의심된다면 다음처럼 명시적으로 바인딩에 COLLATE를 붙여 테스트해볼 수 있다:

LIKE CONCAT('%', #{keyword} COLLATE utf8mb4_0900_ai_ci, '%')

 

3. '%' : 문자열 리터럴 → 세션 기본값(collation_connection)에 따라 utf8mb4_0900_ai_ci

• 쿼리 내에 직접 쓰인 문자열 CONCAT('%', '%#{keyword}, '%')은 해당 세션의 collation_connection 값을 자동으로 따른다.
• 이 값은 MySQL 8.0 이상에서는 기본적으로 utf8mb4_0900_ai_ci 일 가능성이 높다.
• 실제로 확인한 collation_connection 값 도 utf8mb4_0900_ai_ci 였다.

 

 

✅ 오류 원인

위 3가지 내용을 확인해본 결과 LIKE 비교 연산에서 우변(CONCAT('%', #{keyword}, '%')) 내부의 구성 요소들끼리 collation이 달라 병합에 실패했기 때문이라는것을 알 수 있었다.

• 컬럼 collation: utf8mb4_bin
• #{keyword} 파라미터 → JDBC에서 넘어오며 utf8mb4_unicode_ci
• '%' : 문자열 리터럴 → 세션 기본값(collation_connection)에 따라 utf8mb4_0900_ai_ci

→ CONCAT() 내부 구성요소 collation이 달라 병합 실패해  utf8mb4_0900_ai_ci  vs  utf8mb4_unicode_ci 충돌 발생

 

✅ 해결 방법

• 테이블과 컬럼은 모두 동일한 utf8mb4_bin 이었고 우변 내부에서 utf8mb4_unicode_ci와 utf8mb4_0900_ai_ci 간 충돌 발생
• 해결 방법: 좌변/우변에 COLLATE utf8mb4_unicode_ci 명시로 해결

WHERE column_name COLLATE utf8mb4_unicode_ci LIKE CONCAT('%', #{keyword}, '%') COLLATE utf8mb4_unicode_ci

 

📌 나의 경우 LIKE 우변의 CONCAT 처리 과정에서 collation 충돌이 발생했다.
따라서 쿼리에서 명시적으로 COLLATE를 선언하는 것이 가장 현실적이고 간단한 해결책이었지만,

새로운 테이블 추가, 스키마를 설계할 때에는 처음부터 컬럼의 collation을 일관되게 맞춰두는 것이 근본적인 예방책이 될 수 있다.

 

 

정리

• LIKE 오류는 좌/우 비교가 아닌 우변 표현식 내부의 collation 충돌로 발생할 수 있다.
• JDBC 파라미터와 SQL 리터럴의 collation이 다르면 오류 발생 가능
• 좌변에 COLLATE를 명시하는 것이 가장 안정적이며 재현 가능성이 낮아진다.
• SHOW VARIABLES LIKE 'collation_connection'으로 실제 세션 상태를 항상 확인할 것

 

📚 참고 문서

• MySQL 공식 문서 - Collation Handling
• MySQL 공식 문서 - Character Set and Collation in Expressions
• MySQL 8.0 Release Notes - Default Collation Change