MySQL 5.5 Characterset v1.0

MySQL 5.5 Characterset v1.0

 

Date	Ver	Etc.
2011.11.30	1.0

 

 

1.    MySQL Characterset

MySQL 은 다양한 캐릭터셋을 저장하기 위한 지원을 하고 있다.

 

Character set 은 기호와 부호화된 문자의 (encodings) 집합이다.

Collation 은 character set 에서 characters 를 비교하기 위한 규칙이다.

 

MySQL 이 지원하는 캐릭터셋은 ‘SHOW CHARACTER SET’ 으로 조회할 수 있다.

MySQL 이 지원하는 Collation 은 ‘SHOW COLLATION’ 으로 조회할 수 있다.

 

두개의 다른 Character set 은 같은 Collation 을 가질 수 없다.

각 Characterset 은 default collation 을 가질 수 있으며 이는 ‘SHOW CHARACTER SET’으로 확인 가능하다.

 

Database 의 기본 Character set 과 collation 은 system variable ‘character_set_database’ 와 ‘collation_database’ 로 확인가능하다.

 

 

2.    Types of MySQL Characterset

A.     Characterset Size

???

B.     Collation Server

 

l  (character set)_gernerl_ci

General_ci 의 ci 는 case insensitive 의 약자이다. 예를 들면 대문자 ‘C’ 나 소문자 ‘c’ 를 구분하지 않는다.

l  (character set)_bin

Bin 은 binay 를 의미한다. 위 타입을 사용하는 경우 캐릭터를 binary 로 비교를 한다.

이 경우엔 case sensitive 하다. (대소문자를 구분한다.)

 

 

3.    MySQL Collation Dependency

MySQL 의 Character Set 과 Collation Server 는 다음과 같은 관계를 가진다.

( * 아래에서 하위 셋팅이 안되어 있는 경우 상위 셋팅을 따른다. )

 

l  System Variables > Database > Table > Column

(예을 들어 Table 에 아무런 Character set, Collation Server 셋팅을 하지 않으면

Database 의 셋팅을 따른다.)

l  Character Set 만 정의하는 경우 (Character set)_general_ci 가 기본 셋팅이다.

 

 

<DATABASE 의 경우>

 

mysql> show variables like 'character_set_database'; +------------------------+--------+ | Variable_name          | Value  | +------------------------+--------+ | character_set_database | latin1 | +------------------------+--------+ 1 row in set (0.00 sec)   mysql> show variables like 'collation_database'; +--------------------+-------------------+ | Variable_name      | Value             | +--------------------+-------------------+ | collation_database | latin1_swedish_ci | +--------------------+-------------------+ 1 row in set (0.00 sec)

 

Character set / Collation Server 의 System Variables 기본 셋팅이 latin1 / latin1_swedish_ci 임을 알 수 있다.

Database 를 아무 옵션 없이 생성하면 위 셋팅을 따라 생성 될 것이다.

 

mysql> create database sample1; Query OK, 1 row affected (0.02 sec)   mysql> show create database sample1; +----------+--------------------------------------------------------------------+ | Database | Create Database                                                    | +----------+--------------------------------------------------------------------+ | sample1  | CREATE DATABASE `sample1` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

생성구문을 보면 latin1 으로 character set 이 설정된 것을 알 수 있다.

collation 까지 확인하려면 information_schema.schemata 를 조회하면 된다.

 

mysql> select schema_name, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME from schemata; +--------------------+----------------------------+------------------------+ | schema_name        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +--------------------+----------------------------+------------------------+ | information_schema | utf8                       | utf8_general_ci        | | mysql              | latin1                     | latin1_swedish_ci      | | performance_schema | utf8                       | utf8_general_ci        | | sample1            | latin1                     | latin1_swedish_ci      | | test               | latin1                     | latin1_swedish_ci      | +--------------------+----------------------------+------------------------+ 5 rows in set (0.00 sec)

 

또한 Character set 이나 Collation Server 어느 하나만 셋팅하는 경우 다음과 같은 규칙을 따른다.

l  (특정 character set)_bin, (특정 character set)_general_ci 옵션으로 생성하는 경우 해당 Databse 의 default character set 은 (특정 character set) 이 된다.

l  (특정 character set) 으로 database 를 생성하면 default collation server 로
(특정 character set)_(general | 특정국가)_ci 를 가져간다.

 

mysql> create database sample2 collate = euckr_bin; Query OK, 1 row affected (0.03 sec)   mysql> create database sample3 character set = euckr; Query OK, 1 row affected (0.03 sec)   mysql> show create database sample2; +----------+-------------------------------------------------------------------------------------+ | Database | Create Database                                                                     | +----------+-------------------------------------------------------------------------------------+ | sample2  | CREATE DATABASE `sample2` /*!40100 DEFAULT CHARACTER SET euckr COLLATE euckr_bin */ | +----------+-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)   mysql> show create database sample3; +----------+-------------------------------------------------------------------+ | Database | Create Database                                                   | +----------+-------------------------------------------------------------------+ | sample3  | CREATE DATABASE `sample3` /*!40100 DEFAULT CHARACTER SET euckr */ | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec)   mysql> select schema_name, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME from schemata     -> where schema_name in ('sample2','sample3'); +-------------+----------------------------+------------------------+ | schema_name | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-------------+----------------------------+------------------------+ | sample2     | euckr                      | euckr_bin              | | sample3     | euckr                      | euckr_korean_ci        | +-------------+----------------------------+------------------------+ 2 rows in set (0.00 sec)

 

<Table 의 경우>

 

여기서는 Database sample2 (euckr / euckr_bin) 을 사용해 이야기 하겠다.

 

l  Table 을 아무 옵션없이 생성하면 Database Level 의 옵션을 따라간다.

l  Table 에 character set 을 지정하는 경우 (특정 character set)_(general | 특정국가)_ci 를 가져간다.

 

mysql> create table sample2_t1(id char(4)); Query OK, 0 rows affected (0.02 sec)   mysql> create table sample2_t2(id char(4)) character set = euckr; Query OK, 0 rows affected (0.01 sec)   mysql> create table sample2_t3(id char(4)) collate = euckr_bin; Query OK, 0 rows affected (0.02 sec)   mysql> show create table sample2_t1\G *************************** 1. row ***************************        Table: sample2_t1 Create Table: CREATE TABLE `sample2_t1` (   `id` char(4) COLLATE euckr_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin 1 row in set (0.00 sec)   mysql> show create table sample2_t2\G *************************** 1. row ***************************        Table: sample2_t2 Create Table: CREATE TABLE `sample2_t2` (   `id` char(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=euckr 1 row in set (0.00 sec)   mysql> show create table sample2_t3\G *************************** 1. row ***************************        Table: sample2_t3 Create Table: CREATE TABLE `sample2_t3` (   `id` char(4) COLLATE euckr_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin 1 row in set (0.00 sec)

 

sample2_t1 은 옵션없이 생성하였고,

sample2_t2 는 character set 을 정의하였으며,

sample2_t3 는 collate 옵션을 사용해 생성하였다.

 

여기서 얻을 수 있는 사실은 show create 구문의 옵션은 아무 옵션도 정의하지 않은 경우와 collate 만 정의한 경우 같은 결과를 리턴한다는 것이다.

 

그렇다면 character set 만 정의한 경우는 어떻게 되었는지 information_schema.tables 에서 조회해 보겠다.

 

mysql> select table_name, table_collation     -> from tables     -> where table_name in ('sample2_t1','sample2_t2','sample2_t3'); +------------+-----------------+ | table_name | table_collation | +------------+-----------------+ | sample2_t1 | euckr_bin       | | sample2_t2 | euckr_korean_ci | | sample2_t3 | euckr_bin       | +------------+-----------------+ 3 rows in set (0.00 sec)

 

Character set 을 지정했던 ‘sample2_t2’ 테이블의 collation 이 euckr_korean_ci 임을 알 수 있다.

이는 ‘show character set’ 의 ‘euckr’ 의 기본 collation 값이다.

 

Column 에 대한 collation 은 뒤에서 이야기 하겠지만 sample2_t1, sample2_t3 의 id 컬럼은 euckr_bin 을 가져갔지만 sample2_t2 은 collate 옵션이 없다.

이에 대해 실제 어떤 collate 를 가져갔나 추측을 하자면 character set 이 euckr 이고 collation 명시가 안되어 있기에 default collation 을 가져갔을것이고, column 에 따로 옵션이 없기 때문에 character set / collation 옵션을 그대로 가져갈 것이다.

 

다시말해 euckr_korean_ci 임을 추측할 수 있다.

 

mysql> select table_name,column_name,collation_name     -> from columns where table_name = 'sample2_t2'; +------------+-------------+-----------------+ | table_name | column_name | collation_name  | +------------+-------------+-----------------+ | sample2_t2 | id          | euckr_korean_ci | +------------+-------------+-----------------+ 1 row in set (0.01 sec)

 

<column 의 경우>

 

아래의 내용은 sample2 schema ( euckr / euckr_bin ) 에서 진행하였다.

테이블은 아래와 같이 생성하였다.

 

l  sample2_t4 : no option

l  sample2_t5 : table – latin1

l  sample2_t6 : table – latin1_bin

l  sample2_t7 : table – latin1, column - euckr

l  sample2_t8 : table – latin1, column – euckr_bin

 

show create 에 대한 내용은 table 생성시에 이미 확인하였기에 생략하고 columns 를 이용하여 조회해 보겠다.

 

mysql> select table_name, column_name, collation_name from columns     -> where table_name in ('sample2_t4','sample2_t5','sample2_t6','sample2_t7','sample2_t8')     -> order by table_name; +------------+-------------+-------------------+ | table_name | column_name | collation_name    | +------------+-------------+-------------------+ | sample2_t4 | id1         | euckr_bin         | | sample2_t5 | id1         | latin1_swedish_ci | | sample2_t6 | id1         | latin1_bin        | | sample2_t7 | id          | euckr_korean_ci   | | sample2_t8 | id          | euckr_bin         | +------------+-------------+-------------------+

 

sample2_t4 테이블의 경우 아무 옵션없이 만들었고 table 의 기본셋팅을 그대로 계승했다.

 

sample2_t5 테이블의 경우 테이블에 latin1 을 지정하였고, 이로인해 collation 을 latin1_swedish_ci 로 가져갔다. 컬럼레벨에서는 옵션을 사용하지 않았기에 table 의 옵션을 그대로 계승했다.

 

sample2_t6 테이블의 경우 테이블에 collate 를 지정하여 character set 도 자연스럽게 고정되었다. 앞서 본 테이블과 마찬가지로 컬럼레벨에 옵션이 없기에 table 의 옵션을 그대로 계승했다.

 

sample2_t7 테이블의 경우 컬럼에 euckr character set 을 지정하였다. 컬럼에 character set 이든 collation 이든 옵션을 사용하는 순간 테이블의 옵션은 컬럼에 영향을 미치지 못한다. 컬럼의 euckr 이 지정 되었으나 collate 는 생략되었기에 character set 의 기본 collation 을 가져간다. (euckr_korean_ci)

 

sample2_t8 의 경우 collation 을 명확히 지정하였기에 그대로 가져간다.

 

 

4.    Conclusion

create database 의 경우

l  no option : DDL 에 character set 만 지정된다.

l  character set : DDL 에 character set 만 지정된다.

l  collation : DDL 에 character set, collation 모두 지정된다.

 

create table 의 경우

l  no option : DDL 에 character set, collation 모두 지정된다.

l  character set : DDL 에 character set 만 지정된다.

l  collation : DDL 에 character set , collation 모두 지정된다.

 

column 의 경우

l  no option : collation 이 지정된다.

l  character set : character set 이 지정된다.

l  collation : character set, collation 이 지정된다.

 

일반적으로 이관을 할 때 모든 Database 에 대해 실시하진 않는다. 실제 필요한 Database (Schema) 에 대해서만 실시한다.

 

이를 생각했을 때 character set / collation 의 기본 셋팅은 Database Level 에서 시작된다.

Table, Column Level 의 셋팅은 각기 상위레벨 셋팅에 영향을 받는다.

특별한 케이스가 아닌 이상 Table, Column Level 에는 아예 옵션없이 사용하되 특별한 케이스에 한해

Character set 과 Collate (혹은 Collate 만) 를 명시하는게 의도하지 않은 ‘default collation server 의 사용’ 을 피할 수 있을 것이다.

 

5.    mysqldump

 

<Database 레벨로 dump>

mysqldump 로 ddl 을 추출해 확인한 결과 각 DDL 은 show create {database | table} (object_name) 의 결과와 동일했다.

 

./bin/mysqldump -uroot --databases sample2 --no-data > sample2.dmp

 

CREATE TABLE `sample2_t1` (   `id` char(4) COLLATE euckr_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

 

Database Level 로 Dump 를 만드는 경우 생성 스크립트를 확인 한 결과 show create 로 확인한 DDL 문과 동일함을 확인하였다.

결론적으로 src 와 dst 의 character set / collation 이 달라지는 경우는 없다.

 

<Table 레벨로 dump>

 

./bin/mysqldump -uroot --databases sample2 --tables sample2_t1 --no-data > sample2.dmp

 

CREATE TABLE `sample2_t1` (   `id` char(4) COLLATE euckr_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

 

Table Level 의 dump 도 show create 로 조회한 DDL 문과 동일하다.

‘sample2_t1’ 의 경우 아무 옵션을 사용하지 않았던 DDL 이다.

 

CHARACTER SET 을 지정한 create table 의 경우 show create table 에서 collate 옵션이 비어 있었다.

이 경우 셋팅된 character set 의 기본 collation 으로 가져간다.

 

결론을 이야기 하자면 Table 레벨로 덤프를 만들어 import 한다 한들 character set 이나 collation 이 변경되진 않는다.

 

6.    MySQLYOG and MySQL

 

A.     MySQLyog

 

<create database>

 

SQLyog v8.71 을 이용해 character set 과 collation 의 위치를 확인해 보았다.

텍스트를 이용하는 경우는 터미널에서 진행하는 것고 별반 차이가 없기에 UI 를 이용한 경우만 보았다.

 

Database 생성을 호출한 화면이다. 기본적으로 System Variables 의 셋팅을 따르게 되어 있다. 앞서 살펴본 내용들을 고려하면 charset, collation 을 명시적으로 정의하는게 좋다.

 

<create table>

 

 

위 화면은 Table 생성을 위한 대화창이다. 최초 호출하는 경우 우측 하단의 체크박스가 체크되어 있는 상태로, charset 과 collation 에 대한 부분이 감춰져 있다.

 

사용자가 일부러 의도하지 않는 이상 숨겨진 화면을 열어보진 않을것이며 통상적으로 Database 셋팅을 따르는 Table 을 생성하게 될 것이다. (아무 옵션을 사용하지 않은…)

 

 

위 화면은 앞서 본 화면의 advanced properties… 버튼을 클릭했을 때 나오는 화면이다.

앞서 본 화면은 Column Level 의 셋팅화면이었다면, 여기서의 화면은 Table Level 의 셋팅이다.

이 창 역시특별한 경우가 아니라면 호출하지도 않을것이다.

 

사용자가 의도하지 않게 잘못된 셋팅을 한다는 상황은 character set 만 셋팅하는 경우임을 추측할 수 있다. collation 에 대해서는 대응되는 character set 이 하나뿐이기 때문에 잘못 될 일이 없다.

 

B.     mysql workbench

 

mysql workbench 5.2.34 를 이용하였다.

프로그램은 https://edelivery.oracle.com 에서 구할 수 있다.

 

<create database>

 

database 생성화면이다. character set 지정없이 바로 collation 선택할 수 있게 되어 있다.

실수로라도 character set 만 지정할 일은 없다.

 

<create table>

 

 

Table 생성 화면이다. 이곳도 Database 생성과 마찬가지로 Collation 만 지정할 수 있다.

 

Column 에 대한 셋팅화면이다. 이곳도 Collation 을 지정할 수 있다.

 

MySQLyog 와 비교하여 character set 만 셋팅하는 경우는 발생할 수 없다.

 

7.     References

A.     9.1 Character Set Support | dev.mysql.com | http://dev.mysql.com/doc/refman/5.5/en/charset.html

B.     9.1.1 Character Sets and Collations in General | dev.mysql.com |

http://dev.mysql.com/doc/refman/5.5/en/charset-general.html