mysql charset 설정 확인 및 변경(from mac)

MAC 에서 mysql 5.5.x 의 설정파일 위치 %MYSQL_PATH%/support_files 안의 종류별 xxxx.cnf 파일중 하나를 골라서 /etc/my.cnf로 복사한후 편집한다.

** mysql 의 기본설정값 호출 경로 우선 순위 확인방법

${MYSQL_HOME}/bin/mysqld --verbose --help | grep -A 1 'Default options'

** my.cnf 에 추가할 내용(max os x + mysql 5.5.x)

[mysqld] 

character_set_server=euckr
collation_server=euckr_korean_ci
init_connect=set collation_connection=euckr_korean_ci
init_connect=set names euckr
character-set-server=euckr
character-set-client-handshake = TRUE

---utf-8로 설정할경우

character_set_server=utf8
collation_server=utf8_general_ci
init_connect=set collation_connection=utf8_general_ci
init_connect=set names utf8
character-set-server=utf8
character-set-client-handshake = TRUE

=== 아래는 수동 설정(?) 방법 ===

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.28, for osx10.6 (i386) using readline 5.1

Connection id:        459
Current database:    test
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.5.28 MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    euckr
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql.sock
Uptime:            1 hour 20 min 17 sec

Threads: 1  Questions: 643  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.133

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

mysql> SET character_set_client = euckr;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_results = euckr;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_connection = euckr;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER DATABASE test DEFAULT CHARACTER SET euckr;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'c%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | euckr                                                  |
| character_set_connection | euckr                                                  |
| character_set_database   | euckr                                                  |
| character_set_filesystem | binary                                                 |
| character_set_results    | euckr                                                  |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.5.28-osx10.6-x86_64/share/charsets/ |
| collation_connection     | euckr_korean_ci                                        |
| collation_database       | euckr_korean_ci                                        |
| collation_server         | latin1_swedish_ci                                      |
| completion_type          | NO_CHAIN                                               |
| concurrent_insert        | AUTO                                                   |
| connect_timeout          | 10                                                     |
+--------------------------+--------------------------------------------------------+
14 rows in set (0.00 sec)

생성한 database 및 table 의 생성 정보 확인

mysql> show create table users;
+-------+------------------------------------------------------------------------------
|Table|CreateTable
+-------+------------------------------------------------------------------------------
| users | CREATE TABLE `users` (
  `id` varchar(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `password` varchar(10) NOT NULL,
  `level` tinyint(4) NOT NULL,
  `login` int(11) NOT NULL,
  `recommend` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------
1 row in set (0.00 sec)