ãã1.
Unicodeæ¯ä»ä¹
Unicodeï¼ä¸æï¼ä¸å½ç ãå½é
ç ãç»ä¸ç ãåä¸ç ï¼æ¯è®¡ç®æºç§å¦é¢åéçä¸é¡¹ä¸çæ åãå®å¯¹ä¸çä¸å¤§é¨åçæåç³»ç»è¿è¡äºæ´çãç¼ç ï¼ä½¿å¾çµèå¯ä»¥ç¨æ´ä¸ºç®åçæ¹å¼æ¥åç°åå¤çæåã
ç®å说æ¥ï¼å°±æ¯æä¸çä¸ææè¯è¨çåï¼å ä¸ææè½æ¾å°ç符å·ï¼å¦é«é³è°±å·ã麻å°ãemojiï¼ç¨åä¸å¥ç¼ç 表示åºæ¥ã
2.
UTF-8æ¯ä»ä¹
UTF-8ï¼8-bit Unicode Transformation
Formatï¼æ¯ä¸ç§é对Unicodeçå¯åé¿åº¦å符ç¼ç ãå¯åé¿åº¦çææå¨äºï¼å¦æè½ä½¿ç¨1åèç¼ç ï¼UTF-8ç»å¯¹ä¸ä¼ä½¿ç¨2åèå»è¡¨ç¤ºã举个ä¾åï¼UTF-8ç1åèé¨ååASCIIç æ¯ç¸åçãæ以表示'A'è¿ä¸ªå符çæ¶åï¼UTF-8ä¸ASCIIç ä¸ä»
ç¼ç ç¸åï¼èä¸é½æ¯åªä½¿ç¨1åèã
3.
Character SetåCollationæ¯ä»ä¹
Character
Setæ¯ä¸å¥ç¬¦å·ä»¥åç¼ç ãCollationæ¯character setçæåºæ¹æ³ãå¨ä¸æççMySQLä¸ï¼character
set被翻è¯ä¸ºâå符éâï¼collation被翻è¯ä¸ºâæ´çâã
举个ä¾åï¼UTF-8æ¯character
setï¼utf8_unicode_ciåutf8mb4_unicode_ciå°±æ¯collationã
Collationçä½ç¨ä¸»è¦æäºï¼å符æåºä¸æ¥æ¾å符ã
å符æåºçä½ç¨æ¯æ¾èæè§çï¼ä¸è¿è¿æ¯è¦ç¨å 个ä¾åå 以说æãæ¯å¦è¦æ¯è¾aåbç大å°ï¼å 为å¨26个è±æåæ¯éé¢ï¼aå¨båï¼æ以å¨ç¼ç çæ¶åï¼ä¹æaæ¾å¨båé¢ãè¿æ ·å°±äº§çäºç¬¬ä¸ç§æåºæ¹å¼ï¼éè¿å符ç¼ç ç大å°æ¥æåºãèå¨ä¸æéé¢ï¼âå¹´âåâæ¥âçæåºï¼é¤äºæç
§å符ç¼ç 大å°ï¼è¿å¯ä»¥æå¦å¤ä¸äºæ åãæ¯å¦å¯ä»¥æç
§ç¬ç»åºï¼âå¹´âç第ä¸ç¬æ¯ä¸¿ï¼âæ¥âç第ä¸ç¬æ¯ä¸¨ï¼è丨æ¯æå¨ä¸¿åçï¼æ以就å°âæ¥âæå¨åé¢ï¼ä¹å¯ä»¥ææ¼é³åºï¼âå¹´âæ¯nå¼å¤´ï¼âæ¥âæ¯rå¼å¤´ï¼äºæ¯æâå¹´âæå¨åé¢ãé¤æ¤ä»¥å¤ï¼è¿å¯ä»¥å®ä¹é¨é¦åºãç¬ç»æ°åºççï¼èä¸åçæåºæ¹æ³ä¼æä¸åçç»æãè±æä¹æ大å°åææä¸ä¸ææçæåºæ¹å¼ãç§ç§ä¸åçæåºæ¹å¼ï¼å°±å½¢æäºä¸åçcollationsã
Collationç第äºä¸ªä½ç¨åæ¯æ¥æ¾å符æ¯å¦å¨ä¸ä¸ªå符ééé¢ãæ¢ç¶æ¯ä¸ä¸ªæåºçéåï¼åå¯ä»¥å¿«éå°éè¿ä¸ä¸ªç¼ç å¼ç¡®å®ä¸ä¸ªå符æ¯å¦å¨éåå
ãè¿ä¸ªç¹æ§æ¯æ们å¨ä¸ç¥ä¸è§ä¸ä½¿ç¨çãæ¯å¦ä½¿ç¨ä¸æè¾å
¥æ³ï¼å°±æ¯éè¿è¾å
¥æ³æ¾å°ä¸ä¸ªç¼ç ï¼éè¿collationæå®æ¥æ¾åºæ¥çã
4.
Unicodeåæ·±å
¥ï¼Planeåä¸æ¥é©è¶ç»ä¸è¡¨ææå
ããutf8_unicode_ciåutf8mb4_unicode_ciè¿ä¸¤ä¸ªcollationsé½æ¯åºäºUTF-8ç¼ç çï¼ä½æåºæ¹é¢æå¤æå°ä¼æå·®å«ãå¯æ¯æ´å¤§çå·®å«æ¯å®æ¥æ¾å符çéåãè¿éè¦æå°ä¸ä¸ªUnicodeçæ¦å¿µï¼Planeã
ãã4.1.
Plane
Planeä¸æè¯ä½âUnicodeå¹³é¢å符æ å°âï¼ä¸è¿æ们è¿æ¯å«å®plane好å¦ãç®åçUnicodeå符å为17个planesï¼èæ¯ä¸ªplaneæ¥æ65536ï¼å³2^16ï¼ä¸ªä»£ç ç¹ãå¯ä»¥è®¤ä¸ºä¸ä¸ªplaneå°±æ¯ä¸ä¸ªèå´çç¼ç ã
Plane
0ä¹å«åBMPï¼Basic Multilingual Planeï¼åºæ¬å¤æç§å¹³é¢ï¼ï¼åæ¾çä¸çä¸åç§è¯è¨ä¸æ è®°ä¸æ常ç¨çå符ã
Plane
1ä¹å«åSMPï¼Supplementary Multilingual
Planeï¼å¤æç§è¡¥å
å¹³é¢ï¼ï¼æ¾ç表æ
符å·ï¼emojiï¼ãåæ¯ä¸æ°å¦ç¬¦å·ãé³ä¹ç¬¦å·ã太çç»ï¼å¤ªæ符å·ï¼ãè£
饰符å·ãæå
çã麻å°ç¬¦å·ãç®å¤´æ©å±åä¸äºä¸çä¸åç§è¯è¨ä¸å¤ªå¸¸ç¨çæåççã
ããPlane 2ä¹å«åSIPï¼Supplementary Ideographic
Planeï¼è¡¨ææåè¡¥å
å¹³é¢ï¼ï¼ç¨äºåæ¾ç»ä¸æ±åï¼è§4.2ï¼çä¸äºç½ç¨åä¸æ±èè¯ç³»å
¶ä»è¯è¨çç¨åï¼å¦ç²¤è¯ç¨åï¼ã
ãã4.2.
ç»ä¸æ±åçåå¸
对äºç»ä¸æ±åï¼ä¸æ¥é©è¶ç»ä¸è¡¨ææåï¼CJKV Unified
Ideographsï¼æ¥è¯´ï¼BMPåæ¾çæåççæ¬ï¼ä¹æ¯æ常ç¨åï¼ä¸æ©å±Aåºçæ±åãæ©å±Båºå°å³å°å°æ¥çæ©å±Eåºé½æ¾å¨SIPä¸ã
å¨è¿äºåºä¸ï¼é¤äºç¬ç«åæºçåï¼è¿æåä¸ä¸ªåæºæé¨é¦ä¸åçåä½æåæ³ãæ¯å¦âæ·âç第ä¸ç¬ï¼ä¸å½å¤§éä¸é¦æ¸¯åä½âæ·âï¼å°æ¹¾åä½âæ·âï¼æ¥æ¬ååä½âæ¸âãè¿äºå·®å¼ä¹ä¼å¨Unicodeä¸ç¨ä¸ä¸ªä¸åçç¼ç å»è¡¨ç¤ºãæ以Båºå°Eåºæä¸å°æ¤ç§åä½ã
举äºBåºçä¾åãç½ç»ä¸ä¹åæµè¡çâä¸ä¼å夫ä¸è¦è¹æâ被åæââxxå·å«èªâï¼å
¶ä¸âxxâè¿ä¸ªåå°±æ¯å¨Båºãè粤è¯âx鸡âï¼é鸡ï¼ãâxå®æ¾âï¼åä¸ä¸ªäººåçå
³ç³»åå¼ä¹èå»ï¼ä¸¤ä¸ªè¯çé¦åä¹æ¯å¨Båºã
5.
utf8_unicode_ciåutf8mb4_unicode_ciçå¼å
è¿ä¸¤ç§collationsæ对åºçå符é½æ¯UTF-8ç¼ç çä¸ä¸ªåéãutf8_unicode_ciæå¤è½æ¾å°3个åèçUnicodeç¼ç ï¼èutf8mb4_unicode_ciåè½æ¾å°4个åèçç¼ç ãç±äºè°æ´åçUTF-8ç¼ç æ ¼å¼è§å®æå¤ä½¿ç¨4åèï¼åæ¥æ¯6åèï¼ç¼ç ï¼æ以utf8mb4ç³»åå¯ä»¥è¯´æ¯è¦çäºæ´ä¸ªUnicodeç¼ç ã
ç±äºutf8_unicode_ciæå¤è½æ¾å°3个åèçç¼ç ï¼æå³çå®åªæ¯æBMPä¸çå符ï¼å¯¹äºSMPä¸SIP以åå
¶ä»å¤´ä¸åèä¸ä¸º0x00ãéè¦4åèç¼ç çplanesæ¥è¯´ï¼utf8_unicode_ciè¿ç§collationæ¯æ æ³æ¯æãå½ä½¿ç¨4åèçå符ï¼å¦emojiä¸Båºä»¥åçç»ä¸æ±åï¼å¯¹ä½¿ç¨æ¤ç§collationçå段è¿è¡å¢å æ¥æ¹æ¶ï¼æ°æ®åºä¼æ¥ä¸ä¸ªéæ³å符çå¼å¸¸ãèutf8mb4å没ææ¤é®é¢ãç±æ¤ä¹çåºï¼utf8mb4_unicode_ciæ¯utf8_unicode_ciçè¶
éã
6.
utf8mb4_unicode_ciçä¼ç¼ºç¹
utf8mb4ç³»åçCollationå¨MySQL
5.5以ä¸å¼å§æ¯æãç¸æ¯èµ·utf8_unicode_ciï¼å®æå¦ä¸çç¹æ§ï¼
1)
å¨æ°æ®è¡¨ä¸ï¼å¯¹äºBMPä¸çå符ï¼æå¤ä½¿ç¨3åèçå符ï¼æ常ç¨çå符ï¼ï¼ä¸¤ç§collationså
·æå®å
¨ç¸åçåå¨ç¹æ§ï¼ç¸åçç å¼ï¼ç¸åçç¼ç æ¹å¼ï¼ç¸åçåå¨é¿åº¦ãä¸ä¼å¢å ä»»ä½çåå¨å¼éã
2)
å¨æ°æ®è¡¨ä¸ï¼å¯¹äºå
¶ä»plainsçå符ï¼utf8ç³»åçcollationæ ¹æ¬ä¸è½åå¨ï¼èutf8mb4ç³»åçcollationsåå¯ä»¥åå¨ã
3)
å¨æ°æ®è¡¨ä¸ï¼å¯¹äºåé¿çå段ï¼å¦VARCHAR2ï¼TEXTï¼ï¼utf8mb4æ大å¯åå¨çå符å¯è½å°äºutf8ç³»åçcollationã
4)
å¨ç´¢å¼ä¸ï¼å¯¹äºææ¬ç±»åçå段ï¼utf8mb4å¯ç´¢å¼çå符å°äºutf8ç³»åçcollationsãå¦InnoDBçç´¢å¼æå¤ä½¿ç¨767åèãå¦æ使ç¨utf8mb4ï¼æ¯ä¸ä¸ªå符é½ä¼é¢ç4åèåç´¢å¼ï¼èutf8åé¢ç3åèãæ
æ¤åè
æ¯191个å符ï¼åè
æ¯255个å符ã
5)
ç±äº4)çåå ï¼å ä¸å符é大ï¼utf8mb4çæ§è½å¯è½æ¯utf8ç³»åçcollationsä½ã
6)
è¥å级åçå段åäºç´¢å¼ï¼éè¦æç´¢å¼å符éå¶å¨191å符æ以å
ã
7. å½åç³»ç»ç¨åªä¸ªå¥½
å¨å½åçç³»ç»ï¼å
¨é¨é½ä½¿ç¨utf8_unicode_ciè¿ç§collationãä½æ¯å¨åå¨ç½é¡µæ é¢æ¶ï¼æ é¢å¸¦æSMPæè
SIPçå符ï¼å¦emojiã粤è¯åï¼ä¼å¼åæ°æ®åºåå
¥å¼å¸¸ãäºæ¯ï¼å°±æ两ç§è§£å³æ¹åï¼
1)
ææã
1.1) ææææªæå¼åå¼å¸¸çåãéåæ¤ç§æ¹æ³ï¼éè¦å¯¹æ¯ä¸ä¸ªæ é¢è¿è¡æ«æã
1.2)
æææ´æ¡è®°å½ãå¯ä»¥éåæ«ææ³ï¼æè
ææå¼åå¼å¸¸çè®°å½ã
2) å级å°utf8mb4ãä¼ç¥ä¸ºéä½æ°æ®åºæ§è½ã
7.1.
æ§è½èè
é¦å
对äºåå
¥æ§è½ï¼æ¥æ¾åä½çæ§è½æèç±äºå¨åå
¥åå符é½å·²ç»åæç¼ç ï¼åºæ¬å¯ä»¥å¿½ç¥ã对äºç½ç»ä¼ è¾çæ§è½ï¼åéè¦ç»§ç»æ¥æ¾ç¸å
³èµæ继ç»æ¥è¯ãä½åæ¥ä¼°è®¡ç±äºç®åæ°æ®åºå¨æ¬å°ï¼æ
æ¤è¿é¨åå¼éçå¢é¿ä¸å¤ªææ¾ã
è对äºç´¢å¼çæ§è½ï¼ç±äºç½é¡µæ é¢è¿ä¸å段没æåç´¢å¼ï¼å¨å¯é¢è§çå°æ¥ä¹æªææ¤è®¡åï¼æ
æ¤æ²¡ææ§è½çæèï¼ä¹æ²¡æå级å
¼å®¹æ§çæ
å¿ã
åµä¸ï¼åè¥èµ°æææ°æ®çæ¹åï¼è¥éåæ«ææ³ï¼åéè¦ä»åºæ«æçå¼éãè¥éåææè®°å½æ³ï¼åä¼å
触åäºå¡åæ»ï¼å
¶ä»è®°å½éè¦ä¸æ¬¡éæ°åå
¥ãèä¸å½ä¸æ¹è®°å½åå
¥æ¶æk个记å½å¼åå¼å¸¸ï¼åéè¦åæ»ä¸éè¯k次ï¼é¤é使ç¨æ«ææ³é¢å
æ«æåºè¿äºå¼å¸¸çè®°å½ãä½è¿ä¹ä¼å¼å
¥é¢å¤çç¨åºä¸æ°æ®åºå¼éãè¥ä¸ä½¿ç¨äºå¡ï¼åæ°æ®åºæ»ä½åå
¥æ§è½ä¼å¤§ä¸ºéä½ã
è½ç¶æ²¡æå®æµè¿ï¼ä½ä»æè§ä¸æ¥å®æ§å¤æï¼ä¼¼ä¹ææè®°å½æ¯å级collation带æ¥çæ§è½éåè¦å¤§ã
7.2.
åå¨ç©ºé´èè
å½åçç½é¡µæ é¢æ¯ä½¿ç¨VARCHAR2åå¨ã对äºç°å¨å¯ç¨çã常è§çBMPå符ï¼ä¸ä¼å¼å
¥é¢å¤çåå¨å¼éãBMPå符å¨VARCHARçç±»åä¸ä¸ä¼ä¸ºæ¯ä¸å符å¼å
¥é¢å¤33%ç空é´å¼éãåä¹ï¼å®é¿çCHARå°±ä¼å¼å
¥è¿ç§é¢å¤å¼éã
7.3.
ç®æ æ°æ®èè
ç½é¡µæ é¢ä½ä¸ºä»¥åç¹å¾åæçæ°æ®æºãå¨åæéæ±å®å
¨æ²¡æç¡®å®çæ
åµä¸ï¼æ认为ææä»»ä½æ°æ®é½æ¯ä¸å®éåçåæ³ï¼ç¹å«æ¯æ´æ¡è®°å½æææ´æ¯ä¸æ¨èãå 为ç°é¶æ®µæ们没æä¸å¥æ åå»å¤å®ä½ä¸ºæææ°æ®ãä½ä¸ºæ ææ°æ®ãæå¯è½å¼åå¼å¸¸çé£é¨åæ°æ®ç¡®å®æ¯æ²¡ç¨çæ°æ®ï¼ä¹æå¯è½é£é¨å人群æ´å¾åäºå¨æ们平å°ä¸æ´»è·ä½¿ç¨ãæ¢ç¶åç§å¯è½æ§é½åå¨ï¼æ们主å¨æ¾å¼ä¸é¨åå¯è½æ§ï¼ä¼¼ä¹ä¸å¤ªæ°å½ã
7.4.
API设计ä¸å
¼å®¹æ§èè
ç±äºutf8_unicode_ciä¸utf8mb4_unicode_cié½æ¯ä½¿ç¨UTF-8ç¼ç ï¼æ以对äºJAVAï¼ä½¿ç¨MyBatisçæç代ç æ¯ä¸æ ·çï¼é½æ¯ä½¿ç¨Stringç±»åãè¿ç¹å·²ç»å®æµè¿ãå ä¸è¿ä¸¤ç§collationså¨BMPä¸çç¼ç å®å
¨ä¸è´ï¼æ以使ç¨3åèä¸4åèçç³»ç»ï¼å¯¹äºBMPä¸çå符é½æ¯å®å
¨å
¼å®¹ãæ£å¸¸æ¾ç¤ºçãè对äº3åèçç³»ç»ï¼4åèçå符ä¸è¬ä¼æ¾ç¤ºæä¸ä¸ªæ¹æ¡ï¼æè
å¨ä¸ä¸ªæ¹æ¡ä¸æå 个å°æ°åï¼ä¸ä¼å¼åç³»ç»å¼å¸¸ã
8.
æ»ç»
è¯ç¶ï¼emoji对åè¯åæç®åæ¥è¯´è¿æ²¡æä»ä¹ææï¼ç²¤è¯è¯èä¸å¨SIPä¸ä¹åªæ¯å
¶ä¸ä¸é¨åï¼ä¹ä¸ç¥éæå¤å°æ¥æ¬å¨æ¼«æè
ç±æ
å¨ä½ççç½é¡µä¼éå°è¿äºçå»åï¼é³ä¹ç¬¦å·ä¹å°äººç¨ï¼å¤ªæ符å·ä¹ä¸æ¯æ¯æ¬¡é½åºç°ï¼ä¸äºæ°å¦å¢è¡¥çå符ä¸ç®å¤´å¢è¡¥å¾æ¡ä¹ä¸æ¯æ¯ä¸ªäººé½ä¼ç¨ãè¿äºå èµ·æ¥å¯è½ä¸ç¥å¤ä¸å¤å
¨é¨çååä¹ä¸ã
ä½æ¯åè¥æ¯ä¸ä¸¤ä¸ªå°æ¶å°±ä¼ç±äºå符ä¸è½åå
¥ï¼å¼åæ°æ®åºçå¼å¸¸ãéè¿ä¸é¢çåæï¼æ认为å¢å è¿ç§å
¼å®¹æ§å¸¦æ¥çææ¬æ¯å¯ä»¥æ¥åçã
æ
æ¤ï¼æ建议使ç¨å级çæ¹æ³ï¼å
¼å®¹ææUnicodeå符ã
ãã转载
温馨提示:答案为网友推荐,仅供参考