MySQL 인코딩

보통 MySQL의 기본설치는 SYSTEM레벨에서 latin1이다. 시스템 설정을 건드리지 않고 utf8으로 DB를 쓰려면 다음과 같이한다.

DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 같은 인코딩 설정은 DB,TABLE,COLUMN 단위로 적용가능하다.

어떤 기본값을 주지 않았을 때는 상위 SYSTEM->DB->TABLE->COLUMN을 따른다.

ALTER DATABASE ... , ALTER TABLE ... 명령으로 기본 인코딩을 바꿀 수 있다.

( 하지만 기존에 만들어진 것들은 바뀌지 않는다. latin1 DB상태에서 생성한 TABLE의 인코딩은 그대로 유지된다는 말, 새로생성하는 것은 바뀐 인코딩을 따름)

 

    서버의 default 상태는
    mysql> show variables like 'char%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | latin1                     |
    | character_set_connection | latin1                     |
    | character_set_database   | latin1                     |
    | character_set_filesystem | binary                     |
    | character_set_results    | latin1                     |
    | character_set_server     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)

    mysql> CREATE DATABASE mytest;
    Query OK, 1 row affected (0.00 sec)

    mysql> use mytest;
    Database changed
    mysql> show variables like 'char%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | latin1                     |
    | character_set_connection | latin1                     |
    | character_set_database   | latin1                     |
    | character_set_filesystem | binary                     |
    | character_set_results    | latin1                     |
    | character_set_server     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)

    mysql> CREATE TABLE mytest ( fix CHAR(4), var VARCHAR(4) );
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert mytest values ('안녕하세요','안녕하세요');
    Query OK, 1 row affected, 2 warnings (0.00 sec)

    mysql> select * from mytest;
    +------+------+
    | fix  | var  |
    +------+------+
    | 안▒ | 안▒ |
    +------+------+
    1 row in set (0.00 sec)
    latin1상태에서 넣어서 글자가 4byte에서 짤렸음
    mysql> ALTER DATABASE mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    Query OK, 1 row affected (0.00 sec)
    DB의 기본 인코딩 바꿈
    mysql> show variables like 'char%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | latin1                     |
    | character_set_connection | latin1                     |
    | character_set_database   | utf8                       |
    | character_set_filesystem | binary                     |
    | character_set_results    | latin1                     |
    | character_set_server     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)
    클라이언트 인코딩을 utf8으로 바꿈
    mysql> set names utf8;
    Query OK, 0 rows affected (0.00 sec)

    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     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)

    mysql> insert mytest values ('안녕하세요','안녕하세요');
    Query OK, 1 row affected, 2 warnings (0.00 sec)

    mysql> select * from mytest;
    +-----------+-----------+
    | fix       | var       |
    +-----------+-----------+
    | 안ë | 안ë |
    | ????      | ????      |
    +-----------+-----------+
    2 rows in set (0.00 sec)
    table의 인코딩이 바뀌지 않았기 때문에 제대로 작동안함
    mysql> CREATE TABLE mytest2 ( fix CHAR(4), var VARCHAR(4) );
    Query OK, 0 rows affected (0.01 sec)
    새로만드는 테이블은 바뀐 DB 설정을 따름
    mysql> insert mytest2 values ('안녕하세요','안녕하세요');
    Query OK, 1 row affected, 2 warnings (0.00 sec)

    mysql> select * from mytest2;
    +--------------+--------------+
    | fix          | var          |
    +--------------+--------------+
    | 안녕하세 | 안녕하세 |
    +--------------+--------------+
    1 row in set (0.00 sec)

    mysql>

    DB를 덤프시켜보자
    aero@master:~$ mysqldump -d mytest -u root -p
    Enter password:
    -- MySQL dump 10.11
    --
    -- Host: localhost    Database: mytest
    -- ------------------------------------------------------
    -- Server version       5.0.51a-3ubuntu5.4

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

    --
    -- Table structure for table `mytest`
    --

    DROP TABLE IF EXISTS `mytest`;
    SET @saved_cs_client     = @@character_set_client;
    SET character_set_client = utf8;
    CREATE TABLE `mytest` (
      `fix` char(4) default NULL,
      `var` varchar(4) default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    SET character_set_client = @saved_cs_client;

    --
    -- Table structure for table `mytest2`
    --

    DROP TABLE IF EXISTS `mytest2`;
    SET @saved_cs_client     = @@character_set_client;
    SET character_set_client = utf8;
    CREATE TABLE `mytest2` (
      `fix` char(4) default NULL,
      `var` varchar(4) default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    SET character_set_client = @saved_cs_client;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

    -- Dump completed on 2009-08-28  1:46:50


    데이터베이스를 만들때 부터 UTF8으로 만들려면

    CREATE DATABASE mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;