MySQL character set È®ÀÎ ¹× º¯°æ
¾ðÁ¦³ª ÇÑ±Û ±úÁü ¹®Á¦·Î ÀÎÇÑ Ä³¸¯ÅÍ ¼Â È®ÀÎ ¹× º¯°æ ÀÛ¾÷ÀÌ ¸¹´Ù.
ÀÏ´Ü DB¿¡ ÀúÀåµÈ ÇѱÛÀÌ Å¬¶óÀ̾ðÆ®¿¡¼ ¾È º¸ÀÌ´Â °æ¿ì´Â Ŭ¶óÀ̾ðÆ®ÀÇ ÀÎÄÚµù °ªÀ» º¯°æÇؼ Á¶Ä¡¸¦ ÇÒ ¼ö ÀÖ´Ù.
±×·¯³ª ÀúÀå ´ç½ÃÀÇ ÀÎÄÚµù °ªÀÌ ¼·Î ´Ù¸¥ ÇѱÛÀÌ µé¾î°¡ ÀÖÀ» °æ¿ì´Â ¾î¶² ÇѱÛÀº Á¤»óÀûÀ¸·Î º¸ÀÌÁö¸¸, ¾î¶² ÇѱÛÀº ±úÁ®¼ º¸ÀÌ´Â °æ¿ì°¡ ¹ß»ýÇÏ°Ô µÈ´Ù.
MySQLÀº ±âº»À¸·Î ¼¹ö ij¸¯ÅÍ ¼ÂÀÌ latin1À¸·Î ¼³Á¤ µÇ¾î ÀÖ¾î¼ DB »ý¼º ½Ã ij¸¯ÅÍ ¼Â °ªÀ» ÁöÁ¤ÇØÁÖÁö ¾ÊÀ¸¸é latin1À¸·Î »ý¼ºÀÌ µÈ´Ù.
Character set ?
»ç¿ëÇÏ´Â ¾ð¾î¸¦ Ç¥ÇöÇϱâ À§ÇÑ ¹®ÀÚµéÀÇ ÁýÇÕÀ» ÀǹÌ. ÇѱÛÀ» Ç¥ÇöÇϱâ À§ÇÏ¿© »ç¿ëÇϴ ij¸¯ÅÍ ¼Â¿¡´Â ¿©·¯°¡Áö°¡ Á¸Àç ÇÑ´Ù.
Encoding ?
Character SetÀ» ÄÄÇ»ÅÍ°¡ ÀÌÇØÇÒ ¼ö ÀÖ´Â ¹ÙÀÌÆ®¿Í ¸ÅÇÎÇØ ÁÖ´Â °Í.
0. »ç¿ëÇÒ ¼ö Àִ ij¸¯ÅÍ ¼Â Á¾·ù È®ÀÎ
mysql> show character set ; +----------+---------------------------------+---------------------+--------+ | 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 | +----------+---------------------------------+---------------------+--------+
mysql> show character set where description like '%Korean%' ; +---------+---------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+-------------------+--------+ | euckr | EUC-KR Korean | euckr_korean_ci | 2 | +---------+---------------+-------------------+--------+
|
À§¿¡¼ º¸¸é Maxlen Ä®·³ÀÌ º¸À̴µ¥ ¿µ¾î ÀÌ¿ÜÀÇ ¾ð¾î¸¦ ÀúÀåÇϱâ À§Çؼ´Â 2 ÀÌ»óÀÇ °ªÀ» °¡Áø ¾ð¾î ¼ÂÀ» °¡Á®¾ß¸¸ ÇѱÛÀÌ DB¿¡ ÀúÀåÀÌ µÈ´Ù.
euckrÀº ÇѱÛÀ» 2¹ÙÀÌÆ®·Î ÀúÀåÇÏ°í, utf8Àº ÇѱÛÀ» 3¹ÙÀÌÆ®·Î ÀúÀåÇÑ´Ù. (°ø¹éÀ̳ª ¿µ¹®Àº 1¹ÙÀÌÆ®·Î ó¸®)
±×·¡¼ euckrÀº Á¦ÇÑ µÈ Çѱ۸¸ Ç¥ÇöÀÌ °¡´ÉÇÏÁö¸¸, ÇÑ±Û 1±ÛÀÚ´ç 2¹ÙÀÌÆ®¸¦ »ç¿ëÇϹǷÎ, Á¦ÇÑµÈ Çѱ۸¸ »ç¿ëÇϴ ȯ°æ¿¡¼´Â euckrÀ» ¼³Á¤ ÇÏ´Â °ÍÀÌ À¯¸®
1. °£´ÜÇÏ°Ô Ä³¸¯ÅÍ ¼ÂÀ» È®ÀÎ ÇÏ´Â ¹æ¹ý
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.14, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3836 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 11 min 12 sec
Threads: 3 Questions: 33930 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 113 Queries per second avg: 7.942
Servere characterset : µ¥ÀÌÅͺ£À̽º »ý¼º ½Ã ±âº» °ªÀ» ÁöÁ¤ÇØ ÁÖÁö ¾ÊÀ» °æ¿ì, ÀÌ °ªÀ¸·Î µ¥ÀÌÅÍ º£À̽º ij¸¯ÅÍ ¼ÂÀÌ ¼³Á¤ µÊ. DB characterset : Å×ÀÌºí »ý¼º ½Ã ±âº» °ªÀ» ÁöÁ¤ÇØ ÁÖÁö ¾ÊÀ» °æ¿ì, ÀÌ °ªÀ¸·Î Å×À̺íÀÇ Ä³¸¯ÅÍ ¼ÂÀÌ ¼³Á¤ µÊ. Client characterset : Ŭ¶óÀ̾ðÆ® ÀÎÄÚµù ¼³Á¤ °ª.
|
2. µ¥ÀÌÅÍ º£À̽ºÀÇ µðÆúÆ® ij¸¯ÅÍ ¼Â ¼³Á¤
»ý¼º ½Ã ¼³Á¤ÇØ ÁÖ´Â ¹æ¹ý CREATE DATABASE `utf8db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; CREATE DATABASE `euekrdb` DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci ;
Alter ¸í·ÉÀ¸·Î º¯°æ ÇÏ´Â ¹æ¹ý ALTER DATABASE db¸í DEFAULT CHARACTER SET utf8 ;
µ¥ÀÌÅͺ£À̽º ij¸¯ÅÍ ¼Â È®ÀÎ SELECT schema_name , default_character_set_name FROM information_schema.schemata ;
schema_name |default_character_set_name | -------------------|---------------------------| information_schema |utf8 | euekrdb |euckr | mysql |latin1 | performance_schema |utf8 | sys |utf8 | test |latin1 | utf8db |utf8 |
Å×À̺íÀÇ Ä³¸¯ÅÍ ¼Â ¼³Á¤ ¹× È®ÀÎ CREATE TABLE Å×À̺í¸í (id int , name varchar(10)) DEFAULT CHARSET=utf8 ;
SELECT table_schema , table_name , table_collation FROM information_schema.tables WHERE table_schema = 'information_schema' AND table_name = 'PROCESSLIST'
table_schema |table_name |table_collation | -------------------|------------|----------------| information_schema |PROCESSLIST |utf8_general_ci |
|
3. ¼¼¼Ç·¹º§¿¡¼ ÀÎÄÚµù °ª º¯°æ
ÀÓ½ÃÀûÀÎ º¯°æ
set names Äɸ¯Åͼ ;
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.14, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3836 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 11 min 12 sec
Threads: 3 Questions: 33930 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 113 Queries per second avg: 7.942 --------------
mysql> set names latin1 ; Query OK, 0 rows affected (0.00 sec)
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.14, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3836 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 20 min 3 sec
Threads: 3 Questions: 38153 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 113 Queries per second avg: 7.943 --------------
mysql> set names utf8 ; Query OK, 0 rows affected (0.00 sec)
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.14, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3836 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 25 min 38 sec
Threads: 3 Questions: 40805 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 113 Queries per second avg: 7.941 --------------
|
¿µ±¸ÀûÀÎ º¯°æ
/etc/my.cnf ÆÄÀÏ¿¡ ij¸¯ÅÍ ¼ÂÀ» ¸í½Ã ÈÄ MySQL Àç ±âµ¿.
¿¹) utf8·Î º¯°æ ½Ã
[client] default-character-set = utf8
[mysqld] skip-character-set-client-handshake default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci init-connect = SET NAMES utf8
[mysqldump] default-character-set = utf8
[mysql] default-character-set = utf8
|
4. ij¸¯ÅÍ ¼Â Å×½ºÆ®
# Server characterSet : latin1 # Database characterset : euckr # Client ÀÎÄÚµù °ªÀº º¯°æÇØ °¡¸é¼ Å×½ºÆ®
# Client ÀÎÄÚµù°ªÀ» µ¥ÀÌÅͺ£À̽º ij¸¯ÅÍ ¼Â°ú µ¿ÀÏÇÑ °ªÀ¸·Î º¯°æ mysql> set names euckr ; mysql> status ;
Current database: euekrdb Current user: root@localhost Server characterset: latin1 Db characterset: euckr Client characterset: euckr Conn. characterset: euckr
#Å×½ºÆ® Å×ÀÌºí »ý¼º mysql> create table et1 (id int , name varchar(10)) ; mysql> create table eet1 (id int , name varchar(10)) DEFAULT CHARSET=euckr ; mysql> create table eut1 (id int , name varchar(10)) DEFAULT CHARSET=utf8 ; mysql> commit ; mysql> select table_name , table_collation from information_schema.tables where table_name like 'e%1' ; +------------+-----------------+ | table_name | table_collation | +------------+-----------------+ | eet1 | euckr_korean_ci | | et1 | euckr_korean_ci | | eut1 | utf8_general_ci | +------------+-----------------+ ij¸¯ÅÍ ¼ÂÀ» ¸í½ÃÀûÀ¸·Î ÁöÁ¤ÇØ ÁÖÁö ¾Ê¾Ò´ø et1 Å×À̺íÀº µ¥ÀÌÅͺ£À̽º ±âº» ij¸¯ÅÍ ¼ÂÀ¸·Î ¼³Á¤ µÊ.
#µ¥ÀÌÅÍ Insert mysql> insert into et1 values (1 , '11È«11' ) ; mysql> commit ; mysql> insert into eet1 values (1 , '11È«11' ) ; mysql> commit ; mysql> insert into eut1 values (1 , '11È«11' ) ; mysql> commit;
mysql> select * from et1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11È«11 | +------+--------+
mysql> select * from eet1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11È«11 | +------+--------+
mysql> select * from eut1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11È«11 | +------+--------+
# Ŭ¶óÀ̾ðÆ® ÀÎÄÚµù º¯°æ mysql> set names utf8 ; mysql> status ;
Current database: euekrdb Server characterset: latin1 Db characterset: euckr Client characterset: utf8 Conn. characterset: utf8
mysql> select * from et1 ; +------+---------+ | id | name | +------+---------+ | 1 | 11??11 | +------+---------+
mysql> select * from eet1 ; +------+---------+ | id | name | +------+---------+ | 1 | 11??11 | +------+---------+
mysql> select * from eut1 ; +------+---------+ | id | name | +------+---------+ | 1 | 11??11 | +------+---------+
# »õ·Î¿î µ¥ÀÌÅÍ Insert mysql> insert into et1 values (11 , '11È«11' ) ; ERROR 1366 (HY000): Incorrect string value: '\xC8\xAB11' for column 'name' at row 1
mysql> insert into eet1 values (11 , '11È«11' ) ; ERROR 1366 (HY000): Incorrect string value: '\xC8\xAB11' for column 'name' at row 1
mysql> insert into eut1 values (11 , '11È«11' ) ; mysql> commit;
mysql> select * from eut1 ; +------+---------+ | id | name | +------+---------+ | 1 | 11??11 | | 11 | 11È«11 | +------+---------+
# ´Ù½Ã Ŭ¶óÀ̾ðÆ® ÀÎÄÚµù º¯°æ mysql> set names euckr ; mysql> status ;
Current database: euekrdb Server characterset: latin1 Db characterset: euckr Client characterset: euckr Conn. characterset: euckr
mysql> select * from eut1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11È«11 | | 11 | 11?11 | +------+--------+
|
# Server characterSet : latin1 # Database characterset : utf8 # Client ÀÎÄÚµù °ªÀº º¯°æÇØ °¡¸é¼ Å×½ºÆ®
# Client ÀÎÄÚµù°ªÀ» µ¥ÀÌÅͺ£À̽º ij¸¯ÅÍ ¼Â°ú µ¿ÀÏÇÑ °ªÀ¸·Î º¯°æ mysql> set names utf8; mysql> status
Current database: utf8db Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8
#Å×½ºÆ® Å×ÀÌºí »ý¼º mysql> create table ut1 (id int , name varchar(10)) ; mysql> create table uet1 (id int , name varchar(10)) DEFAULT CHARSET=euckr ; mysql> create table uut1 (id int , name varchar(10)) DEFAULT CHARSET=utf8 ; mysql> commit ; mysql> select table_name , table_collation from information_schema.tables where table_name like 'u%1' ; +------------+-----------------+ | table_name | table_collation | +------------+-----------------+ | uet1 | euckr_korean_ci | | ut1 | utf8_general_ci | | uut1 | utf8_general_ci | +------------+-----------------+
#µ¥ÀÌÅÍ Insert mysql> insert into ut1 values (1 , '11È«11' ) ; mysql> commit ; mysql> insert into uet1 values (1 , '11È«11' ) ; ERROR 1366 (HY000): Incorrect string value: '\xC8\xAB11' for column 'name' at row 1 mysql> insert into uut1 values (1 , '11È«11' ) ; mysql> commit;
mysql> select * from ut1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11È«11 | +------+--------+
mysql> select * from uet1 ; Empty set (0.00 sec)
mysql> select * from uut1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11È«11 | +------+--------+
# Ŭ¶óÀ̾ðÆ® ÀÎÄÚµù º¯°æ mysql> set names euckr ; mysql> status
Current database: utf8db Server characterset: latin1 Db characterset: utf8 Client characterset: euckr Conn. characterset: euckr
# »õ·Î¿î µ¥ÀÌÅÍ Insert mysql> insert into ut1 values (11 , '11È«11' ) ; mysql> commit ; mysql> insert into uet1 values (11 , '11È«11' ) ; mysql> commit ; mysql> insert into uut1 values (11 , '11È«11' ) ; mysql> commit ;
mysql> select * from ut1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11?11 | | 11 | 11È«11 | +------+--------+
mysql> select * from uet1 ; +------+--------+ | id | name | +------+--------+ | 11 | 11È«11 | +------+--------+
mysql> select * from uut1 ; +------+--------+ | id | name | +------+--------+ | 1 | 11?11 | | 11 | 11È«11 | +------+--------+
# ´Ù½Ã Ŭ¶óÀ̾ðÆ® ÀÎÄÚµù º¯°æ mysql> set names utf8 ; mysql> status
Current database: utf8db Server characterset: latin1 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8
mysql> select * from ut1 ; +------+---------+ | id | name | +------+---------+ | 1 | 11È«11 | | 11 | 11??11 | +------+---------+
mysql> select * from uet1 ; +------+---------+ | id | name | +------+---------+ | 11 | 11??11 | +------+---------+
mysql> select * from uet1 ; +------+---------+ | id | name | +------+---------+ | 11 | 11??11 | +------+---------+
mysql> select * from uut1 ; +------+---------+ | id | name | +------+---------+ | 1 | 11È«11 | | 11 | 11??11 | +------+---------+ |
utf8»óÅ´ ÇѱÛÀ» 3¹ÙÀÌÆ®·Î ÀúÀåÀ» ÇÑ´Ù. ±×·¡¼ utf8ÀÎ Å×ÀÌºí¿¡ euckr·Î ÇѱÛÀ» ÀúÀåÇÏ°Ô µÇ¸é 2¹ÙÀÌÆ®·Î ÀúÀåÇÏ°Ô µÇ¹Ç·Î, ÀúÀåÀÌ µÈ´Ù.
±×·¯³ª euckr »óÅÂÀÎ Å×ÀÌºí¿¡ utf8·Î µ¥ÀÌÅ͸¦ ÀúÀåÇÏ·Á°í Çϸé, 2¹ÙÀÌÆ®·Î ÇѱÛÀ» ÀúÀåÇØ¾ß Çϴµ¥ 3¹ÙÀÌÆ®·Î ÀúÀåÀ» ½ÃµµÇÏ°Ô µÇ¹Ç·Î ¿¡·¯°¡ ¹ß»ý ÇÑ´Ù.
Å×À̺íÀÇ Ä³¸¯ÅÍ ¼Â°ú »ó°ü¾øÀÌ ÇѱÛÀ» ÀúÀåÇÒ ´ç½ÃÀÇ ÀÎÄÚµù °ª°ú ÀÏÄ¡ÇÏ´Â Çѱ۸¸ Á¦´ë·Î Ç¥ÇöÀÌ µÈ´Ù.
±×·¡¼ Å×ÀÌºí º°·Î ij¸¯ÅÍ ¼ÂÀ» ÁöÁ¤ÇØ ÁÙ ¼ö ÀÖÁö¸¸, µ¥ÀÌÅÍ º£À̽º ·¹º§¿¡¼ ij¸¯ÅÍ ¼ÂÀ» ÅëÀÏÇÏ¿©¼ ÇѱÛÀ» »ç¿ëÇϸé, ÇÑ±Û ±úÁü ¹®Á¦¸¦ ¹Ì¸® ¹æÁöÇÒ ¼ö ÀÖ´Ù.