Chapter聽10.聽Character Set Support - MySQL 5.1参考手册中文版
鐩綍
鏈珷璁ㄨ浠ヤ笅涓婚锛
路 浠涔堟槸瀛楃闆嗗拰鏍″瑙勫垯锛
路 澶氱骇榛樿绯荤粺
路 瀛楃闆嗚娉
路 鐩稿叧鍑芥暟鍜岃繍绠
路 Unicode鏀寔
路 姣忎釜瀛楃闆嗗拰鏍″瑙勫垯鐨勫惈涔
MySQL5.1涓殑瀛楃闆嗘敮鎸佸寘鎷湪MyISAM銆MEMORY鍜InnoDB瀛樺偍寮曟搸涓
瀛楃闆鏄竴濂楃鍙峰拰缂栫爜銆鏍″瑙勫垯鏄湪瀛楃闆嗗唴鐢ㄤ簬姣旇緝瀛楃鐨勪竴濂楄鍒欍傝鎴戜滑浣跨敤涓涓亣鎯冲瓧绗﹂泦鐨勪緥瀛愭潵鍖哄埆娓呮銆
鍋囪鎴戜滑鏈変竴涓瓧姣嶈〃浣跨敤浜嗗洓涓瓧姣嶏細鈥A鈥欍佲B鈥欍佲a鈥欍佲b鈥欍傛垜浠负姣忎釜瀛楁瘝璧嬩簣涓涓暟鍊硷細鈥A鈥=0锛屸B鈥= 1锛屸a鈥= 2锛屸b鈥= 3銆傚瓧姣嶁A鈥欐槸涓涓鍙凤紝鏁板瓧0鏄A鈥欑殑缂栫爜锛岃繖鍥涗釜瀛楁瘝鍜屽畠浠殑缂栫爜缁勫悎鍦ㄤ竴璧锋槸涓涓瀛楃闆銆
鍋囪鎴戜滑甯屾湜姣旇緝涓や釜瀛楃涓茬殑鍊硷細鈥A鈥欏拰鈥B鈥欍傛瘮杈冪殑鏈绠鍗曠殑鏂规硶鏄煡鎵剧紪鐮侊細鈥A鈥欎负0锛屸B鈥欎负1銆傚洜涓0 灏忎簬1锛屾垜浠彲浠ヨ鈥A鈥欏皬浜庘B鈥欍傛垜浠仛鐨勪粎浠呮槸鍦ㄦ垜浠殑瀛楃闆嗕笂搴旂敤浜嗕竴涓 鏍″瑙勫垯銆傛牎瀵硅鍒欐槸涓濂楄鍒欙紙鍦ㄨ繖绉嶆儏鍐典笅浠呬粎鏄竴濂楄鍒欙級锛氣滃缂栫爜杩涜姣旇緝銆鈥濇垜浠О杩欑鍏ㄩ儴鍙兘鐨勮鍒欎腑鐨勬渶绠鍗曠殑 鏍″瑙勫垯涓轰竴涓binary锛堜簩鍏冿級鏍″瑙勫垯銆
浣嗘槸锛屽鏋滄垜浠笇鏈涘皬鍐欏瓧姣嶅拰澶у啓瀛楁瘝鏄瓑浠风殑锛屽簲璇ユ庢牱锛熼偅涔堬紝鎴戜滑灏嗚嚦灏戞湁涓や釜瑙勫垯锛氾紙1锛夋妸灏忓啓瀛楁瘝鈥a鈥欏拰鈥b鈥欒涓轰笌鈥A鈥欏拰鈥B鈥欑瓑浠凤紱锛2锛夌劧鍚庢瘮杈冪紪鐮併傛垜浠О杩欐槸涓涓ぇ灏忓啓涓嶆晱鎰熺殑 鏍″瑙勫垯銆傛瘮浜屽厓鏍″瑙勫垯澶嶆潅涓浜涖
鍦ㄥ疄闄呯敓娲讳腑锛屽ぇ澶氭暟瀛楃闆嗘湁璁稿瀛楃锛氫笉浠呬粎鏄A鈥欏拰鈥B鈥欙紝鑰屾槸鏁翠釜瀛楁瘝琛紝鏈夋椂鍊欐湁璁稿绉嶅瓧姣嶈〃锛屾垨鑰呬竴涓笢鏂圭殑浣跨敤涓婂崈涓瓧绗︾殑涔﹀啓绯荤粺锛岃繕鏈夎澶氱壒娈婄鍙峰拰鏍囩偣绗﹀彿銆傚苟涓斿湪瀹為檯鐢熸椿涓紝澶у鏁 鏍″瑙勫垯鏈夎澶氫釜瑙勫垯锛氫笉浠呬粎鏄ぇ灏忓啓涓嶆晱鎰燂紝杩樺寘鎷噸闊崇涓嶆晱鎰燂紙鈥閲嶉煶绗鈥 鏄檮灞炰簬涓涓瓧姣嶇殑绗﹀彿锛岃薄寰疯鐨勨脰鈥欑鍙凤級鍜屽瀛楄妭鏄犲皠锛堜緥濡傦紝浣滀负瑙勫垯鈥脰鈥=鈥OE鈥欏氨鏄袱涓痉璇 鏍″瑙勫垯鐨勪竴绉嶏級銆
MySQL5.1鑳藉鍋氳繖浜涗簨鎯咃細
路 浣跨敤澶氱瀛楃闆嗘潵瀛樺偍瀛楃涓
路 浣跨敤澶氱鏍″瑙勫垯鏉ユ瘮杈冨瓧绗︿覆
路 鍦ㄥ悓涓鍙版湇鍔″櫒銆佸悓涓涓暟鎹簱鎴栫敋鑷冲湪鍚屼竴涓〃涓娇鐢ㄤ笉鍚屽瓧绗﹂泦鎴栨牎瀵硅鍒欐潵娣峰悎瀛楃涓
路 鍏佽瀹氫箟浠讳綍绾у埆鐨勫瓧绗﹂泦鍜屾牎瀵硅鍒
鍦ㄨ繖浜涙柟闈紝MySQL5.1涓嶄粎姣MySQL4.1浠ュ墠鐨勭増鏈伒娲诲緱澶氾紝鑰屼笖姣斿叾瀹冨ぇ澶氭暟鏁版嵁搴撶鐞嗙郴缁熻秴鍓嶈澶氥備絾鏄紝涓轰簡鏈夋晥鍦颁娇鐢ㄨ繖浜涘姛鑳斤紝浣犻渶瑕佷簡瑙ｅ摢浜涘瓧绗﹂泦鍜 鏍″瑙勫垯鏄彲鐢ㄧ殑锛屾庢牱鏀瑰彉榛樿鍊硷紝浠ュ強瀹冧滑鎬庢牱褰卞搷瀛楃鎿嶄綔绗﹀拰瀛楃涓插嚱鏁扮殑琛屼负銆
MySQL鏈嶅姟鍣ㄨ兘澶熸敮鎸佸绉嶅瓧绗﹂泦銆傚彲浠ヤ娇鐢SHOW CHARACTER SET璇彞鍒楀嚭鍙敤鐨勫瓧绗﹂泦锛
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 |
...
锛堝畬鏁村垪琛ㄥ弬瑙10.10鑺傦紝鈥淢ySQL鏀寔鐨勫瓧绗﹂泦鍜屾牎瀵光銆傦級
浠讳綍涓涓粰瀹氱殑瀛楃闆嗚嚦灏戞湁涓涓牎瀵硅鍒欍傚畠鍙兘鏈夊嚑涓牎瀵硅鍒欍
瑕佹兂鍒楀嚭涓涓瓧绗﹂泦鐨勬牎瀵硅鍒欙紝浣跨敤SHOW COLLATION璇彞銆備緥濡傦紝瑕佹兂鏌ョ湅latin1锛堚瑗挎ISO-8859-1鈥锛夊瓧绗﹂泦鐨 鏍″瑙勫垯锛屼娇鐢ㄤ笅闈㈢殑璇彞鏌ユ壘閭ｄ簺鍚嶅瓧浠latin1寮澶寸殑 鏍″瑙勫垯锛
mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| latin1_danish_ci | latin1 | 15 | | | 0 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 1 |
| latin1_general_ci | latin1 | 48 | | | 0 |
| latin1_general_cs | latin1 | 49 | | | 0 |
| latin1_spanish_ci | latin1 | 94 | | | 0 |
+---------------------+---------+----+---------+----------+---------+
latin1鏍″瑙勫垯鏈変笅闈㈢殑鍚箟锛
|
鏍″瑙勫垯 |
鍚箟 |
|
latin1_german1_ci |
寰峰浗DIN-1 |
|
latin1_swedish_ci |
鐟炲吀/鑺叞 |
|
latin1_danish_ci |
涓归害/鎸▉ |
|
latin1_german2_ci |
寰峰浗 DIN-2 |
|
latin1_bin |
绗﹀悎latin1缂栫爜鐨勪簩杩涘埗 |
|
latin1_general_ci |
澶氱璇█(瑗挎) |
|
latin1_general_cs |
澶氱璇█(瑗挎ISO),澶у皬鍐欐晱鎰 |
|
latin1_spanish_ci |
鐜颁唬瑗跨彮鐗 |
鏍″瑙勫垯涓鑸湁杩欎簺鐗瑰緛锛
路 涓や釜涓嶅悓鐨勫瓧绗﹂泦涓嶈兘鏈夌浉鍚岀殑鏍″瑙勫垯銆
路 姣忎釜瀛楃闆嗘湁涓涓榛樿鏍″瑙勫垯銆備緥濡傦紝latin1榛樿鏍″瑙勫垯鏄latin1_swedish_ci銆
路 瀛樺湪鏍″瑙勫垯鍛藉悕绾﹀畾锛氬畠浠互鍏剁浉鍏崇殑瀛楃闆嗗悕寮濮嬶紝閫氬父鍖呮嫭涓涓瑷鍚嶏紝骞朵笖浠_ci锛堝ぇ灏忓啓涓嶆晱鎰燂級銆_cs锛堝ぇ灏忓啓鏁忔劅锛夋垨_bin锛堜簩鍏冿級缁撴潫銆
MySQL鎸夌収濡備笅鏂规硶纭畾鏈嶅姟鍣ㄥ瓧绗﹂泦鍜屾湇鍔″櫒鏍″瑙勫垯锛
路 褰撴湇鍔″櫒鍚姩鏃舵牴鎹湁鏁堢殑閫夐」璁剧疆
路 鏍规嵁杩愯鏃剁殑璁惧畾鍊
鍦ㄦ湇鍔″櫒绾у埆锛岀‘瀹氭柟娉曞緢绠鍗曘傚綋鍚姩mysqld鏃讹紝鏍规嵁浣跨敤鐨勫垵濮嬮夐」璁剧疆鏉ョ‘瀹氭湇鍔″櫒瀛楃闆嗗拰 鏍″瑙勫垯銆傚彲浠ヤ娇鐢--default-character-set璁剧疆瀛楃闆嗭紝骞朵笖鍙互鍦ㄥ瓧绗﹂泦鍚庨潰涓 鏍″瑙勫垯娣诲姞--default-collation銆傚鏋滄病鏈夋寚瀹氫竴涓瓧绗﹂泦锛岄偅灏变笌--default-character-set=latin1鐩稿悓銆濡傛灉浣犱粎鎸囧畾浜嗕竴涓瓧绗﹂泦锛堜緥濡傦紝latin1锛夛紝浣嗘槸娌℃湁鎸囧畾涓涓 鏍″瑙勫垯锛岄偅灏变笌--default-charset=latin1 --default-collation=latin1_swedish_ci鐩稿悓锛鍥犱负latin1_swedish_ci鏄latin1鐨榛樿鏍″瑙勫垯銆傚洜姝わ紝浠ヤ笅涓変釜鍛戒护鏈夌浉鍚岀殑鏁堟灉锛
shell> mysqld
shell> mysqld --default-character-set=latin1
shell> mysqld --default-character-set=latin1 \
--default-collation=latin1_swedish_ci
鏇存敼璁惧畾鍊肩殑涓涓柟娉曟槸閫氳繃閲嶆柊缂栬瘧銆傚鏋滃笇鏈涘湪浠庢簮绋嬪簭鏋勫缓鏃舵洿鏀归粯璁ゆ湇鍔″櫒瀛楃闆嗗拰鏍″瑙勫垯锛屼娇鐢細--with-charset鍜--with-collation浣滀负configure鐨勫弬閲忋備緥濡傦細
shell> ./configure --with-charset=latin1
鎴栬咃細
shell> ./configure --with-charset=latin1 \
--with-collation=latin1_german1_ci
mysqld鍜configure閮介獙璇佸瓧绗﹂泦/鏍″瑙勫垯缁勫悎鏄惁鏈夋晥銆傚鏋滄棤鏁堬紝姣忎釜绋嬪簭閮芥樉绀轰竴涓敊璇俊鎭紝鐒跺悗缁堟銆
褰撳墠鐨勬湇鍔″櫒瀛楃闆嗗拰鏍″瑙勫垯鍙互鐢ㄤ綔character_set_server鍜collation_server绯荤粺鍙橀噺鐨勫笺傚湪杩愯鏃惰兘澶熸敼鍙樿繖浜涘彉閲忕殑鍊笺
10.3.2. 鏁版嵁搴撳瓧绗﹂泦鍜屾牎瀵
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
渚嬪锛
CREATE DATABASE db_name
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL杩欐牱閫夋嫨鏁版嵁搴撳瓧绗﹂泦鍜屾暟鎹簱鏍″瑙勫垯锛
路 濡傛灉鎸囧畾浜CHARACTER SET X鍜COLLATE Y锛岄偅涔堥噰鐢ㄥ瓧绗﹂泦X鍜屾牎瀵硅鍒Y銆
路 濡傛灉鎸囧畾浜CHARACTER SET X鑰屾病鏈夋寚瀹COLLATE Y锛岄偅涔堥噰鐢CHARACTER SET X鍜CHARACTER SET X鐨勯粯璁ゆ牎瀵硅鍒欍
路 鍚﹀垯锛岄噰鐢ㄦ湇鍔″櫒瀛楃闆嗗拰鏈嶅姟鍣ㄦ牎瀵硅鍒欍
MySQL鐨CREATE DATABASE ... DEFAULT CHARACTER SET ...璇硶涓庢爣鍑SQL鐨CREATE SCHEMA ... CHARACTER SET ...璇硶绫讳技銆傚洜姝わ紝鍙互鍦ㄥ悓涓涓MySQL鏈嶅姟鍣ㄤ笂鍒涘缓浣跨敤涓嶅悓瀛楃闆嗗拰 鏍″瑙勫垯鐨勬暟鎹簱銆
濡傛灉鍦CREATE TABLE璇彞涓病鏈夋寚瀹氳〃瀛楃闆嗗拰鏍″瑙勫垯锛屽垯浣跨敤鏁版嵁搴撳瓧绗﹂泦鍜屾牎瀵硅鍒欎綔涓洪粯璁ゅ笺傚畠浠病鏈夊叾瀹冪洰鐨勩
榛樿鏁版嵁搴撶殑瀛楃闆嗗拰鏍″瑙勫垯鍙互鐢ㄤ綔character_set_database鍜 collation_database绯荤粺鍙橀噺銆傛棤璁轰綍鏃堕粯璁ゆ暟鎹簱鏇存敼浜嗭紝鏈嶅姟鍣ㄩ兘璁剧疆杩欎袱涓彉閲忕殑鍊笺傚鏋滄病鏈 榛樿鏁版嵁搴擄紝杩欎袱涓彉閲忎笌鐩稿簲鐨勬湇鍔″櫒绾у埆鐨勫彉閲忥紙character_set_server鍜collation_server锛夊叿鏈夌浉鍚岀殑鍊笺
10.3.3. 琛ㄥ瓧绗﹂泦鍜屾牎瀵
CREATE TABLE tbl_name (column_list)
[DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]
ALTER TABLE tbl_name
[DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]
渚嬪锛
CREATE TABLE t1 ( ... )
DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
MySQL鎸夌収涓嬮潰鐨勬柟寮忛夋嫨琛ㄥ瓧绗﹂泦鍜 鏍″瑙勫垯锛
路 濡傛灉鎸囧畾浜CHARACTER SET X鍜COLLATE Y锛岄偅涔堥噰鐢CHARACTER SET X鍜COLLATE Y銆
路 濡傛灉鎸囧畾浜CHARACTER SET X鑰屾病鏈夋寚瀹COLLATE Y锛岄偅涔堥噰鐢CHARACTER SET X鍜CHARACTER SET X鐨勯粯璁ゆ牎瀵硅鍒欍
路 鍚﹀垯锛岄噰鐢ㄦ湇鍔″櫒瀛楃闆嗗拰鏈嶅姟鍣ㄦ牎瀵硅鍒欍
濡傛灉鍦ㄥ垪瀹氫箟涓病鏈夋寚瀹氬垪瀛楃闆嗗拰鏍″瑙勫垯锛屽垯榛樿浣跨敤琛ㄥ瓧绗﹂泦鍜屾牎瀵硅鍒欍傝〃瀛楃闆嗗拰鏍″瑙勫垯鏄MySQL鐨勬墿灞;鍦ㄦ爣鍑SQL涓病鏈夈
10.3.4. 鍒楀瓧绗﹂泦鍜屾牎瀵
col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name [COLLATE collation_name]]
渚嬪锛
CREATE TABLE Table1
(
column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);
MySQL鎸夌収涓嬮潰鐨勬柟寮忛夋嫨鍒楀瓧绗﹂泦鍜屾牎瀵硅鍒欙細
路 濡傛灉鎸囧畾浜CHARACTER SET X鍜COLLATE Y锛岄偅涔堥噰鐢CHARACTER SET X鍜COLLATE Y銆
路 濡傛灉鎸囧畾浜CHARACTER SET X鑰屾病鏈夋寚瀹COLLATE Y锛岄偅涔堥噰鐢CHARACTER SET X鍜CHARACTER SET X鐨勯粯璁ゆ牎瀵硅鍒欍
路 鍚﹀垯锛岄噰鐢ㄨ〃瀛楃闆嗗拰鏈嶅姟鍣ㄦ牎瀵硅鍒欍
CHARACTER SET鍜COLLATE瀛愬彞鏄爣鍑嗙殑SQL銆
10.3.5. 瀛楃闆嗗拰鏍″鍒嗛厤绀轰緥
浠ヤ笅渚嬪瓙鏄剧ず浜MySQL鎬庢牱纭畾榛樿瀛楃闆嗗拰鏍″瑙勫垯銆
绀轰緥1锛氳〃鍜屽垪瀹氫箟
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;
鍦ㄨ繖閲屾垜浠湁涓涓垪浣跨敤latin1瀛楃闆嗗拰latin1_german1_ci鏍″瑙勫垯銆傛槸鏄惧紡鐨勫畾涔夛紝鍥犳绠鍗曟槑浜嗐傞渶瑕佹敞鎰忕殑鏄紝鍦ㄤ竴涓latin2琛ㄤ腑瀛樺偍涓涓latin1鍒椾笉浼氬瓨鍦ㄩ棶棰樸
绀轰緥2锛氳〃鍜屽垪瀹氫箟
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
杩欐鎴戜滑鏈変竴涓垪浣跨敤latin1瀛楃闆嗗拰涓涓粯璁ゆ牎瀵硅鍒欍傚敖绠″畠鏄惧緱鑷劧锛岄粯璁ゆ牎瀵硅鍒欏嵈涓嶆槸琛ㄧ骇銆傜浉鍙嶏紝鍥犱负latin1鐨勯粯璁ゆ牎瀵硅鍒欐绘槸latin1_swedish_ci锛屽垪c1鏈変竴涓牎瀵硅鍒latin1_swedish_ci锛堣屼笉鏄latin1_danish_ci锛夈
绀轰緥3锛氳〃鍜屽垪瀹氫箟
CREATE TABLE t1
(
c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
鎴戜滑鏈変竴涓垪浣跨敤涓涓粯璁ゅ瓧绗﹂泦鍜屼竴涓粯璁ゆ牎瀵硅鍒欍傚湪杩欑鎯呭喌涓嬶紝MySQL鏌ユ壘琛ㄧ骇鍒潵纭畾鍒楀瓧绗﹂泦鍜 鏍″瑙勫垯銆傚洜姝わ紝鍒c1鐨勫瓧绗﹂泦鏄latin1锛屽畠鐨 鏍″瑙勫垯鏄latin1_danish_ci銆
绀轰緥4锛氭暟鎹簱銆佽〃鍜屽垪瀹氫箟
CREATE DATABASE d1
DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
c1 CHAR(10)
);
鎴戜滑鍒涘缓浜嗕竴涓病鏈夋寚瀹氬瓧绗﹂泦鍜屾牎瀵硅鍒欑殑鍒椼傛垜浠篃娌℃湁鎸囧畾琛ㄧ骇瀛楃闆嗗拰鏍″瑙勫垯銆傚湪杩欑鎯呭喌涓嬶紝MySQL鏌ユ壘鏁版嵁搴撶骇鐨勭浉鍏宠缃傦紙鏁版嵁搴撶殑璁剧疆鍙樹负琛ㄧ殑璁剧疆锛屽叾鍚庡彉涓哄垪鐨勮缃傦級鍥犳锛屽垪c1鐨勫瓧绗﹂泦涓烘槸latin2锛瀹冪殑 鏍″瑙勫垯鏄latin2_czech_ci銆
路 鏈嶅姟鍣ㄥ瓧绗﹂泦鍜屾牎瀵硅鍒欏彲浠ョ敤浣character_set_server鍜collation_server鍙橀噺鐨勫笺
路 榛樿鏁版嵁搴撶殑瀛楃闆嗗拰鏍″瑙勫垯鍙互鐢ㄤ綔character_set_database鍜collation_database鍙橀噺鐨勫笺
鍦ㄥ鎴风鍜屾湇鍔″櫒鐨勮繛鎺ュ鐞嗕腑涔熸秹鍙婁簡瀛楃闆嗗拰鏍″瑙勫垯鍙橀噺銆傛瘡涓涓鎴风鏈変竴涓繛鎺ョ浉鍏崇殑瀛楃闆嗗拰鏍″瑙勫垯鍙橀噺銆
鑰冭檻浠涔堟槸涓涓杩炴帴鈥濓細瀹冩槸杩炴帴鏈嶅姟鍣ㄦ椂鎵浣滅殑浜嬫儏銆傚鎴风鍙戦SQL璇彞锛屼緥濡傛煡璇紝閫氳繃杩炴帴鍙戦佸埌鏈嶅姟鍣ㄣ傛湇鍔″櫒閫氳繃杩炴帴鍙戦佸搷搴旂粰瀹㈡埛绔紝渚嬪缁撴灉闆嗐傚浜庡鎴风杩炴帴锛岃繖鏍蜂細瀵艰嚧涓浜涘叧浜庤繛鎺ョ殑瀛楃闆嗗拰 鏍″瑙勫垯鐨勯棶棰橈紝杩欎簺闂鍧囪兘澶熼氳繃绯荤粺鍙橀噺鏉ヨВ鍐筹細
路 褰撴煡璇㈢寮瀹㈡埛绔悗锛屽湪鏌ヨ涓娇鐢ㄥ摢绉嶅瓧绗﹂泦锛
鏈嶅姟鍣ㄤ娇鐢character_set_client鍙橀噺浣滀负瀹㈡埛绔彂閫佺殑鏌ヨ涓娇鐢ㄧ殑瀛楃闆嗐
路 鏈嶅姟鍣ㄦ帴鏀跺埌鏌ヨ鍚庡簲璇ヨ浆鎹负鍝瀛楃闆嗭紵
杞崲鏃讹紝鏈嶅姟鍣ㄤ娇鐢character_set_connection鍜collation_connection绯荤粺鍙橀噺銆傚畠灏嗗鎴风鍙戦佺殑鏌ヨ浠character_set_client绯荤粺鍙橀噺杞崲鍒character_set_connection锛堥櫎闈炲瓧绗︿覆鏂囧瓧鍏锋湁璞_latin1鎴_utf8鐨勫紩浠嬭瘝锛夈collation_connection瀵规瘮杈冩枃瀛楀瓧绗︿覆鏄噸瑕佺殑銆傚浜庡垪鍊肩殑瀛楃涓叉瘮杈冿紝瀹冧笉閲嶈锛屽洜涓哄垪鍏锋湁鏇撮珮鐨 鏍″瑙勫垯浼樺厛绾с
路 鏈嶅姟鍣ㄥ彂閫佺粨鏋滈泦鎴栬繑鍥為敊璇俊鎭埌瀹㈡埛绔箣鍓嶅簲璇ヨ浆鎹负鍝瀛楃闆嗭紵
character_set_results鍙橀噺鎸囩ず鏈嶅姟鍣ㄨ繑鍥炴煡璇㈢粨鏋滃埌瀹㈡埛绔娇鐢ㄧ殑瀛楃闆嗐傚寘鎷粨鏋滄暟鎹紝渚嬪鍒楀煎拰缁撴灉鍏冩暟鎹紙濡傚垪鍚嶏級銆
浣犺兘澶熻皟鏁磋繖浜涘彉閲忕殑璁剧疆锛屾垨鍙互渚濊禆榛樿鍊硷紙杩欐牱锛屼綘鍙互璺宠繃鏈珷锛夈
鏈変袱涓鍙ュ奖鍝嶈繛鎺ュ瓧绗﹂泦锛
SET NAMES 'charset_name'
SET CHARACTER SET charset_name
SET NAMES鏄剧ず瀹㈡埛绔彂閫佺殑SQL璇彞涓娇鐢ㄤ粈涔堝瓧绗﹂泦銆傚洜姝わ紝SET NAMES 'cp1251'璇彞鍛婅瘔鏈嶅姟鍣ㄢ灏嗘潵浠庤繖涓鎴风浼犳潵鐨勪俊鎭噰鐢ㄥ瓧绗﹂泦cp1251鈥銆傚畠杩樹负鏈嶅姟鍣ㄥ彂閫佸洖瀹㈡埛绔殑缁撴灉鎸囧畾浜嗗瓧绗﹂泦銆傦紙渚嬪锛屽鏋滀綘浣跨敤涓涓SELECT璇彞锛瀹冭〃绀哄垪鍊间娇鐢ㄤ簡浠涔堝瓧绗﹂泦銆傦級
SET NAMES 'x'璇彞涓庤繖涓変釜璇彞绛変环锛
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;
灏x璁剧疆涓character_set_connection涔熷氨璁剧疆浜collation_connection鏄x鐨勯粯璁ゆ牎瀵硅鍒欍
SET CHARACTER SET璇彞鏄被浼肩殑锛屼絾鏄负 榛樿鏁版嵁搴撹缃繛鎺ュ瓧绗﹂泦鍜屾牎瀵硅鍒欍SET CHARACTER SET x璇彞涓庤繖涓変釜璇彞绛変环锛
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
褰撲竴涓鎴风杩炴帴鏃讹紝瀹冨悜鏈嶅姟鍣ㄥ彂閫佸笇鏈涗娇鐢ㄧ殑瀛楃闆嗗悕绉般傛湇鍔″櫒涓洪偅涓瓧绗﹂泦璁剧疆character_set_client銆character_set_results鍜 character_set_connection鍙橀噺銆傦紙瀹為檯涓婏紝鏈嶅姟鍣ㄤ负浣跨敤璇ュ瓧绗﹂泦鎵ц涓涓SET NAMES鎿嶄綔銆傦級
瀵逛簬mysql瀹㈡埛绔紝濡傛灉浣犲笇鏈涗娇鐢ㄤ笌榛樿瀛楃闆嗕笉鍚岀殑瀛楃闆嗭紝涓嶉渶瑕佹瘡娆″惎鍔ㄦ椂鎵цSET NAMES璇彞銆傚彲浠ュ湪mysql璇彞琛屼腑鎴栬呴夐」鏂囦欢涓坊鍔犱竴涓--default-character-set閫夐」璁剧疆銆備緥濡傦紝浣犳瘡娆¤繍琛mysql鏃讹紝浠ヤ笅鐨勯夐」鏂囦欢璁剧疆鎶婁笁涓瓧绗﹂泦鍙橀噺淇敼涓koi8r锛
[mysql]
default-character-set=koi8r
渚嬪锛氬亣璁column1瀹氫箟涓CHAR(5) CHARACTER SET latin2銆傚鏋滄病鏈夎瀹SET NAMES鎴SET CHARACTER SET锛岄偅涔堝浜SELECT column1 FROM t锛屽綋杩炴帴鍚庯紝鏈嶅姟鍣ㄤ娇鐢ㄥ鎴风鎸囧畾鐨勫瓧绗﹂泦杩斿洖鍒column1鐨勬墍鏈夊笺傚彟涓鏂归潰锛屽鏋滀綘璁惧畾SET NAMES 'latin1'鎴SET CHARACTER SET latin1锛岄偅涔堝彂閫佺粨鏋滀箣鍓嶏紝鏈嶅姟鍣ㄨ浆鎹latin2鍊煎埌latin1銆傝浆鎹㈠彲鑳戒細涓㈠け閭ｄ簺涓嶅睘浜庝袱绉嶅瓧绗﹂泦鐨勫瓧绗︺
濡傛灉涓嶅笇鏈涙湇鍔″櫒鎵ц浠讳綍杞崲锛岃缃character_set_results涓NULL锛
mysql> SET character_set_results = NULL;
10.3.7. 瀛楃涓叉枃瀛楀瓧绗﹂泦鍜屾牎瀵
涓涓瓧绗︿覆鏂囧瓧鍙兘鏈変竴涓彲閫夌殑瀛楃闆嗗紩浠嬭瘝鍜COLLATE瀛愬彞锛
[_charset_name]'string' [COLLATE collation_name]
渚嬪锛
SELECT 'string';
SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;
瀵逛簬绠鍗曠殑璇彞SELECT 'string'锛屽瓧绗︿覆浣跨敤鐢character_set_connection鍜collation_connection绯荤粺鍙橀噺瀹氫箟鐨勫瓧绗﹂泦鍜 鏍″瑙勫垯銆
_charset_name琛ㄨ揪寮忔寮忕О鍋氫竴涓寮曚粙璇銆傚畠鍛婅瘔瑙ｆ瀽绋嬪簭锛屸滃悗闈㈠皢瑕佸嚭鐜扮殑瀛楃涓蹭娇鐢ㄥ瓧绗﹂泦X銆鈥濆洜涓轰互鍓嶄汉浠姝ゆ劅鍒板洶鎯戯紝鎴戜滑寮鸿皟寮曚粙璇嶄笉瀵艰嚧浠讳綍杞崲; 瀹冧粎鏄竴涓鍙凤紝涓嶆敼鍙樺瓧绗︿覆鐨勫笺傚紩浠嬭瘝鍦ㄦ爣鍑嗗崄鍏繘鍒跺瓧姣嶅拰鏁板瓧鍗佸叚杩涘埗绗﹀彿锛x'literal'鍜 0xnnnn锛変腑鏄悎娉曠殑锛屼互鍙锛锛堝綋鍦ㄤ竴涓紪绋嬭瑷鎺ュ彛涓娇鐢ㄩ澶勭悊鐨勮鍙ユ椂杩涜鍙傛暟鏇挎崲锛夈
渚嬪锛
SELECT _latin1 x'AABBCC';
SELECT _latin1 0xAABBCC;
SELECT _latin1 ?;
MySQL杩欐牱纭畾涓涓枃瀛楀瓧绗﹂泦鍜屾牎瀵硅鍒欙細
路 濡傛灉鎸囧畾浜CHARACTER SET X鍜COLLATE Y锛岄偅涔堜娇鐢CHARACTER SET X鍜COLLATE Y銆
路 濡傛灉鎸囧畾浜CHARACTER SET X鑰屾病鏈夋寚瀹COLLATE Y锛岄偅涔堜娇鐢CHARACTER SET X鍜CHARACTER SET X鐨勯粯璁ゆ牎瀵硅鍒欍
路 鍚﹀垯锛屼娇鐢ㄩ氳繃character_set_connection 鍜 collation_connection绯荤粺鍙橀噺缁欏嚭鐨勫瓧绗﹂泦鍜 鏍″瑙勫垯銆
渚嬪锛
路 浣跨敤latin1瀛楃闆嗗拰latin1_german1_ci鏍″瑙勫垯鐨勫瓧绗︿覆锛
路 SELECT _latin1'M眉ller' COLLATE latin1_german1_ci;
路 浣跨敤latin1瀛楃闆嗗拰鍏堕粯璁ゆ牎瀵硅鍒欑殑瀛楃涓诧紙鍗筹紝latin1_swedish_ci锛夛細
路 SELECT _latin1'M眉ller';
路 浣跨敤杩炴帴榛樿瀛楃闆嗗拰鏍″瑙勫垯鐨勫瓧绗︿覆锛
路 SELECT 'M眉ller';
瀛楃闆嗗紩浠嬭瘝鍜COLLATE瀛愬彞鏄牴鎹爣鍑SQL瑙勮寖瀹炵幇鐨勩
10.3.8. 鍦⊿QL璇彞涓娇鐢–OLLATE
- 浣跨敤COLLATE瀛愬彞锛岃兘澶熶负涓涓瘮杈冭鐩栦换浣曢粯璁ゆ牎瀵硅鍒欍COLLATE鍙互鐢ㄤ簬澶氱SQL璇彞涓備笅闈㈡槸涓浜涗緥瀛愶細
路 浣跨敤ORDER BY锛
路 SELECT k
路 FROM t1
路 ORDER BY k COLLATE latin1_german2_ci;
路 浣跨敤AS锛
路 SELECT k COLLATE latin1_german2_ci AS k1
路 FROM t1
路 ORDER BY k1;
路 浣跨敤GROUP BY锛
路 SELECT k
路 FROM t1
路 GROUP BY k COLLATE latin1_german2_ci;
路 浣跨敤鑱氬悎鍑芥暟锛
路 SELECT MAX(k COLLATE latin1_german2_ci)
路 FROM t1;
路 浣跨敤DISTINCT锛
路 SELECT DISTINCT k COLLATE latin1_german2_ci
路 FROM t1;
路 浣跨敤WHERE锛
路 SELECT *
路 FROM t1
路 WHERE _latin1 'M眉ller' COLLATE latin1_german2_ci = k;
路 SELECT *
路 FROM t1
路 WHERE k LIKE _latin1 'M眉ller' COLLATE latin1_german2_ci;
路 浣跨敤HAVING锛
路 SELECT k
路 FROM t1
路 GROUP BY k
路 HAVING k = _latin1 'M眉ller' COLLATE latin1_german2_ci;
COLLATE瀛愬彞鏈夎緝楂樼殑浼樺厛绾э紙楂樹簬||锛夛紝鍥犳涓嬮潰涓や釜琛ㄨ揪寮忔槸绛変环鐨勶細
x || y COLLATE z
x || (y COLLATE z)
SELECT * FROM t1 ORDER BY BINARY a;
SELECT * FROM t1 ORDER BY a COLLATE latin1_bin;
10.3.11. 鏍″纭畾杈冧负澶嶆潅鐨勪竴浜涚壒娈婃儏鍐
鍦ㄧ粷澶у鏁版煡璇腑锛MySQL浣跨敤鍝鏍″瑙勫垯杩涜姣旇緝鏄緢鏄剧劧鐨勩備緥濡傦紝鍦ㄤ笅鍒楁儏鍐典腑锛屾牎瀵硅鍒欐槑鏄剧殑鏄鍒x鐨勫垪鏍″瑙勫垯鈥濓細
SELECT x FROM T ORDER BY x;
SELECT x FROM T WHERE x = x;
SELECT DISTINCT x FROM T;
浣嗘槸锛屽綋娑夊強澶氫釜鎿嶄綔鏁版椂锛屽彲鑳戒笉鏄庣‘銆備緥濡傦細
SELECT x FROM T WHERE x = 'Y';
杩欎釜鏌ヨ搴旇浣跨敤鍒x鐨 鏍″瑙勫垯锛岃繕鏄瓧绗︿覆鏂囧瓧'Y'鐨 鏍″瑙勫垯锛
鏍囧噯鍖SQL浣跨敤鈥鍙帇缂╂鈥濊鍒欒В鍐宠繖绉嶉棶棰樸傚熀鏈笂锛岃繖涓剰鎬濇槸锛氭棦鐒x鍜'Y'閮芥湁 鏍″瑙勫垯锛屽摢涓牎瀵硅鍒欎紭鍏堬紵杩欏彲鑳芥瘮杈冮毦瑙ｅ喅锛屼絾鏄互涓嬭鍒欓傚悎澶у鏁版儏鍐碉細
路 涓涓鍦ㄧ殑COLLATE瀛愬彞鍙帇缂╂ф槸0锛堟牴鏈笉鑳藉帇缂┿傦級
路 浣跨敤涓嶅悓鏍″瑙勫垯鐨勪袱涓瓧绗︿覆杩炴帴鐨勫彲鍘嬬缉鎬ф槸1銆
路 鍒楁牎瀵硅鍒欑殑鍙帇缂╂ф槸2銆
路 鈥绯荤粺甯告暟鈥濓紙濡USER()鎴VERSION()鍑芥暟杩斿洖鐨勫瓧绗︿覆锛夊彲鍘嬬缉鎬ф槸3銆
路 鏂囧瓧瑙勫垯鐨勫彲鍘嬬缉鎬ф槸4銆
路 NULL鎴栦粠NULL娲剧敓鐨勮〃杈惧紡鐨勫彲鍘嬬缉鎬ф槸 5銆
涓婅堪鍙帇缂╂у兼槸MySQL5.1褰撳墠鎵鐢ㄧ殑銆
杩欐牱涓婅堪瑙勫垯鍙互妯＄硦瑙ｅ喅锛
路 浣跨敤鏈浣庣殑鍙帇缂╂у肩殑鏍″瑙勫垯銆
路 濡傛灉涓や晶鏈夌浉鍚岀殑鍙帇缂╂э紝閭ｄ箞濡傛灉鏍″瑙勫垯涓嶅悓鍒欏彂鐢熼敊璇
渚嬪锛
|
column1 = 'A' |
浣跨敤column1鐨勬牎瀵硅鍒 |
|
column1 = 'A' COLLATE x |
浣跨敤'A'鐨勬牎瀵硅鍒 |
|
column1 COLLATE x = 'A' COLLATE y |
閿欒 |
浣跨敤COERCIBILITY锛堬級鍑芥暟纭畾涓涓瓧绗︿覆琛ㄨ揪寮忕殑鍙帇缂╂э細
mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);
-> 0
mysql> SELECT COERCIBILITY(VERSION());
-> 3
mysql> SELECT COERCIBILITY('A');
-> 4
娌℃湁绯荤粺甯告暟鎴栧彲蹇界暐鐨勫帇缂╂с傚嚱鏁板USER()鐨勫彲鍘嬬缉鎬ф槸2鑰屼笉鏄3锛屾枃瀛楃殑鍙帇缂╂ф槸3鑰屼笉鏄4銆
璇锋敞鎰忔瘡涓瓧绗﹂泦鏈変竴涓垨澶氫釜鏍″瑙勫垯锛屽苟涓旀瘡涓牎瀵硅鍒欏彧鑳藉睘浜庝竴涓瓧绗﹂泦銆傚洜姝わ紝浠ヤ笅璇彞浼氫骇鐢熶竴涓敊璇俊鎭紝鍥犱负鏍″瑙勫垯latin2_bin瀵逛簬瀛楃闆latin1闈炴硶锛
mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1251: COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'
鍋囪琛T涓殑鍒X鏈夎繖浜latin1鍒楀硷細
Muffler
M眉ller
MX Systems
MySQL
鍋囪浣跨敤涓嬮潰鐨勮鍙ヨ幏鍙栧垪鍊硷細
SELECT X FROM T ORDER BY X COLLATE collation_name;
浣跨敤涓嶅悓鏍″瑙勫垯鐨勫垪鍊肩粨鏋滄帓搴忚涓嬭〃锛
|
latin1_swedish_ci |
latin1_german1_ci |
latin1_german2_ci |
|
Muffler |
Muffler |
M眉ller |
|
MX绯荤粺 |
M眉ller |
Muffler |
|
M眉ller |
MX绯荤粺 |
MX绯荤粺 |
|
MySQL |
MySQL |
MySQL |
鏈〃鏄剧ず浜嗘垜浠湪ORDER BY瀛楀彞涓娇鐢ㄤ笉鍚屾墍鏍″瑙勫垯鐨勬晥鏋滅殑绀轰緥銆傚湪鏈緥涓鑷翠笉鍚屾帓搴忕殑瀛楃鏄笂闈㈠甫鏈変袱涓渾鐐圭殑U锛眉锛夛紝瀹冨湪寰疯涓彂闊充负"U-umlaut"銆
路 绗竴鍒楁樉绀虹殑鏄娇鐢ㄧ憺鍏/鑺叞鏍″瑙勫垯鐨SELECT璇彞鐨勭粨鏋滐紝瀹冭绉颁綔U-umlaut浣跨敤Y鎺掑簭銆
路 绗簩鍒楁樉绀虹殑鏄娇鐢ㄥ痉璇DIN-1鏍″瑙勫垯鐨SELECT璇彞鐨勭粨鏋滐紝瀹冭绉颁綔U-umlaut浣跨敤U鎺掑簭銆
路 绗笁鍒楁樉绀虹殑鏄娇鐢ㄥ痉璇DIN-2鏍″瑙勫垯鐨SELECT璇彞鐨勭粨鏋滐紝瀹冭绉颁綔U-umlaut浣跨敤UE鎺掑簭銆
瀵逛簬绠鍗曠殑鍑芥暟锛屽嵆鎺ユ敹瀛楃涓茶緭鍏ョ劧鍚庤繑鍥炰竴涓瓧绗︿覆缁撴灉浣滀负杈撳嚭鐨勫嚱鏁帮紝杈撳嚭鐨勫瓧绗﹂泦鍜屾牎瀵硅鍒欎笌鍘熷杈撳叆鐨勭浉鍚屻備緥濡傦紝UPPER锛X锛杩斿洖涓涓瓧绗︿覆锛屽叾瀛楃鍜 鏍″瑙勫垯涓X鐩稿悓銆傜被浼肩殑鍑芥暟杩樻湁INSTR()銆LCASE()銆LOWER()銆LTRIM()銆MID()銆REPEAT()銆REPLACE()銆REVERSE()銆RIGHT()銆RPAD()銆RTRIM()銆SOUNDEX()銆SUBSTRING()銆TRIM()銆UCASE()鍜UPPER()銆傦紙杩橀渶瑕佹敞鎰忥細REPLACE()鍑芥暟涓嶅悓浜庡叾瀹冨嚱鏁帮紝瀹冩绘槸蹇界暐杈撳叆瀛楃涓茬殑 鏍″瑙勫垯锛屽苟涓旇繘琛屽ぇ灏忓啓涓嶆晱鎰熺殑姣旇緝銆傦級
瀵逛簬鍚堝苟澶氫釜瀛楃涓茶緭鍏ュ苟涓旇繑鍥炲崟涓瓧绗︿覆杈撳嚭鐨勮繍绠楋紝搴旂敤鏍囧噯SQL鈥滆仛鍚瑙勫垯鈥濓細
路 濡傛灉瀛樺湪鏄惧紡鐨勬牎瀵硅鍒X锛岄偅涔堜娇鐢X銆
路 濡傛灉瀛樺湪鏄惧紡鐨勬牎瀵硅鍒X鍜Y锛岄偅涔堜骇鐢熶竴涓敊璇
路 鍚﹀垯锛屽鏋滃叏閮ㄦ牎瀵硅鍒欐槸X锛岄偅涔堜娇鐢X銆
路 鍏跺畠鎯呭喌锛岀粨鏋滄病鏈夋牎瀵硅鍒欍
渚嬪锛屼娇鐢CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END銆傜粨鏋滄牎瀵硅鍒欐槸X銆傚浜CASE銆UNION銆||銆CONCAT()銆ELT()銆GREATEST()銆IF()鍜LEAST()鎯呭喌鐩稿悓銆
瀵逛簬杞崲涓哄瓧绗︽暟鎹殑杩愮畻锛屼粠杩愮畻寰楀埌鐨勭粨鏋滃瓧绗︿覆鐨勫瓧绗﹂泦鍜屾牎瀵硅鍒欑敱character_set_connection鍜collation_connection绯荤粺鍙橀噺瀹氫箟銆傝繖閫傜敤浜CAST()銆CHAR()銆CONV()銆FORMAT()銆HEX()鍜SPACE()鍑芥暟銆
10.4.2.聽CONVERT()
CONVERT(expr USING transcoding_name)
鍦 MySQL涓紝杞崲浠ｇ爜鍚嶄笌鐩稿簲鐨勫瓧绗﹂泦鍚嶇浉鍚屻
渚嬪瓙锛
SELECT CONVERT(_latin1'M眉ller' USING utf8);
INSERT INTO utf8table (utf8column)
SELECT CONVERT(latin1field USING utf8) FROM latin1table;
CONVERT(... USING ...)鏍规嵁鏍囧噯SQL瑙勮寖瀹炴柦銆
鍦ㄤ紶缁SQL妯″紡涓紝濡傛灉浣犺浆鎹竴涓0鈥濇棩鏈熷瓧绗︿覆鍒版棩鏈熺被鍨嬶紝CONVERT锛堬級鍑芥暟杩斿洖NULL銆傚湪MySQL5.1涓繕浜х敓涓鏉¤鍛娿
10.4.3.聽CAST()
CAST(character_string AS character_data_type CHARACTER SET charset_name)
渚嬪锛
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);
濡傛灉浣跨敤CAST()鏃舵病鏈夋寚瀹CHARACTER SET锛岀粨鏋滃瓧绗﹂泦鍜屾牎瀵硅鍒欓氳繃character_set_connection 鍜 collation_connection绯荤粺鍙橀噺瀹氫箟銆傚鏋滅敤CAST()骞跺甫鏈CHARACTER SET X閫夐」锛岄偅涔堢粨鏋滃瓧绗﹂泦鍜屾牎瀵硅鍒欐槸X鍜屽叾 榛樿鐨勬牎瀵硅鍒欍
浣犲彲鑳戒笉鑳藉湪CAST()涓娇鐢COLLATE瀛愬彞锛屼絾鏄綘鍙互鍦ㄥ閮ㄤ娇鐢ㄥ畠銆備篃灏辨槸璇达紝涓嶆槸CAST(... COLLATE ...)锛岃屾槸CAST(...) COLLATE ...銆
渚嬪锛
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;
鍦ㄤ紶缁SQL妯″紡涓紝濡傛灉浣犺浆鎹竴涓0鈥濇棩鏈熷瓧绗︿覆鍒版棩鏈熺被鍨嬶紝CAST()鍑芥暟杩斿洖NULL銆傚湪MySQL5.1涓繕浜х敓涓鏉¤鍛娿
10.4.4.聽SHOW璇彞
涓浜SHOW璇彞鎻愪緵棰濆鐨勫瓧绗﹂泦淇℃伅銆傝繖浜涜鍙ュ寘鎷SHOW CHARACTER SET銆SHOW COLLATION銆SHOW CREATE DATABASE銆SHOW CREATE TABLE鍜SHOW COLUMNS銆
SHOW CHARACTER SET鍛戒护鏄剧ず鍏ㄩ儴鍙敤鐨勫瓧绗﹂泦銆傚畠甯︽湁涓涓彲閫夌殑LIKE瀛愬彞鏉ユ寚绀哄尮閰嶅摢浜涘瓧绗﹂泦鍚嶃備緥濡傦細
mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
+---------+-----------------------------+-------------------+--------+
瑙13.5.4.1鑺傦紝鈥淪HOW CHARACTER SET璇硶鈥銆
SHOW COLLATION璇彞鐨勮緭鍑哄寘鎷叏閮ㄥ彲鐢ㄧ殑瀛楃闆嗐傚畠甯︽湁涓涓彲閫夌殑LIKE瀛愬彞鏉ユ寚绀哄尮閰嶅摢浜 鏍″瑙勫垯鍚嶃備緥濡傦細
mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 |
| latin1_danish_ci | latin1 | 15 | | | 0 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 0 |
| latin1_general_ci | latin1 | 48 | | | 0 |
| latin1_general_cs | latin1 | 49 | | | 0 |
| latin1_spanish_ci | latin1 | 94 | | | 0 |
+-------------------+---------+----+---------+----------+---------+
瑙13.5.4.2鑺傦紝鈥淪HOW COLLATION璇硶鈥銆
SHOW CREATE DATABASE璇彞鏄剧ず鍒涘缓缁欏畾鏁版嵁搴撶殑CREATE DATABASE璇彞銆傜粨鏋滃寘鎷叏閮ㄦ暟鎹簱閫夐」銆傛敮鎸DEFAULT CHARACTER SET鍜COLLATE銆傚叏閮ㄦ暟鎹簱閫夐」瀛樺偍鍦ㄥ懡鍚嶄负db.Opt鐨勬枃鏈枃浠朵腑锛岃鏂囦欢鑳藉鍦ㄦ暟鎹簱鐩綍涓壘鍒般
mysql> SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
瑙13.5.4.4鑺傦紝鈥淪HOW CREATE DATABASE璇硶鈥
SHOW CREATE TABLE涓SHOW CREATE DATABASE鐩镐技锛屼絾鏄樉绀哄垱寤虹粰瀹氭暟鎹簱鐨CREATE TABLE璇彞銆傚垪瀹氫箟鏄剧ず浠讳綍瀛楃闆嗚鏍硷紝骞朵笖琛ㄩ夐」鍖呮嫭瀛楃闆嗕俊鎭
瑙13.5.4.5鑺傦紝鈥淪HOW CREATE TABLE璇硶鈥
褰撲互SHOW FULL COLUMNS璋冪敤鏃讹紝SHOW COLUMNS璇彞鏄剧ず琛ㄤ腑鍒楃殑鏍″瑙勫垯銆傚叿鏈CHAR銆VARCHAR鎴TEXT鏁版嵁绫诲瀷鐨勫垪鏈夐潪NULL鐨 鏍″瑙勫垯銆傛暟鍊煎垪鍜屽叾瀹冮潪瀛楃绫诲瀷鐨勫垪鏈NULL鏍″瑙勫垯銆備緥濡傦細
mysql> SHOW FULL COLUMNS FROM person\G
*************************** 1. row ***************************
Field: id
Type: smallint(5) unsigned
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: name
Type: char(60)
Collation: latin1_swedish_ci
Null: NO
Key:
Default:
Extra:
Privileges: select,insert,update,references
Comment:
瀛楃闆嗕笉鏄樉绀虹殑閮ㄥ垎銆傦紙瀛楃闆嗗悕闅愬惈鍦ㄦ牎瀵硅鍒欏悕涓傦級
MySQL 5.1鏀寔涓ょ瀛楃闆嗕互淇濆瓨Unicode鏁版嵁锛
路 ucs2锛UCS-2 Unicode瀛楃闆嗐
路 utf8锛Unicode瀛楃闆嗙殑UTF8缂栫爜銆
鍦UCS-2锛堜簩杩涘埗Unicode琛ㄧず娉曪級涓紝姣忎竴涓瓧绗︾敤涓涓弻瀛楄妭鐨Unicode缂栫爜鏉ヨ〃绀虹殑锛岀涓涓瓧鑺傝〃绀洪噸瑕佺殑鎰忎箟銆備緥濡傦細"LATIN CAPITAL LETTER A"鐨Unicode缂栫爜鏄0x0041锛屽畠鎸夐『搴忓瓨鍌ㄤ负涓や釜瀛楄妭锛0x00 0x41銆"CYRILLIC SMALL LETTER YERU"锛Unicode 0x044B锛夐『搴忓瓨鍌ㄤ负涓や釜瀛楄妭锛0x04 0x4B銆傚浜Unicode瀛楃鍜屽畠浠殑缂栫爜锛岃鍙傝Unicode 涓婚〉銆
褰撳墠锛UCS-2杩樹笉鑳藉鐢ㄤ綔涓哄鎴风瀛楃闆嗭紝杩欐剰鍛崇潃SET NAMES 'ucs2'涓嶈捣浣滅敤銆
UTF8瀛楃闆嗭紙杞崲Unicode琛ㄧず锛夋槸瀛樺偍Unicode鏁版嵁鐨勪竴绉嶅彲閫夋柟娉曘傚畠鏍规嵁 RFC 3629鎵ц銆UTF8瀛楃闆嗙殑鎬濇兂鏄笉鍚Unicode瀛楃閲囩敤鍙橀暱瀛楄妭搴忓垪缂栫爜锛
路 鍩烘湰鎷変竵瀛楁瘝銆佹暟瀛楀拰鏍囩偣绗﹀彿浣跨敤涓涓瓧鑺傘
路 澶у鏁扮殑娆ф床鍜屼腑涓滄墜鍐欏瓧姣嶉傚悎涓や釜瀛楄妭搴忓垪锛氭墿灞曠殑鎷変竵瀛楁瘝锛堝寘鎷彂闊崇鍙枫侀暱闊崇鍙枫侀噸闊崇鍙枫佷綆闊崇鍙峰拰鍏跺畠闊崇锛夈佽タ閲屽皵瀛楁瘝銆佸笇鑵婅銆佷簹缇庡凹浜氳銆佸笇浼潵璇侀樋鎷変集璇佸彊鍒╀簹璇拰鍏跺畠璇█銆
路 闊╄銆佷腑鏂囧拰鏃ユ湰璞″舰鏂囧瓧浣跨敤涓変釜瀛楄妭搴忓垪銆
RFC 3629璇存槑浜嗛噰鐢ㄤ竴鍒板洓涓瓧鑺傜殑缂栫爜搴忓垪銆傚綋鍓嶏紝MySQLUTF8涓嶆敮鎸佸洓涓瓧鑺傘傦紙UTF8缂栫爜鐨勬棫鏍囧噯鏄敱RFC 2279缁欏嚭锛屽畠鎻忚堪浜嗕粠涓鍒板叚涓瓧鑺傜殑UTF8缂栫爜搴忓垪銆RFC 3629琛ュ厖浜嗕綔搴熺殑RFC 2279锛涘洜姝わ紝涓嶅啀浣跨敤5涓瓧鑺傚拰6涓瓧鑺傜殑缂栫爜搴忓垪銆傦級
鎻愮ず锛氫娇鐢UTF8鏃朵负浜嗚妭鐪佺┖闂达紝浣跨敤VARCHAR鑰屼笉瑕佺敤CHAR銆傚惁鍒欙紝MySQL蹇呴』涓轰竴涓CHAR(10) CHARACTER SET utf8鍒楅澶30涓瓧鑺傦紝鍥犱负杩欐槸鍙兘鐨勬渶澶ч暱搴︺
鍏冩暟鎹鏄鍏充簬鏁版嵁鐨勬暟鎹鈥濄傛弿杩版暟鎹簱鐨勪换浣曟暟鎹斾綔涓烘暟鎹簱鍐呭鐨勫绔嬮潰鈥旀槸鍏冩暟鎹傚洜姝わ紝鍒楀悕銆佹暟鎹簱鍚嶃佺敤鎴峰悕銆佺増鏈悕浠ュ強浠SHOW璇彞寰楀埌鐨勭粨鏋滀腑鐨勫ぇ閮ㄥ垎瀛楃涓叉槸鍏冩暟鎹傝繕鍖呮嫭INFORMATION_SCHEMA鏁版嵁搴撲腑鐨勮〃涓殑鍐呭锛屽洜涓哄畾涔夌殑閭ｄ簺琛ㄥ瓨鍌ㄥ叧浜庢暟鎹簱瀵硅薄鐨勪俊鎭
鍏冩暟鎹〃杩板繀椤绘弧瓒宠繖浜涢渶姹傦細
路 鍏ㄩ儴鍏冩暟鎹繀椤诲湪鍚屼竴瀛楃闆嗗唴銆傚惁鍒欙紝瀵INFORM涓涓TION_SCHEMA鏁版嵁搴撲腑鐨勮〃鎵ц鐨SHOW鍛戒护鍜SELECT鏌ヨ涓嶈兘姝ｅ父宸ヤ綔锛屽洜涓鸿繖浜涜繍绠楃粨鏋滀腑鐨勫悓涓鍒楃殑涓嶅悓琛屽皢浼氫娇鐢ㄤ笉鍚岀殑瀛楃闆嗐
路 鍏冩暟鎹繀椤诲寘鎷墍鏈夎瑷鐨勬墍鏈夊瓧绗︺傚惁鍒欙紝鐢ㄦ埛灏嗕笉鑳藉浣跨敤瀹冧滑鑷繁鐨勮瑷鏉ュ懡鍚嶅垪鍜岃〃銆
涓轰簡婊¤冻杩欎袱涓渶姹傦紝MySQL浣跨敤Unicode瀛楃闆嗗瓨鍌ㄥ厓鏁版嵁锛屽嵆UTF8銆傚鏋滀綘浠庝笉浣跨敤閲嶉煶瀛楃锛岃繖涓嶄細瀵艰嚧浠讳綍鐮村潖銆備絾濡傛灉浣犱娇鐢ㄩ噸闊冲瓧绗︼紝搴旇娉ㄦ剰鐨勬槸鍏冩暟鎹槸鐢UTF8瀛樺偍銆
杩欐剰鍛崇潃锛USER()銆CURRENT_USER()銆DATABASE()鍜VERSION()鍑芥暟鐨勮繑鍥炲艰 榛樿璁剧疆涓UTF8瀛楃闆嗭紝杩欎笌鍚屼箟鍑芥暟濡SESSION_USER() 鍜SYSTEM_USER()鐨勭粨鏋滅浉鍚屻
鏈嶅姟鍣ㄥ皢character_set_system绯荤粺鍙橀噺璁剧疆涓哄厓鏁版嵁瀛楃闆嗙殑鍚嶏細
mysql> SHOW VARIABLES LIKE 'character_set_system';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_system | utf8 |
+----------------------+-------+
瀛樺偍鍏冩暟鎹娇鐢Unicode骞朵笉鎰忓懗鐫鍒楀ご鍜DESCRIBE鍑芥暟鐨勭粨鏋滈粯璁ゅ湪character_set_system瀛楃闆嗕腑銆傚綋浣犱娇鐢SELECT column1 FROM t璇彞鏃讹紝鍚嶅瓧涓column1鐨勫垪浠庢湇鍔″櫒杩斿洖瀹㈡埛绔苟浣跨敤鐢SET NAMES璇彞纭畾鐨勫瓧绗﹂泦銆傛洿鏄庣‘鍦拌锛屼娇鐢ㄧ殑瀛楃闆嗘槸鐢character_set_results绯荤粺鍙橀噺鐨勫肩‘瀹氱殑銆傚鏋滆繖涓郴缁熷彉閲忚缃负NULL锛屼笉鎵ц瀛楃杞崲锛屾湇鍔″櫒浣跨敤鏈鍒濈殑瀛楃闆嗭紙瀛楃闆嗙敱character_set_system绯荤粺鍙橀噺璁剧疆锛夎繑鍥炲厓鏁版嵁銆
濡傛灉浣犲笇鏈涙湇鍔″櫒涓嶄娇鐢UTF8瀛楃闆嗚繑鍥炲厓鏁版嵁缁撴灉锛岄偅涔堜娇鐢SET NAMES璇彞寮哄埗鏈嶅姟鍣ㄦ墽琛屽瓧绗﹂泦杞崲锛堣10.3.6鑺傦紝鈥滆繛鎺ュ瓧绗﹂泦鍜屾牎瀵光锛夛紝鎴栬呭湪瀹㈡埛绔墽琛岃浆鎹€傚湪瀹㈡埛绔墽琛岃浆鎹㈡晥鐜囪緝楂橈紝浣嗚繖绉嶉夐」骞朵笉鑳戒娇鐢ㄤ簬鍏ㄩ儴瀹㈡埛绔
濡傛灉浣犳鍦ㄤ竴涓鍙ヤ腑浣跨敤锛堜緥濡傦級USER()鍑芥暟杩涜姣旇緝鎴栬祴鍊硷紝涓嶈鎷呭績銆MySQL涓轰綘鎵ц涓浜涘師瀛愯浆鎹€
SELECT * FROM Table1 WHERE USER() = latin1_column;
杩欐槸鍙互鐨勶紝鍥犱负鍦ㄦ瘮杈冧箣鍓latin1_column鍒楃殑鍐呭浼氳嚜鍔ㄨ浆鎹㈠埌UTF8銆
INSERT INTO Table1 (latin1_column) SELECT USER();
杩欐槸鍙互鐨勶紝鍥犱负璧嬪间箣鍓USER()鍑芥暟杩斿洖鐨勫唴瀹硅嚜鍔ㄨ浆鎹负latin1銆傝嚦浠婏紝鑷姩杞崲娌℃湁鍏ㄩ儴瀹炴柦锛屼絾鏄互鍚庣殑鐗堟湰涓簲璇ュ伐浣滄甯搞
灏界鑷姩杞崲涓嶅睘浜SQL鏍囧噯锛SQL鏍囧噯鍖栨枃妗ｄ腑璇存瘡涓涓瓧绗﹂泦鏄紙鏍规嵁鏀寔鐨勫瓧绗︼級Unicode鐨勨瀛愰泦鈥濄傚洜姝わ紝涓涓煡鍚嶇殑鍘熷垯鏄紝鈥滈傜敤瓒呴泦鐨勫瓧绗﹂泦鑳藉搴旂敤浜庡叾瀛愰泦鈥濓紝鎴戜滑鐩镐俊Unicode鐨 鏍″瑙勫垯鑳藉搴旂敤浜庨潪Unicode瀛楃涓茬殑姣旇緝銆
娉ㄩ噴锛氬湪MySQL5.1涓紝errmsg.txt鏂囦欢鍏ㄩ儴浣跨敤UTF8銆傚鎴风瀛楃闆嗙殑杞崲鏄嚜鍔ㄨ繘琛岀殑锛屽鍚屽厓鏁版嵁銆
瀵逛簬MaxDB鍏煎鎬э紝涓嬮潰涓や釜璇彞鏄浉鍚岀殑锛
CREATE TABLE t1 (f1 CHAR(n) UNICODE); CREATE TABLE t1 (f1 CHAR(n) CHARACTER SET ucs2);
CHAR(10) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)
涓嬮潰鍚屾牱锛
VARCHAR(10) CHARACTER SET utf8
NATIONAL VARCHAR(10)
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(10)
浣犺兘澶熶娇鐢N'literal'鏉ュ垱寤轰竴涓娇鐢ㄥ浗瀹剁壒鏈夊瓧绗﹂泦鐨勫瓧绗︿覆銆傝繖涓や釜璇彞鏄瓑浠风殑锛
SELECT N'some text';
SELECT _utf8'some text';
鍏充簬MySQL浠4.1浠ュ墠鐨勭増鏈埌5.1鐗堟湰瀛楃闆嗗崌绾х殑淇℃伅锛岃鍙傝MySQL4.1鍙傝冩墜鍐銆
10.10. MySQL鏀寔鐨勫瓧绗﹂泦鍜屾牎瀵
MySQL鏀寔30澶氱瀛楃闆嗙殑70澶氱 鏍″瑙勫垯銆傚瓧绗﹂泦鍜屽畠浠殑榛樿鏍″瑙勫垯鍙互閫氳繃SHOW CHARACTER SET璇彞鏄剧ず锛
mysql> SHOW CHARACTER SET; +----------+-----------------------------+---------------------+ | Charset | Description | Default collation | +----------+-----------------------------+---------------------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | | dec8 | DEC West European | dec8_swedish_ci | | cp850 | DOS West European | cp850_general_ci | | hp8 | HP West European | hp8_english_ci | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | | latin1 | cp1252 West European | latin1_swedish_ci | | latin2 | ISO 8859-2 Central European | latin2_general_ci | | swe7 | 7bit Swedish | swe7_swedish_ci | | ascii | US ASCII | ascii_general_ci | | ujis | EUC-JP Japanese | ujis_japanese_ci | | sjis | Shift-JIS Japanese | sjis_japanese_ci | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | | tis620 | TIS620 Thai | tis620_thai_ci | | euckr | EUC-KR Korean | euckr_korean_ci | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | | greek | ISO 8859-7 Greek | greek_general_ci | | cp1250 | Windows Central European | cp1250_general_ci | | gbk | GBK Simplified Chinese | gbk_chinese_ci | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | | utf8 | UTF-8 Unicode | utf8_general_ci | | ucs2 | UCS-2 Unicode | ucs2_general_ci | | cp866 | DOS Russian | cp866_general_ci | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | | macce | Mac Central European | macce_general_ci | | macroman | Mac West European | macroman_general_ci | | cp852 | DOS Central European | cp852_general_ci | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | | cp1251 | Windows Cyrillic | cp1251_general_ci | | cp1256 | Windows Arabic | cp1256_general_ci | | cp1257 | Windows Baltic | cp1257_general_ci | | binary | Binary pseudo charset | binary | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | +----------+-----------------------------+---------------------+
MySQL鏈変袱绉Unicode瀛楃闆嗐備綘鑳藉浣跨敤杩欎簺瀛楃闆嗕繚瀛樺ぇ绾650绉嶈瑷鐨勬枃鏈
路 ucs2 (UCS-2 Unicode)鏍″瑙勫垯:
mysql> SHOW COLLATION LIKE 'ucs2%'; +--------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------+---------+-----+---------+----------+---------+ | ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 | | ucs2_bin | ucs2 | 90 | | Yes | 1 | | ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 | | ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 | | ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 | | ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 | | ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 | | ucs2_polish_ci | ucs2 | 133 | | Yes | 8 | | ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 | | ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 | | ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 | | ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 | | ucs2_czech_ci | ucs2 | 138 | | Yes | 8 | | ucs2_danish_ci | ucs2 | 139 | | Yes | 8 | | ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 | | ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 | | ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 | | ucs2_roman_ci | ucs2 | 143 | | Yes | 8 | | ucs2_persian_ci | ucs2 | 144 | | Yes | 8 | | ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 | +--------------------+---------+-----+---------+----------+---------+
utf8 (UTF-8 Unicode)鏍″瑙勫垯:
mysql> SHOW COLLATION LIKE 'utf8%'; +--------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | | utf8_latvian_ci | utf8 | 194 | | Yes | 8 | | utf8_romanian_ci | utf8 | 195 | | Yes | 8 | | utf8_slovenian_ci | utf8 | 196 | | Yes | 8 | | utf8_polish_ci | utf8 | 197 | | Yes | 8 | | utf8_estonian_ci | utf8 | 198 | | Yes | 8 | | utf8_spanish_ci | utf8 | 199 | | Yes | 8 | | utf8_swedish_ci | utf8 | 200 | | Yes | 8 | | utf8_turkish_ci | utf8 | 201 | | Yes | 8 | | utf8_czech_ci | utf8 | 202 | | Yes | 8 | | utf8_danish_ci | utf8 | 203 | | Yes | 8 | | utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 | | utf8_slovak_ci | utf8 | 205 | | Yes | 8 | | utf8_spanish2_ci | utf8 | 206 | | Yes | 8 | | utf8_roman_ci | utf8 | 207 | | Yes | 8 | | utf8_persian_ci | utf8 | 208 | | Yes | 8 | | utf8_esperanto_ci | utf8 | 209 | | Yes | 8 | +--------------------+---------+-----+---------+----------+---------+
utf8_unicode_ci鏍″瑙勫垯鏄牴鎹Unicode鏍″瑙勫垯绠楁硶锛UCA锛夋墽琛岀殑锛 鏍″瑙勫垯鎻忚堪瑙 http://www.unicode.org/reports/tr10/銆傛鏍″瑙勫垯浣跨敤UCA 4.0.0鐗堟湰鐮濈爜閿細http://www.unicode.org/Public/UC涓涓/4.0.0/涓涓llkeys-4.0.0.txt銆傦紙浠ヤ笅璁ㄨ浣跨敤utf8_unicode_ci锛屼絾鍚屾牱閫傚悎ucs2_unicode_ci銆傦級
褰撳墠锛utf8_unicode_ci鏍″瑙勫垯浠呴儴鍒嗘敮鎸Unicode鏍″瑙勫垯绠楁硶銆備竴浜涘瓧绗﹁繕鏄笉鑳芥敮鎸併傚苟涓旓紝涓嶈兘瀹屽叏鏀寔缁勫悎鐨勮鍙枫傝繖涓昏褰卞搷瓒婂崡鍜屼縿缃楁柉鐨勪竴浜涘皯鏁版皯鏃忚瑷锛屽锛Udmurt 銆Tatar銆Bashkir鍜Mari銆
utf8_unicode_ci鐨勬渶涓昏鐨勭壒鑹叉槸鏀寔鎵╁睍锛屽嵆褰撴妸涓涓瓧姣嶇湅浣滀笌鍏跺畠瀛楁瘝缁勫悎鐩哥瓑鏃躲備緥濡傦紝鍦ㄥ痉璇拰涓浜涘叾瀹冭瑷涓脽鈥欑瓑浜庘ss鈥欍
utf8_general_ci鏄竴涓仐鐣欑殑 鏍″瑙勫垯锛屼笉鏀寔鎵╁睍銆傚畠浠呰兘澶熷湪瀛楃涔嬮棿杩涜閫愪釜姣旇緝銆傝繖鎰忓懗鐫utf8_general_ci鏍″瑙勫垯杩涜鐨勬瘮杈冮熷害寰堝揩锛屼絾鏄笌浣跨敤utf8_unicode_ci鐨 鏍″瑙勫垯鐩告瘮锛屾瘮杈冩纭ц緝宸級銆
渚嬪锛屼娇鐢utf8_general_ci鍜utf8_unicode_ci涓ょ 鏍″瑙勫垯涓嬮潰鐨勬瘮杈冪浉绛夛細
脛 = A
脰 = O
脺 = U
涓ょ鏍″瑙勫垯涔嬮棿鐨勫尯鍒槸锛屽浜utf8_general_ci涓嬮潰鐨勭瓑寮忔垚绔嬶細
脽 = s
浣嗘槸锛屽浜utf8_unicode_ci涓嬮潰绛夊紡鎴愮珛锛
脽 = ss
瀵逛簬涓绉嶈瑷浠呭綋浣跨敤utf8_unicode_ci鎺掑簭鍋氱殑涓嶅ソ鏃讹紝鎵嶆墽琛屼笌鍏蜂綋璇█鐩稿叧鐨utf8瀛楃闆 鏍″瑙勫垯銆備緥濡傦紝瀵逛簬寰疯鍜屾硶璇紝utf8_unicode_ci宸ヤ綔鐨勫緢濂斤紝鍥犳涓嶅啀闇瑕佷负杩欎袱绉嶈瑷鍒涘缓鐗规畩鐨utf8鏍″瑙勫垯銆
utf8_general_ci涔熼傜敤涓庡痉璇拰娉曡锛岄櫎浜嗏脽鈥欑瓑浜庘s鈥欙紝鑰屼笉鏄ss鈥欎箣澶栥傚鏋滀綘鐨勫簲鐢ㄨ兘澶熸帴鍙楄繖浜涳紝閭ｄ箞搴旇浣跨敤utf8_general_ci锛鍥犱负瀹冮熷害蹇傚惁鍒欙紝浣跨敤utf8_unicode_ci锛屽洜涓哄畠姣旇緝鍑嗙‘銆
utf8_swedish_ci锛屼笌鍏跺畠璇█鐩稿叧鐨utf8鐨勬牎瀵硅鍒欑浉浼硷紝鏉ユ簮浜utf8_unicode_ci锛浣跨敤棰濆鐨勮瑷瑙勫垯銆備緥濡傦紝鍦ㄧ憺鍏歌涓紝浠ヤ笅鐨勫叧绯诲紡鎴愮珛锛屽畠鍦ㄥ痉璇拰娉曡涓笉鎴愮珛锛
脺 = Y < 脰
utf8_spanish_ci鍜utf8_spanish2_ci鏍″瑙勫垯鍒嗗埆閫傜敤浜庣幇浠ｅ拰鍙ゅ吀瑗跨彮鐗欒銆傚湪涓ょ 鏍″瑙勫垯涓紝帽鈥欙紙n-鍙戦煶绗︼級鏄n鈥欏拰鈥o鈥欎箣闂寸殑闂撮殧瀛楁瘝銆傚彟澶栵紝瀵逛簬鍙ゅ吀瑗跨彮鐗欒锛屸ch鈥欐槸鈥c鈥欏拰d涔嬮棿鐨勯棿闅斿瓧姣嶏紝骞朵笖鈥ll鈥欐槸鈥l鈥欏拰鈥m鈥欎箣闂寸殑闂撮殧瀛楁瘝銆
瑗挎瀛楃闆嗚鐩栧ぇ澶氭暟瑗挎璇█锛屽娉曡銆佽タ鐝墮璇佸姞娉扮綏灏间簹璇佸反鏂厠浜鸿銆佽憽钀勭墮璇佹剰澶у埄璇侀樋鑰屽反灏间簹璇佽嵎鍏拌銆佸痉璇佷腹楹﹁銆佺憺鍏歌銆佹尓濞佽銆佽姮鍏拌銆佹硶缃椾汉璇佸啺宀涜銆佺埍灏斿叞璇佽嫃鏍煎叞璇拰鑻辫銆
路 ascii锛US ASCII锛夋牎瀵硅鍒欙細
o ascii_bin
o ascii_general_ci锛 榛樿锛
路 cp850锛DOS瑗挎锛 鏍″瑙勫垯锛
o cp850_bin
o cp850_general_ci锛 榛樿锛
路 dec8锛DEC 瑗挎锛夋牎瀵硅鍒欙細
o dec8_bin
o dec8_swedish_ci锛 榛樿锛
路 hp8锛HP 瑗挎锛夋牎瀵硅鍒欙細
o hp8_bin
o hp8_english_ci锛 榛樿锛
路 latin1锛cp1252 瑗挎锛夋牎瀵硅鍒欙細
o latin1_bin
o latin1_danish_ci
o latin1_general_ci
o latin1_general_cs
o latin1_german1_ci
o latin1_german2_ci
o latin1_spanish_ci
o latin1_swedish_ci锛 榛樿锛
latin1鏄 榛樿瀛楃闆嗐latin1_swedish_ci鏄 榛樿鐨勬牎瀵硅鍒欙紝瀹冪敤浜庡ぇ澶氭暟MySQL瀹㈡埛銆傝櫧鐒剁粡甯歌瀹冧互鐟炲吀/鑺叞 鏍″瑙勫垯涓哄熀纭锛屼絾鐟炲吀鍜岃姮鍏颁汉涓嶅悓鎰忚繖绉嶈娉曘
latin1_german1_ci鍜latin1_german2_ci鏍″瑙勫垯鍩轰簬DIN-1鍜DIN-2鏍囧噯锛岃繖閲DIN浠ｈ〃Deutsches Institut f眉r Normung锛堝痉璇瓑浠蜂簬ANSI锛夈DIN-1琚彨鍋氣瀛楀吀鏍″瑙勫垯鈥濓紝DIN-2琚彨鍋氣鐢佃瘽绨挎牎瀵硅鍒鈥濄
o latin1_german1_ci锛堝瓧鍏革級瑙勫垯锛
o 脛 = a
o 脰 = O
o 脺 = U
o 脽 = s
o latin1_german2_ci锛鐢佃瘽绨锛夎鍒欙細
o 脛 = aE
o 脰 = OE
o 脺 = UE
o 脽 = ss
鍦 latin1_spanish_ci鏍″瑙勫垯涓紝鈥帽鈥欙紙n-tilde锛夋槸鈥n鈥欏拰鈥o鈥欎箣闂寸殑闂撮殧瀛楁瘝銆
路 macroma锛Mac瑗挎锛 鏍″瑙勫垯锛
o macroman_bin
o macroman_general_ci锛 榛樿锛
路 swe7锛7浣嶇憺鍏歌锛 鏍″瑙勫垯锛
o swe7_bin
o swe7_swedish_ci锛 榛樿锛
路 cp1250锛Windows涓锛 鏍″瑙勫垯锛
o cp1250_bin
o cp1250_croatian_ci
o cp1250_czech_cs
o cp1250_general_ci锛 榛樿锛
路 cp852锛DOS 涓锛夋牎瀵硅鍒欙細
o cp852_bin
o cp852_general_ci锛 榛樿锛
路 keybcs2锛DOS Kamenicky Czech-Slovak锛夋牎瀵硅鍒欙細
o keybcs2_bin
o keybcs2_general_ci锛 榛樿锛
路 latin2锛ISO 8859-2 涓锛夋牎瀵硅鍒欙細
o latin2_bin
o latin2_croatian_ci
o latin2_czech_cs
o latin2_general_ci锛 榛樿锛
o latin2_hungarian_ci
路 macce锛Mac 涓锛夋牎瀵硅鍒欙細
o macce_bin
o macce_general_ci锛 榛樿锛
10.10.4. 鍗楁涓庝腑涓滃瓧绗﹂泦
路 armscii8锛ARMSCII-8 浜氱編灏间簹璇級鏍″瑙勫垯锛
o armscii8_bin
o armscii8_general_ci锛 榛樿锛
路 cp1256锛堥樋鎷変集璇Windows锛 鏍″瑙勫垯锛
o cp1256_bin
o cp1256_general_ci锛 榛樿锛
路 geostd8锛GEOSTD8涔旀不浜氳锛 鏍″瑙勫垯锛
o geostd8_bin
o geostd8_general_ci锛 榛樿锛
路 greek锛ISO 8859-7甯岃厞璇級鏍″瑙勫垯锛
o greek_bin
o greek_general_ci锛 榛樿锛
路 hebrew锛ISO 8859-8甯屼集鑾辫锛夋牎瀵硅鍒欙細
o hebrew_bin
o hebrew_general_ci锛 榛樿锛
路 latin5锛ISO 8859-9 鍦熻冲叾璇級鏍″瑙勫垯锛
o latin5_bin
o latin5_turkish_ci锛 榛樿锛
10.10.5. 娉㈢綏鐨勬捣瀛楃闆
路 cp1251锛Windows 瑗块噷灏旓級鏍″瑙勫垯锛
o cp1251_bin
o cp1251_bulgarian_ci
o cp1251_general_ci锛 榛樿锛
o cp1251_general_cs
o cp1251_ukrainian_ci
路 cp866锛DOS 淇勮锛夋牎瀵硅鍒欙細
o cp866_bin
o cp866_general_ci锛 榛樿锛
路 koi8r锛KOI8-R Relcom 淇勮锛夋牎瀵硅鍒欙細
o koi8r_bin
o koi8r_general_ci锛 榛樿锛
路 koi8u锛KOI8-U 涔屽厠鍏拌锛夋牎瀵硅鍒欙細
o koi8u_bin
o koi8u_general_ci锛 榛樿锛
10.10.7. 浜氭床瀛楃闆
鎴戜滑鏀寔鐨勪簹娲插瓧绗﹂泦鍖呮嫭涓枃銆佹棩璇侀煩璇拰娉板浗璇傝繖浜涘彲鑳芥瘮杈冨鏉傘備緥濡傦紝涓枃瀛楃闆嗗繀椤昏冭檻鍒颁笂鍗冪涓嶅悓鐨勫瓧绗︺
路 big5锛Big5浼犵粺涓枃锛 鏍″瑙勫垯锛
o big5_bin
o big5_chinese_ci锛 榛樿锛
路 cp932锛SJIS Windows鏃ヨ锛夋牎瀵硅鍒欙細
o cp932_bin
o cp932_japanese_ci锛 榛樿锛
路 eucjpms锛UJIS Windows鏃ヨ锛夋牎瀵硅鍒欙細
o eucjpms_bin
o eucjpms_japanese_ci锛 榛樿锛
路 euckr锛EUC-KR 闊╄锛夋牎瀵硅鍒欙細
o euckr_bin
o euckr_korean_ci锛 榛樿锛
路 gb2312锛GB2312 绠浣撲腑鏂囷級鏍″瑙勫垯锛
o gb2312_bin
o gb2312_chinese_ci锛 榛樿锛
路 gbk锛GBK绠浣撲腑鏂囷級 鏍″瑙勫垯锛
o gbk_bin
o gbk_chinese_ci锛 榛樿锛
路 sjis锛Shift-JIS 鏃ヨ锛夋牎瀵硅鍒欙細
o sjis_bin
o sjis_japanese_ci锛 榛樿锛
路 tis620锛TIS620 娉板浗璇級鏍″瑙勫垯锛
o tis620_bin
o tis620_thai_ci锛 榛樿锛
路 ujis锛EUC-JP 鏃ヨ锛夋牎瀵硅鍒欙細
o ujis_bin
o ujis_japanese_ci锛 榛樿锛
涓轰粈涔堥渶瑕cp932锛
鍦MySQL涓紝sjis瀛楃闆嗗搴斾簬鐢IANA瀹氫箟鐨Shift_JIS瀛楃闆嗭紝瀹冩敮鎸JIS X0201鍜JIS X0208瀛楃銆傦紙瑙 http://www.iana.org/assignments/character-sets銆傦級
浣嗘槸锛屸SHIFT JIS鈥濅綔涓烘弿杩版ф湳璇殑鍚箟鍙樺緱闈炲父鍚硦涓嶆竻锛屽苟涓斿畠甯稿父鍖呮嫭鐢变笉鍚屼緵搴斿晢瀹氫箟鐨Shift_JIS鎵╁睍閮ㄥ垎銆
渚嬪锛屼娇鐢ㄥ湪鏃ユ湰Windows鐜涓娇鐢ㄧ殑鈥SHIFT JIS鈥濇槸Microsoft瀵Shift_JIS鐨Microsoft鎵╁睍锛屽畠鐨勫噯纭悕瀛楁槸Microsoft Windows Codepage: 932鎴cp932銆闄ょ敱Shift_JIS鏀寔鐨勫瓧绗︿箣澶栵紝cp932鏀寔鎵╁睍瀛楃锛屽NEC閫夋嫨鐨IBM鎵╁睍瀛楃鍜IBM鎵╁睍瀛楃銆
璁稿鏃ユ湰鐢ㄦ埛鍦ㄤ娇鐢ㄨ繖浜涙墿灞曞瓧绗﹁繃绋嬩腑纰板埌杩囦竴浜涢棶棰樸傝繖浜涢棶棰樻槸鐢变簬浠ヤ笅鎯呭喌寮曡捣鐨勶細
路 MySQL鑷姩杞崲瀛楃闆嗐
路 瀛楃闆嗛氳繃Unicode杞崲锛ucs2锛夈
路 sjis瀛楃闆嗕笉鏀寔杩欎簺鎵╁睍瀛楃杞崲銆
路 浠庡彿绉扳SHIFT JIS鈥濆埌Unicode鐨勮浆鎹紝瀛樺湪涓浜涜浆鎹㈣鍒欙紝骞朵笖涓浜涘瓧绗﹁浆鎹㈠埌Unicode渚濊禆涓嶅悓鐨勮浆鎹㈣鍒欍MySQL浠呮敮鎸佽繖浜涜浆鎹㈣鍒欎腑鐨勪竴绉嶏紙鍦ㄥ悗闈㈡弿杩帮級銆
MySQLcp932瀛楃闆嗗彲浠ヨВ鍐宠繖浜涜浆鎹㈤棶棰樸
鍥犱负MySQL鏀寔瀛楃闆嗚浆鎹紝灏IANA Shift_JIS 鍜 cp932鍒嗙涓轰袱绉嶄笉鍚屽瓧绗﹂泦鏄噸瑕佺殑锛屽洜涓哄畠浠彁渚涗笉鍚岀殑杞崲瑙勫垯銆
cp932涓sjis鏈変粈涔堜笉鍚岋紵
cp932瀛楃闆嗕笌sjis瀛樺湪浠ヤ笅涓嶅悓鐐癸細
路 cp932鏀寔NEC鐗规畩瀛楃銆NEC閫夋嫨鐨IBM鎵╁睍瀛楃鍜IBM閫夋嫨鐨勫瓧绗︺
路 涓浜cp932瀛楃鏈変袱涓笉鍚岀殑缂栫爜鐐癸紝杩欎袱绉嶇紪鐮佺偣杞崲涓虹浉鍚Unicode缂栫爜鐐广傚洜姝わ紝褰撲粠Unicode杞崲鍥炲埌cp932鏃讹紝蹇呴』閫夋嫨涓涓紪鐮佺偣銆傚浜庤繖绉嶁滅浉浜杞崲鈥锛浣跨敤鐢Microsoft鎺ㄨ崘鐨勮浆鎹㈣鍒欍傦紙瑙 http锛//support.microsoft.com/kb/170559/EN-US/銆傦級
杞崲瑙勫垯濡備笅锛
o 濡傛灉瀛楃鍦JIS X 0208 鍜NEC鐗规畩瀛楃涓悓鏃跺瓨鍦紝浣跨敤JIS X 0208 鐨勭紪鐮佺偣銆
o 濡傛灉瀛楃鍦NEC鐗规畩瀛楃鍜IBM閫夋嫨鐨勫瓧绗︿腑鍚屾椂瀛樺湪锛屼娇鐢NEC鐗规畩瀛楃鐨勭紪鐮佺偣銆
o 濡傛灉瀛楃鍦IBM閫夋嫨鐨勫瓧绗﹀拰NEC閫夋嫨鐨IBM鎵╁睍瀛楃涓悓鏃跺瓨鍦紝浣跨敤IBM鎵╁睍瀛楃鐨勭紪鐮佺偣銆
鍏充簬cp932瀛楃鐨Unicode 鍊肩殑鍒楄〃鏄剧ず淇℃伅瑙http://www.microsoft.com/globaldev/reference/dbcs/932.htm銆傚浜cp932琛ㄤ腑鐨勫甫鏈変笅闈㈡湁鍥涗綅鏁板瓧鍑虹幇鐨勫瓧绗︾殑瀹炰綋锛屾暟瀛椾唬琛ㄧ浉搴旂殑Unicode锛ucs2锛夌紪鐮併傚浜庤〃涓湁涓や釜甯︿笅鍒掔嚎鐨勬暟瀛楀嚭鐜扮殑瀹炰綋锛屾嫨鏈変竴涓互閭ｄ袱涓暟瀛楀紑澶寸殑cp932瀛楃鍊肩殑鑼冨洿銆傜偣鍑讳竴涓繖绉嶈〃鐨勫疄浣擄紝灏嗗甫浣犲埌涓涓〉锛岃椤垫樉绀烘瘡涓互閭ｄ簺鏁板瓧寮澶寸殑cp932瀛楃鐨Unicode鍊笺
浠ヤ笅杩炴帴寰堥噸瑕併傚畠浠笌涓嬪垪瀛楃闆嗙殑缂栫爜鐩稿搴旓細
o NEC鐗规畩瀛楃锛
http://www.microsoft.com/globaldev/reference/dbcs/932/932_87.htm
o NEC閫夋嫨鐨IBM鎵╁睍瀛楃锛
o http://www.microsoft.com/globaldev/reference/dbcs/932/932_ED.htm
o http://www.microsoft.com/globaldev/reference/dbcs/932/932_EE.htm
o IBM閫夋嫨鐨勫瓧绗︼細
o http://www.microsoft.com/globaldev/reference/dbcs/932/932_FA.htm
o http://www.microsoft.com/globaldev/reference/dbcs/932/932_FB.htm
o http://www.microsoft.com/globaldev/reference/dbcs/932/932_FC.htm
路 cp932涓eucjpms缁撳悎鏀寔鐢ㄦ埛鑷畾涔夊瓧绗︾殑杞崲锛屽苟涓旇В鍐sjis/ujis杞崲闂銆傝缁嗕俊鎭紝璇峰弬瑙http://www.opengroup.or.jp/jvc/cde/sjis-euc-e.html銆
路 瀵逛簬涓浜涘瓧绗︼紝涓ucs2涔嬮棿鐨勮浆鎹笌sjis鍜cp932涔嬮棿鐨勮浆鎹㈡槸涓嶅悓鐨勩備笅琛ㄤ妇渚嬭鏄庝簡杩欎簺涓嶅悓銆
杞崲鍒ucs2锛
|
sjis/cp932鍊 |
sjis鈫 ucs2杞崲 |
cp932 鈫 ucs2杞崲 |
|
5C |
005C |
005C |
|
7E |
007E |
007E |
|
815C |
2015 |
2015 |
|
815F |
005C |
FF3C |
|
8160 |
301C |
FF5E |
|
8161 |
2016 |
2225 |
|
817C |
2212 |
FF0D |
|
8191 |
00a2 |
FFE0 |
|
8192 |
00a3 |
FFE1 |
|
81Ca |
00aC |
FFE2 |
浠ucs2杞崲锛
|
ucs2鍊 |
ucs2 鈫 sjis杞崲 |
ucs2 鈫 cp932杞崲 |
|
005C |
815F |
5C |
|
007E |
7E |
7E |
|
00a2 |
8191 |
3F |
|
00a3 |
8192 |
3F |
|
00aC |
81Ca |
3F |
|
2015 |
815C |
815C |
|
2016 |
8161 |
3F |
|
2212 |
817C |
3F |
|
2225 |
3F |
8161 |
|
301C |
8160 |
3F |
|
FF0D |
3F |
817C |
|
FF3C |
3F |
815F |
|
FF5E |
3F |
8160 |
|
FFE0 |
3F |
8191 |
|
FFE1 |
3F |
8192 |
|
FFE2 |
3F |
81Ca |
杩欐槸MySQL鍙傝冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝冩墜鍐岋紝璇疯闂dev.mysql.com銆 鍘熷鍙傝冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆