Chapter聽9.聽Language Structure - MySQL 5.1参考手册中文版

瀛楃�︿覆鎸囩敤鍗曞紩鍙�(鈥�'鈥�)鎴栧弻寮曞彿(鈥�"鈥�)寮曡捣鏉ョ殑瀛楃�﹀簭鍒椼�備緥濡傦細

'a string'

"another string"

濡傛灉SQL鏈嶅姟鍣ㄦā寮忓惎鐢ㄤ簡NSI_QUOTES锛屽彲浠ュ彧鐢ㄥ崟寮曞彿寮曠敤瀛楃�︿覆銆傜敤鍙屽紩鍙峰紩鐢ㄧ殑瀛楃�︿覆琚�瑙ｉ噴涓轰竴涓�璇嗗埆绗︺��

瀛楃�︿覆鍙�浠ユ湁涓�涓�鍙�閫夊瓧绗﹂泦寮曚粙璇嶅拰COLLATE瀛愬彞锛�

[_charset_name]'string' [COLLATE collation_name]

渚嬪�傦細

SELECT _latin1'string';

SELECT _latin1'string' COLLATE latin1_danish_ci;

鍏充簬杩欎簺瀛楃�︿覆璇�娉曞舰寮忕殑璇︾粏淇℃伅锛屽弬瑙�10.3.7鑺傦紝鈥滃瓧绗︿覆鏂囧瓧瀛楃�﹂泦鍜屾牎瀵光��銆�

鍦ㄥ瓧绗︿覆涓�锛屾煇浜涘簭鍒楀叿鏈夌壒娈婂惈涔夈�傝繖浜涘簭鍒楀潎鐢ㄥ弽鏂滅嚎(鈥�\鈥�)寮�濮嬶紝鍗虫墍璋撶殑杞�涔夊瓧绗�銆�MySQL璇嗗埆涓嬮潰鐨勮浆涔夊簭鍒楋細

杩欎簺搴忓垪瀵瑰ぇ灏忓啓鏁忔劅銆備緥濡傦紝鈥�\b鈥欒В閲婁负閫�鏍硷紝浣嗏��\B鈥欒В閲婁负鈥�B鈥欍��

鈥�\%鈥欏拰鈥�\_鈥欏簭鍒楃敤浜庢悳绱㈠彲鑳戒細瑙ｉ噴涓洪�氶厤绗︾殑妯″紡鍖归厤鐜�澧冧腑鐨勨��%鈥欏拰鈥�_鈥欐枃瀛楀疄渚嬨�傚弬瑙�12.3.1鑺傦紝鈥滃瓧绗︿覆姣旇緝鍑芥暟鈥�銆傝�锋敞鎰忓�傛灉浣犲湪鍏跺畠鐜�澧冧腑浣跨敤鈥�\%鈥欐垨鈥�\_鈥欙紝瀹冧滑杩斿洖瀛楃�︿覆鈥�\%鈥欏拰鈥�\_鈥欙紝鑰屼笉鏄�鈥�%鈥欏拰鈥�_鈥欍��

鍦ㄥ叾瀹冭浆涔夊簭鍒椾腑锛屽弽鏂滅嚎琚�蹇界暐銆備篃灏辨槸璇达紝杞�涔夊瓧绗﹁В閲婁负浠夸經娌℃湁杞�涔夈��

鏈夊嚑绉嶆柟寮忓彲浠ュ湪瀛楃�︿覆涓�鍖呮嫭寮曞彿锛�

路         鍦ㄥ瓧绗︿覆鍐呯敤鈥�'鈥欏紩鐢ㄧ殑鈥�'鈥欏彲浠ュ啓鎴愨��''鈥欍��

路         鍦ㄥ瓧绗︿覆鍐呯敤鈥�"鈥欏紩鐢ㄧ殑鈥�"鈥欏彲浠ュ啓鎴愨��""鈥欍��

路         鍙�浠ュ湪寮曞彿鍓嶅姞杞�涔夊瓧绗�(鈥�\鈥�)銆�

路         鍦ㄥ瓧绗︿覆鍐呯敤鈥�"鈥欏紩鐢ㄧ殑鈥�'鈥欎笉闇�瑕佺壒娈婂�勭悊锛屼笉闇�瑕佺敤鍙屽瓧绗︽垨杞�涔夈�傚悓鏍凤紝鍦ㄥ瓧绗︿覆鍐呯敤鈥�'鈥欏紩鐢ㄧ殑鈥�"鈥欎篃涓嶉渶瑕佺壒娈婂�勭悊銆�

涓嬮潰鐨�SELECT璇�鍙ユ樉绀轰簡寮曠敤鍜岃浆涔夊�備綍宸ヤ綔锛�

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';

+-------+---------+-----------+--------+--------+

| hello | "hello" | ""hello"" | hel'lo | 'hello |

+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";

+-------+---------+-----------+--------+--------+

| hello | 'hello' | ''hello'' | hel"lo | "hello |

+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';

+--------------------+

| This

Is

Four

Lines |

+--------------------+

mysql> SELECT 'disappearing\ backslash';

+------------------------+

| disappearing backslash |

+------------------------+

濡傛灉浣犳兂瑕佸湪瀛楃�︿覆鍒楀唴鎻掑叆浜岃繘鍒舵暟鎹�(渚嬪��BLOB)锛屽繀椤婚�氳繃杞�涔夊簭鍒楄〃绀轰笅闈㈢殑瀛楃�︼細

褰撶紪鍐欏簲鐢ㄧ▼搴忔椂锛屽湪鍖呭惈杩欎簺鐗规畩瀛楃�︾殑瀛楃�︿覆鐢ㄤ簬鍙戦�佸埌MySQL鏈嶅姟鍣ㄧ殑SQL璇�鍙ヤ腑鐨勬暟鎹�鍊间箣鍓嶏紝蹇呴』瀵瑰畠浠�姝ｇ‘杩涜�岃浆涔夈�傚彲浠ョ敤涓ょ�嶆柟娉曟潵瀹屾垚锛�

路         鐢ㄨ浆涔夌壒娈婂瓧绗︾殑鍑芥暟澶勭悊瀛楃�︿覆銆備緥濡傦紝鍦�C绋嬪簭涓�锛屽彲浠ヤ娇鐢�mysql_real_escape_string() C API鍑芥暟鏉ヨ浆涔夊瓧绗︺�傚弬瑙�25.2.3.52鑺傦紝鈥渕ysql_real_escape_string()鈥�銆�Perl DBI鎺ュ彛鎻愪緵涓�涓�quote鏂规硶鏉ュ皢鐗规畩瀛楃�﹁浆鎹�涓烘�ｇ‘鐨勮浆涔夊簭鍒椼�傚弬瑙�25.4鑺傦紝鈥淢ySQL Perl API鈥�銆�

路         鏄惧紡杞�涔夌壒娈婂瓧绗︼紝璁稿��MySQL API鎻愪緵浜嗗崰浣嶇�﹀姛鑳斤紝鍏佽�镐綘鍦ㄦ煡璇㈠瓧绗︿覆涓�鎻掑叆鐗规畩鏍囪�帮紝鐒跺悗褰撲綘鍙戝嚭鏌ヨ�㈡椂灏嗘暟鎹�鍊煎悓瀹冧滑缁戝畾璧锋潵銆傚湪杩欑�嶆儏鍐典笅锛�API鍏虫敞杞�涔夊�间腑鐨勭壒娈婂瓧绗︺��

鏁存暟鐢ㄤ竴绯诲垪闃挎媺浼�鏁板瓧琛ㄧず銆傛诞鐐规暟浣跨敤鈥�.鈥欎綔涓哄崄杩涘埗闂撮殧绗︺�備袱绉嶇被鍨嬬殑鏁板�煎潎鍙�浠ュ湪鍓嶉潰鍔犱竴涓�鈥�-鈥欐潵琛ㄧず璐熷�笺��

鍚堟硶鏁存暟鐨勪緥瀛愶細

1221

0

-32

鍚堟硶娴�鐐规暟鐨勪緥瀛愶細

294.42

-32032.6809e+10

148.00

鏁存暟鍙�浠ョ敤鍦ㄦ诞鐐圭幆澧冧腑锛涘畠琚�瑙ｉ噴涓轰笌娴�鐐规暟绛夋晥銆�

mysql> CREATE TABLE t (b BIT(8));

mysql> INSERT INTO t SET b = b'11111111';

mysql> INSERT INTO t SET b = b'1010';

+------+----------+----------+----------+

| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |

+------+----------+----------+----------+

|  255 | 11111111 | 377      | FF       |

|   10 | 1010     | 12       | A        |

+------+----------+----------+----------+

NULL鍊艰〃绀衡�滄病鏈�鏁版嵁鈥濄��NULL鍙�浠ュ啓鎴愬ぇ鍐欐垨灏忓啓銆�

璇锋敞鎰�NULL鍊间笉鍚屼簬鏁板瓧绫诲瀷鐨�0鎴栧瓧绗︿覆绫诲瀷鐨勭┖瀛楃�︿覆銆傚弬瑙�A.5.3鑺傦紝鈥滀笌NULL鍊兼湁鍏崇殑闂�棰�鈥�銆�

瀵逛簬鐢�LOAD DATA INFILE鎴�SELECT ...INTO OUTFILE鎵ц�岀殑鏂囨湰鏂囦欢瀵煎叆鎴栧�煎嚭鎿嶄綔锛�NULL鐢ㄥ簭鍒�\N琛ㄧず銆傚弬瑙�13.2.5鑺傦紝鈥淟OAD DATA INFILE璇�娉曗��銆�

MySQL鍏佽�镐娇鐢ㄧ敱鍗曚釜璇嗗埆绗︽垨澶氫釜璇嗗埆绗︾粍鎴愮殑鍚嶅瓧銆傚�氶儴鍒嗗悕鍚勭粍浠朵箣闂村簲浠ュ彞鐐�(鈥�.鈥�)闂撮殧寮�銆傚�氶儴鍒嗗悕鐨勫紑澶撮儴鍒嗕綔涓洪檺瀹氳瘝锛屽悗闈㈢殑璇嗗埆绗﹁��瑙ｉ噴銆�

鍦�MySQL涓�鍙�浠ュ紩鐢ㄤ笅闈㈠舰寮忕殑鍒楋細

濡傛灉澶氶儴鍒嗗悕鐨勭粍浠堕渶瑕佸紩鐢�锛屽簲鍒嗗埆灏嗗畠浠�寮曡捣鏉ヨ�屼笉瑕佸皢鏁翠釜鍚嶅紩璧锋潵銆備緥濡傦紝`my-tables`.`my-column` 鏈夋晥锛岃��`my-tables.my-column`鏃犳晥銆�

涓嶉渶瑕佸湪璇�鍙ヤ腑涓哄垪鎸囧畾tbl_name鎴�db_name.tbl_name鍓嶇紑锛岄櫎闈炲垪浼氬緢妯＄硦銆傚亣瀹氳〃t1鍜�t2鍚勫寘鍚�涓�涓�鍒�c锛屼綘浣跨敤SELECT璇�鍙ュ湪t1鍜�t2涓�鎼滅储c銆傚湪杩欑�嶆儏鍐典笅锛�c寰堟ā绯婏紝鍥犱负瀹冨湪璇�鍙ヤ腑浣跨敤鐨勮〃鍐呬笉鍞�涓�銆備綘蹇呴』鐢ㄨ〃鍚�t1.c鎴�t2.c闄愬畾瀹冿紝琛ㄧず鎸囧摢涓�琛ㄣ�傚悓鏍凤紝瑕佹兂鐢ㄥ悓涓�璇�鍙ユ悳绱㈡暟鎹�搴�db1涓�鐨勮〃t鍜屾暟鎹�搴�db2涓�鐨勮〃t锛屼綘蹇呴』灏嗛偅浜涜〃涓�鐨勫垪鎸囦负db1.t.col_name鍜�db2.t.col_name銆�

闄愬畾鍚嶄腑鍙ョ偣鍚庨潰鐨勫瓧蹇呴』涓轰竴涓�璇嗗埆绗︼紝鍥犳�や笉闇�瑕佸皢瀹冨紩璧锋潵锛屽嵆浣挎槸涓�涓�淇濈暀瀛椼��

璇�娉�.tbl_name琛ㄧず褰撳墠鏁版嵁搴撲腑鐨�tbl_name銆傝�ヨ��娉曚笌ODBC鍏煎�癸紝鍥犱负鏌愪簺ODBC绋嬪簭鍦ㄨ〃鍚嶅墠闈㈠姞鍓嶇紑鈥�.鈥欏瓧绗︺��

鍦�MySQL涓�锛屾暟鎹�搴撳�瑰簲鏁版嵁鐩�褰曚腑鐨勭洰褰曘�傛暟鎹�搴撲腑鐨勬瘡涓�琛ㄨ嚦灏戝�瑰簲鏁版嵁搴撶洰褰曚腑鐨勪竴涓�鏂囦欢(涔熷彲鑳芥槸澶氫釜锛屽彇鍐充簬瀛樺偍寮曟搸)銆傚洜姝わ紝鎵�浣跨敤鎿嶄綔绯荤粺鐨勫ぇ灏忓啓鏁忔劅鎬у喅瀹氫簡鏁版嵁搴撳悕鍜岃〃鍚嶇殑澶у皬鍐欐晱鎰熸�с�傝繖璇存槑鍦ㄥぇ澶氭暟Unix涓�鏁版嵁搴撳悕鍜岃〃鍚嶅�瑰ぇ灏忓啓鏁忔劅锛岃�屽湪Windows涓�瀵瑰ぇ灏忓啓涓嶆晱鎰熴�備竴涓�鏄捐憲鐨勪緥澶栨儏鍐垫槸Mac OS X锛屽畠鍩轰簬Unix浣嗕娇鐢ㄩ粯璁ゆ枃浠剁郴缁熺被鍨�(HFS+)锛屽�瑰ぇ灏忓啓涓嶆晱鎰熴�傜劧鑰岋紝Mac OS X涔熸敮鎸�UFS鍗凤紝璇ュ嵎瀵瑰ぇ灏忓啓鏁忔劅锛屽氨鍍�Unix涓�鏍枫�傚弬瑙�1.8.4鑺傦紝鈥淢ySQL瀵规爣鍑哠QL鐨勬墿灞曗��銆�

娉ㄩ噴锛�灏界�″湪鏌愪簺骞冲彴涓�鏁版嵁搴撳悕鍜岃〃鍚嶅�瑰ぇ灏忓啓涓嶆晱鎰燂紝涓嶅簲鍦ㄥ悓涓�鏌ヨ��涓�浣跨敤涓嶅悓鐨勫ぇ灏忓啓鏉ュ紩鐢ㄧ粰瀹氱殑鏁版嵁搴撴垨琛ㄣ�備笅闈㈢殑鏌ヨ��涓嶄細宸ヤ綔锛屽洜涓哄畠鍚屾椂寮曠敤浜嗚〃my_tables鍜�as MY_tables锛�

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

鍒椼�佺储寮曘�佸瓨鍌ㄥ瓙绋嬪簭鍜岃Е鍙戝櫒鍚嶅湪浠讳綍骞冲彴涓婂�瑰ぇ灏忓啓涓嶆晱鎰燂紝鍒楃殑鍒�鍚嶄篃涓嶆晱鎰熴��

榛樿�ゆ儏鍐碉紝琛ㄥ埆鍚嶅湪Unix涓�瀵瑰ぇ灏忓啓鏁忔劅锛屼絾鍦�Windows鎴�Mac OS X涓�瀵瑰ぇ灏忓啓涓嶆晱鎰熴�備笅闈㈢殑鏌ヨ�㈠湪Unix涓�涓嶄細宸ヤ綔锛屽洜涓哄畠鍚屾椂寮曠敤浜嗗埆鍚�a鍜�A锛�

mysql> SELECT col_name FROM tbl_name AS a

    -> WHERE a.col_name = 1 OR A.col_name = 2;

鐒惰�岋紝璇ユ煡璇㈠湪Windows涓�鏄�鍙�浠ョ殑銆傝�佹兂閬垮厤鍑虹幇宸�鍒�锛屾渶濂介噰鐢ㄤ竴鑷寸殑杞�鎹�锛屼緥濡傛�绘槸鐢ㄥ皬鍐欏垱寤哄苟寮曠敤鏁版嵁搴撳悕鍜岃〃鍚嶃�傚湪澶у�氭暟绉绘�嶅拰浣跨敤涓�寤鸿��浣跨敤璇ヨ浆鎹�銆�

鍦�MySQL涓�濡備綍鍦ㄧ‖鐩樹笂淇濆瓨鍜屼娇鐢ㄨ〃鍚嶅拰鏁版嵁搴撳悕鐢�lower_case_tables_name绯荤粺鍙橀噺纭�瀹氾紝鍙�浠ュ湪鍚�鍔�mysqld鏃惰�剧疆銆�lower_case_tables_name鍙�浠ラ噰鐢ㄤ笅闈㈢殑浠讳竴鍊硷細

鍦�Windows鍜�Mac OS X涓�锛�lower_case_tables_name鐨� 榛樿�ゅ�兼槸1銆�

濡傛灉鍙�鍦ㄤ竴涓�骞冲彴涓婁娇鐢�MySQL锛岄�氬父涓嶉渶瑕佹洿鏀�lower_case_tables_name鍙橀噺銆傜劧鑰岋紝濡傛灉浣犳兂瑕佸湪瀵瑰ぇ灏忓啓鏁忔劅涓嶅悓鐨勬枃浠剁郴缁熺殑骞冲彴涔嬮棿杞�绉昏〃锛屼細閬囧埌鍥伴毦銆備緥濡傦紝鍦�Unix涓�锛�my_tables鍜�MY_tables鏄�涓や釜涓嶅悓鐨勮〃锛屼絾鍦�Windows涓�锛岃繖涓や釜琛ㄥ悕鐩稿悓銆傝�佹兂閬垮厤鐢变簬鏁版嵁搴撴垨琛ㄥ悕鐨勫ぇ灏忓啓閫犳垚鐨勬暟鎹�杞�绉婚棶棰橈紝鍙�浣跨敤涓や釜閫夐」锛�

路         鍦ㄤ换浣曠郴缁熶腑鍙�浠ヤ娇鐢�lower_case_tables_name=1銆備娇鐢ㄨ�ラ�夐」鐨勪笉鍒╀箣澶勬槸褰撲娇鐢�SHOW TABLES鎴�SHOW DATABASES鏃讹紝鐪嬩笉鍑哄悕瀛楀師鏉ユ槸鐢ㄥぇ鍐欒繕鏄�灏忓啓銆�

路         鍦�Unix涓�浣跨敤lower_case_tables_name=0锛屽湪Windows涓�浣跨敤lower_case_tables_name=2銆傝繖鏍蜂簡鍙�浠ヤ繚鐣欐暟鎹�搴撳悕鍜岃〃鍚嶇殑澶у皬鍐欍�備笉鍒╀箣澶勬槸蹇呴』纭�淇濆湪Windows涓�鏌ヨ�㈡�绘槸鐢ㄦ�ｇ‘澶у皬鍐欏紩鐢ㄦ暟鎹�搴撳悕鍜岃〃鍚嶃�傚�傛灉灏嗘煡璇㈣浆绉诲埌Unix涓�锛岀敱浜庡湪Unix涓�澶у皬鍐欏緢閲嶈�侊紝濡傛灉澶у皬鍐欎笉姝ｇ‘锛屽畠浠�涓嶅伐浣溿��

渚嬪��锛氬�傛灉浣犳�ｄ娇鐢�InnoDB琛�锛屽湪浠讳綍骞冲彴涓婂潎搴斿皢lower_case_tables_name璁剧疆涓�1锛屼互寮哄埗灏嗗悕杞�鎹�涓哄皬鍐欍��

璇锋敞鎰忓湪Unix涓�灏�lower_case_tables_name璁剧疆涓�1涔嬪墠锛岄噸鍚�mysqld涔嬪墠锛屽繀椤诲厛灏嗘棫鐨勬暟鎹�搴撳悕鍜岃〃鍚嶈浆鎹�涓哄皬鍐欍��

缁撴瀯寮忓彉閲忓湪涓や釜鏂归潰涓嶅悓浜庡父瑙勭郴缁熷彉閲忥細

路         鍏跺�兼槸涓�涓�鍚�缁勪欢鐨勭粨鏋勶紝鍙�浠ユ寚瀹氭湇鍔″櫒鍙傛暟锛屼竴鑸�绱у瘑鐩稿叧銆�

路         鍙�鑳芥槸缁欏畾绫诲瀷鐨勭粨鏋勫紡鍙橀噺鐨勫嚑涓�瀹炰緥銆傛瘡涓�瀹炰緥鏈変竴涓�涓嶅悓鐨勫悕锛屾寚鍚戞湇鍔″櫒缁存姢鐨勪笉鍚岀殑璧勬簮銆�

MySQL 5.1鏀�鎸佺粨鏋勫紡鍙橀噺绫诲瀷锛屽彲浠ユ寚瀹氱洃鎺ч敭鍊肩紦瀛樻搷浣滅殑鍙傛暟銆傞敭鍊肩紦瀛樼粨鏋勫紡鍙橀噺鏈変互涓嬬粍浠讹細

路         key_buffer_size

路         key_cache_block_size

路         key_cache_division_limit

路         key_cache_age_threshold

璇ヨ妭鎻忚堪浜嗗紩鐢ㄧ粨鏋勫紡鍙橀噺鐨勮��娉曘�傚湪璇�娉曠殑渚嬪瓙涓�浣跨敤浜嗛敭鍊肩紦瀛樺彉閲忥紝浣嗗叧浜庨敭鍊肩紦瀛樺�備綍鎿嶄綔鐨勫叿浣撹�︽儏鍦ㄥ叾瀹冪珷鑺備腑锛屽�� 7.4.6鑺傦紝鈥淢yISAM閿�楂橀�熺紦鍐测��銆�

瑕佸紩鐢ㄧ粨鏋勫紡鍙橀噺瀹炰緥鐨勭粍浠讹紝鍙�浠ヤ娇鐢�instance_name.component_name鏍煎紡鐨勫�嶅悎鍚嶃�備緥濡傦細

hot_cache.key_buffer_size

hot_cache.key_cache_block_size

cold_cache.key_cache_block_size

瀵逛簬姣忎釜缁撴瀯寮忕郴缁熷彉閲忥紝鎬绘槸棰勫畾涔夊悕涓�default鐨勪竴涓�瀹炰緥銆傚�傛灉涓嶄娇鐢ㄤ换浣曞疄渚嬪悕寮曠敤缁撴瀯寮忓彉閲忕殑涓�涓�缁勪欢锛�default瀹炰緥琚�浣跨敤銆傝繖鏍凤紝default.key_buffer_size鍜�key_buffer_sizeboth鍧囨寚鍚戝悓涓�绯荤粺鍙橀噺銆�

缁撴瀯寮忓彉閲忓疄渚嬪拰缁勪欢鐨勫懡鍚嶈�勫垯涓猴細

路         瀵逛簬缁欏畾绫诲瀷鐨勭粨鏋勫紡鍙橀噺锛屾瘡涓�瀹炰緥蹇呴』鏈変竴涓�鍦ㄨ�ョ被鍙橀噺涓�鍞�涓�鐨勪竴涓�鍚嶃�備絾鏄�锛屽湪涓嶅悓绫诲瀷鐨勭粨鏋勫紡鍙橀噺涓�瀹炰緥鍚嶄笉闇�瑕佸敮涓�銆備緥濡傦紝姣忎釜缁撴瀯寮忓彉閲忔湁涓�涓�瀹炰緥default锛屽洜姝ゅ湪涓嶅悓鐨勫彉閲忕被鍨嬩腑default涓嶆槸鍞�涓�鐨勩��

路         姣忎釜缁撴瀯寮忓彉閲忕被鍨嬬殑缁勪欢鍚嶅湪鎵�鏈夌郴缁熷彉閲忓悕涓�蹇呴』鏄�鍞�涓�鐨勩�傚�傛灉涓嶆槸杩欐牱(涔熷氨鏄�璇达紝濡傛灉涓や釜涓嶅悓绫诲瀷鐨勭粨鏋勫紡鍙橀噺鑳藉�熷叡浜�缁勪欢鎴愬憳鍚�)锛屽皢涓嶆竻妤氫娇鐢ㄥ摢涓�榛樿�ょ粨鏋勫紡鍙橀噺鏉ョ敤浣滄病鏈変娇鐢ㄥ疄渚嬪悕闄愬畾鐨勬垚鍛樺悕銆�

路         濡傛灉缁撴瀯寮忓彉閲忓疄渚嬪悕浣滀负鏈�寮曡捣鏉ョ殑璇嗗埆绗︿笉鍚堟硶锛屽皢瀹冪敤鍙嶅嬀鍙峰紩璧锋潵浣滀负璇嗗埆绗︺�備緥濡傦紝hot-cache涓嶅悎娉曪紝浣�`hot-cache` 鍚堟硶銆�

路         global銆�session鍜�local涓嶆槸鍚堟硶瀹炰緥鍚嶃�傝繖鏍峰彲浠ラ伩鍏嶄笌寮曠敤闈炵粨鏋勫紡绯荤粺鍙橀噺鐨勭�﹀彿鐨勫啿绐侊紝渚嬪��@@global.var_name銆�

鐩�鍓嶏紝杩欏墠涓や釜瑙勫垯涓嶅彲鑳借繚鑳岋紝鍥犱负鍞�涓�鐨勭粨鏋勫紡鍙橀噺绫诲瀷鏄�閿�鍊肩紦瀛樸�傚湪灏嗘潵鍒涘缓鍏跺畠绫诲瀷鐨勭粨鏋勫紡鍙橀噺锛岃繖浜涜�勫垯灏嗘湁閲嶈�佺殑鎰忎箟銆�

涓�涓�渚嬪�栨槸锛屽彲浠ュ湪鍙�鑳藉嚭鐜扮畝鍗曞彉閲忓悕鐨勪笂涓嬫枃涓�浣跨敤澶嶅悎鍚嶅紩鐢ㄧ粨鏋勫紡鍙橀噺缁勪欢銆備緥濡傦紝鍙�浠ヤ娇鐢ㄤ竴涓�鍛戒护琛岄�夐」鏈�鏌愪釜缁撴瀯寮忓彉閲忓垎閰嶄竴涓�鍊硷細

shell> mysqld --hot_cache.key_buffer_size=64K

鍦ㄩ�夐」鏂囦欢涓�锛屼娇鐢�锛�

[mysqld]

hot_cache.key_buffer_size=64K

濡傛灉鐢ㄨ�ラ�夐」鍚�鍔ㄦ湇鍔″櫒锛岄櫎浜嗛粯璁ゅぇ灏忎负8MB鐨勯粯璁ら敭鍊肩紦瀛橈紝杩樺垱寤轰竴涓�鍚嶄负hot_cache鐨勯敭鍊肩紦瀛橈紝澶у皬涓�64KB銆�

鍋囧畾浣犺繖鏍峰惎鍔ㄦ湇鍔″櫒锛�

shell> mysqld --key_buffer_size=256K \

         --extra_cache.key_buffer_size=128K \

         --extra_cache.key_cache_block_size=2048

鍦ㄨ繖绉嶆儏鍐典笅锛屾湇鍔″櫒灏嗛粯璁ら敭鍊肩紦瀛樼殑澶у皬璁惧畾涓�256KB銆�(涔熷彲浠ュ啓鎴�--default.key_buffer_size=256K锛�銆� 骞朵笖锛屾湇鍔″櫒鍒涘缓涓�涓�鍚嶄负extra_cache鐨勭��2涓�閿�鍊肩紦瀛橈紝澶у皬涓�128KB锛岀紦瀛樿〃绱㈠紩鍧楃殑鍧楃紦瀛樺尯鐨勫ぇ灏忚�剧疆涓�2048瀛楄妭銆�

鍦ㄤ笅闈㈢殑渚嬪瓙涓�锛岀敤3涓�涓嶅悓鐨勯敭鍊肩紦瀛橈紙澶у皬姣斾緥涓�3:1:1锛夊惎鍔ㄦ湇鍔″櫒锛�

shell> mysqld --key_buffer_size=6M \

         --hot_cache.key_buffer_size=2M \

         --cold_cache.key_buffer_size=2M

涔熷彲浠ュ湪杩愯�屾椂璁剧疆鍜屾��绱㈢粨鏋勫紡鍙橀噺鍊笺�備緥濡傦紝瑕佹兂灏嗗悕涓�hot_cache鐨勪竴涓�閿�鍊肩紦瀛樼殑澶у皬璁剧疆涓�10MB锛屼娇鐢ㄤ笅闈�浠讳綍涓�涓�璇�鍙ワ細

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;

mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

瑕佹兂妫�绱㈢紦瀛樺ぇ灏忥紝鎵ц�岋細

mysql> SELECT @@global.hot_cache.key_buffer_size;

浣嗘槸锛屼笅闈㈢殑璇�鍙ヤ笉宸ヤ綔銆傚彉閲忎笉瑙ｉ噴涓轰竴涓�澶嶅悎鍚嶏紝鑰屾槸瑙ｉ噴涓�LIKE妯″紡鍖归厤鎿嶄綔鐨勭畝鍗曞瓧绗︿覆锛�

mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';

杩欐槸涓�涓�鍦ㄥ彲鑳藉嚭鐜扮畝鍗曠殑鍙橀噺鍚嶆椂浣跨敤缁撴瀯寮忓彉閲忓悕鐨勪緥瀛愩��