MySQL Character Sets 과 Collations 의 의미

Unicode 는 늘 어렵고 헷갈리는 주제라 공부하고 이해한 내용을 정리

Character set 과 Collation

MySQL 에서 Database 를 생성할 때 아래와 같이 character set 과 collate 를 지정할 수 있다. (생략시에는 MySQL 서버의 기본 설정을 따른다.)

--MySQL DB 생성
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin; 
grant all privileges on mydb .* to 'user'@'localhost' IDENTIFIED BY 'userPwd';

Character Set

먼저 character set 은 아래와 같이 정의하고 있다.(대략적으로 의미는 알지만 아래 설명은 너무 어려워서 생략..)

A repertoire of characters/graphemes
A value given to each character/grapheme (codepoint)
An encoding which defines the binary representation of the values

Encoding

character 나 grapheme 을 어떻게 binary 로 표시할지 규정한 방법을 의미한다. 예로 ASCII, ISO-8859-1/Latin-1, EUC-KR 등을 character encoding 이라고 할 수 있다.

ASCII 나 Latin-1 은 character 와 byte 가 1:1 매칭되는 방식이라 아주 심플하게 처리할 수 있다.

encoding 방식에 따라 저장하지 못하는 글자가 있을 수 있으며 예로 EUC-KR 을 사용할 경우 한글을 2,350 자만 표현할 수 있으므로 코드에 없는 똠, 샾이 포함된 단어인 윗똠길이나 더샾 같은 정보를 저장할 수가 없다.

Unicode 는 다음과 같이 3가지 인코딩을 정의하고 있다.

UTF-8: 문자당 1 ~ 4 바이트 사용
UTF-16: 문자당 2 또는 4 바이트 사용
UTF-32: 문자당 4 바이트 사용

기존에 작성한 문서나 데이타와 호환성을 염두에 두고 설계한 인코딩인 UTF-8 이 가장 호환성은 좋지만 복잡한 코드 체계와 인/디코딩때문에 처리 속도는 느리고 저장 공간을 많이 먹고 기존에 작성한 자료와 호환이 안 되는 UTF-32 가 처리 속도는 가장 빠르다.

Character set examples

출처: https://www.percona.com/live/e17/sites/default/files/slides/Collations%20in%20MySQL%208.0.pdf

Collation

Collation is the assembly of written information into a standard order. (WIkipedia)

콜레이션은 해당 문자셋을 어떻게 정렬할지를 결정하는 알고리즘을 의미하며 다음과 같이 많은 고려 사항이 있다.

대소문자 구분 여부('A' 와 'a')
악센트( 'O' 와 'Ò' )
Locale-specific rules (e.g. 'A' vs. 'Å' vs. 'AA' in Danish and Norwegian)
Numeric characters (e.g. '2' vs. 'ⅱ')
Punctuation (e.g. 'blackbird' vs. 'black-bird')

MySQL 에서 collation 은 ORDER BY 나 LIKE, Primary Key 나 Unique, 비교 연산자 등 여러 SQL 연산에 영향을 주므로 정확한 이해가 필요하다. (한글/한자/영어만 사용한다면 깊이 알 필요는 없다고 생각한다.

MySQL 이 제공하는 character set

Character set 목록 확인

mysql> show character set;

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| 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 |
+----------+---------------------------------+---------------------+--------+

Collation 목록

mysql>  SHOW COLLATION;


+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                  | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| utf8mb4_0900_ai_ci         | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4  | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4  | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bin                | utf8mb4  |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_croatian_ci        | utf8mb4  | 245 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_cs_0900_ai_ci      | utf8mb4  | 266 |         | Yes      |       0 | NO PAD        |
| utf8mb4_cs_0900_as_cs      | utf8mb4  | 289 |         | Yes      |       0 | NO PAD        |

DBMS & NoSQL