Chapter聽10.聽Character Set Support - MySQL 5.1参考手册中文版

10.3.1. 鏈嶅姟鍣ㄥ瓧绗﹂泦鍜屾牎瀵�

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. 瀛楃�﹂泦鍜屾牎瀵瑰垎閰嶇ず渚�

10.3.6. 杩炴帴瀛楃�﹂泦鍜屾牎瀵�

路         鏈嶅姟鍣ㄥ瓧绗﹂泦鍜屾牎瀵硅�勫垯鍙�浠ョ敤浣�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璇�鍙ヤ腑浣跨敤COLLATE

10.3.9.聽COLLATE瀛愬彞浼樺厛

10.3.10.聽BINARY鎿嶄綔绗�

SELECT * FROM t1 ORDER BY BINARY a;

SELECT * FROM t1 ORDER BY a COLLATE latin1_bin;

10.3.11. 鏍″�圭‘瀹氳緝涓哄�嶆潅鐨勪竴浜涚壒娈婃儏鍐�

10.3.12. 鏍″�瑰繀椤婚�傚悎瀛楃�﹂��

10.3.13. 鏍″�规晥鏋滅殑绀轰��

10.4.1. 缁撴灉瀛楃�︿��

瀵逛簬绠�鍗曠殑鍑芥暟锛屽嵆鎺ユ敹瀛楃�︿覆杈撳叆鐒跺悗杩斿洖涓�涓�瀛楃�︿覆缁撴灉浣滀负杈撳嚭鐨勫嚱鏁帮紝杈撳嚭鐨勫瓧绗﹂泦鍜屾牎瀵硅�勫垯涓庡師濮嬭緭鍏ョ殑鐩稿悓銆備緥濡傦紝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璇�鍙�

10.10.1. Unicode瀛楃�﹂��

10.10.2. 瑗挎�у瓧绗﹂��

10.10.3. 涓�娆у瓧绗﹂��

路         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. 娉㈢綏鐨勬捣瀛楃�﹂��

路         cp1257锛�Windows娉㈢綏鐨勬捣锛� 鏍″�硅�勫垯锛�

o        cp1257_bin

o        cp1257_general_ci锛� 榛樿�わ��

o        cp1257_lithuanian_ci

路         latin7锛�ISO 8859-13娉㈢綏鐨勬捣锛夋牎瀵硅�勫垯锛�

o        latin7_bin

o        latin7_estonian_cs

o        latin7_general_ci锛� 榛樿�わ��

o        latin7_general_cs

10.10.6. 瑗块噷灏斿瓧绗﹂泦

路         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. 浜氭床瀛楃�﹂��