데이터가 없는 상태에서 작업하는 것이 좋으며, 이미 데이터가 존재할 경우 문자셋 변경으로 인한 깨짐 현상이 발생할 수 있으므로 반드시 백업을 진행해야 한다. 데이터가 포함된 상태에서字符셋迁移 시 깨진 데이터가 발생할 수 있다. 테스트 환경 구성

mysql> create database sample_db;
Query OK, 1 row affected (0.00 sec)

mysql> use sample_db;
Database changed
mysql> create table user_table(user_id int, user_name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into user_table values(1,"honggildong");
Query OK, 1 row affected (0.00 sec)

(1).문자셋 확인 1）MySQL 클라이언트, 서버 및 데이터베이스의 기본 문자셋 조회

mysql> show VARIABLES like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       | //클라이언트 사용 인코딩
| character_set_connection | utf8                       | //연결 시 사용 인코딩
| character_set_database   | latin1                     | //데이터베이스 사용 인코딩
| 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)

collation 정보 확인은 아래 명령어로 가능하다.

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

2）MySQL 지원 문자셋 목록 확인

mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      | 1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

3）기존 데이터베이스의 문자셋 확인

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

4）기존 테이블의 문자셋 확인

mysql> show table status from sample_db like 'user_table';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| user_table  | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2019-06-04 09:55:25 | 2019-06-04 09:55:35 | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

또는 다음과 같이 확인 가능:

mysql> show create table sample_db.user_table;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                      |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+
| user_table  | CREATE TABLE `user_table` (
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5）기존 테이블의 컬럼 문자셋 확인

mysql> show full columns from sample_db.user_table;
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| user_id  | int(11)     | NULL              | YES  |     | NULL    |       | select,insert,update,references |         |
| user_name| varchar(20) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

(2).문자셋 변경 1）기본 문자셋 변경 임시 변경 방법 (세션 단위):

//연결 시 사용할 인코딩 설정
set character_set_connection=utf8;
//데이터베이스 기본 인코딩
set character_set_database=utf8;
//결과집합 인코딩
set character_set_results=utf8;
//MySQL 서버 인코딩
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

영구 변경 방법 (/etc/my.cnf 파일 수정): [mysqld] 섹션에 해당 설정을 추가한 후 mysqld 서비스를 재시작해야 한다.配置文件에서 character_set_server=utf8을 설정하면 해당 서버의 기본 데이터베이스 문자셋도 utf8로 변경된다. 설정 적용 후 확인:

mysql> show VARIABLES like 'character%';
+--------------------------+----------------------------+
| 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.01 sec)

2）기존 데이터베이스의 문자셋 변경

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

mysql> alter database sample_db default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database sample_db;
+-----------+------------------------------------------------------------------+
| Database  | Create Database                                                  |
+-----------+------------------------------------------------------------------+
| sample_db | CREATE DATABASE `sample_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

3）기존 테이블의 문자셋 변경

mysql> show create table sample_db.user_table;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                      |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+
| user_table  | CREATE TABLE `user_table` (
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table sample_db.user_table convert to character set utf8;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table sample_db.user_table;
+-------------+---------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                    |
+-------------+---------------------------------------------------------------------------------------------------------------------------------+
| user_table  | CREATE TABLE `user_table` (
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4）컬럼의 문자셋 변경

mysql> show full columns from sample_db.user_table;
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| user_id  | int(11)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
| user_name| varchar(20) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

mysql> alter table sample_db.user_table modify user_name varchar(20) character set gbk; 
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show full columns from sample_db.user_table;
+----------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation      | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| user_id  | int(11)     | NULL           | YES  |     | NULL    |       | select,insert,update,references |         |
| user_name| varchar(20) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)