MySQL의 캐릭터셋 인코딩 이해하기

Database가 문자와 관련된 데이터 타입이 주를 이루기 때문에 캐릭터셋 인코딩에 대해서는 꼭 짚고 넘어가야한다.

본인도 문제가 발생할 때마다 검색을 통한 해당 문제 해결에 집중하였던 터라, 반성을 하고 이렇게 MySQL 캐릭터셋에 대해서 정리하고자 한다.

문자 집합(Character Set) 혹은 문자 인코딩이란? 문자 집합(character set, charset) 또는 문자셋은 정보를 표현하기 위한 글자들의 집합을 정의한 것으로, 직접적으로 사용되지 않을 수도 있고 한 문자 집합을 여러 문자 인코딩에서 쓸 수도 있다. 특히 집합 안의 문자들에 음수가 아닌 정수들을 배정한 것을 부호화된 문자 집합(coded character set, CCS)이라 한다. 문자 집합은 ASCII와 같이 더 이상의 문자가 추가될 수 없기도 하고, 유니코드와 같이 문자가 계속 추가될 수 있기도 하다. 일반적으로 문자 집합과 문자 인코딩은 어떤 문자를 사용할 수 있으며 어떤 식으로 표현되는지를 나타낸다는 데서 동의어로 취급되기도 한다. 역사적인 이유로 MIME이나 그에 기반한 시스템은 문자 집합("charset")을 문자 인코딩을 나타내는 데 사용한다. (출처 : https://ko.wikipedia.org/wiki/%EB%AC%B8%EC%9E%90_%EC%9D%B8%EC%BD%94%EB%94%A9) 우리가 흔히 아는 Character Set(인코딩)은 EUC-KR, UTF-8, MSWIN949, UTF-16 등등이 있다.

Collation은 무엇인가? Database의 문자 데이터(CHAR/VARCHAR,TEXT, 등...)에 대한 값 비교시 사용되는 방식을 의미한다. 대소문자 구분(Case Intensitive)을 위한 Collation은 collation 이름의 끝에 "CI"라는 문자가 붙는다.

MySQL에서 문자열 데이터 타입의 컬럼에 대해서, 다양한 인코딩 방식을 제공하고 있으며 아래의 명령어를 통해서 지원 인코딩을 확인할 수 있다.

DBMS의 관점에서 Character Set은 문자 데이터를 저장하는 데이터 파일에 해당 문자를 어떻게 인코딩할 것인지를 지정한다. 지정된 인코딩 방식에 의해서 인코딩 된 바이너리(혹은 바이트 배열)가 데이터 파일에 최종적으로 저장 된다. 즉, UTF-8 Character Set을 사용하는 MySQL에 "한글"이라는 데이터를 Varchar 타입의 컬럼에 저장한다면, 실제 데이터 파일에는 UTF-8 인코딩 결과인 "0xED959CEAB880"이 저장될 것이다.

MySQL DBMS에서 캐릭터셋과 Collation을 지정하기 위해서는 그 범위에 따라 크게 네 가지로 분류된다.

Server Character Set >Database Character Set > Table Character Set > Column Character Set - Database 생성시 Character Set을 미지정 할 경우, Server Character Set을 사용 함 - Table 생성시 Character Set을 미지정할 경우, Database Character Set을 사용 함 - Column에 대한 Character Set을 미지정 할 경우, Table의 Character Set을 사용 함

Server Character Set과 Database Character Set은 간단하게 "status" 명령어를 사용하여 확인할 수 있다.

위의 결과에서 Server/Db character set 이외에 client/connection character set이 있는데, 이는 추후에 설명하기로 한다.

MySQL에서 캐릭터셋을 지정하는 방법에 대해서 알아보자.

1. Server Character Set

Server Character Set을 지정하는 첫 번째 방법은 /etc/my.cnf 파일을 수정하고 mysqld 를 재시작 하는 것이다. (영구적)

[/etc/my.cnf 파일 수정하기]

[mysqld] character-set-server = utf8 collation-server = utf8_general_ci

파일을 수정한 이후에 mysqld를 재시작하자.

# systemctl restart mysqld

재시작 수행 후 mysql에 접속하여,  "status" 명령어를 사용하여 확인하면 Server Characterset이 utf8로 변경되어 있음을 확인할 수 있을 것이다.

Server Character Set을 지정하는 두 번째 방법은 mysql에 접속하여 set 명령어를 사용하여 변경하는 방법이다. (세션에서만 유지 됨)

mysql> set character_set_server=utf8; mysql> set collation_server=utf8_general_ci;

[Database Character Set]

Database Character Set을 지정하는 방식은 데이터 베이스 생성시 직접 지정하는 방식이 있다.

CREATE DATABASE db_name     [[DEFAULT] CHARACTER SET charset_name]     [[DEFAULT] COLLATE collation_name]

이미 생성된 Database에 대해서 변경도 가능하다.

ALTER DATABASE db_name     [[DEFAULT] CHARACTER SET charset_name]     [[DEFAULT] COLLATE collation_name]

현재 사용중인 Database의 Character Set을 확인하는 방법은 "status" 명령어를 통해서도 가능하며, 아래와 같은 방식으로도 가능하다.

USE db_name; SELECT @@character_set_database, @@collation_database;

[Table Character Set]

Table 고유의 Character Set을 지정하는 방법과 변경하는 방법은 아래의 SQL문을 통해서 가능하다.

CREATE TABLE tbl_name (column_list)     [[DEFAULT] CHARACTER SET charset_name]     [COLLATE collation_name]] ALTER TABLE tbl_name     [[DEFAULT] CHARACTER SET charset_name]     [COLLATE collation_name]

[Column Character Set]

테이블의 특정 컬럼에 대해서 Character Set을 지정하는 방법과 변경하는 방법은 아래의 SQL 문을 통해서 가능하다.

col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

아래의 예제를 참고하기 바란다.

CREATE TABLE t1 (     col1 VARCHAR(5)       CHARACTER SET latin1       COLLATE latin1_german1_ci ); ALTER TABLE t1 MODIFY     col1 VARCHAR(5)       CHARACTER SET latin1       COLLATE latin1_swedish_ci;

각 컬럼의 캐릭터 셋을 확인하기 위해서는 아래의 SQL 문을 사용하면 된다.

SHOW FULL COLUMNS FROM TABLE_NAME;

지금까지 MySQL의 Server/Database/Table/Column별 캐릭터셋에 대한 확인 및 설정 방법에 대해서 알아보았다. 즉, Server Side에서 고려해야할 캐릭터 셋에 대해서만 설명한 것이다.

이제는 Client Side와 연계된 Character Set에 대해서 설명하고자 한다. 아래의 캡쳐 화면은 client의 session과 관련된 다양한 캐릭터 셋을 조회한 결과이다.

• character_set_client : MySQL 클라이언트의 기본이 되는 캐릭터셋이다. 클라이언트에서 서버로 전송하는 SQL문에 대한 인코딩을 의미한다. 서버는 SQL문을 수신하면, server의 character set으로 SQL문을 conversion 하기 위해서 해당 캐릭터셋을 사용한다.(참고 : https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_client)

• character_set_connection : 클라이언트로부터 수신한 Character set introducer가 없는 리터럴에 대한 기본 캐릭터 셋을 의미한다. 또한, Number를 문자열로 변환할 때에서 해당 캐릭터셋이 사용된다. (참고 : https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_connection)
  

• character_set_results : Client가 데이터를 조회할 경우, Server는 해당 캐릭터 셋으로 인코딩하여 전송하게 된다. (참고 : https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_results)

위의 세 가지 변수는 세션 연결시 설정 가능하며, 아래의 쿼리를 사용하여 세개를 동시에 수정 가능하다.

mysql> SET NAMES utf8; --> character_set_client, character_set_connection, character_set_results이 모두 utf8로 수정된다.