MYSQL Character Sets 와 Collations

개요

최근에 경험했던 이슈에서 개인적으로 충격이었던 mysql의 collation에 대해서 정리를 해보려고 한다. 이슈에 대해서 간략하게 설명하면 다국어를 지원하는 어떤 테이블의 유니크한 컬럼 요소는 웹어플리케이션에서 한번 중복체크를 하고 삽입을 시도한다.

문제는 웹어플리케이션에선 중복체크를 통과하였지만 dbms에서 unique constraint violation이 발생한 것이다. 여기서 발생했던 문제는 무었이었을까?

유니코드와 UTF8

유니코드는 컴퓨터에서 전세계 언어를 표현하기 위한 약속이다. 그리고 이를 컴퓨터의 메모리에 저장할 수 있도록 인코딩하는 방식중 하나가 utf-8이다. utf-8은 아스키코드와의 호환성을 위해 1~3 바이트 가변으로 하나의 글자를 표현한다. 다국어 이외에도 요즘 채팅에서 흔히 사용되는 이모지를 지원하기 위해 4바이트까지 필요하게 되었고 이에 사용되는 포맷이 utf8mb4 이다.

character sets와 collations

문제가 되던 부분은 ü와 같은 영어가 아닌 라틴계열 문자였다. 그래서 처음에는 인코딩에 문제가 있는지 확인을 해보았다. 하지만 인코딩은 utf8mb4로 대부분의 다국어는 물론 이모지도 지원하는 인코딩이었다. 실제로 DB내부에도 영어가 아닌 라틴문자들이 잘 들어가 있었다.

그렇다면 비교하는 과정에서 문제가 있다는 의미인 것 같아 한가지 테스트를 해보았다. u가 들어가 있는 상태에서 ü를 넣으려고 하니 unique constraint violation이 발생하였다! 해당 부분에 대해서 자세히 살펴보니 mysql은 저장하는 문자의 인코딩(character set)과 비교 혹은 정렬을 위한 요소(collation)가 별개로 설정되어 있었다.

mysql의 reference를 참고해보면 아래와 같이 이야기 한다.

• 다양한 character set을 사용하여 문자열을 저장합니다.
• 다양한 collation을 사용하여 문자열을 비교합니다.

그러면 mysql에서 utf8mb4에 정보를 확인해보자

SHOW CHARACTER SET LIKE 'utf8mb4';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
+---------+------------------+--------------------+--------+

여기서 default collation을 확인할 수 있는데 특별히 collation을 설정하지 않으면 utf8mb4_general_ci로 설정된다는 의미이다. 컨벤션과 함께 collation에 대해서 간단하게 확인을 해보자.

_general : utf8mb4에 대한 general한 비교, 여기선 accent를 구분하지 않음
_ci : 대문자, 소문자를 구분하지 않음(case-insensitive)

그러면 이제 원인이 파악될 것이다. collation이 accent를 구분하지 않기 때문에 u와 ü를 구분하지 못했던 것이다. collation은 WEIGHT_STRING을 사용해서 문자를 비교하는데 아래와 같이 확인을 해볼수있다.

SELECT HEX(WEIGHT_STRING('u' COLLATE utf8mb4_general_ci))=HEX(WEIGHT_STRING('ü' COLLATE utf8mb4_general_ci)) as is_same;
+---------+
| is_same |
+---------+
|       1 |
+---------+

accent를 비교하지 않기 때문에 weigt가 동일하다.

그렇다면 어떤 collation을 선택해야 할까? dbms의 버전이나 종류(aurora같은)에 따라 다르기 때문에 직접 확인이 필요하겠지만 일반적으로 accent를 비교하고 싶다면 _as(accent-sensitive) 라고 붙어 있는 collation 을 찾거나 utf8mb4_bin을 사용하면 된다. 여기서 _bin은 binary로 weigt를 사용하지 않고 문자의 byte값을 그대로 비교하는 것이다. utf8mb4_bin을 사용하면 이러한 문제가 100% 발생하지 않겠지만 탐색에서 성능이 떨어지게 될 것이다.

결론

성능을 유지하려면 적절한 collation을 찾는 것이 가장 좋겠지만 다국어를 지원해야 하고 앞으로 어떤 문자를 지원해야 할지 모르는 상황이라 해당 컬럼에 대해서만 utf8mb4_bin을 적용하였다. collation은 데이터베이스, 테이블, 컬럼 단위로 적용이 가능한데 필요한 부분이 하나의 컬럼이라 아래와 같이 해당 부분에만 적용을 하였다.

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_bin;

부록

흔히 발생하는 Collation에 의해 발생하는 이슈들

mysql reference에서 소개하는 Collation에 의해 발생하는 이슈들 중에서 자주 발생할 만한 이슈 혹은 알면 좋은 내용 3가지를 가져왔다.

Character Sets와 Collations 간 호환성

하나의 Character Set은 여러개의 Collation과 호환이 되지만 하나의 Collation은 무조건 하나의 Character Set과 호환된다.

Trailing Space Handling

Collation은 PAD_ATTRIBUTE 에 따라 trailing space를 다르게 비교합니다.

• PAD_SPACE : trailing space를 비교하지 않습니다.
• NO_PAD : trailing space를 비교합니다.

mysql 5.7의 경우에는 _bin collation은 모두 NO_PAD 그 이외에는 모두 PAD_SPACE 입니다. mysql 8.0 부터는 collation에 따라 다르니 확인이 필요합니다.

collation의 효과

각 collation으로 동일한 단어들을 삽입하고 정렬을 해보면 아래와 같이 출력된다. 서론에서 언급했던 문제가 왜 발생했는지 알 수 있다.

latin1_swedish_ci	latin1_german1_ci	latin1_german2_ci
Muffler	Muffler	Müller
MX Systems	Müller	Muffler
Müller	MX Systems	MX Systems
MySQL	MySQL	MySQL

• Latin1_swedish_ci 에선 ü가 y와 같이 정렬(y와 동일하게 취급)
• Latin1_german1_ci 에선 u와 같이 정렬(u와 동일하게 취급)
• Latin1_german2_ci 에선 ü 별개로 정렬(별개로 취급)