[MySQL] 문자셋과 Collation 정리

 

이전까지는 아무 생각없이 MySQL 스키마, 테이블을 만들 때 utf8 charset과 utf8_bin collation으로 지정하고 사용해왔었다. 그런데 팀플을 하고 프로그래밍 교육을 들으면서 팀원들과 사람들이 나와는 다른 문자셋을 사용하는 걸 발견했다.... 

 

찾아보니 utf8mb4 라는 문자셋과 utf8mb4_unicode_ci, utf8_general_ci, utf8_bin 등 다양한 collation이 있다는 걸 알게되었고, 원하는 서비스를 만들기 위해서는 이런 문자셋도 고려해야한다는 걸 알게되었다. (ex. 이모지를 제공하는 편지서비스 등)

이전에는 운 좋게 문자셋 관련한 오류를 발견하지 못했지만, 내가 사용하던 문자셋을 그대로 사용하면 어떤 오류가 터질 지 무서워졌다. 그래서 이번 기회에 제대로 공부해보려고 글을 쓴다.

 

 

CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

CREATE TABLE `database_name`.`table_name` (
	`id` INT PRIMARY KEY
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

 

 

이전 프로젝트에서 사용하던 스키마와 테이블의 문자셋, collation을 가져와봤다.

스키마와 테이블을 지정할 때 CHARACTER SET과 COLLATE를 지정해주는 걸 흔히 볼 수 있다. 물론 지정해주지 않는다면 디폴드 값이 들어간다. (mysql 8.0의 디폴트 문자셋은 utf8mb4이고, 디폴트 collation은 utf8mb4_0900_ai_ci다)

 

이렇듯 모든 데이터베이스에는 데이터베이스 문자집합과 Collation이 있다. 이 둘이 뭔지 차근차근 알아보자!

 

 

 

 

CHARACTER_SET

utf8   utf8mb3   utf8mb4

character_set은 문자를 저장할 저장공간의 크기이다. 한 글자를 저장할 때 몇 바이트의 저장공간에 저장할 지 결정하기 위해 문자셋을 지정해준다. 

 

MySQL은 이전에 utf8을 3바이트 가변 자료형으로 설계하였다. 이는 전세계 모든 언어가 21bit안에 저장되기 때문에 공간절약 + 속도향상을 위한 것이었다. 

 

하지만 최근에는 이모지(😀😍💚💥)와 같이 4바이트에 저장해야하는 값이 등장했고, 이를 기존의 3바이트 utf8에 저장하면 값 손실이 발생한다. 

MySQL은 이 때문에 2010년 3월 24일에 가변 4바이트 UTF-8 문자열을 저장할 수 있는 자료형(utf8mb4)을 추가하였다. (MySQL 5.5.3 버전)

Th utf8mb4 character set has been added. This is similar to utf8, but its encoding allows to four bytes per character to enable support for supplementary characters.

https://sshkim.tistory.com/128

(현재는 5.5.3 릴리즈 노트가 삭제된 상태이기 때문에, 캡쳐본을 가지고 계신 블로그의 링크를 첨부한다)

 

utf8mb4는 기존 utf8이 저장할 수 있는 Basic Plane과 이모지인 Supplementary Plane을 모두 저장할 수 있는 자료형이다. 

(Plane 문자열에 대한 자료: https://en.wikipedia.org/wiki/Plane_(Unicode))

 

 

 

단순 한글 표시만을 위해서는 utf8을 써도 되겠지만, 기본 이모지를 표현하지 위해서는 utf8mb4를 무조건 사용해야한다는 걸 알 수 있다. 공식문서에서는 utf8(utf8mb3)이 더 이상 사용되지 않으니 utf8mb4를 사용하라고 나와있다.

utf8mb4: 문자당 1~4바이트를 사용하는 유니코드 문자 집합의 UTF-8 인코딩입니다.
utf8mb3: 문자당 1~3바이트를 사용하는 유니코드 문자 집합의 UTF-8 인코딩입니다. 이 문자 세트는 MySQL 8.0에서 더 이상 사용되지 않으며 대신 utf8mb4를 사용해야 합니다.
utf8: utf8mb3의 별칭입니다. MySQL 8.0에서는 이 별칭이 더 이상 사용되지 않습니다. 대신 utf8mb4를 사용하세요. utf8은 향후 릴리스에서 utf8mb4의 별칭이 될 것으로 예상됩니다.
ucs2: 문자당 2바이트를 사용하는 유니코드 문자 집합의 UCS-2 인코딩입니다. MySQL 8.0.28에서는 더 이상 사용되지 않습니다. 이 문자 집합에 대한 지원은 향후 릴리스에서 제거될 것으로 예상됩니다.
utf16: 문자당 2바이트 또는 4바이트를 사용하는 유니코드 문자 집합에 대한 UTF-16 인코딩입니다. ucs2와 비슷하지만 보조 문자에 대한 확장자가 있습니다.
utf16le: 유니코드 문자 집합에 대한 UTF-16LE 인코딩입니다. utf16과 비슷하지만 빅엔디안이 아닌 리틀엔디안입니다.
utf32: 문자당 4바이트를 사용하는 유니코드 문자 집합에 대한 UTF-32 인코딩입니다.

 

 

 

MySQL 5.5.3 이전에는 문자셋 utf8, collation utf8_general_ci 가 일반적이었지만,

MySQL 8부터는 문자셋utf8mb4, collation utf8mb4_0900_ai_ci 가 기본값으로 세팅된다.

 

 

 

 

 

Collation(COLLATE)

utf8mb4_general_ci   utf8mb4_0900_ai_ci   utf8mb4_unicode_ci

collation은 정렬을 뜻한다. 텍스트 데이터를 정렬할 때 사용하는 방법을 collation에 지정한다.

collation은 텍스트 데이터를 정렬할 때만 적용되는 속성이다.

 

예를 들면, 정렬 시 a와 A를 같은 크기로 취급할건지, 기존 유니코드 방식대로 A보다 a가 더 크도록 취급할건지에 따라 다른 collation을 지정해줄 수 있다. 

 

collation은 utf8_general_ci, utf8_0900_ai_ci 등 다양한 게 존재하는데, 붙을 수 있는 suffix의 의미에 대해 먼저 알아보겠다.

• ai: 악센트를 구분하지 않음
• ci: 대소문자를 구분하지 않음

 

즉 utf8mb4_general_ci는 대소문자를 구분하지 않고, utf8mb4_0900_ai_ci는 악센트와 대소문자 모두 구분하지 않는다는 걸 알 수 있다. 

 

또한 utf8mb4_0900_ai_ci 처럼 번호가 붙은 경우가 있는데, 이는 데이터 정렬의 기반이 되는 UCA(유니코드 데이터 정렬 알고리즘)의 버전을 나타내는 버전 번호가 포함된 것이다.

(유니코드 정렬 알고리즘: 유니코드 표준의 요구 사항을 준수하는 두 개의 유니코드 문자열을 비교하는 데 사용되는 방법)

4.0.0보다 높은 UCA 버전을 기반으로 하는 유니코드 collation은 이름에 버전이 포함된다.

• utf8mb4_unicode_ci : 이름에 버전번호가 없는 UCA기반 데이터 정렬은 UCA 4.0.0 버전 가중치 키를 사용한다
• utf8mb4_0900_ai_ci: UCA 9.0.0 가중치 키를 기반으로 함
• utf8mb4_unicode_520_ci: UCA 5.2.0 가중치 키를 기반으로 함

 

주로 많이 사용하는 collation들은 다음과 같다.

1. utf8mb4_bin
   • 바이너리 값 그대로 정렬
   • A는 41, a는 41이므로
   • 정렬순서: ABCD....abcd....
2. utf8mb4_general_ci
   • 텍스트 정렬 시 a다음에 b가 나타나야한다는 생각에서 나온 정렬방식
   • a < B로 인식된다 (대소문자 구분 x)
   • 정렬순서: AaBbCc...
3. utf8mb4_unicode_ci
   
   • 한국어, 영어, 중국어, 일본어 사용환경에서는 general_ci와 unicode_ci의 결과가 동일하다
   • 더 특수한 문자의 정렬 순서가 변경된다
   • 데이터 정렬은 버전 4.0.0 UCA weight key를 사용한다
   • +) general_ci가 unicode_ci보다 동작이 일반적으로 빠르지만, 정확도는 살짝 떨어진다. unicode_ci가 좀 더 확장된 규칙을 지원하기 때문이다. general_ci는 오래된 collation이기 때문에 expansion, contraction, ignorable character를 지원하지 않음
4. utf8mb4_0900_ai_ci
   • MySQL 8.0.1 버전부터 기본값으로 적용되었다
   • 0900은 유니코드 정렬 알고리즘의 버전이다
   • Oracle 측은 이 collation을 채택한 이유를 해당 콜레이션이 가장 성능이 뛰어나기 때문이라고 설명했다
   • 하지만 한글이나 동아시아 계열의 문자를 사용하는 나라에서는 치명적인 문제가 있다고 한다 (아래에서 더 설명)

 

MySQL 8부터는 문자셋utf8mb4, collation utf8mb4_0900_ai_ci 가 기본값으로 세팅된다고 하였는데, 기본 collation인 utf8mb4_0900_ai_ci에 문제점이 있다고 하여 조금 더 살펴보려고 한다. 

 

 

 

 

utf8mb4_0900_ai_ci 의 문제점

이 collation은 성능이 가장 뛰어나다고 하지만 한글이나 동아시아 계열의 문자를 사용하는 나라에서는 문제가 있다고 하는데, 이 collation은 한글의 자음 모음, 일본의 가타카나, 히라가나를 같은 문자열로 인식하여 처리한다고 한다...

 

accent, 대소문자, 히라가나와 가타카나, 한글 자음과 결합문자를 구분하지 않는다 ( '가나다', 'ㄱㅏ나다', 'ㄱㅏㄴㅏㄷㅏ'를 모두 같다고 인식한다. 참고자료: https://rastalion.me/mysql-8-0-1-%EB%B2%84%EC%A0%84%EB%B6%80%ED%84%B0-%EC%B1%84%ED%83%9D%EB%90%9C-utf8mb4_0900_ai_ci%EC%9D%98-%ED%95%9C%EA%B8%80-%EC%82%AC%EC%9A%A9%EC%97%90-%EB%8C%80%ED%95%9C-%EB%AC%B8%EC%A0%9C%EC%A0%90/)

 

글로벌 서비스의 경우 해당 collation을 주의해서 사용해야할 것 같다.

 

 

또한 추가적으로 PAD관련한 문제도 있다.

MySQL은 전통적으로 데이터 값의 뒤 공백을 제거하여 비교하는 방법을 사용하는데, 8.0부터는 Collation에 따라 공백을 제거하지 않고 비교하는 게 가능해졌다. 

그래서 utf8mb4_general_ci의 경우 공백을 인식하지 않지만, utf8mb4_0900_ai_ci의 경우 뒤 공백을 인식한다.

(WAS의 connection string에서도 꼭 connectionCollation을 지정해야하는 이유라고도 한다)

 

 

 

 

결론

MySQL의 utf8은 기존에는 가변 3바이트로 글자를 저장하였지만, 4바이트로 저장해야하는 이모지등이 사용되면서 가변 4바이트인 utf8mb4 자료형이 등장하였고, 이 자료형이 권장된다.

 

각 collation마다 다양한 한계점이 존재하므로, collation의 특성을 잘 알고 사용해야한다. 서비스 특징을 고려하여 잘 설정하자!

• 정렬 시 대소문자 구분 여부
• 가, ㄱㅏ 구분여부
• あ(히라가나 아)와 ア(가타카나 아)를 구분할 필요가 있는가?
• accent와 äccent를 다르게 취급할 필요가 있는가
• 후행에 공백이 들어왔을 때 공백이 없는 경우와 서로 다른 문자로 취급할 것인가?

 

 

 

 

참고자료

• 좋은 참고자료들이 많아서 꼭 읽어보시면 좋을 것 같습니다.

https://dev.mysql.com/doc/refman/8.3/en/charset-database.html

https://cirius.tistory.com/1769

https://dungbeetle.co.kr/tomcat의-utf-8-과-mysql의-utf8-utf8mb3-utf8mb4-의-차이는-뭘까/

https://blog.gangnamunni.com/post/aws-rds-mysql-utf8mb4/

https://sshkim.tistory.com/128

https://blog.naver.com/sory1008/223071678680

https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html

https://rastalion.me/mysql-8-0-1-버전부터-채택된-utf8mb4_0900_ai_ci의-한글-사용에-대한-문제점/

https://blog.naver.com/PostView.naver?blogId=sory1008&logNo=222401508955&categoryNo=79&parentCategoryNo=0&viewDate=¤tPage=1&postListTopCurrentPage=1&from=postView

 

 

+) 더 읽어보면 좋을 글

https://peter.eisentraut.org/blog/2023/03/14/how-collation-works

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html