Appendix聽I.聽Feature Restrictions - MySQL 5.1参考手册中文版

杩欓噷浠嬬粛鐨勬煇浜涢檺鍒堕�傜敤浜庢墍鏈夌殑瀛樺偍瀛愮▼搴忥紝鍗冲瓨鍌ㄧ▼搴忓拰瀛樺偍鍑芥暟銆傛煇浜涢檺鍒朵粎閫傜敤浜庡瓨鍌ㄥ嚱鏁拌�屼笉鏄�瀛樺偍绋嬪簭銆�

瀵瑰瓨鍌ㄥ嚱鏁扮殑鐨勬墍鏈夐檺鍒朵篃閫傜敤浜庤Е鍙戠▼搴忋��

娉ㄩ噴锛�濡傛灉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浜嬪姟锛屼絾鍦ㄤ笉鏇存敼浜岃繘鍒舵棩蹇楁満鍒剁殑鎯呭喌涓嬩笉鑳借繖鏍枫��