Appendix聽I.聽Feature Restrictions - MySQL 5.1参考手册中文版
鍦ㄦ湰闄勫綍涓紝浠嬬粛浜嗕娇鐢ㄨ濡傚瓙鏌ヨ鎴栬鍥剧瓑MySQL鐗规ф椂瀛樺湪鐨勯檺鍒躲
杩欓噷浠嬬粛鐨勬煇浜涢檺鍒堕傜敤浜庢墍鏈夌殑瀛樺偍瀛愮▼搴忥紝鍗冲瓨鍌ㄧ▼搴忓拰瀛樺偍鍑芥暟銆傛煇浜涢檺鍒朵粎閫傜敤浜庡瓨鍌ㄥ嚱鏁拌屼笉鏄瓨鍌ㄧ▼搴忋
瀵瑰瓨鍌ㄥ嚱鏁扮殑鐨勬墍鏈夐檺鍒朵篃閫傜敤浜庤Е鍙戠▼搴忋
娉ㄩ噴锛濡傛灉SQL璇彞锛屽SELECT ... INTO璇彞鍖呭惈鍏锋湁鐩稿悓鍚嶇О鐨瀵瑰垪鐨勫紩鐢浠ュ強澹版槑鐨勫眬閮ㄥ彉閲忥紝MySQL浼氬皢寮曠敤瑙ｉ噴涓哄彉閲忕殑鍚嶇О銆傝繖鏄竴绉嶉潪鏍囧噯鐨勮涓猴紝浼樺厛椤哄簭閫氬父鏄垪鍚嶃佺劧鍚庢槸SQL鍙橀噺鍜屽弬鏁般傝鍙傝20.2.9.3鑺傦紝鈥淪ELECT ... INTO璇彞鈥銆
瀛樺偍瀛愮▼搴忎笉鑳藉寘鍚换鎰SQL璇彞銆傚湪瀛樺偍瀛愮▼搴忎腑锛岀姝娇鐢ㄤ笅杩拌鍙ワ細
路 CHECK TABLES
路 LOCK TABLES, UNLOCK TABLES
路 LOAD DATA, LOAD TABLE
路 SQL棰勫鐞嗚鍙ワ紙PREPARE銆EXECUTE銆DEALLOCATE PREPARE锛夈傞殣鍚剰涔夛細涓嶈兘鍦ㄥ瓨鍌ㄥ瓙绋嬪簭涓娇鐢ㄥ姩鎬SQL璇彞锛堝叾涓紝鑳藉浠ュ瓧绗︿覆褰㈠紡鏋勯犲姩鎬佽鍙ワ紝鐒跺悗鎵ц瀹冧滑锛夈備粠MySQL 5.0.13寮濮嬶紝瀵逛簬瀛樺偍绋嬪簭鏀惧浜嗚闄愬埗锛屼絾璇ラ檺鍒朵粛閫傜敤浜庡瓨鍌ㄥ嚱鏁板拰瑙﹀彂绋嬪簭銆
路 OPTIMIZE TABLE
瀵逛簬瀛樺偍鍑芥暟锛堣屼笉鏄瓨鍌ㄧ▼搴忥級锛岀姝笅杩伴澶栬鍙ワ細
路 鎵ц鏄惧紡鎴栭殣寮忔彁浜ゆ垨鍥炴粴鎿嶄綔鐨勮鍙ャ
路 杩斿洖缁撴灉闆嗙殑璇彞銆傚寘鎷病鏈INFO瀛愬彞鐨SELECT璇彞锛屼互鍙SHOW璇彞銆傝兘澶熺敤SELECT 鈥 INTO锛屾垨浣跨敤鍏夋爣鍜FETCH璇彞澶勭悊缁撴灉闆嗙殑鍑芥暟銆
路 FLUSH璇彞銆傛敞鎰忥紝灏界鑳藉鍦ㄥ瓨鍌ㄧ▼搴忎腑浣跨敤FLUSH锛屼絾涓嶈兘浠庡瓨鍌ㄥ嚱鏁版垨瑙﹀彂绋嬪簭璋冪敤杩欑被瀛樺偍绋嬪簭銆
娉ㄦ剰锛屽敖绠℃煇浜涢檺鍒跺湪姝ｅ父鎯呭喌涓嬮傜敤浜庡瓨鍌ㄥ嚱鏁板拰瑙﹀彂绋嬪簭锛屼笉閫傜敤浜庡瓨鍌ㄧ▼搴忥紝濡傛灉瀹冧滑鏄粠瀛樺偍鍑芥暟鎴栬Е鍙戠▼搴忎腑璋冪敤鐨勶紝杩欎簺闄愬埗涔熼傜敤浜庡瓨鍌ㄧ▼搴忋
浣跨敤瀛樺偍瀛愮▼搴忎細瀵艰嚧澶嶅埗闂銆傚叧浜庤繖鏂归潰鐨勮繘涓姝ヨ璁猴紝璇峰弬瑙20.4鑺傦紝鈥滃瓨鍌ㄥ瓙绋嬪簭鍜岃Е鍙戠▼搴忕殑浜岃繘鍒舵棩蹇楀姛鑳解銆
INFORMATION_SCHEMA灏氫笉鍖呭惈PARAMETERS琛紝鍥犳锛屽浜庨渶瑕佸湪杩愯鏃惰幏鍙栧瓙绋嬪簭鍙傛暟淇℃伅鐨勫簲鐢ㄧ▼搴忔潵璇达紝蹇呴』閲囩敤鐩稿簲鐨勮閬块敊璇紝濡傝В鏋SHOW CREATE璇彞鐨勮緭鍑恒
娌℃湁瀛樺偍瀛愮▼搴忚皟璇曞伐鍏枫
瀛樺偍瀛愮▼搴忎娇鐢ㄤ簡鍏蜂綋鍖栫殑鍏夋爣锛岃屼笉鏄浐鏈夊厜鏍囷紙鍦ㄦ湇鍔″櫒绔敓鎴愮粨鏋滈泦骞跺缁撴灉闆嗚繘琛岄珮閫熺紦鍐插鐞嗭紝鐒跺悗鍦ㄥ鎴风鑾峰彇缁撴灉闆嗘椂鎸夎杩斿洖锛夈
涓嶈兘鎻愬墠澶勭悊CALL璇彞銆傛棤璁烘槸瀵规湇鍔″櫒绔澶勭悊璇彞杩樻槸SQL棰勫鐞嗚鍙ワ紝鍧囨垚绔嬨
涓轰簡闃叉鏈嶅姟鍣ㄧ嚎绋嬮棿鐨勪氦浜掗棶棰橈紝褰撳鎴风鍙戝嚭璇彞鏃讹紝鏈嶅姟鍣ㄥ皢浣跨敤鍙敤鐨勩佺敤浜庤鍙ユ墽琛岀殑瀛愮▼搴忓拰瑙﹀彂绋嬪簭蹇収銆備篃灏辨槸璇达紝鏈嶅姟鍣ㄥ皢璁＄畻鍑哄彲鍦ㄨ鍙ユ墽琛屾湡闂翠娇鐢ㄧ殑瀛樺偍绋嬪簭銆佸嚱鏁板拰瑙﹀彂绋嬪簭鐨勫垪琛紝鍔犺浇瀹冧滑锛岀劧鍚庤繘鍏ヨ鍙ユ墽琛屻傝繖鎰忓懗鐫锛屽湪璇彞鎵ц鐨勫悓鏃讹紝瀹冧笉浼氱湅鍒板叾浠栫嚎绋嬪瀛愮▼搴忔墍浣滅殑鍙樻洿銆
浠MySQL 5.0.2寮濮嬶紝閫氳繃mysql_stmt_attr_set() C API鍑芥暟瀹炵幇浜嗘湇鍔″櫒绔厜鏍囥傛湇鍔″櫒绔厜鏍囧厑璁稿湪鏈嶅姟鍣ㄧ鐢熸垚缁撴灉闆嗭紝浣嗕笉浼氬皢鍏朵紶杈撳埌瀹㈡埛绔紝闄ら潪瀹㈡埛绔姹傝繖浜涜銆備緥濡傦紝濡傛灉瀹㈡埛绔墽琛屼簡鏌ヨ锛屼絾浠呭绗1琛屾劅鍏磋叮锛岄偅涔堜笉浼氫紶杈撳墿浣欑殑琛屻
鍏夋爣鏄彧璇荤殑锛屼笉鑳戒娇鐢ㄥ厜鏍囨潵鏇存柊琛屻
鏈疄鏂UPDATE WHERE CURRENT OF鍜DELETE WHERE CURRENT OF锛岃繖鏄洜涓轰笉鏀寔鍙洿鏂扮殑鍏夋爣銆
鍏夋爣鏄笉鍙繚鎸佺殑锛堟彁浜ゅ悗涓嶅啀淇濇寔鎵撳紑锛夈
鍏夋爣鏄笉鏁忔劅鐨勩
鍏夋爣鏄笉鍙粴鍔ㄧ殑銆
鍏夋爣鏄湭鍛藉悕鐨勩傝鍙ュ鐞嗙▼搴忚捣鐫鍏夋爣ID鐨勪綔鐢ㄣ
瀵逛簬姣忔潯棰勫鐞嗚鍙ワ紝浠呰兘鎵撳紑1涓厜鏍囥傚鏋滈渶瑕佸涓厜鏍囷紝蹇呴』澶勭悊澶氭潯璇彞銆
濡傛灉鍦ㄩ澶勭悊妯″紡涓嬩笉鏀寔璇彞锛屼笉鑳藉湪鐢熸垚缁撴灉闆嗙殑璇彞涓婁娇鐢ㄥ厜鏍囥傚寘鎷CHECK TABLES銆HANDLER READ鍜SHOW BINLOG EVENTS璇彞銆
闅忓悗灏嗘洿姝ｇ殑涓鑷寸己闄凤細濡傛灉灏NULL鍊间笌浣跨敤ALL銆ANY鎴SOME鐨勫瓙鏌ヨ杩涜姣旇緝锛岃屼笖瀛愭煡璇㈣繑鍥炵┖缁撴灉锛屾瘮杈冩搷浣滃皢瀵NULL鐨勯潪鏍囧噯缁撴灉杩涜璇勪及锛岃屼笉鏄TRUE鎴FALSE銆
瀛愭煡璇㈢殑澶栭儴璇彞鍙互鏄SELECT銆INSERT銆UPDATE銆DELETE銆SET鎴DO涓殑浠讳綍涓涓
浠呴儴鍒嗘敮鎸佽姣旇緝鎿嶄綔锛
路 瀵逛簬expr IN (subquery)锛expr鍙互鏄n-tuple锛堥氳繃琛屾瀯閫犵▼搴忚娉曟寚瀹氾級锛岃屼笖瀛愭煡璇㈣兘杩斿洖n-tuples涓銆
路 瀵逛簬expr op {ALL|ANY|SOME} (subquery)锛expr蹇呴』鏄爣搴﹀硷紝瀛愭煡璇㈠繀椤绘槸鍒楀瓙鏌ヨ锛屼笉鑳借繑鍥炲鍒楄銆
鎹㈠彞璇濊锛屽浜庤繑鍥n-tuples琛岀殑瀛愭煡璇紝鏀寔锛
(val_1, ..., val_n) IN (subquery)
浣嗕笉鏀寔锛
(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)
鏀寔閽堝IN鐨勮姣旇緝锛屼絾涓嶆敮鎸侀拡瀵瑰叾浠栫殑琛屾瘮杈冿紝鍘熷洜鍦ㄤ簬锛IN瀹炴柦鏄氳繃灏嗗叾閲嶆柊缂栧啓涓衡=鈥濇瘮杈冨拰AND鎿嶄綔鐨勫簭鍒楀畬鎴愮殑銆傝鏂规硶涓嶈兘鐢ㄤ簬ALL銆ANY鎴SOME銆
鏈壇濂戒紭鍖栬鏋勯犵▼搴忋備笅闈㈢殑涓や釜琛ㄨ揪寮忔槸绛夋晥鐨勶紝浣嗗彧鏈夌2涓〃杈惧紡鑳借浼樺寲锛
(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...
瀵逛簬IN鐨勫瓙鏌ヨ浼樺寲涓嶅瀵光=鈥濈殑浼樺寲閭ｆ牱鏈夋晥銆
瀵逛簬涓嶈壇IN鎬ц兘鐨勪竴绉嶅吀鍨嬫儏鍐垫槸锛屽綋瀛愭煡璇㈣繑鍥炲皯閲忚锛屼絾澶栭儴鏌ヨ杩斿洖灏嗕笌瀛愭煡璇㈢粨鏋滅浉姣旇緝鐨勫ぇ閲忚銆
FROM瀛愬彞涓殑瀛愭煡璇笉鑳戒笌瀛愭煡璇鏈夊叧绯汇傚湪璇勪及澶栭儴鏌ヨ涔嬪墠锛屽皢瀵瑰畠浠繘琛屽叿浣撳寲澶勭悊锛堟墽琛屼互鐢熸垚缁撴灉闆嗭級锛屽洜姝わ紝涓嶈兘鎸夌収澶栭儴鏌ヨ鐨勮瀵瑰畠浠繘琛岃瘎浼般
涓鑸岃█锛屼笉鑳芥洿鏀硅〃锛屽苟浠庡瓙鏌ヨ鍐呯殑鐩稿悓琛ㄨ繘琛岄夋嫨銆備緥濡傦紝璇ラ檺鍒堕傜敤浜庡叿鏈変笅杩板舰寮忕殑璇彞锛
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
渚嬪锛氬鏋滀负FROM瀛愬彞涓洿鏀圭殑琛ㄤ娇鐢ㄥ瓙鏌ヨ锛屽墠杩扮浠ゅ皢涓嶅啀閫傜敤銆備緥濡傦細
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
绂佷护鍦ㄦ涓嶉傜敤锛岃繖鏄洜涓FROM涓殑瀛愭煡璇㈠凡琚叿浣撳寲涓轰复鏃惰〃锛屽洜姝もt鈥濅腑鐨勭浉鍏宠宸插湪婊¤冻鈥t鈥濇潯浠剁殑鎯呭喌涓嬨佸湪鏇存柊鏃惰閫変腑銆
涓庡瓙鏌ヨ鐩告瘮锛岄拡瀵硅仈鍚堢殑浼樺寲绋嬪簭鏇存垚鐔燂紝鍥犳锛屽湪寰堝鎯呭喌涓嬶紝濡傛灉灏嗗叾鏀瑰啓涓join锛堣仈鍚堬級锛屼娇鐢ㄥ瓙鏌ヨ鐨勮鍙ヨ兘澶熸洿鏈夋晥鍦版墽琛屻
浣嗕笅杩版儏褰緥澶栵細IN瀛愭煡璇㈠彲琚敼鍐涓SELECT DISTINCT鑱斿悎銆備緥濡傦細
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);
鍙皢璇ヨ鍙ユ敼鍐欎负锛
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;
浣嗗湪璇ユ儏鍐典笅锛岃仈鍚堥渶瑕侀澶栫殑DISTINCT鎿嶄綔锛岃屼笖涓庡瓙鏌ヨ鐩告瘮锛屾晥鐜囧苟涓嶉珮銆
鍙兘鐨勬湭鏉ヤ紭鍖栵細MySQL涓嶆敼鍐欓拡瀵瑰瓙鏌ヨ璇勪及鐨勮仈鍚堥『搴忋傚湪鏌愪簺鎯呭喌涓嬶紝濡傛灉MySQL灏嗗叾鏀瑰啓涓鸿仈鍚堬紝鑳藉鏇存湁鏁堝湴鎵ц瀛愭煡璇€傝繖鏍凤紝浼樺寲绋嬪簭灏辫兘鍦ㄦ洿澶氱殑鎵ц鏂规闂磋繘琛岄夋嫨銆備緥濡傦紝瀹冭兘鍐冲畾鏄惁棣栧厛璇诲彇鏌愪竴琛ㄦ垨鍏朵粬銆
渚嬪锛
SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
瀵逛簬璇ユ煡璇紝MySQL鎬讳細棣栧厛鎵弿outer_table锛屽鐒跺悗閽堝姣忎竴琛屽湪inner_table涓婃墽琛屽瓙鏌ヨ銆濡傛灉outer_table鏈夊緢澶氳鑰inner_table鍙湁灏戦噺琛岋紝鏌ヨ鐨勬墽琛岄熷害鎴栬瑕佹參浜庢湰搴旀湁鐨勯熷害銆
鍓嶈堪鏌ヨ鍙敼鍐欎负锛
SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;
鍦ㄨ鎯呭喌涓嬶紝鎴戜滑鑳芥壂鎻忓皬鐨勮〃锛inner_table锛夊苟鏌ヨouter_table涓殑琛岋紝濡傛灉鍦鈥渙t.a,ot.b鈥涓婃湁绱㈠紩锛岄熷害浼氭洿蹇
鍙兘鐨勬湭鏉ヤ紭鍖栵細瀵瑰閮ㄦ煡璇㈢殑姣忎竴琛岃瘎浼板叧鑱旂殑瀛愭煡璇€傛洿濂界殑鏂规硶鏄紝濡傛灉澶栭儴琛岀殑鍊间笌涔嬪墠鐨勮鐩告瘮娌℃湁鍙樺寲锛屼笉瀵瑰瓙鏌ヨ杩涜鍐嶆璇勪及锛岃屾槸浣跨敤浠ュ墠鐨勭粨鏋溿
鍙兘鐨勬湭鏉ヤ紭鍖栵細閫氳繃灏嗙粨鏋滃叿浣撳寲鍒颁复鏃惰〃锛岃屼笖璇ヨ〃涓嶄娇鐢ㄧ储寮曪紝瀵FROM瀛愬彞涓殑瀛愭煡璇㈣繘琛岃瘎浼般傚湪鏌ヨ涓笌鍏朵粬琛ㄨ繘琛屾瘮杈冩椂锛屽敖绠″彲鑳芥槸鏈夌敤鐨勶紝浣嗕笉鍏佽浣跨敤绱㈠紩銆
鍙兘鐨勬湭鏉ヤ紭鍖栵細濡傛灉FROM瀛愬彞涓殑瀛愭煡璇㈢被浼间簬鍙柦鍔MERGE绠楁硶鐨勮鍥撅紝鏀瑰啓鏌ヨ骞堕噰鐢MERGE绠楁硶锛屼互渚胯兘澶熶娇鐢ㄧ储寮曘備笅杩拌鍙ュ寘鍚繖绫诲瓙鏌ヨ锛
SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
璇ヨ鍙ュ彲琚敼鍐欎负鑱斿悎锛屽涓嬫墍绀猴細
SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
杩欑被鏀瑰啓鍏锋湁涓や釜浼樼偣锛
1. 閬垮厤浣跨敤閭ｄ簺涓嶈兘浣跨敤绱㈠紩鐨勪复鏃惰〃銆傚湪鏀瑰啓鐨勬煡璇腑锛屼紭鍖栫▼搴忓彲鍦t1涓婁娇鐢ㄧ储寮曘
2. 浼樺寲绋嬪簭鍦ㄩ夋嫨涓嶅悓鐨勬墽琛岃鍒掓柟闈㈠叿鏈夋洿澶х殑鑷敱銆備緥濡傦紝灏嗘煡璇㈡敼鍐欎负鑱斿悎锛岄偅涔堝氨鍏佽浼樺寲绋嬪簭棣栧厛浣跨敤t1鎴t2銆
鍙兘鐨勬湭鏉ヤ紭鍖栵細瀵逛簬娌℃湁鍏宠仈瀛愭煡璇㈢殑IN銆= ANY銆<> ANY銆= ALL銆佷互鍙<> ALL锛屼负缁撴灉浣跨敤鈥鍐呭瓨涓鈥鍝堝笇澶勭悊锛屾垨瀵硅緝澶х殑缁撴灉浣跨敤鍏锋湁绱㈠紩鐨勪复鏃惰〃銆備緥濡傦細
SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM table WHERE condition)
鍦ㄨ鎯呭喌涓嬶紝鍙垱寤轰复鏃惰〃锛
CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM table WHERE condition)
鐒跺悗锛屽big_table涓殑姣忎竴琛岋紝鏍规嵁bt.non_key_field锛屽湪鈥渢鈥涓繘琛岄敭鏌ユ壘銆
瑙嗗浘澶勭悊鍔熻兘姒傚康鏈紭鍖栵細
路 涓嶈兘鍦ㄨ鍥句笂鍒涘缓绱㈠紩銆
路 瀵逛簬浣跨敤MERGE绠楁硶澶勭悊鐨勮鍥撅紝鍙互浣跨敤绱㈠紩銆備絾鏄紝瀵逛簬浣跨敤涓存椂琛ㄧ畻娉曞鐞嗙殑瑙嗗浘锛屼笉鑳藉湪鍏跺熀琛ㄤ笂鍒╃敤绱㈠紩鎻愪緵鐨勪紭鐐癸紙灏界鑳藉鍦ㄤ复鏃惰〃鐨勭敓鎴愯繃绋嬩腑浣跨敤绱㈠紩锛夈
鍦ㄨ鍥剧殑FROM瀛愬彞涓笉鑳戒娇鐢ㄥ瓙鏌ヨ銆傛湭鏉ヨ闄愬埗灏嗚鏀惧銆
瀛樺湪涓涓竴鑸師鍒欙紝涓嶈兘鏇存敼鏌愪竴琛ㄥ苟鍦ㄥ瓙鏌ヨ鐨勭浉鍚岃〃鍐呰繘琛岄夋嫨銆傝鍙傝I.3鑺傦紝鈥滃瀛愭煡璇㈢殑闄愬埗鈥銆
濡傛灉浠庤〃閫夋嫨浜嗚鍥惧苟鎺ョ潃浠庤鍥捐繘琛屼簡閫夋嫨锛屽悓鏍风殑鍘熺悊涔熼傜敤锛屽鏋滃湪瀛愭煡璇腑浠庤〃閫夋嫨浜嗚鍥惧苟浣跨敤MERGE绠楁硶璇勪及浜嗚鍥撅紝涔熷悓鏍枫備緥濡傦細
CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);
UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
濡傛灉瑙嗗浘鏄娇鐢ㄤ复鏃惰〃璇勪及鐨勶紝鍙粠瑙嗗浘瀛愭煡璇腑鐨勮〃杩涜閫夋嫨锛屽苟浠嶈兘鏇存敼澶栭儴鏌ヨ涓殑琛ㄣ傚湪璇ユ儏鍐典笅锛岃鍥惧皢琚叿浣撳寲锛屽洜姝わ紝浣犲疄闄呬笂涓嶈兘浠庡瓙鏌ヨ鐨勮〃涓繘琛岄夋嫨骞垛滃悓鏃垛濇洿鏀瑰畠锛堣繖鏄綘鎵撶畻寮哄埗MySQL浣跨敤涓存椂琛ㄧ畻娉曠殑鍙︿竴鍘熷洜锛屽叾鏂规硶鏄湪瑙嗗浘瀹氫箟涓寚瀹ALGORITHM = TEMPTABLE鍏抽敭瀛锛夈
鍙互浣跨敤DROP TABLE鎴ALTER TABLE鏉ヨ垗寮冩垨鏇存敼瑙嗗浘瀹氫箟涓娇鐢ㄧ殑琛紙瀹冧細鏄鍥惧け鏁堬級锛岃屼笖鑸嶅純鎴栨洿鏀规搷浣滀笉浼氬鑷村憡璀︺浣嗗湪浠ュ悗浣跨敤瑙嗗浘鏃朵細鍑洪敊銆
瑙嗗浘瀹氫箟鏄氳繃鐗瑰畾璇彞鈥滃喕缁撯濈殑锛
路 濡傛灉PREPARE棰勫鐞嗙殑璇彞寮曠敤浜嗚鍥撅紝浠ュ悗姣忔鎵ц璇彞鏃剁湅鍒扮殑瑙嗗浘鍐呭涓庨澶勭悊瑙嗗浘鏃剁殑鍐呭鐩稿悓銆傚嵆浣垮湪璇彞棰勫鐞嗗畬鎴愪箣鍚庛佸湪鎵ц璇彞涔嬪墠鏇存敼浜嗚鍥惧畾涔夛紝鎯呭喌涔熷悓鏍枫備緥濡傦細
路 CREATE VIEW v AS SELECT 1;
路 PREPARE s FROM 'SELECT * FROM v';
路 ALTER VIEW v AS SELECT 2;
路 EXECUTE s;
EXECUTE璇彞杩斿洖鐨勭粨鏋滄槸1锛岃屼笉鏄2銆
路 濡傛灉瀛樺偍瀛愮▼搴忎腑鐨勮鍙ュ紩鐢ㄤ簡瑙嗗浘锛岃鍙ユ墍瑙佸埌鐨勮鍥惧唴瀹逛笌棣栨鎵ц璇彞鏃剁殑鐩稿悓銆傝繖鎰忓懗鐫锛屽鏋滆鍙ユ槸浠ュ惊鐜柟寮忔墽琛岀殑锛岃繘涓姝ョ殑璇彞杩唬瑙佸埌鐨勮鍥惧唴瀹规槸鐩稿悓鐨勶紝鍗充娇鍦ㄥ惊鐜繃绋嬩腑鏇存敼浜嗚鍥惧畾涔変篃鍚屾牱銆備緥濡傦細
路 CREATE VIEW v AS SELECT 1;
路 delimiter //
路 CREATE PROCEDURE p ()
路 BEGIN
路 DECLARE i INT DEFAULT 0;
路 WHILE i < 5 DO
路 SELECT * FROM v;
路 SET i = i + 1;
路 ALTER VIEW v AS SELECT 2;
路 END WHILE;
路 END;
路 //
路 delimiter ;
路 CALL p();
濡傛灉璋冪敤浜嗙▼搴p()锛屾瘡娆￠氳繃寰幆鏃SELECT杩斿洖1锛屽嵆浣垮湪寰幆鍐呮洿鏀逛簡瑙嗗浘瀹氫箟涔熷悓鏍枫
鍏充簬瑙嗗浘鐨勫彲鏇存柊鎬э紝瀵逛簬瑙嗗浘锛屽叾鎬讳綋鐩爣鏄紝濡傛灉浠讳綍瑙嗗浘浠庣悊璁轰笂璁叉槸鍙洿鏂扮殑锛屽湪瀹為檯涓婁篃搴旀槸鍙洿鏂扮殑銆傝繖鍖呮嫭鍦ㄥ叾瀹氫箟涓湁UNION鐨勮鍥俱傜洰鍓嶏紝骞堕潪鎵鏈夌悊璁轰笂鍙洿鏂扮殑瑙嗗浘鍧囪兘琚洿鏂般傛渶鍒濈殑瑙嗗浘瀹炴柦鏈夋剰閲囩敤璇ユ柟寮忥紝涓虹殑鏄敖蹇湴鍦MySQL涓幏寰楁湁鐢ㄧ殑鍙洿鏂拌鍥俱傚緢澶氱悊璁轰笂鍙洿鏂扮殑瑙嗗浘鐜板凡鑳芥洿鏂帮紝浣嗛檺鍒朵緷鐒跺瓨鍦細
路 鍏跺瓙鏌ヨ浣嶄簬WHERE瀛愬彞涔嬪浠讳綍浣嶇疆鐨勫彲鏇存柊瑙嗗浘銆傚浜庢煇浜涘叾瀛愭煡璇綅浜SELECT鍒楄〃涓殑瑙嗗浘锛屼篃鏄彲鏇存柊鐨勩
路 涓嶈兘浣跨敤UPDATE鏉ユ洿鏂板畾涔変负Join鐨勮鍥剧殑1涓互涓婄殑鍩鸿〃銆
路 涓嶈兘浣跨敤DELETE鏉ユ洿鏂板畾涔変负Join鐨勮鍥俱
XA浜嬪姟鏀寔闄愪簬InnoDB瀛樺偍寮曟搸銆
MySQL XA瀹炴柦鏄拡瀵瑰閮XA鐨勶紝鍏朵腑锛MySQL鏈嶅姟鍣ㄤ綔涓鸿祫婧愮鐞嗗櫒锛岃屽鎴风绋嬪簭浣滀负浜嬪姟绠＄悊鍣ㄣ傛湭瀹炴柦鈥鍐呴儴XA鈥濄傝繖鏍凤紝灏卞厑璁MySQL鏈嶅姟鍣ㄥ唴鐨勫崟鐙瓨鍌ㄥ紩鎿庝綔涓RM锛堣祫婧愮鐞嗗櫒锛夛紝鑰屾湇鍔″櫒鏈韩浣滀负TM锛堜簨鍔＄鐞嗗櫒锛夈傚鐞嗗寘鍚1涓互涓婂瓨鍌ㄥ紩鎿庣殑XA浜嬪姟鏃讹紝闇瑕佸唴閮XA銆傚唴閮XA鐨勫疄鏂芥槸涓嶅畬鏁寸殑锛岃繖鏄洜涓猴紝瀹冭姹傚瓨鍌ㄥ紩鎿庡湪琛ㄥ鐞嗙▼搴忓眰闈笂鏀寔涓ら樁娈垫彁浜わ紝鐩墠浠呭InnoDB瀹炵幇浜嗚鐗规с
瀵逛簬XA START锛屼笉鏀寔JOIN鍜RESUME瀛愬彞銆
瀵逛簬XA END锛屼笉鏀寔SUSPEND [FOR MIGRATE]瀛愬彞銆
鍦ㄥ叏灞浜嬪姟鍐咃紝瀵逛簬姣忎釜XA浜嬪姟锛xid鍊肩殑bqual閮ㄥ垎搴旀槸涓嶅悓鐨勶紝璇ヨ姹傛槸瀵瑰綋鍓MySQL XA瀹炴柦鐨勯檺鍒躲傚畠涓嶆槸XA瑙勮寖鐨勭粍鎴愰儴鍒嗐
濡傛灉XA浜嬪姟杈惧埌PREPARED鐘舵佽屼笖MySQL鏈嶅姟鍣ㄥ畷鏈猴紝褰撴湇鍔″櫒閲嶅惎鍚庯紝鑳藉缁х画澶勭悊浜嬪姟銆傚氨鍍忓師鏈簲褰撶殑閭ｆ牱銆備絾鏄紝濡傛灉瀹㈡埛绔繛鎺ヤ腑姝㈣屾湇鍔″櫒缁х画杩愯锛屾湇鍔″櫒灏嗗洖婊氫换浣曟湭瀹屾垚鐨XA浜嬪姟锛屽嵆浣胯浜嬪姟宸茶揪鍒PREPARED鐘舵佷篃鍚屾牱銆傚畠搴旇兘鎻愪氦鎴栧洖婊PREPARED XA浜嬪姟锛屼絾鍦ㄤ笉鏇存敼浜岃繘鍒舵棩蹇楁満鍒剁殑鎯呭喌涓嬩笉鑳借繖鏍枫
杩欐槸MySQL鍙傝冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝冩墜鍐岋紝璇疯闂dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆