Mysql character set 과 collation

모든 데이터베이스 시스템은 텍스트 데이터를 어떻게 인코딩할 것인가를 결정해야한다. Mysql에서는 charater set이라는 속성값을 통해 이를 결정하는데, 다른 데이터베이스 시스템과는 다르게 mysql에서는 collaction이라는 속성도 존재한다.

character set

위에서 말했듯이 characte set은 텍스트 데이터를 어떤 방식으로 인코딩하여 저장할 것인가를 결정하는 값이다. 한글 인코딩을 위해 가장 널리 사용되는 인코딩 방식으로는 UTF-8과 EUC-KR이 존재하지만 show character set; 명령어를 통해서 지원하는 인코딩 목록을 확인할 수 있다.

# mysql 8.0.20 기준
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

 

mysql에서 지원하는 인코딩(character set)에는 여러 종류가 있지만 그 중에서 많이 사용되는 utf8, utf8mb4 에 대해서 설명하려한다.

utf8과 utf8mb4 방식은 사실 데이터베이스가 한 글자당 최대로 할당하는 byte만 다를 뿐이라고 생각하면 되는데. utf8은 글자당 최대 3bytes의 데이터를 할당하지만 utf8mb4는 최대 4bytes까지 할당한다. 예전에는 utf8만으로도 거의 모든 글자를 표현할 수 있었기 때문에 불편함이 없었지만, 이모티콘이 생겨나고 각종 문자들이 탄생하면서 3bytes만으로는 부족하게 되었고 그런 이유로 대부분 utf8mb4를 character set으로 사용하는 추세이다.

collation

collation은 정해진 character set에 의해 저장된 데이터들이 어떤 방식으로 정렬(order by) 될지를 결정하는 옵션이다. 물론 직관적인 자료(예를 들어 int형이나 date 형)에서는 collation의 차이가 큰 의미는 없다.

int 형 : 1, 6, 3, 152, 39104, 313, 111
-> 1, 3, 6, 111, 152, 313, 39104

date 형: 2018년, 3012년, 150년, 333년
-> 150년, 333년, 2018년, 3012년

 

하지만 문자를 정렬하는 것은 생각보다 어렵다는 것을 알 수 있는데, 예를 들어 'A' 와 '가'  혹은 'a' 와 'á' 와 같은 문자들은 정렬 방식을 설정하는 것이 상당히 곤란하다. 이런 이유로 mysql은 collation 속성을 통해서 텍스트 데이터들의 정렬방식을 결정하는데 이 역시 show collation 명령어를 통해서 확인할 수 있다.

 

# mysql 8.0.20 기준
# show collation where charset='utf8';
+--------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------------+---------+-----+---------+----------+---------+---------------+
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 | PAD SPACE     |
| utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 | PAD SPACE     |
| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 | PAD SPACE     |
| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 | PAD SPACE     |
| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 | PAD SPACE     |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 | PAD SPACE     |
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 | PAD SPACE     |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 | PAD SPACE     |
| utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 | PAD SPACE     |
| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 | PAD SPACE     |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 | PAD SPACE     |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 | PAD SPACE     |
| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 | PAD SPACE     |
| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 | PAD SPACE     |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 | PAD SPACE     |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 | PAD SPACE     |
| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 | PAD SPACE     |
| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 | PAD SPACE     |
| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 | PAD SPACE     |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 | PAD SPACE     |
| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 | PAD SPACE     |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 | PAD SPACE     |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 | PAD SPACE     |
| utf8_tolower_ci          | utf8    |  76 |         | Yes      |       1 | PAD SPACE     |
| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 | PAD SPACE     |
| utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 | PAD SPACE     |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 | PAD SPACE     |
| utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 | PAD SPACE     |
+--------------------------+---------+-----+---------+----------+---------+---------------+

 

위의 리스트는 utf8 인코딩에서 사용되는 collation인데 이번 포스트에서는 이 중 utf8_bin, utf8_general_ci, utf8_unicode_ci 세 가지 collaction만 확인해보려한다. 

 

utf8_bin

utf8_bin은 ASCII 코드표의 16진수 순서로 정렬되는데 A는 0x41, B는 0x42 이기 때문에 A, B 순서대로 정렬된다.

문자	16진수 표기
A	0x41
B	0x42
a	0x61
b	0x62

16진수 순서대로 되므로 위와같은 순서로 정렬된다.

 

utf8_unicode_ci

utf8_unicode_ci 는 유니코드 표준을 기반으로 정렬한다. 인코딩에 대한 포스트 에서 확인할 수 있듯 유니코드는 전 세계에 존재하는 모든 문자 인코딩을 통합하려는 목적으로 제작되었기 때문에 사람이 사용할 수 있는 거의 모든 언어들이 표현되어있으며 이를 기준으로 정렬하는 방식을 말한다. 

 

utf8_general_ci

utf8_unicode_ci 와 정렬 방법은 똑같지만, 정렬 속도 향상을 위해서 일반적으로 잘 사용되지 않는 문자들을 정렬 기준에서 제외한 방법을 말한다. 예를 들어 우리는 'abcd ABCD' 와 같은 알파벳은 일반적으로 많이 사용하는데 비해 'ÀÁÅåāă' 와 같은 문자들은 거의 사용하지 않는다. 굳이 사용하지 않는 문자들까지 정렬 기준에 포함하여 엄격하게 나누어 모두 'A'와 같은 우선순위로 인식해서 정렬 수행속도를 빠르게 하자는 취지로 개발되었다.

 

결론적으로 요즘처럼 이모티콘을 많이 사용하는 시대에서 일반적인 어플리케이션을 만들고자 한다면 utf8mb4와 utf8mb4_unicode_ci를 사용하는 것이 좋다. 얼핏 설명을 들어보면 general_ci 를 사용하는 것이 더 현명해 보이지만 시대가 지나오면서 CPU의 성능이 상승하고 그에 따라 수행속도에 대한 이슈는 크게 중요하지 않아졌다. 하지만 오늘날에 서비스는 점점 더 세계화되고 모든 언어를 지원하는 것이 수행 성능보다 더 심각한 이슈로 받아들여지고 있기 때문에 unicode_ci를 사용하는 것이 더 좋다.