MySQL에서 캐릭터셋의 collation이 있는 이유는?

MySQL에서 캐릭터셋을 확인하다 보면 같이 등장하는 collation있습니다. Oracle과 같은 dbms에는 없는 이 개념은 왜 나온 걸까요? 오히려 MySQL 테이블마다, 또는 database마다 다르게 설정된 collation때문에 애를 먹고 나서 더욱더 이상하게 생각됩니다. 
 

MySQL collation 이란

 

사건의 발단

Chatrater Set까지 맞는데, collation이 맞지 않아 조인 속가 느리고, 심지어 조인이 안되는 경우가 생긴다는 김시원의 말을 들었습니다. 상황을 파악해 보니, 한쪽은 원래 데이터베이스를 생성할 때 디폴트 collation이었던 utf8mb4_0900_ai_ci 되어 있었고, 다른 쪽은 Tool로 생성되어서 인지 각 컬럼마다 utf8mb4_general_ci 를 적용한 상태였습니다. 

 

"아니 컬래이션이 뭐길래 서로 다른 문자로 인식까지 하는 거지?’ 라는 생각이 들더군요. 그래서 김사원에게 이렇게 말했죠. “이번 기회에 collation에 대해 샅샅히 조사해 보자” 우리는 단지 두 collation 중 하나로 선택하자는 결론보다는 더 건설적인 안을 찾기로 했습니다. 

 

Collation이 무엇일까?

Collation은 문자 컬럼의 값에 대한 비교나 정렬 순서를 위한 규칙입니다. 쉽게 말해 문자끼리 비교할 때 대소문자 등을 처리할 때 더 크거나 작은 것을 판단하는 방법을 정의한 것이죠. 
 
MySQL에서 collation을 확인하려면 ‘show collation’ 으로 가능합니다. 여러가지 옵션이 있는데요.  MySQL 5.5.3버전 이후는 디폴트 캐릭터셋은 utf8mb4이고, 디폴트 collation이 utf8mb4_0900_ai_ci 그 이전에는 utf8mb4_general_ci 였습니다. 지금 MySQL 8점대까지 나왔으니, utf8mb4_0900_ai_ci를 예로 의미를 설명해 보겠습니다. 
 
utf8mb4 : 이는 캐릭터셋을 나타내며, utf-8 인코딩의 확장버전이라는 의미입니다. 최대 4 bytes까지 사용할 수 있으며, 모든 유니코드 문자를 지원합니다. 

0900 : 이 숫자는 유니코드 버전을 나타내며 Unicode 9.0을 의미합니다. 즉 collation은 Unicode 9.0 기반으로 문자와 기호의 국제 표준을 정의합니다. 

ai : 이는 accent insensitive를 말하며, 발음 액센트를 무시하고 비교합니다. 예를 들어, “é”와 “e”를 동일하게 간주합니다. 

ci : case insensitive를 의미하며, 대소문자를 구분하지 않고 비교합니다. 예를 들어, “A”, “a”를 동일하게 간주합니다. 

오해하면 안되는 것이 대소문자를 구분하지 못한다는 말이 아니고 단지 정렬을 할 때 기준을 말하는 것입니다. Collation은 문자형의 정렬에 대한 규칙입니다. 그러면 이걸 설정하려면 어떻게 해야 할까요?

 

collation 설정 위치

컬래이션의 설정은 3 가지 Level에서 진행할 수 있습니다. 각각의 레벨로 alter문을 통해 collation을 변경할 수 있습니다.

• 데이터베이스 Level : 데이터베이스 생성할 때 캐릭터셋과 collation을 설정할 수 있음
• 테이블 Level : 테이블을 생성할 때도 특정 캐릭터셋과 collation을 설정할 수 있음
• 컬럼 Level : 각 column마다 캐릭터셋과 collation을 지정할 수 있음

ALTER DATABASE '데이터베이스명' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE `테이블명` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ; ALTER TABLE `테이블명` MODIFY COLUMN 컬럼명 VARCHAR(12) COLLATE utf8mb4_general_ci;

 

이미 달라진 collation을 바꿀 때는 데이터베이스 또는 테이블 레벨로 alter 한다고 해도 모든 컬럼이 변경되는 것은 아닙니다. 앞으로 테이블을 생성할 때 collation을 명시적으로 주지 않을 경우만 기본(default) 값으로 적용하게 됩니다.

 

그래서 제일 마지막 alter문을 이용해서 각 컬럼별로 변경해 주어야 합니다. 그런데 collation은 오라클에서는 사용하지 않는 MySQL에만 있는 규칙입니다. 왜 사용하는 걸까요? 이제 그 이유를 알아보시죠.

MySQL에 collation이 있는 이유?

이유를 알기 위해 MySQL이 오픈소스로 제공하는 시점으로 거슬러 올라가야 합니다. MySQL 데이터베이스 사상은 다양성이었고, 사용자들이 특정 지역 언어에 특화된 collation을 요청하면 MySQL팀은 이를 반영한 새로운 collation을 추가했죠. 

그래서 언어마다 다른 collation이 다양하게 나오게 됩니다. 또 사업에 따라서도 다른 collation을 요구하기도 합니다. 한국어, 일본어, 중국어와 같은 다중 바이트 문자를 사용하는 언어에는 그에 맞는 collation이 필요했습니다. 결국 MySQL팀은 상용dbms와 차별화를 위해 더 많은 옵션(option)을 제공했던 것이죠. 

하지만 Oracle과 같은 강한 dbms는 그들이 제시하는 최적의 문자 정렬 방식을 사용해 오픈소스dbms 보다 성능이 좋을 겁니다. 약자인 오픈소스 계열의 MySQL은 여러 옵션을 통해 그 격차를 좁히려 했다고 볼 수 있겠네요. 그렇다면 우리는 우리 환경에 맞는 collation을 찾아 적용하면 된다는 결론이 나오는 군요. 그럼 비교 들어갑니다. 

주요 collation 비교

어떤 collation이 우리 database에 적절한 것인지를 알기 위해서 각 collation의 특징과 장단점으로 조사해야만 했습니다. 모든 collation을 확인할 필요는 없고 최근 많이 활용하는 세 가지로 압축하기로 합니다. 바로 utf8mb4_0900_ai_ci, utf8mb4_general_ci, utf8mb4_bin 입니다. 

Collation	특징	장점	단점
utf8mb4_0900_ai_ci	Unicode 9.0 표준 기반 발음 구별 기호 무시 대소문자 구분 없음	최신 유니코드 표준 기반으로 다양한 언어 지원 정확한 비교 및 정렬 제공	다른 collation 대비 비교 및 정렬 성능이 느림
utf8mb4_general_ci	Unicode 기반 대소문자 구분 없음	비교 및 정렬 속도가 빠름 일반적인 용도로 사용하기 적합	복잡한 유니코드 비교 규칙을 완벽히 지원하지 않음 일부 언어에서 정확성이 떨어짐
utf8mb4_bin	이진 비교(Binary Comparison) 즉, 바이트 다위 비교	가장 빠른 비교 및 정렬 성능 완벽한 대소문자 및 발음 구별 기호 구분	사용자에게 직관적이지 않은 정렬 순서 제공

 

최종적으로 가장 빠른 비교를 자랑하는 utf8mb4_bin을 사용하기로 결정했습니다. 성능이 가장 좋고 대소문자를 완벽하게 구분하기 때문이죠.

 

마무리

살펴본 봐와 같이 MySQL에 collation이 있는 이유가 있었습니다. 특정 요구 사항과 용도에 따라 선택할 수 있습니다. 프로젝트 데이터의 성격과 프로그램의 요구 사항을 고려하여 collation을 선택하는 것을 권장합니다.