/
/

[TIL] Real MySQL 콜레이션

박상준
2025.03.15
·
8 min read

콜레이션 정의

  • 문자를 비교하거나 정렬할때 사용되는 규칙임.

    • 문자가 어떻게 서로 비교되는지, 어떤 순서로 나열되는지 정의한다.

    • 알파벳 대문자 A , 소문자 a 가 같은지,, 어느 것이 먼저 오는지 결정함.

콜레이션

  • 문자집합 - 캐릭터셋의 종속적이다.

    • 문자집합은

      • 각 문자에 할당된 고유한 코드값의 집합을 의미한다.

        • 대문자 A

          • U+0041

        • 소문자 a

          • U+0061

        • 해당 코드값을 통해 컴퓨터가 문자를 인식하고 처리할 수 있음.

  • MySQL 에서는 문자열 타입을 가지는 모든 컬럼들은 자신만의 독립적인 문자 집합과 콜레이션을 가질 수 있음.

    • MySQL 서버 혹시 DB 내 오브젝트들에 별도로 콜레이션을 설정하지 않으면, MySQL 서버에 설정된 문자 집합의 디폴트 콜레이션을 사용한다.

    • DB 내에 생성되는 오브젝트 들에도 해당 내용이 적용됨.

콜레이션 네이밍 컨벤션

  • 4 부분으로 구성되어 있습니다.

첫번째 : 문자 집합

  • 콜레이션 이름 시작 부분

  • 해당 콜레이션이 속한 문자 집합을 나타냄.

  • UTF8MB4, UTF8MB3 등

두번째 : 언어종속(선택)

  • 특정 언어에 대해 해당 언어에서 정의한 정렬 순서가 문자 비교 시 적용됨

  • locale 코드나 language 값이 표시된다.

  • 표시된 언어에 대해서 그 언어에서 정의한 정렬 순서가 사용

세번째 : UCA 버전(선택)

  • 문자열 비교 표준 알고리즘 UCA(Unicode Collation Algorithm) 버전 정보를 타나냄

  • 0900 은 UCA 9.0.0 버전

  • GENERAL 이라는 단어가 포함된 콜레이션은 UCA 기반은 아니고

  • MySQL 에서 비교, 성능 향상 등을 위해 자체적으로 커스텀한 규칙이 적용된 콜레이션임

네번째 : 민감도

  • 콜레이션에서 문자열 비교

    • 악센트 구분 (AI : Accent Insensitive, AS : Accent Sensitive )

    • 대소문자 구분 (CI : Case Insentive, CS : Case Sensitive )

    • 일본어 히라가나 가타카나 구분 (KS : Kana Sensitive )

    • 바이너리 값 기반 비교 (BIN : Binary 비교 )

유니코드 기반 문자 집합 어떻게 저장되나

  • 가장 많이 사용되는 문자집합은 UTF8MB4 이다

  • 문자열 데이터 저장시 UTF-8 인코딩 방식이 사용된다.

    • UTF-8 인코딩 방식은 가변 길이 인코딩

    • 코드 포인트 범위에 따라 문자가 1 ~ 4 바이트로 인코딩된다.

      • 문자열 가 의 유니코드 코드 포인트는 AC00 으로, UTF-8 인코딩에서 3바이트로 인코딩된다.

      • 16진수로 EAB080 이 된다.

문자열 데이터 비교 방식

  • 문자열 비교시 , 각 문자열에 대해 가중치 값 을 계산한 후에 비교한다고 한다.

  • 유니코드 기반 문자 집합에 속하는 콜레이션에서는..

    • DUCET (Default Unicode Collation Element Table) 이라는 데이터 시트에 정의된 가중치 값을 사용해 비교한다.

      • DUCET 에서 문자별 가중치 값은

        • 단계적으로 구성되어 있는데

          • Primart weight

            • 기본 문자 비교

          • Secondary weight

            • 악센트 구분

          • Tertiary weight

            • 대소문자 구분

      • 콜레이션에 따라 사용되는 가중치 값도 다르다

        • AI_CI

          • Accent Insensitive, Case Insensitive → Primary weight 까지만 사용

        • AS_CI

          • Accent Sensitive, Case Insensitive → Secondary weight 까지만 사용

콜레이션 사용시 주의

  1. 서로 다른 콜레이션을 가진 컬럼을 비교하는 경우

    • 에러가 발생함 쿼리 수행이 안됨.

    • 쿼리에서 COLLATE 를 명시에서 비교 연산 시 원하는 콜레이션을 명시해야 한다

  2. 쿼리의 WHERE 절에서 COLLATE 키워드로 컬럼의 콜레이션을 변경함

    • WHERE name COLLATE utf8mb4_0900_ai_ci = 'ester' → 인덱스 사용이 안됨.

    • 인덱스 자체는 콜레이션을 바탕으로 정렬되어 있어서

      • COLLATE 로 변경 하는 경우 인덱스 활용 자체가 안됨.

    • 해결 방안

      • 함수 기반 인덱스를 생성해야 한다.

        • 인덱스 자체에 콜레이션을 걸어버리는 것임.

        CREATE INDEX idx_name_collate ON table_name ((name COLLATE utf8mb4_0900_ai_ci));

  3. 테이블 고유키 (PK, UK)

    • 콜레이션 영향을 받는다.

    • 문자열 컬럼은 중복 여부 확인을 위해 값을 비교함. + 콜레이션이 사용됨.

      • CS ( case sensitive )

        • ester 와 Ester 는 다른 값으로 인식한다.

      • CI ( case insensitive )

        • ester 와 Ester 는 같은 값으로 인식한다.

  4. 기본 콜레이션에서의 한글 문자열의 비교

    • UTF8MB4_0900_AI_CI

      • 한글 문자 가 와 ㄱ + ㅏ 가 동일한 값으로 인식됨.

      • 이유

        • DUCET 에서 한글 초성, 중성, 종성 및 개별 문자에 대한 가중치 값이 동일하게 설정됨,.

대소문자 구분을 위한 콜레이션 선택

  • 기본적으로 CI 로 MySQL 에서 기본값으로는 대소문자 구분은 하지 않는다.

  • 대소문자 구분이 필요하면

    • 대소문자 구분 필요, 후행 공백 무시: UTF8MB4_BIN

    • 대소문자 구분 필요, 후행 공백 인식 또는 상관없음: UTF8MB4_0900_BIN 또는 UTF8MB4_0900_AS_CS

    • 모든 문자 명확히 구분: UTF8MB4_BIN 또는 UTF8MB4_0900_BIN

- 컬렉션 아티클