MySQL 쿼리에서의 콜레이션 혼란: LIKE 연산자의 함정

카테고리

프로그래밍/소프트웨어 개발

서브카테고리

웹 개발

대상자

- MySQL 데이터베이스 쿼리 작성자, 특수 문자 처리가 필요한 개발자

- 난이도: 중급(콜레이션 개념과 LIKE 연산자 동작 방식 이해 필요)

핵심 요약

• 콜레이션(collation) 설정은 LIKE 연산자의 검색 결과에 직접적인 영향을 미침

- utf8mb4_general_ci vs utf8mb4_unicode_ci의 차이점

• ß와 같은 특수 문자의 경우, utf8mb4_unicode_ci는 ss로 매핑

- utf8mb4_general_ci는 s로 간단히 매핑

• LIKE 연산자는 문자 단위 매칭을 수행

- utf8mb4_unicode_ci에서도 ß LIKE 'ss'는 FALSE로 평가

섹션별 세부 요약

1. 예제를 통한 문제 발생

• Fußball, Mädchen 등 특수 문자 포함 데이터베이스 테이블 생성
• LIKE "%ß%" 사용 시 예상치 못한 결과 발생 (예: summer이 포함됨)
• utf8mb4_general_ci 사용 시 ß가 s로 간단히 매핑

2. 콜레이션의 역할

• 콜레이션은 문자 비교, 정렬 등에 직접 영향을 미침

- utf8mb4_general_ci: 빠르지만 정확도 낮음

- utf8mb4_unicode_ci: 복잡한 매핑 지원, 정확도 높음

• ß의 경우:

- utf8mb4_general_ci: ß = s

- utf8mb4_unicode_ci: ß = ss

3. `LIKE` 연산자의 특이한 동작

• LIKE는 문자 단위로 매칭

- utf8mb4_unicode_ci에서도 ß LIKE 'ss'는 FALSE

• = 연산자와 LIKE 연산자의 차이점:

- ß = 'ss'는 TRUE

- ß LIKE 'ss'는 FALSE

4. 해결 방안

• 필요한 경우 명시적 콜레이션 지정

- SELECT * FROM foo WHERE text_column LIKE "%ß%" COLLATE utf8mb4_unicode_ci;

• 언어별 특수 규칙 적용 (예: 독일어 DIN-2 정렬에 utf8mb4_german2_ci 사용)

결론

• LIKE 연산자 사용 시 콜레이션 설정을 명시적으로 지정해야 정확한 검색 결과 확보 가능

- COLLATE 절을 사용한 명시적 콜레이션 설정 권장

• 특수 문자 처리는 콜레이션 선택과 LIKE/= 연산자 차이에 따라 결과가 극적으로 달라질 수 있음

- 데이터베이스 설계 단계에서 콜레이션 정책을 명확히 정의해야 함