MySQL CharacterSet 변경

MySQL의 경우 최초 설치시 Latin1로 설치가 되어 있다. 그래서 한글이 포함된 쿼리를 넣었을 경우 한글이 깨져서 웹페이지에 표시가 된다. 이 때 확인/변경법은 다음과 같다

확인

1. \s 또는 status 입력

mysql> \s

--------------

mysql  Ver 14.14 Distrib 5.1.73, for ibm-aix5.1.0.0 (powerpc) using readline 5.1

Connection id:          36

Current database:       

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server version:         5.1.73 MySQL Community Server (GPL)

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    utf8

Db     characterset:    utf8

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                 14 hours 12 min 3 sec

Threads: 1  Questions: 2248  Slow queries: 0  Opens: 1027  Flush tables: 2  Open tables: 64  Queries per second avg: 

0.43

--------------

2. show variables like 'char%'; 입력

mysql> show variables like 'char%';

+--------------------------+-------------------------------------+

| Variable_name            | Value                               |

+--------------------------+-------------------------------------+

| character_set_client     | utf8                                |

| character_set_connection | utf8                                |

| character_set_database   | utf8                                |

| character_set_filesystem | binary                              |

| character_set_results    | utf8                                |

| character_set_server     | utf8                                |

| character_set_system     | utf8                                |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+-------------------------------------+

8 rows in set (0.00 sec)

3. db.opt 파일 확인 

vi /usr/local/mysql/data/dbtest/db.opt

default-character-set=utf8

default-collation=utf8_general_ci

db에 설정된 내용 확인 가능

4. Schema 조회

 4-1 database 설정 내용 확인

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S

    -> WHERE schema_name = "mysql";

+----------------------------+

| default_character_set_name |

+----------------------------+

| utf8                       |

+----------------------------+

1 row in set (0.00 sec)

 4-2 table 설정 내용 확인

mysql> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,

    ->        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA

WHERE CCSA.collation_name = T.table_collation

    -> WHERE CCSA.collation_name = T.table_collation

    ->   AND T.table_schema = "mysql"

    ->   AND T.table_name = "db";

+--------------------+

| character_set_name |

+--------------------+

| utf8               |

+--------------------+

1 row in set (0.00 sec)

 4-3 table 상세 내용

mysql> use mysql

mysql> SHOW FULL COLUMNS FROM db;

+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field                 | Type          | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Host                  | char(60)      | utf8_bin        | NO   | PRI |         |       | select,insert,update,references |         |

| Db                    | char(64)      | utf8_bin        | NO   | PRI |         |       | select,insert,update,references |         |

| User                  | char(16)      | utf8_bin        | NO   | PRI |         |       | select,insert,update,references |         |

| Select_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |

| Insert_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |

| Update_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |

| Delete_priv           | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |    

| Trigger_priv          | enum('N','Y') | utf8_general_ci | NO   |     | N       |       | select,insert,update,references |         |

+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

22 rows in set (0.00 sec)

변경

1. 명령어를 이용한 변경법(mysql 재시작시 초기화됨)

mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;
mysql> ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8;
mysql> commit;

commit 명령어는 하위 테이블에 적용하는 명령어

2. my.cnf에 적용하는 방법(mysql 재기동 필요)

[mysql]

default-character-set=utf8

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

character-set-client-handshake=FALSE

init_connect=SET collation_connection = utf8_general_ci

init_connect=SET NAMES utf8

default-character-set=utf8

character-set-server=utf8

collation-server=utf8_general_ci

old_passwords=1

[client]

default-character-set=utf8

[mysqldump]

default-character-set=utf8

my.cnf 파일에 위 내용을 넣고 mysql을 재기동

생성

1. 데이타 베이스 생성 : utf8 

CREATE DATABASE DB명 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;