MySQL Character Set과 Collation

아재발자·2024년 6월 20일
Collationcharacter setmysql
1

MySQL

목록 보기
6/6

MySQL을 다루면서 알게된 지식을 간단하게 정리하는 글입니다.

Character Set과 Collation은 MySQL이 Text 데이터를 관리하는 데 있어서 가장 기본적이고 중요한 요소 중 하나입니다.

하지만 많은 사람들이 무심코 넘어가는 이 녀석들. MySQL의 성능과 데이터 저장 방식, 검색 결과 등에 영향을 끼치는 아주아주 중요한 역할을 한다는 것을 알고 계셨나요?

이 글에서는 Character Set과 Collation에 대해서 간단하게 정리를 해보려고 합니다.

Character Set

Character Set은 MySQL이 문자열 데이터를 저장하고 처리할 때 사용하는 문자 집합을 의미합니다.

Character Set설명
ASCII기본 영어 알파벳과 숫자, 몇몇 제어 문자로 구성된 7비트 문자 집합.
최대 128개의 문자를 표현할 수 있습니다.
latin1 (ISO-8859-1)대부분의 서유럽 언어를 지원하는 1 Byte 문자 집합.
최대 256개의 문자를 표현할 수 있습니다.
utf8 (utf8mb3)최대 3Byte의 유니코드 문자를 인코딩할 수 있는 문자 집합.
BMP(Basic Multilingual Plane) 내의 문자만 표현할 수 있습니다.
utf8mb4최대 4Byte의 유니코드의 모든 문자를 인코딩할 수 있는 문자 집합.
BMP 외의 문자와 이모지까지 표현할 수 있습니다.

MySQL에서 지원하는 Character Set 확인하기

아래의 쿼리를 통해서 MySQL에서 사용 가능한 Character Set 목록을 확인할 수 있습니다.

SHOW CHARACTER SET;

Character Set 관련 변수

이 글에서는 MySQL에서 데이터를 액세스하고, Client와 통신할 때 영향을 받는 변수들만 정리합니다.

character_set_database

참고!
여기에서 말 하는 Database는 MySQL의 Schema를 의미합니다.

Database의 기본 Character Set을 정의합니다.

Database를 생성할 때 Character Set을 명시하지 않으면 이 변수에 설정된 Character Set으로 자동 생성됩니다.

만약 이 변수의 값이 설정되지 않았다면 character_set_server 변수 값을 따릅니다.

해당 변수는 추후 deprecated 될 예정이라고 하니 참고하시기 바랍니다.

공식 문서 내용
The global character_set_database and collation_database system variables are deprecated; expect them to be removed in a future version of MySQL.

character_set_client

Client가 MySQL 서버로 데이터(SQL 쿼리, Text 데이터 등)를 전송할 때 사용한 Character Set을 지정할 때 사용합니다.

기본적으로 MySQL 서버는 Client가 전송한 데이터의 Character Set을 알 수가 없습니다. 그렇기 때문에 MySQL 서버는 요청이 들어온 Client의 Session 변수(character_set_client)를 참고하여 Character Set을 유추할 수 있습니다.

그렇기 때문에 Client는 MySQL 서버로 데이터를 전송할 때 character_set_client 세션 변수 값을 변경하여 MySQL 서버에게 '나는 이런 Character Set을 사용하여 데이터를 전송했어'라고 알려주는 과정이 중요합니다.

character_set_connection

Client가 전송한 데이터를 처리할 때 사용할 Character Set을 설정합니다.

Client가 요청한 데이터의 인코딩을 character_set_client → character_set_connection으로 재인코딩하여 처리를 수행합니다.

기본적으로 이 값은 MySQL 서버의 Character Set과 동일하게 맞춰 사용을 합니다.

character_set_results

MySQL 서버가 Client로 결과를 반환할 때 사용하는 Character Set을 설정합니다.

character_set_client 변수가 Client에서 MySQL 서버로 전송할 때 라면, 이 변수는 MySQL 서버에서 Client로 데이터를 전송할 때 사용하는 Character Set을 의미합니다.

문자열 데이터 Type의 길이 측정 방식

문자열 데이터 Type은 최대로 저장할 수 있는 문자열 길이를 Byte로 관리를 합니다.

아래는 주로 사용하는 Type에 대해 정리한 표 입니다.

Type최대 Byte문자열 길이 필드
varchar255 Byte1 Byte
varchar256 Byte ~ 65,535 Byte2 Byte
text65,535 Byte2 Byte
mediumtext16,777,215 Byte3 Byte
.........

기본적으로 MySQL은 문자열 데이터를 관리할 때 문자열 길이와 문자열을 조합하여 저장합니다.

예를 들어 Eat라는 문자열을 저장하면 3Eat와 같이 문자열 길이와 데이터가 조합된 형식으로 저장이 되기 때문에 문자열 길이 사이즈를 제외한 Byte만큼 문자열을 저장할 수 있습니다.

그리고 이 제한은 Character Set이 지원하는 문자당 최대 Byte 수에도 영향을 받습니다.

아래는 Character Set이 지원하는 문자당 최대 Byte 수와 Eat 문자열을 저장했을 때 실제로 계산되는 길이 Byte를 정리한 표 입니다.

Character Set지원 Byte(최대)디스크 용량(Byte)길이 용량(Byte)
latin1 (ISO-8859-1)1Byte4Byte (3Eat 각 1Byte)3Byte (Eat 각 1Byte)
utf8 (utf8mb3)3Byte4Byte (3Eat 각 1Byte)9Byte (Eat 각 3Byte)
utf8mb44Byte4Byte (3Eat 각 1Byte)12Byte (Eat 각 4Byte)

따라서 utf8mb4 기준으로 varchar Type에 저장할 수 있는 문자열은 최대 16,383 글자입니다.

그 근거로 컬럼을 varchar(65535) Type으로 생성하려고 했을 때 utf8mb3와 utf8mb4에서 발생하는 에러 메세지를 출력한 내용입니다.

  • utf8mb3 

    [42000][1074] Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead.

    21,845는 varchar Type으로 선언할 수 있는 최대 길이인 65,535에서 3Byte를 나눈 수 입니다.

  • utf8mb4 

    [42000][1074] Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead.

    16,383은 varchar Type으로 선언할 수 있는 최대 길이인 65,535에서 4Byte를 나눈 수 입니다.

이는 MySQL이 각 Character Set에서 지원하는 최대 Byte에 해당하는 문자열을 가지고 데이터를 저장하는 최악의 시나리오로 가정을 하고 문자열 길이를 계산하기 때문입니다.

인덱스에도 영향을 끼친다.

길이를 계산할 때 해당 Character Set에서 지원하는 문자당 최대 Byte를 가지고 계산한다고 말씀드렸는데, 이러한 관리 방법은 인덱스 Key 에서도 동일하게 적용이 됩니다.

참고!
The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format.

For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

Character Set을 utf8mb4로 설정하는 경우 실제 Byte보다 더 긴 길이를 사용하는 것으로 판단하기 때문에 하나의 인덱스 페이지에서 관리할 수 있는 인덱스 엔트리 수가 줄어들게 됩니다.

결국 데이터를 조회할 때 더 많은 인덱스 페이지를 탐색해야하기 때문에 Disk I/O가 증가할 수 있습니다.

Character Set Casting

SubQuery 또는 Join 조건으로 지정된 컬럼들이 서로 다른 Character Set으로 설정이 되어있다면 MySQL은 데이터 비교를 위해 Character Set 변환이 발생할 수 있습니다.

따라서 관계를 맺는 컬럼은 동일한 Character Set을 사용하는 것이 좋습니다.

예시)
A 테이블의 code와 B 테이블의 ref_code가 다른 Character Set으로 지정된 상태에서
B.ref_code IN (SELECT code FROM A ...) 와 같이 사용할 경우 Character Set 캐스팅이 발생하면서 조회 비용이 증가할 수 있습니다.

Collation

Collation은 Character Set에 대해서 비교 값이나 정렬 순서를 정의한 규칙을 의미합니다.

지금 이 글을 읽고 계신 분에게 "숫자 1은 숫자 5보다 작은게 맞아?"라고 물어본다면, 여러분은 "숫자 1은 숫자 5보다 작은게 맞지!" 라고 바로 대답을 할 수 있을겁니다.

하지만 "문자열 A는 문자열 B보다 작은게 맞아?"라고 물어본다면 여러분은 바로 대답을 하실 수 있으신가요?

개발을 모르는 일반 사람들은 쉽게 대답을 하지 못할 것이고, Ascii 코드를 아는 사람이라면 A와 B를 Ascii 코드로 치환해서 비교를 할 수도 있고, 이 내용을 알고 계신 분들이라면 "Collation 설정에 따라 다르겠지" 라고 생각하실 수 있을겁니다.

이렇게 문자열에 대한 비교는 주체가 누구냐에 따라 기준이 다르기 때문에 쉽게 비교를 할 수 없는 상황이 펼쳐집니다.

MySQL은 이러한 상황을 방지하기 위해 Collation을 기준으로 문자열을 정렬하거나 비교하게 됩니다.

Collation 이름 규칙

자세한 내용은 MySQL 문서를 참고해주세요.

Bin

바이너리 비교를 수행하는 Collation 입니다.

대소문자를 구분하고, 각 문자의 바이너리 값으로 비교, 정렬을 수행합니다.

utf8mb4_bin 과 같은 이름을 가진 Collation을 의미합니다.

AI (Accent Insensitive)

악센트를 무시하고 비교하는 Collation 입니다.

악센트(예: é, è, ê 등)에 대해서 같은 비교 값을 가지기 때문에 eat와 êat는 같은 문자열로 취급합니다.

한글의 경우 가와 ㄱㅏ를 같은 문자열로 취급합니다.

AS (Accent Sensitive)

악센트를 구분하여 비교하는 Collation 입니다.

악센트(예: é, è, ê 등)에 대해서 다른 비교 값을 가지기 때문에 eat와 êat는 다른 문자열로 취급합니다.

한글의 경우 가와 ㄱㅏ를 다른 문자열로 취급합니다.

CI (Case Insensitive)

대소문자를 구분하지 않고 비교하는 Collation 입니다.

대소문자에 대해서 같은 비교 값을 가지기 때문에 eat와 EAt는 같은 문자열로 취급됩니다.

CS (Case Sensitive)

대소문자를 구분하여 비교하는 Collation 입니다.
대소문자에 대해서 다른 비교 값을 가지기 때문에 eat와 EAt는 다른 문자열로 취급됩니다.

MySQL 기본 Character Set / Collation

MySQL은 버전별로 Default Character Set과 Collation이 다릅니다.

버전Character SetCollation
5.7latin1latin1_swedish_ci
8.0utf8mb4utf8mb4_0900_ai_ci
8.4utf8mb4utf8mb4_0900_ai_ci

보시다시피 MySQL은 기본적으로 대소문자를 구분하지 않는 Collation을 기본 값으로 사용하고 있습니다.

따라서 대소문자를 구분해야하는 환경에서는 Collation을 꼭! 확인하셔야 합니다.

MySQL에서 지원하는 Collation

# Collation 목록 조회
SHOW COLLATION;

참고 문서

profile
아재발자
안녕하세요. 아재 개발자입니다. 공부한 내용을 기록하고 잘못된 부분에 대해서 조언을 받기 위해 velog를 시작했습니다. :)
이전 포스트

MySQL 통계 정보

0개의 댓글