æ¥çã设置ä¸ä¿®æ¹MySQLå符é
åä¸æ®µæ¶é´ï¼ä¸ç´è¢«MySQLçå符éå°æ°ï¼ä»å¤©å°±è¿æ¹é¢çç¥è¯æ»ç»ä¸ä¸ã MySQLçå符éæ¯æ(Character Set Support)æ两个æ¹é¢ï¼å符é(Character set)åæåºæ¹å¼(Collation)ã对äºå符éçæ¯æç»åå°å个å±æ¬¡: æå¡å¨(server)ï¼æ°æ®åº(database)ï¼æ°æ®è¡¨(tabl
åä¸æ®µæ¶é´ï¼ä¸ç´è¢«MySQLçå符éå°æ°ï¼ä»å¤©å°±è¿æ¹é¢çç¥è¯æ»ç»ä¸ä¸ã
MySQLçå符éæ¯æ(Character Set Support)æ两个æ¹é¢ï¼å符é(Character set)åæåºæ¹å¼(Collation)ã对äºå符éçæ¯æç»åå°å个å±æ¬¡: æå¡å¨(server)ï¼æ°æ®åº(database)ï¼æ°æ®è¡¨(table)åè¿æ¥(connection)ã
1. MySQLé»è®¤å符é
MySQL对äºå符éçæå®å¯ä»¥ç»åå°ä¸ä¸ªæ°æ®åºï¼ä¸å¼ 表ï¼ä¸åï¼åºè¯¥ç¨ä»ä¹å符éãä½æ¯ï¼ä¼ ç»çç¨åºå¨å建æ°æ®åºåæ°æ®è¡¨æ¶å¹¶æ²¡æ使ç¨é£ä¹å¤æçé ç½®ï¼å®ä»¬ç¨çæ¯é»è®¤çé ç½®ï¼é£ä¹ï¼é»è®¤çé ç½®ä»ä½èæ¥å¢ï¼
- ç¼è¯MySQL æ¶ï¼æå®äºä¸ä¸ªé»è®¤çå符éï¼è¿ä¸ªå符éæ¯ latin1ï¼
- å®è£ MySQL æ¶ï¼å¯ä»¥å¨é ç½®æ件 (my.ini) ä¸æå®ä¸ä¸ªé»è®¤ççå符éï¼å¦æ没æå®ï¼è¿ä¸ªå¼ç»§æ¿èªç¼è¯æ¶æå®çï¼
- å¯å¨mysqld æ¶ï¼å¯ä»¥å¨å½ä»¤è¡åæ°ä¸æå®ä¸ä¸ªé»è®¤ççå符éï¼å¦æ没æå®ï¼è¿ä¸ªå¼ç»§æ¿èªé ç½®æ件ä¸çé ç½®ï¼æ¤æ¶ character_set_server 被设å®ä¸ºè¿ä¸ªé»è®¤çå符éï¼
- å½å建ä¸ä¸ªæ°çæ°æ®åºæ¶ï¼é¤éæç¡®æå®ï¼è¿ä¸ªæ°æ®åºçå符é被缺ç设å®ä¸ºcharacter_set_serverï¼
- å½éå®äºä¸ä¸ªæ°æ®åºæ¶ï¼character_set_database 被设å®ä¸ºè¿ä¸ªæ°æ®åºé»è®¤çå符éï¼
- å¨è¿ä¸ªæ°æ®åºéå建ä¸å¼ 表æ¶ï¼è¡¨é»è®¤çå符é被设å®ä¸º character_set_databaseï¼ä¹å°±æ¯è¿ä¸ªæ°æ®åºé»è®¤çå符éï¼
- å½å¨è¡¨å 设置ä¸æ æ¶ï¼é¤éæç¡®æå®ï¼å¦åæ¤æ 缺ççå符éå°±æ¯è¡¨é»è®¤çå符éï¼
ç®åçæ»ç»ä¸ä¸ï¼å¦æä»ä¹å°æ¹é½ä¸ä¿®æ¹ï¼é£ä¹ææçæ°æ®åºçææ表çæææ ä½çé½ç¨ latin1 åå¨ï¼ä¸è¿æ们å¦æå®è£ MySQLï¼ä¸è¬é½ä¼éæ©å¤è¯è¨æ¯æï¼ä¹å°±æ¯è¯´ï¼å®è£ ç¨åºä¼èªå¨å¨é ç½®æ件ä¸æ default_character_set 设置为 UTF-8ï¼è¿ä¿è¯äºç¼ºçæ åµä¸ï¼ææçæ°æ®åºçææ表çæææ ä½çé½ç¨ UTF-8 åå¨ã
2. æ¥çé»è®¤å符é
é»è®¤æ åµä¸ï¼mysqlçå符éæ¯latin1(ISO_8859_1)ãé常ï¼æ¥çç³»ç»çå符éåæåºæ¹å¼ç设å®å¯ä»¥éè¿ä¸é¢ç两æ¡å½ä»¤ï¼
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
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
3. ä¿®æ¹é»è®¤å符é
æç®åçä¿®æ¹æ¹æ³ï¼å°±æ¯ä¿®æ¹mysqlçmy.iniæ件ä¸çå符éé®å¼ï¼å¦ï¼
default-character-set = utf8 character_set_server = utf8
ä¿®æ¹å®åï¼éå¯mysqlçæå¡ã
ä½¿ç¨ mysql> SHOW VARIABLES LIKE 'character%';æ¥çï¼åç°æ°æ®åºç¼ç åå·²æ¹æ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
è¿æä¸ç§ä¿®æ¹å符éçæ¹æ³ï¼å°±æ¯ä½¿ç¨mysqlçå½ä»¤ï¼
mysql> SET character_set_client = utf8 ; mysql> SET character_set_connection = utf8 ; mysql> SET character_set_database = utf8 ; mysql> SET character_set_results = utf8 ; mysql> SET character_set_server = utf8 ; mysql> SET collation_connection = utf8 ; mysql> SET collation_database = utf8 ; mysql> SET collation_server = utf8 ;
å 为character_set_clientåé并ä¸ä½ä¸ºæ¥æ¶æ¾ç¤ºæ¶ä½¿ç¨çå符éï¼åªæcharacter_set_resultsæä½ä¸ºæ¾ç¤ºæ¶ä½¿ç¨çå符éï¼æ以éè¦å°å ¶ææ两个åéã
character setæ¯ä¸ç»ç¬¦å·åä»ä»¬å¯¹åºçç¼ç ï¼collationæ¯ä¸ç»è§åï¼è§å®äºå符ä¹é´å¦ä½æ¯è¾ï¼å¤§å°ï¼ãæ¯ä¸ä¸ªcharacter seté½å¯¹åºçä¸ç»ï¼è³å°ä¸ä¸ªï¼collationï¼èæ¯ä¸ä¸ªcollation对åºå¯ä¸ä¸ä¸ªcharacter setï¼é常ä»ä»¬ä¸¤ä¸ªéè¦æ对åºç°ï¼å·²å®ææ°æ®åºéçç¸å ³æä½ï¼æ¯å¦æåºï¼å符串è¿æ¥çæä½ã
å¨ä¸è¿°å个级å«ä¸ï¼å¯¹character setåcollationé½æé»è®¤ç设置ï¼serverå±çé»è®¤ä¸ºlatin1ålatin1_swedish_ci.(ci:case insensible)ãå¨å建å个å±æ¬¡çå®ä½æ¶é½æç¸åºçåå¥æè åé项å¯ä»¥ä½¿ç¨ï¼ä»¥æ¾å¼ç声æå个å®ä¹ å°è¦ä½¿ç¨çå符éåæ ¡å¯¹éã
ä¸è¬å°±ç®è®¾ç½®äºè¡¨çé»è®¤å符é为utf8并ä¸éè¿UTF-8ç¼ç åéæ¥è¯¢ï¼ä½ ä¼åç°åå ¥æ°æ®åºçä»ç¶æ¯ä¹±ç ãé®é¢å°±åºå¨è¿ä¸ªconnectionè¿æ¥å±ä¸ã解å³æ¹æ³æ¯å¨åéæ¥è¯¢åæ§è¡ä¸ä¸ä¸é¢è¿å¥ï¼SET NAMES 'utf8';
å®ç¸å½äºä¸é¢çä¸å¥æ令ï¼
SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8;
- character_set_clientï¼å®¢æ·ç«¯åéè¿æ¥æåçå符é
- character_set_resultsï¼åéç»å®¢æ·ç«¯çç»ææ使ç¨çå符é
- character_set_connectionï¼ç¨äºè¿æ¥çå符é
character_set_client,character_set_connectionè¿ä¸¤ä¸ªåéä» ç¨ä¸ä¿è¯ä¸character_set_databaseç¼ç çä¸è´ï¼ècharacter_set_resultsåç¨ä¸ä¿è¯SELECTè¿åçç»æä¸ç¨åºçç¼ç ä¸è´ã
ä¾å¦ï¼ä½ çæ°æ®åº(character_set_database)ç¨çæ¯utf8çå符éï¼é£ä¹ä½ å°±è¦ä¿è¯character_set_client,character_set_connectionä¹æ¯utf8çå符éãèä½ çç¨åºä¹è®¸éç¨ç并ä¸æ¯utf8ï¼æ¯å¦ä½ çç¨åºç¨çæ¯gbkï¼é£ä¹ä½ è¥æcharacter_set_resultsä¹è®¾ç½®ä¸ºutf8çè¯å°±ä¼åºç°ä¹±ç é®é¢ãæ¤æ¶ä½ åºè¯¥æcharacter_set_results设置为gbkãè¿æ ·å°±è½ä¿è¯æ°æ®åºè¿åçç»æä¸ä½ çç¨åºçç¼ç ä¸è´ã
é¤æ¤ä¹å¤ï¼è¿åºè¯¥æ³¨æï¼
- è¦ä¿è¯æ°æ®åºä¸åçæ°æ®ä¸æ°æ®åºç¼ç ä¸è´ï¼å³æ°æ®ç¼ç ä¸character_set_databaseä¸è´ï¼
- è¦ä¿è¯é讯çå符éä¸æ°æ®åºçå符éä¸è´ï¼å³character_set_client,character_set_connectionä¸character_set_databaseä¸è´ï¼
- è¦ä¿è¯SELECTçè¿åä¸ç¨åºçç¼ç ä¸è´ï¼å³character_set_resultsä¸ç¨åºç¼ç ä¸è´ï¼
- è¦ä¿è¯ç¨åºç¼ç ä¸æµè§å¨ç¼ç ä¸è´ï¼å³ç¨åºç¼ç ä¸ä¸è´ã
æ»ç»
å æ¤ï¼ä½¿ç¨ä»ä¹æ°æ®åºçæ¬ï¼ä¸ç®¡æ¯3.xï¼è¿æ¯4.0.xè¿æ¯4.1.xï¼å ¶å®å¯¹æ们æ¥è¯´ä¸éè¦ï¼éè¦çæ两ç¹ï¼
- æ£ç¡®ç设å®æ°æ®åºç¼ç ãMySQL4.0以ä¸çæ¬çå符éæ»æ¯é»è®¤ISO8859-1ï¼MySQL4.1å¨å®è£ çæ¶åä¼è®©ä½ éæ©ãå¦æä½ åå¤ä½¿ç¨UTF- 8ï¼é£ä¹å¨å建æ°æ®åºçæ¶åå°±è¦æå®å¥½UTF-8(å建好以åä¹å¯ä»¥æ¹ï¼4.1以ä¸çæ¬è¿å¯ä»¥åç¬æå®è¡¨çå符é)
- æ£ç¡®ç设å®æ°æ®åºconnectionç¼ç ã设置好æ°æ®åºçç¼ç åï¼å¨è¿æ¥æ°æ®åºæ¶åï¼åºè¯¥æå®connectionçç¼ç ï¼æ¯å¦ä½¿ç¨jdbcè¿æ¥æ¶ï¼æå®è¿æ¥ä¸ºutf8æ¹å¼ã
上一篇: 怎么在织梦的分词词库中添加新词
下一篇: 关于include的变量运用