ÃֽŠ°Ô½Ã±Û(DB)
2017.03.13 / 14:46
MySQL charset encoding Charset°ú CollationÀÇ Â÷ÀÌ
Mans
Ãßõ ¼ö 309
MySQL charset encoding
Charset°ú CollationÀÇ Â÷ÀÌ
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. ¹®ÀÚ¼Â(character set)Àº ½Éº¼(±ÛÀÚ)°ú ÀÎÄÚµùÀÇ ¹À½ÀÌ°í, CollationÀº ¹®ÀÚ¼ÂÀÇ ¹®ÀÚµéÀ» ºñ±³ÇÏ´Â ±ÔÄ¢ÀÌ´Ù. ¿¹¸¦µé¾î ±ÛÀÚ A=0, B=1, a=10, b=11 À̶ó°í ÇÒ ¶§ 'A'´Â ±ÛÀÚÀÌ°í '0'Àº ÀÎÄÚµùÀÌ´Ù. ¿©±â¼ º¼ ¶§ 'A'¿Í 'a'´Â ¼·Î ´Ù¸¥ ÀÎÄÚµùÀ» °¡Áö¹Ç·Î ´Ù¸£´Ù°í º¼ ¼ö ÀÖ´Ù. ÇÏÁö¸¸ ¸¸¾à ´ë¼Ò¹®ÀÚ¸¦ ±¸ºÐÇÏÁö ¾Ê´Â(case insensitive) collationÀ» ÁöÁ¤Çϸé 'A'='a'°¡ µÇ°Ô µÇ´Â °ÍÀÌ´Ù. ´ë¼Ò¹®ÀÚ¸¦ ÁöÁ¤ÇÏÁö ¾Ê´Â CollationÀº ''_ci''·Î ³¡³´Ù.
±âº» ¼³Á¤
- UTF-8 ¼³Á¤ (Ubuntu :
/etc/mysql/conf.d/encoding.cnf
, RedHat/etc/my.cnf
)#¹®Àڼ ÁöÁ¤ [client] character-sets-dir=utf8 [mysqld] init_connect=SET collation_connection = utf8_general_ci init_connect=SET NAMES utf8 #default-character-set=utf8 # deprecated character-set-server=utf8 collation-server=utf8_general_ci
- ÀÌÈÄ
service mysql restart
DB Å×ÀÌºí »ý¼º½Ã ¹®Àڼ ÁöÁ¤
ÂüÁ¶ :MySQL and UTF-8
Database: (CREATE | ALTER) DATABASE ... DEFAULT CHARACTER SET utf8 Table: (CREATE | ALTER) TABLE ... DEFAULT CHARACTER SET utf8
ÇöÀç ¹®Àڼ Á¤º¸ º¸±â
show variables like 'c%';
°á°ú –>
character_set_client : utf8 character_set_connection : utf8 character_set_database : utf8 character_set_results : utf8 character_set_server : utf8 character_set_system : utf8 character_sets_dir : /usr/share/mysql/charsets/ collation_connection : utf8_general_ci collation_database : utf8_general_ci collation_server : utf8_general_ci
ÀÌ¹Ì »ý¼ºµÈ µ¥ÀÌÅͺ£À̽ºÀÇ ¹®Àڼ ¹Ù²Ù±â
-- Å×À̺íÀÇ ¹®Àڼ Á¤º¸ È®ÀÎ. INFORMATION_SCHEMA.TABLES or SHOW CREATE TABLE [TABLENAME];
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;
ÀÌ¹Ì µ¥ÀÌÅÍ°¡ µé¾î°£ Å×À̺íÀÇ ¹®Àڼ º¯È¯
create table test (merong varchar(20) collate latin1_general_ci); -- ÀÌ·¸°Ô ¸¸µé¾îÁø Å×ÀÌºí¿¡ ÇÑ±Û µ¥ÀÌÅ͸¦ ³ÖÀº ÈÄ Çʵ带 euckr ·Î º¯°æÇÏ·Á¸é -- ´ÙÀ½Ã³·³ ÇØ¾ß ÇÕ´Ï´Ù. alter table test modify merong binary(100); alter table test modify merong varchar(20) collate euckr_korean_ci; -- binary ·Î ¹Ù²Ù¸é ¹®Àڼ Ư¼ºÀÌ »ç¶óÁö±â ¶§¹®¿¡ ÀÌ·± º¯È¯°úÁ¤À» °ÅÃÄ¾ß -- ÇÕ´Ï´Ù(¸Þ´º¾ó¿¡ ÀÇÇϸé). ±×³É ¹Ù²Ù¸é ¹®ÀÚµéÀÌ ¼Õ»óµË´Ï´Ù.