Chapter聽22.聽Views - MySQL 5.1参考手册中文版

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

璇ヨ��鍙ョ敤浜庢洿鏀瑰凡鏈夎�嗗浘鐨勫畾涔夈�傚叾璇�娉曚笌CREATE VIEW绫讳技銆傝�峰弬瑙�22.2鑺傦紝鈥淐REATE VIEW璇�娉曗��銆傝�ヨ��鍙ラ渶瑕佸叿鏈夐拡瀵硅�嗗浘鐨�CREATE VIEW鍜�DROP鏉冮檺锛屼篃闇�瑕侀拡瀵�SELECT璇�鍙ヤ腑寮曠敤鐨勬瘡涓�鍒楃殑鏌愪簺鏉冮檺銆�

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

璇ヨ��鍙ヨ兘鍒涘缓鏂扮殑瑙嗗浘锛屽�傛灉缁欏畾浜�OR REPLACE瀛愬彞锛岃�ヨ��鍙ヨ繕鑳�鏇挎崲宸叉湁鐨勮�嗗浘銆�select_statement鏄�涓�绉�SELECT璇�鍙ワ紝瀹冪粰鍑轰簡瑙嗗浘鐨勫畾涔夈�傝�ヨ��鍙ュ彲浠庡熀琛ㄦ垨鍏朵粬瑙嗗浘杩涜�岄�夋嫨銆�

璇ヨ��鍙ヨ�佹眰鍏锋湁閽堝�硅�嗗浘鐨�CREATE VIEW鏉冮檺锛屼互鍙婇拡瀵圭敱SELECT璇�鍙ラ�夋嫨鐨勬瘡涓�鍒椾笂鐨勬煇浜涙潈闄愩��瀵逛簬鍦�SELECT璇�鍙ヤ腑鍏朵粬鍦版柟浣跨敤鐨勫垪锛�蹇呴』鍏锋湁SELECT鏉冮檺銆�濡傛灉杩樻湁OR REPLACE瀛愬彞锛屽繀椤诲湪瑙嗗浘涓婂叿鏈�DROP鏉冮檺銆�

瑙嗗浘灞炰簬鏁版嵁搴撱�傚湪榛樿�ゆ儏鍐典笅锛屽皢鍦ㄥ綋鍓嶆暟鎹�搴撳垱寤烘柊瑙嗗浘銆傝�佹兂鍦ㄧ粰瀹氭暟鎹�搴撲腑鏄庣‘鍒涘缓瑙嗗浘锛屽垱寤烘椂锛屽簲灏嗗悕绉版寚瀹氫负db_name.view_name銆�

mysql> CREATE VIEW test.v AS SELECT * FROM t;

琛ㄥ拰瑙嗗浘鍏变韩鏁版嵁搴撲腑鐩稿悓鐨勫悕绉扮┖闂达紝鍥犳�わ紝鏁版嵁搴撲笉鑳藉寘鍚�鍏锋湁鐩稿悓鍚嶇О鐨勮〃鍜岃�嗗浘銆�

瑙嗗浘蹇呴』鍏锋湁鍞�涓�鐨勫垪鍚嶏紝涓嶅緱鏈夐噸澶嶏紝灏卞儚鍩鸿〃閭ｆ牱銆傞粯璁ゆ儏鍐典笅锛岀敱SELECT璇�鍙ユ��绱㈢殑鍒楀悕灏嗙敤浣滆�嗗浘鍒楀悕銆傝�佹兂涓鸿�嗗浘鍒楀畾涔夋槑纭�鐨勫悕绉帮紝鍙�浣跨敤鍙�閫夌殑column_list瀛愬彞锛屽垪鍑虹敱閫楀彿闅斿紑鐨�ID銆�column_list涓�鐨勫悕绉版暟鐩�蹇呴』绛変簬SELECT璇�鍙ユ��绱㈢殑鍒楁暟銆�

SELECT璇�鍙ユ��绱㈢殑鍒楀彲浠ユ槸瀵硅〃鍒楃殑绠�鍗曞紩鐢ㄣ�備篃鍙�浠ユ槸浣跨敤鍑芥暟銆佸父閲忓�笺�佹搷浣滅�︾瓑鐨勮〃杈惧紡銆�

瀵逛簬SELECT璇�鍙ヤ腑涓嶅悎鏍肩殑琛ㄦ垨瑙嗗浘锛屽皢鏍规嵁榛樿�ょ殑鏁版嵁搴撹繘琛岃В閲娿�傞�氳繃鐢ㄦ伆褰撶殑鏁版嵁搴撳悕绉伴檺瀹氳〃鎴栬�嗗浘鍚嶏紝瑙嗗浘鑳藉�熷紩鐢ㄨ〃鎴栧叾浠栨暟鎹�搴撲腑鐨勮�嗗浘銆�

鑳藉�熶娇鐢ㄥ�氱��SELECT璇�鍙ュ垱寤鸿�嗗浘銆傝�嗗浘鑳藉�熷紩鐢ㄥ熀琛ㄦ垨鍏朵粬瑙嗗浘銆傚畠鑳戒娇鐢ㄨ仈鍚堛��UNION鍜屽瓙鏌ヨ��銆�SELECT鐢氳嚦涓嶉渶寮曠敤浠讳綍琛ㄣ��鍦ㄤ笅闈㈢殑绀轰緥涓�锛屽畾涔変簡浠庡彟涓�琛ㄩ�夋嫨涓ゅ垪鐨勮�嗗浘锛屽苟缁欏嚭浜嗘牴鎹�杩欎簺鍒楄�＄畻鐨勮〃杈惧紡锛�

mysql> CREATE TABLE t (qty INT, price INT);

mysql> INSERT INTO t VALUES(3, 50);

mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

mysql> SELECT * FROM v;

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

| qty  | price | value |

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

|    3 |    50 |   150 |

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

瑙嗗浘瀹氫箟鏈嶄粠涓嬭堪闄愬埗锛�

路         SELECT璇�鍙ヤ笉鑳藉寘鍚�FROM瀛愬彞涓�鐨勫瓙鏌ヨ��銆�

路         SELECT璇�鍙ヤ笉鑳藉紩鐢�绯荤粺鎴栫敤鎴峰彉閲忋��

路         SELECT璇�鍙ヤ笉鑳�寮曠敤棰勫�勭悊璇�鍙ュ弬鏁般��

路         鍦ㄥ瓨鍌ㄥ瓙绋嬪簭鍐咃紝瀹氫箟涓嶈兘寮曠敤瀛愮▼搴忓弬鏁版垨灞�閮ㄥ彉閲忋��

路         鍦ㄥ畾涔変腑寮曠敤鐨勮〃鎴栬�嗗浘蹇呴』瀛樺湪銆備絾鏄�锛屽垱寤轰簡瑙嗗浘鍚庯紝鑳藉�熻垗寮冨畾涔夊紩鐢ㄧ殑琛ㄦ垨瑙嗗浘銆傝�佹兂妫�鏌ヨ�嗗浘瀹氫箟鏄�鍚﹀瓨鍦ㄨ繖绫婚棶棰橈紝鍙�浣跨敤CHECK TABLE璇�鍙ャ��

路         鍦ㄥ畾涔変腑涓嶈兘寮曠敤TEMPORARY琛�锛�涓嶈兘鍒涘缓TEMPORARY瑙嗗浘銆�

路         鍦ㄨ�嗗浘瀹氫箟涓�鍛藉悕鐨勮〃蹇呴』宸插瓨鍦ㄣ��

路         涓嶈兘灏嗚Е鍙戠▼搴忎笌瑙嗗浘鍏宠仈鍦ㄤ竴璧枫��

鍦ㄨ�嗗浘瀹氫箟涓�鍏佽�镐娇鐢�ORDER BY锛屼絾鏄�锛屽�傛灉浠庣壒瀹氳�嗗浘杩涜�屼簡閫夋嫨锛岃�岃�ヨ�嗗浘浣跨敤浜嗗叿鏈夎嚜宸�ORDER BY鐨勮��鍙ワ紝瀹冨皢琚�蹇界暐銆�

瀵逛簬瀹氫箟涓�鐨勫叾浠栭�夐」鎴栧瓙鍙ワ紝瀹冧滑灏嗚��澧炲姞鍒板紩鐢ㄨ�嗗浘鐨勮��鍙ョ殑閫夐」鎴栧瓙鍙ヤ腑锛屼絾鏁堟灉鏈�瀹氫箟銆備緥濡傦紝濡傛灉鍦ㄨ�嗗浘瀹氫箟涓�鍖呭惈LIMIT瀛愬彞锛岃�屼笖浠庣壒瀹氳�嗗浘杩涜�屼簡閫夋嫨锛岃�岃�ヨ�嗗浘浣跨敤浜嗗叿鏈夎嚜宸�LIMIT瀛愬彞鐨勮��鍙�锛岄偅涔堝�逛娇鐢ㄥ摢涓�LIMIT鏈�浣滃畾涔夈�傜浉鍚岀殑鍘熺悊涔熼�傜敤浜庡叾浠栭�夐」锛屽�傝窡鍦�SELECT鍏抽敭瀛楀悗鐨�ALL銆�DISTINCT鎴�SQL_SMALL_RESULT锛�骞堕�傜敤浜庡叾浠栧瓙鍙ワ紝濡�INTO銆�FOR UPDATE銆�LOCK IN SHARE MODE銆佷互鍙�PROCEDURE銆�

濡傛灉鍒涘缓浜嗚�嗗浘锛屽苟閫氳繃鏇存敼绯荤粺鍙橀噺鏇存敼浜嗘煡璇㈠�勭悊鐜�澧冿紝浼氬奖鍝嶄粠瑙嗗浘鑾峰緱鐨勭粨鏋滐細

mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));

Query OK, 0 rows affected (0.00 sec)

mysql> SET NAMES 'latin1';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;

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

| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |

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

| latin1            | latin1_swedish_ci   |

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

1 row in set (0.00 sec)

mysql> SET NAMES 'utf8';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;

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

| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |

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

| utf8              | utf8_general_ci     |

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

1 row in set (0.00 sec)

鍙�閫夌殑ALGORITHM瀛愬彞鏄�瀵规爣鍑�SQL鐨�MySQL鎵╁睍銆�ALGORITHM鍙�鍙栦笁涓�鍊硷細MERGE銆�TEMPTABLE鎴�UNDEFINED銆傚�傛灉娌℃湁ALGORITHM瀛愬彞锛岄粯璁ょ畻娉曟槸UNDEFINED锛堟湭瀹氫箟鐨勶級銆傜畻娉曚細褰卞搷MySQL澶勭悊瑙嗗浘鐨勬柟寮忋��

瀵逛簬MERGE锛屼細灏嗗紩鐢ㄨ�嗗浘鐨勮��鍙ョ殑鏂囨湰涓庤�嗗浘瀹氫箟鍚堝苟璧锋潵锛屼娇寰楄�嗗浘瀹氫箟鐨勬煇涓�閮ㄥ垎鍙栦唬璇�鍙ョ殑瀵瑰簲閮ㄥ垎銆�

瀵逛簬TEMPTABLE锛岃�嗗浘鐨勭粨鏋滃皢琚�缃�浜庝复鏃惰〃涓�锛岀劧鍚庝娇鐢ㄥ畠鎵ц�岃��鍙ャ��

瀵逛簬UNDEFINED锛�MySQL灏嗛�夋嫨鎵�瑕佷娇鐢ㄧ殑绠楁硶銆傚�傛灉鍙�鑳斤紝瀹冨�惧悜浜�MERGE鑰屼笉鏄�TEMPTABLE锛岃繖鏄�鍥犱负MERGE閫氬父鏇存湁鏁堬紝鑰屼笖濡傛灉浣跨敤浜嗕复鏃惰〃锛岃�嗗浘鏄�涓嶅彲鏇存柊鐨勩��

鏄庣‘閫夋嫨TEMPTABLE鐨�1涓�鍘熷洜鍦ㄤ簬锛屽垱寤轰复鏃惰〃涔嬪悗銆佸苟鍦ㄥ畬鎴愯��鍙ュ�勭悊涔嬪墠锛岃兘澶熼噴鏀惧熀琛ㄤ笂鐨勯攣瀹氥�備笌MERGE绠楁硶鐩告瘮锛岄攣瀹氶噴鏀剧殑閫熷害鏇村揩锛岃繖鏍凤紝浣跨敤瑙嗗浘鐨勫叾浠栧�㈡埛绔�涓嶄細琚�灞忚斀杩囬暱鏃堕棿銆�

瑙嗗浘绠楁硶鍙�浠ユ槸UNDEFINED锛屾湁涓夌�嶆柟寮忥細

路         鍦�CREATE VIEW璇�鍙ヤ腑娌℃湁ALGORITHM瀛愬彞銆�

路         CREATE VIEW璇�鍙ユ湁1涓�鏄惧紡ALGORITHM = UNDEFINED瀛愬彞銆�

路         涓轰粎鑳界敤涓存椂琛ㄥ�勭悊鐨勮�嗗浘鎸囧畾ALGORITHM = MERGE銆傚湪杩欑�嶆儏鍐典笅锛�MySQL灏嗙敓鎴愬憡璀︼紝骞跺皢绠楁硶璁剧疆涓�UNDEFINED銆�

姝ｅ�傚墠闈㈡墍浠嬬粛鐨勯偅鏍凤紝閫氳繃灏嗚�嗗浘瀹氫箟涓�鐨勫�瑰簲閮ㄥ垎鍚堝苟鍒板紩鐢ㄨ�嗗浘鐨勮��鍙ヤ腑锛屽��MERGE杩涜�屽�勭悊銆傚湪涓嬮潰鐨勭ず渚嬩腑锛岀畝瑕佷粙缁嶄簡MERGE鐨勫伐浣滄柟寮忋�傚湪璇ョず渚嬩腑锛屽亣瀹氭湁1涓�鍏锋湁涓嬭堪瀹氫箟鐨�瑙嗗浘v_merge锛�

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS

SELECT c1, c2 FROM t WHERE c3 > 100;

绀轰緥1锛氬亣瀹氬彂鍑轰簡涓嬭堪璇�鍙ワ細

SELECT * FROM v_merge;

MySQL浠ヤ笅杩版柟寮忓�勭悊璇�鍙ワ細

路         v_merge鎴愪负t

路         *鎴愪负vc1銆�vc2锛�涓�c1銆�c2瀵瑰簲

路         澧炲姞瑙嗗浘WHERE瀛愬彞

鎵�浜х敓鐨勫皢鎵ц�岀殑璇�鍙ヤ负锛�

SELECT c1, c2 FROM t WHERE c3 > 100;

绀轰緥2锛氬亣瀹氬彂鍑轰簡涓嬭堪璇�鍙ワ細

SELECT * FROM v_merge WHERE vc1 < 100;

璇ヨ��鍙ョ殑澶勭悊鏂瑰紡涓庡墠闈�浠嬬粛鐨勭被浼硷紝浣�vc1 < 100鍙樹负c1 < 100锛�骞朵娇鐢�AND杩炴帴璇嶅皢瑙嗗浘鐨�WHERE瀛愬彞娣诲姞鍒拌��鍙ョ殑WHERE瀛愬彞涓�锛堝�炲姞浜嗗渾鎷�鍙蜂互纭�淇濅互姝ｇ‘鐨勪紭鍏堥『搴忔墽琛屽瓙鍙ラ儴鍒嗭級銆傛墍寰楃殑灏嗚�佹墽琛岀殑璇�鍙ュ彉涓猴細

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

浜嬪疄涓婏紝灏嗚�佹墽琛岀殑璇�鍙ユ槸鍏锋湁涓嬭堪褰㈠紡鐨�WHERE瀛愬彞锛�

WHERE (select WHERE) AND (view WHERE)

MERGE绠楁硶瑕佹眰瑙嗗浘涓�鐨勮�屽拰鍩鸿〃涓�鐨勮�屽叿鏈変竴瀵逛竴鐨勫叧绯汇�傚�傛灉涓嶅叿鏈夎�ュ叧绯汇�傚繀椤讳娇鐢ㄤ复鏃惰〃鍙栬�屼唬涔嬨�傚�傛灉瑙嗗浘鍖呭惈涓嬭堪缁撴瀯涓�鐨勪换浣曚竴绉嶏紝灏嗗け鍘讳竴瀵逛竴鐨勫叧绯伙細

路         鑱氬悎鍑芥暟锛�SUM(), MIN(), MAX(), COUNT()绛夛級銆�

路         DISTINCT

路         GROUP BY

路         HAVING

路         UNION鎴�UNION ALL

路         浠呭紩鐢ㄦ枃瀛楀�硷紙鍦ㄨ�ユ儏鍐典笅锛屾病鏈夊熀鏈�琛�锛夈��

鏌愪簺瑙嗗浘鏄�鍙�鏇存柊鐨勩�備篃灏辨槸璇达紝鍙�浠ュ湪璇稿��UPDATE銆�DELETE鎴�INSERT绛夎��鍙ヤ腑浣跨敤瀹冧滑锛屼互鏇存柊鍩鸿〃鐨勫唴瀹广�傚�逛簬鍙�鏇存柊鐨勮�嗗浘锛屽湪瑙嗗浘涓�鐨勮�屽拰鍩鸿〃涓�鐨勮�屼箣闂村繀椤诲叿鏈変竴瀵逛竴鐨勫叧绯�銆傝繕鏈変竴浜涚壒瀹氱殑鍏朵粬缁撴瀯锛岃繖绫荤粨鏋勪細浣垮緱瑙嗗浘涓嶅彲鏇存柊銆傛洿鍏蜂綋鍦拌�诧紝濡傛灉瑙嗗浘鍖呭惈涓嬭堪缁撴瀯涓�鐨勪换浣曚竴绉嶏紝閭ｄ箞瀹冨氨鏄�涓嶅彲鏇存柊鐨勶細

路         鑱氬悎鍑芥暟锛�SUM(), MIN(), MAX(), COUNT()绛夛級銆�

路         DISTINCT

路         GROUP BY

路         HAVING

路         UNION鎴�UNION ALL

路         浣嶄簬閫夋嫨鍒楄〃涓�鐨勫瓙鏌ヨ��

路         Join

路         FROM瀛愬彞涓�鐨勪笉鍙�鏇存柊瑙嗗浘

路         WHERE瀛愬彞涓�鐨勫瓙鏌ヨ��锛屽紩鐢�FROM瀛愬彞涓�鐨勮〃銆�

路         浠呭紩鐢ㄦ枃瀛楀�硷紙鍦ㄨ�ユ儏鍐典笅锛屾病鏈夎�佹洿鏂扮殑鍩烘湰琛�锛夈��

路         ALGORITHM = TEMPTABLE锛堜娇鐢ㄤ复鏃惰〃鎬讳細浣胯�嗗浘鎴愪负涓嶅彲鏇存柊鐨勶級銆�

鍏充簬鍙�鎻掑叆鎬э紙鍙�鐢�INSERT璇�鍙ユ洿鏂帮級锛屽�傛灉瀹冧篃婊¤冻鍏充簬瑙嗗浘鍒楃殑涓嬭堪棰濆�栬�佹眰锛屽彲鏇存柊鐨勮�嗗浘涔熸槸鍙�鎻掑叆鐨勶細

路         涓嶅緱鏈夐噸澶嶇殑瑙嗗浘鍒楀悕绉般��

路         瑙嗗浘蹇呴』鍖呭惈娌℃湁榛樿�ゅ�肩殑鍩鸿〃涓�鐨勬墍鏈夊垪銆�

路         瑙嗗浘鍒楀繀椤绘槸绠�鍗曠殑鍒楀紩鐢ㄨ�屼笉鏄�瀵煎嚭鍒椼�傚�煎嚭鍒椾笉鏄�绠�鍗曠殑鍒楀紩鐢�锛岃�屾槸浠庤〃杈惧紡瀵煎嚭鐨勩�備笅闈㈢粰鍑轰簡涓�浜涘�煎嚭鍒楃ず渚嬶細

路                3.14159

路                col1 + 3

路                UPPER(col2)

路                col3 / col4

路                (subquery)

娣峰悎浜嗙畝鍗曞垪寮曠敤鍜屽�煎嚭鍒楃殑瑙嗗浘鏄�涓嶅彲鎻掑叆鐨勶紝浣嗘槸锛屽�傛灉浠呮洿鏂伴潪瀵煎嚭鍒楋紝瑙嗗浘鏄�鍙�鏇存柊鐨勩�傝�冭檻涓嬭堪瑙嗗浘锛�

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

璇ヨ�嗗浘鏄�涓嶅彲鎻掑叆鐨勶紝杩欐槸鍥犱负col2鏄�浠庤〃杈惧紡瀵煎嚭鐨勩�備絾鏄�锛屽�傛灉鏇存柊鏃朵笉鏇存柊col2锛屽畠鏄�鍙�鏇存柊鐨勩�傝繖绫绘洿鏂版槸鍏佽�哥殑锛�

UPDATE v SET col1 = 0;

涓嬭堪鏇存柊鏄�涓嶅厑璁哥殑锛屽師鍥犲湪浜庯紝瀹冭瘯鍥炬洿鏂板�煎嚭鍒楋細

UPDATE v SET col2 = 0;

鍦ㄦ煇浜涙儏鍐典笅锛岃兘澶熸洿鏂板�氳〃瑙嗗浘锛屽亣瀹氬畠鑳戒娇鐢�MERGE绠楁硶杩涜�屽�勭悊銆備负姝わ紝瑙嗗浘蹇呴』浣跨敤鍐呴儴鑱斿悎锛堣�屼笉鏄�澶栭儴鑱斿悎鎴�UNION锛夈��姝ゅ�栵紝浠呰兘鏇存柊瑙嗗浘瀹氫箟涓�鐨勫崟涓�琛�锛屽洜姝わ紝SET瀛愬彞蹇呴』浠呭懡鍚嶈�嗗浘涓�鏌愪竴琛ㄧ殑鍒椼�傚嵆浣夸粠鐞嗚�轰笂璁蹭篃鏄�鍙�鏇存柊鐨勶紝涓嶅厑璁镐娇鐢�UNION ALL鐨勮�嗗浘锛岃繖鏄�鍥犱负锛屽湪瀹炴柦涓�灏嗕娇鐢ㄤ复鏃惰〃鏉ュ�勭悊瀹冧滑銆�

瀵逛簬澶氳〃鍙�鏇存柊瑙嗗浘锛屽�傛灉鏄�灏嗗叾鎻掑叆鍗曚釜琛ㄤ腑锛�INSERT鑳藉�熷伐浣溿��涓嶆敮鎸�DELETE銆�

瀵逛簬鍙�鏇存柊瑙嗗浘锛屽彲缁欏畾WITH CHECK OPTION瀛愬彞鏉ラ槻姝㈡彃鍏ユ垨鏇存柊琛岋紝闄ら潪浣滅敤鍦ㄨ�屼笂鐨�select_statement涓�鐨�WHERE瀛愬彞涓衡�滅湡鈥濄��

鍦ㄥ叧浜庡彲鏇存柊瑙嗗浘鐨�WITH CHECK OPTION瀛愬彞涓�锛屽綋瑙嗗浘鏄�鏍规嵁鍙︿竴涓�瑙嗗浘瀹氫箟鐨勬椂锛�LOCAL鍜�CASCADED鍏抽敭瀛�鍐冲畾浜嗘��鏌ユ祴璇曠殑鑼冨洿銆�LOCAL鍏抽敭瀛楀��CHECK OPTION杩涜�屼簡闄愬埗锛屼娇鍏朵粎浣滅敤鍦ㄥ畾涔夌殑瑙嗗浘涓婏紝CASCADED浼氬�瑰皢杩涜�岃瘎浼扮殑鍩鸿〃杩涜�屾��鏌ャ�傚�傛灉鏈�缁欏畾浠讳竴鍏抽敭瀛楋紝榛樿�ゅ�间负CASCADED銆傝�疯�冭檻涓嬭堪琛ㄥ拰瑙嗗浘闆嗗悎鐨勫畾涔夛細

mysql> CREATE TABLE t1 (a INT);

mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2

    -> WITH CHECK OPTION;

mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0

    -> WITH LOCAL CHECK OPTION;

mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0

    -> WITH CASCADED CHECK OPTION;

杩欓噷锛岃�嗗浘v2鍜�v3鏄�鏍规嵁鍙︿竴瑙嗗浘v1瀹氫箟鐨�銆�v2鍏锋湁LOCAL妫�鏌ラ�夐」锛屽洜姝わ紝浠呬細閽堝��v2妫�鏌ュ�规彃鍏ラ」杩涜�屾祴璇曘��v3鍏锋湁CASCADED妫�鏌ラ�夐」锛屽洜姝わ紝涓嶄粎浼氶拡瀵瑰畠鑷�宸辩殑妫�鏌ュ�规彃鍏ラ」杩涜�屾祴璇曪紝涔熶細閽堝�瑰熀鏈�瑙嗗浘鐨勬��鏌�瀵规彃鍏ラ」杩涜�屾祴璇�銆傚湪涓嬮潰鐨勮��鍙ヤ腑锛屼粙缁嶄簡杩欎簺宸�寮傦細

ql> INSERT INTO v2 VALUES (2);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v3 VALUES (2);

ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

瑙嗗浘鐨勫彲鏇存柊鎬у彲鑳戒細鍙楀埌绯荤粺鍙橀噺updatable_views_with_limit鐨勫�肩殑褰卞搷銆�璇峰弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

INFORMATION_SCHEMA鍖呭惈1涓�VIEWS琛�锛屼粠璇ヨ〃鍙�鑾峰彇鍏充簬瑙嗗浘瀵硅薄鐨勪俊鎭�銆傝�峰弬瑙�23.1.15鑺傦紝鈥淚NFORMATION_SCHEMA VIEWS琛ㄢ��銆�

DROP VIEW [IF EXISTS]

    view_name [, view_name] ...

    [RESTRICT | CASCADE]

DROP VIEW鑳藉�熷垹闄�1涓�鎴栧�氫釜瑙嗗浘銆傚繀椤诲湪姣忎釜瑙嗗浘涓婃嫢鏈�DROP鏉冮檺銆�

鍙�浠ヤ娇鐢ㄥ叧閿�瀛�IF EXISTS鏉ラ槻姝�鍥犱笉瀛樺湪鐨勮�嗗浘鑰�鍑洪敊銆�缁欏畾浜嗚�ュ瓙鍙ユ椂锛屽皢涓烘瘡涓�涓嶅瓨鍦ㄧ殑瑙嗗浘鐢熸垚NOTE銆�璇峰弬瑙�13.5.4.22鑺傦紝鈥淪HOW WARNINGS璇�娉曗��銆�

濡傛灉缁欏畾浜�RESTRICT鍜�CASCADE锛屽皢瑙ｆ瀽骞跺拷鐣ュ畠浠�銆�

SHOW CREATE VIEW view_name

璇ヨ��鍙ョ粰鍑轰簡1涓�鍒涘缓缁欏畾瑙嗗浘鐨�CREATE VIEW璇�鍙ャ��

mysql> SHOW CREATE VIEW v;

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

| View | Create View                                        |

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

| v    | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` |

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

杩欐槸MySQL鍙傝�冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝�冩墜鍐岋紝璇疯�块棶dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝�冩墜鍐岀浉姣旓紝鏈�缈昏瘧鐗堝彲鑳戒笉鏄�鏈�鏂扮殑銆�