Chapter 20. Stored Procedures and Functions - MySQL 5.1参考手册中文版

·         鍒涘缓瀛樺偍瀛愮▼搴忛渶瑕丆REATE ROUTINE鏉冮檺銆�

·         鎻愰啋鎴栫Щ闄ゅ瓨鍌ㄥ瓙绋嬪簭闇�瑕丄LTER ROUTINE鏉冮檺銆傝繖涓�鏉冮檺鑷�鍔ㄦ巿浜堝瓙绋嬪簭鐨勫垱寤鸿�呫��

·         鎵ц�屽瓙绋嬪簭闇�瑕丒XECUTE鏉冮檺銆傜劧鑰岋紝杩欎釜鏉冮檺鑷�鍔ㄦ巿浜� 瀛愮▼搴忕殑鍒涘缓鑰呫�傚悓鏍凤紝瀛愮▼搴忛粯璁ょ殑SQL SECURITY 鐗瑰緛鏄疍EFINER锛屽畠鍏佽�哥敤璇ュ瓙绋嬪簭璁块棶鏁版嵁搴撶殑鐢ㄦ埛涓庢墽琛屽瓙绋嬪簭鑱旂郴鍒颁竴璧枫��

20.2.1. CREATE PROCEDURE鍜孋REATE FUNCTION

20.2.2. ALTER PROCEDURE鍜孉LTER FUNCTION

20.2.3. DROP PROCEDURE鍜孌ROP FUNCTION

20.2.4. SHOW CREATE PROCEDURE鍜孲HOW CREATE FUNCTION

20.2.5. SHOW PROCEDURE STATUS鍜孲HOW FUNCTION STATUS

20.2.6. CALL璇�鍙�

20.2.7. BEGIN ... END澶嶅悎璇�鍙�

20.2.8. DECLARE璇�鍙�

20.2.9. 瀛樺偍绋嬪簭涓�鐨勫彉閲�

20.2.10. 鏉′欢鍜屽�勭悊绋嬪簭

20.2.11. 鍏夋爣

20.2.12. 娴佺▼鎺у埗鏋勯��

·         褰撲竴涓�瀛愮▼搴忚��璋冪敤鏃讹紝涓�涓�闅愬惈鐨刄SE db_name 琚�鎵ц�岋紙褰撳瓙绋嬪簭缁堟�㈡椂鍋滄�㈡墽琛岋級銆�瀛樺偍瀛愮▼搴忓唴鐨�USE璇�鍙ユ椂涓嶅厑璁哥殑銆�

·         浣犲彲浠ヤ娇鐢ㄦ暟鎹�搴撳悕闄愬畾瀛愮▼搴忓悕銆傝繖鍙�浠ヨ��鐢ㄦ潵寮曠敤涓�涓�涓嶅湪褰撳墠鏁版嵁搴撲腑鐨勫瓙绋嬪簭銆傛瘮濡傦紝瑕佸紩鐢ㄤ竴涓�涓巘est鏁版嵁搴撳叧鑱旂殑瀛樺偍绋嬪簭p鎴栧嚱鏁癴锛屼綘鍙�浠ヨ�碈ALL test.p()鎴杢est.f()銆�

·         鏁版嵁搴撶Щ闄ょ殑鏃跺�欙紝涓庡畠鍏宠仈鐨勬墍鏈夊瓨鍌ㄥ瓙绋嬪簭涔熼兘琚�绉婚櫎銆�

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

    proc_parameter:

    [ IN | OUT | INOUT ] param_name type

    func_parameter:

    param_name type

type:

    Any valid MySQL data type

characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

routine_body:

    Valid SQL procedure statement or statements

杩欎簺璇�鍙ュ垱寤哄瓨鍌ㄥ瓙绋嬪簭銆傝�佸湪MySQL 5.1涓�鍒涘缓瀛愮▼搴忥紝蹇呴』鍏锋湁CREATE ROUTINE鏉冮檺锛屽苟涓擜LTER ROUTINE鍜孍XECUTE鏉冮檺琚�鑷�鍔ㄦ巿浜堝畠鐨勫垱寤鸿�呫�傚�傛灉浜岃繘鍒舵棩蹇楀姛鑳借��鍏佽�革紝浣犱篃鍙�鑳介渶瑕�SUPER鏉冮檺锛岃�峰弬闃�20.4鑺�锛�鈥滃瓨鍌ㄥ瓙绋嬪簭鍜岃Е鍙戠▼搴忕殑浜岃繘鍒舵棩蹇楀姛鑳�”銆�

榛樿�ゅ湴锛屽瓙绋嬪簭涓庡綋鍓嶆暟鎹�搴撳叧鑱斻�傝�佹槑纭�鍦版妸瀛愮▼搴忎笌涓�涓�缁欏畾鏁版嵁搴撳叧鑱旇捣鏉ワ紝鍙�浠ュ湪鍒涘缓瀛愮▼搴忕殑鏃跺�欐寚瀹氬叾鍚嶅瓧涓�db_name.sp_name銆�

濡傛灉瀛愮▼搴忓悕鍜屽唴寤虹殑SQL鍑芥暟鍚嶄竴鏍凤紝瀹氫箟瀛愮▼搴忔椂锛屼綘闇�瑕佸湪杩欎釜鍚嶅瓧鍜岄殢鍚庢嫭鍙蜂腑闂存彃鍏ヤ竴涓�绌烘牸锛屽惁鍒欏彂鐢熻��娉曢敊璇�銆傚綋浣犻殢鍚庤皟鐢ㄥ瓙绋嬪簭鐨勬椂鍊欎篃瑕佹彃鍏ャ�備负姝わ紝鍗充娇鏈夊彲鑳藉嚭鐜拌繖绉嶆儏鍐碉紝鎴戜滑杩樻槸寤鸿��鏈�濂介伩鍏嶇粰浣犺嚜宸辩殑 瀛樺偍瀛愮▼搴忓彇涓庡瓨鍦ㄧ殑SQL鍑芥暟涓�鏍风殑鍚嶅瓧銆�

鐢辨嫭鍙峰寘鍥寸殑鍙傛暟鍒楀繀椤绘�绘槸瀛樺湪銆傚�傛灉娌℃湁鍙傛暟锛屼篃璇ヤ娇鐢ㄤ竴涓�绌哄弬鏁板垪()銆傛瘡涓�鍙傛暟 榛樿�ら兘鏄�涓�涓狪N鍙傛暟銆傝�佹寚瀹氫负鍏跺畠鍙傛暟锛屽彲鍦ㄥ弬鏁板悕涔嬪墠浣跨敤鍏抽敭璇� OUT鎴朓NOUT

娉ㄦ剰: 鎸囧畾鍙傛暟涓篒N, OUT, 鎴朓NOUT 鍙�瀵筆ROCEDURE鏄�鍚堟硶鐨勩�傦紙FUNCTION鍙傛暟鎬绘槸琚�璁や负鏄疘N鍙傛暟锛�

RETURNS瀛楀彞鍙�鑳藉�笷UNCTION鍋�鎸囧畾锛屽�瑰嚱鏁拌�岃█杩欐槸寮哄埗鐨勩�傚畠鐢ㄦ潵鎸囧畾鍑芥暟鐨勮繑鍥炵被鍨嬶紝鑰屼笖鍑芥暟浣撳繀椤诲寘鍚�涓�涓�RETURN value璇�鍙ャ��

routine_body 鍖呭惈鍚堟硶鐨凷QL杩囩▼璇�鍙ャ�傚彲浠ヤ娇鐢ㄥ�嶅悎璇�鍙ヨ��娉曪紝璇峰弬闃�20.2.7鑺傦紝鈥淏EGIN ... END澶嶅悎璇�鍙�”銆傚�嶅悎璇�鍙ュ彲浠ュ寘鍚� 澹版槑锛屽惊鐜�鍜屽叾瀹冩帶鍒剁粨鏋勮��鍙ャ�傝繖浜涜��鍙ョ殑璇�娉曞湪鏈�绔犲悗鍏嶄粙缁嶏紝涓句緥锛岃�峰弬闃�20.2.8鑺傦紝鈥淒ECLARE璇�鍙�”鍜�20.2.12鑺傦紝鈥�娴佺▼鎺у埗鏋勯�犫��銆�

CREATE FUNCTION璇�鍙ヨ��鐢ㄥ湪鏇存棭鐨凪ySQL鐗堟湰涓�浠�鏀�鎸乁DF 锛堣嚜瀹氫箟鍑芥暟锛夈�傝�峰弬闃�27.2鑺傦紝鈥�缁橫ySQL娣诲姞鏂板嚱鏁�”銆� UDF缁х画琚�鏀�鎸侊紝鍗充娇鐜板湪 鏈変簡瀛樺偍鍑芥暟銆俇DF浼氳��璁や负涓�涓�澶栭儴瀛樺偍鍑芥暟銆傜劧鑰岋紝涓嶈�佽�╁瓨鍌ㄥ嚱鏁颁笌UDF鍑芥暟鍏变韩鍚嶅瓧绌洪棿銆�

澶栭儴瀛樺偍绋嬪簭鐨勬�嗘灦灏嗗湪涓嶄箙鐨勫皢鏉ュ紩鍏ャ�傝繖灏嗗厑璁镐綘鐢�SQL涔嬪�栫殑璇�瑷�缂栧啓瀛樺偍绋嬪簭銆傛渶鍙�鑳界殑鏄�锛岀��涓�涓�琚�鏀�鎸佽��瑷�鏄疨HP锛屽洜涓烘牳蹇働HP寮曟搸寰堝皬锛岀嚎绋嬪畨鍏�锛屼笖鍙�浠ヨ��鏂逛究鍦板祵鍏ャ�傚洜涓烘�嗘灦鏄�鍏�寮�鐨勶紝瀹冨笇鏈涜�稿�氬叾瀹冭��瑷�涔熻兘琚�鏀�鎸併��

濡傛灉绋嬪簭鎴栫嚎绋嬫�绘槸瀵瑰悓鏍风殑杈撳叆鍙傛暟浜х敓鍚屾牱鐨勭粨鏋滐紝鍒欒��璁や负瀹冩槸“纭�瀹氱殑”锛屽惁鍒欏氨鏄�“闈炵‘瀹�”鐨勩�傚�傛灉鏃㈡病鏈夌粰瀹欴ETERMINISTIC涔熸病鏈夌粰瀹歂OT DETERMINISTIC锛岄粯璁ょ殑灏辨槸NOT DETERMINISTIC銆�

涓鸿繘琛屽�嶅埗锛屼娇鐢∟OW()鍑芥暟锛堟垨瀹冪殑鍚屼箟璇嶏級鎴�RAND()鍑芥暟浼氫笉蹇呰�佸湴浣垮緱涓�涓�瀛愮▼搴忛潪纭�瀹氥�傚�筃OW()鑰岃█锛屼簩杩涘埗鏃ュ織鍖呮嫭鏃堕棿鎴冲苟琚�姝ｇ‘澶嶅埗銆�RAND() 鍙�瑕佸湪涓�涓�瀛愮▼搴忚��鍐呭簲鐢ㄤ竴娆′篃浼氳��姝ｇ‘澶嶅埗銆傦紙浣犲彲浠ユ妸瀛愮▼搴忔墽琛屾椂闂存埑鍜岄殢鏈烘暟绉嶅瓙璁や负寮哄埗杈撳叆锛屽畠浠�鍦ㄤ富浠庝笂鏄�鍚屾牱鐨勩�傦級

褰撳墠鏉ヨ�诧紝DETERMINISTIC鐗瑰緛琚�鎺ュ彈锛屼絾杩樻病鏈夎��浼樺寲绋嬪簭鎵�浣跨敤銆傜劧鑰屽�傛灉浜岃繘鍒舵棩蹇楀姛鑳借��鍏佽�镐簡锛岃繖涓�鐗瑰緛褰卞搷鍒�MySQL鏄�鍚︿細鎺ュ彈瀛愮▼搴忓畾涔夈��璇峰弬闃�20.4鑺�锛屸��瀛樺偍瀛愮▼搴忓拰瑙﹀彂绋嬪簭鐨勪簩杩涘埗鏃ュ織鍔熻兘”銆�

涓�浜涚壒寰佹彁渚涘瓙绋嬪簭浣跨敤鏁版嵁鐨勫唴鍦ㄤ俊鎭�銆侰ONTAINS SQL琛ㄧず瀛愮▼搴忎笉鍖呭惈璇绘垨鍐欐暟鎹�鐨勮��鍙ャ�侼O SQL琛ㄧず瀛愮▼搴忎笉鍖呭惈SQL璇�鍙ャ�俁EADS SQL DATA琛ㄧず瀛愮▼搴忓寘鍚�璇绘暟鎹�鐨勮��鍙ワ紝浣嗕笉鍖呭惈鍐欐暟鎹�鐨勮��鍙ャ�侻ODIFIES SQL DATA琛ㄧず瀛愮▼搴忓寘鍚�鍐欐暟鎹�鐨勮��鍙ャ�傚�傛灉杩欎簺鐗瑰緛娌℃湁鏄庣‘缁欏畾锛�榛樿�ょ殑鏄疌ONTAINS SQL銆�

SQL SECURITY鐗瑰緛鍙�浠ョ敤鏉ユ寚瀹� 瀛愮▼搴忚�ョ敤鍒涘缓瀛愮▼搴忚�呯殑璁稿彲鏉ユ墽琛岋紝杩樻槸浣跨敤璋冪敤鑰呯殑璁稿彲鏉ユ墽琛屻�傞粯璁ゅ�兼槸DEFINER銆傚湪SQL:2003涓�鑰呮槸涓�涓�鏂扮壒鎬с�傚垱寤鸿�呮垨璋冪敤鑰呭繀椤荤敱璁块棶 瀛愮▼搴忓叧鑱旂殑鏁版嵁搴撶殑璁稿彲銆傚湪MySQL 5.1涓�锛屽繀椤绘湁EXECUTE鏉冮檺鎵嶈兘鎵ц�屽瓙绋嬪簭銆傚繀椤绘嫢鏈夎繖涓�鏉冮檺鐨勭敤鎴疯�佷箞鏄�瀹氫箟鑰咃紝瑕佷箞鏄�璋冪敤鑰咃紝杩欏彇鍐充簬SQL SECURITY鐗瑰緛鏄�濡備綍璁剧疆鐨勩��

MySQL瀛樺偍sql_mode绯荤粺鍙橀噺璁剧疆锛岃繖涓�璁剧疆鍦ㄥ瓙绋嬪簭琚�鍒涘缓鐨勬椂鍊欒捣浣滅敤锛孧ySQL鎬绘槸寮哄埗浣跨敤杩欎釜璁剧疆鏉ユ墽琛� 瀛愮▼搴忋��

COMMENT瀛愬彞鏄�涓�涓狹ySQL鐨勬墿灞曪紝瀹冨彲浠ヨ��鐢ㄦ潵鎻忚堪 瀛樺偍绋嬪簭銆傝繖涓�淇℃伅琚玈HOW CREATE PROCEDURE鍜� SHOW CREATE FUNCTION璇�鍙ユ潵鏄剧ず銆�

MySQL鍏佽�稿瓙绋嬪簭鍖呭惈DDL璇�鍙ワ紝濡侰REATE鍜孌ROP銆侻ySQL涔熷厑璁稿瓨鍌ㄧ▼搴忥紙浣嗕笉鏄� 瀛樺偍鍑芥暟锛夊寘鍚玈QL 浜や簰璇�鍙ワ紝濡侰OMMIT銆傚瓨鍌ㄥ嚱鏁颁笉鍙�浠ュ寘鍚�閭ｄ簺鍋氭槑纭�鐨勫拰缁濆�圭殑鎻愪氦鎴栬�呭仛鍥炴粴鐨勮��銆係QL鏍囧噯涓嶈�佹眰瀵硅繖浜涜��鍙ョ殑鏀�鎸侊紝SQL鏍囧噯澹版槑姣忎釜DBMS鎻愪緵鍟嗗彲浠ュ喅瀹氭槸鍚﹀厑璁告敮鎸佽繖浜涜��鍙ャ��

瀛樺偍瀛愮▼搴忎笉鑳戒娇鐢↙OAD DATA INFILE銆�

杩斿洖缁撴灉鍖呯殑璇�鍙ヤ笉鑳借��鐢ㄥ湪瀛樺偍鍑芥暟绉嶃�傝繖鍖呮嫭涓嶄娇鐢↖NTO缁欏彉閲忚�诲彇 鍒楀�肩殑SELECT璇�鍙ワ紝SHOW 璇�鍙ワ紝鍙婂叾瀹冭�稿�侲XPLAIN杩欐牱鐨勮��鍙ャ�傚�逛簬鍙�鍦ㄥ嚱鏁板畾涔夋椂闂磋��鍐冲畾瑕佽繑鍥炰竴涓�缁撴灉鍖呯殑璇�鍙ワ紝鍙戠敓涓�涓�鍏佽�镐粠鍑芥暟閿欒��杩斿洖缁撴灉鍖呯殑Not锛圗R_SP_NO_RETSET_IN_FUNC锛夈�傚�逛簬鍙�鍙�鍦ㄨ繍琛屾椂鍐冲畾瑕佽繑鍥炰竴涓�缁撴灉鍖呯殑璇�鍙ワ紝 鍙戠敓涓�涓�涓嶈兘鍦ㄧ粰瀹氫笂涓嬫枃閿欒��杩斿洖缁撴灉鍖呯殑PROCEDURE %s 锛圗R_SP_BADSELECT)銆�

涓嬮潰鏄�涓�涓�浣跨敤OUT鍙傛暟鐨勭畝鍗曠殑瀛樺偍绋嬪簭鐨勪緥瀛愩�備緥瀛愪负锛屽湪 绋嬪簭琚�瀹氫箟鐨勬椂鍊欙紝鐢�mysql瀹㈡埛绔痙elimiter鍛戒护鏉ユ妸璇�鍙ュ畾鐣岀�︿粠 ;鍙樹负//銆傝繖灏卞厑璁哥敤鍦� 绋嬪簭浣撲腑鐨�;瀹氱晫绗﹁��浼犻�掑埌鏈嶅姟鍣ㄨ�屼笉鏄�琚�mysql鑷�宸辨潵瑙ｉ噴銆�

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

    -> BEGIN

    ->   SELECT COUNT(*) INTO param1 FROM t;

    -> END

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;

+------+

| @a   |

+------+

| 3    |

+------+

1 row in set (0.00 sec)

褰撲娇鐢╠elimiter鍛戒护鏃讹紝浣犲簲璇ラ伩鍏嶄娇鐢ㄥ弽鏂滄潬(‘\’)瀛楃�︼紝鍥犱负閭ｆ槸MySQL鐨� 杞�涔夊瓧绗︺��

涓嬪垪鏄�涓�涓�渚嬪瓙锛屼竴涓�閲囩敤鍙傛暟鐨勫嚱鏁颁娇鐢ㄤ竴涓猄QL鍑芥暟鎵ц�屼竴涓�鎿嶄綔锛屽苟杩斿洖缁撴灉锛�

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

    -> RETURN CONCAT('Hello, ',s,'!');

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world');

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

| hello('world') |

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

| Hello, world!  |

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

1 row in set (0.00 sec)

濡傛灉鍦ㄥ瓨鍌ㄥ嚱鏁颁腑鐨凴ETURN璇�鍙ヨ繑鍥炰竴涓�绫诲瀷涓嶅悓浜庡湪鍑芥暟鐨�RETURNS瀛愬彞涓�鎸囧畾绫诲瀷鐨勫�硷紝杩斿洖鍊艰��寮哄埗涓烘伆褰撶殑绫诲瀷銆傛瘮濡傦紝濡傛灉涓�涓�鍑芥暟杩斿洖涓�涓狤NUM鎴朣ET鍊硷紝浣嗘槸RETURN璇�鍙ヨ繑鍥炰竴涓�鏁存暟锛屽�逛簬SET鎴愬憳闆嗙殑鐩稿簲鐨凟NUM鎴愬憳锛屼粠鍑芥暟杩斿洖鐨勫�兼槸瀛楃�︿覆銆�

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

characteristic:

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

杩欎釜璇�鍙ュ彲浠ヨ��鐢ㄦ潵鏀瑰彉涓�涓�瀛樺偍绋嬪簭鎴栧嚱鏁扮殑鐗瑰緛銆傚湪MySQL 5.1涓�锛屼綘蹇呴』鐢ˋLTER ROUTINE鏉冮檺鎵嶅彲鐢ㄦ�ゅ瓙绋嬪簭銆傝繖涓�鏉冮檺琚�鑷�鍔ㄦ巿浜堝瓙绋嬪簭鐨勫垱寤鸿�呫�傚��20.4鑺傦紝鈥�瀛樺偍瀛愮▼搴忓拰瑙﹀彂绋嬪簭鐨勪簩杩涘埗鏃ュ織鍔熻兘”涓�鎵�杩帮紝 濡傛灉浜岃繘鍒舵棩蹇楀姛鑳借��鍏佽�镐簡锛屼綘鍙�鑳戒篃闇�瑕丼UPER鏉冮檺銆�

鍦ˋLTER PROCEDURE鍜孉LTER FUNCTION璇�鍙ヤ腑锛屽彲浠ユ寚瀹氳秴杩囦竴涓�鐨勬敼鍙樸��

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

杩欎釜璇�鍙ヨ��鐢ㄦ潵绉婚櫎涓�涓�瀛樺偍绋嬪簭鎴栧嚱鏁般�傚嵆锛屼粠鏈嶅姟鍣ㄧЩ闄や竴涓�鍒跺畾鐨勫瓙绋嬪簭銆傚湪MySQL 5.1涓�锛屼綘蹇呴』鏈堿LTER ROUTINE鏉冮檺鎵嶅彲鐢ㄦ�ゅ瓙绋嬪簭銆傝繖涓�鏉冮檺琚�鑷�鍔ㄦ巿浜堝瓙绋嬪簭鐨勫垱寤鸿�呫��

IF EXISTS 瀛愬彞鏄�涓�涓狹ySQL鐨勬墿灞曘�傚�傛灉绋嬪簭鎴栧嚱鏁颁笉瀛樺偍锛屽畠闃叉�㈠彂鐢熼敊璇�銆備骇鐢熶竴涓�鍙�浠ョ敤SHOW WARNINGS鏌ョ湅鐨勮�﹀憡銆�

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

杩欎釜璇�鍙ユ槸涓�涓狹ySQL鐨勬墿灞曘�傜被浼间簬SHOW CREATE TABLE锛屽畠杩斿洖涓�涓�鍙�鐢ㄦ潵閲嶆柊鍒涘缓宸插懡鍚� 瀛愮▼搴忕殑纭�鍒囧瓧绗︿覆銆�

mysql> SHOW CREATE FUNCTION test.hello\G

*************************** 1. row ***************************

       Function: hello

       sql_mode:

Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)

RETURN CONCAT('Hello, ',s,'!')

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

杩欎釜璇�鍙ユ槸涓�涓狹ySQL鐨勬墿灞曘�傚畠杩斿洖瀛愮▼搴忕殑鐗瑰緛锛屽�傛暟鎹�搴擄紝鍚嶅瓧锛岀被鍨嬶紝鍒涘缓鑰呭強鍒涘缓鍜屼慨鏀规棩鏈熴�傚�傛灉娌℃湁鎸囧畾鏍峰紡锛屾牴鎹�浣犱娇鐢ㄧ殑璇�鍙ワ紝鎵�鏈� 瀛樺偍绋嬪簭鍜屾墍鏈夊瓨鍌ㄥ嚱鏁扮殑淇℃伅閮借��鍒楀嚭銆�

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G

*************************** 1. row ***************************

           Db: test

         Name: hello

         Type: FUNCTION

      Definer: testuser@localhost

     Modified: 2004-08-03 15:29:37

      Created: 2004-08-03 15:29:37

Security_type: DEFINER

      Comment:

浣犲彲浠ヤ粠INFORMATION_SCHEMA涓�鐨凴OUTINES琛ㄨ幏寰楁湁鍏冲瓨鍌ㄥ瓙绋嬪簭鐨勪俊鎭�銆傝�峰弬闃�23.1.14鑺傦紝鈥淚NFORMATION_SCHEMA ROUTINES 琛�”銆�

CALL sp_name([parameter[,...]])

CALL璇�鍙ヨ皟鐢ㄤ竴涓�鍏堝墠鐢–REATE PROCEDURE鍒涘缓鐨勭▼搴忋��

CALL璇�鍙�鍙�浠ョ敤 澹版槑涓篛UT鎴栫殑INOUT鍙傛暟鐨勫弬鏁扮粰瀹冪殑璋冪敤鑰呬紶鍥炲�笺�傚畠涔�“杩斿洖”鍙楀奖鍝嶇殑琛屾暟锛屽�㈡埛绔�绋嬪簭鍙�浠ュ湪SQL绾у埆閫氳繃璋冪敤ROW_COUNT()鍑芥暟鑾峰緱杩欎釜鏁帮紝浠嶤涓�鏄�璋冪敤the mysql_affected_rows() C API鍑芥暟鏉�鑾峰緱銆�

[begin_label:] BEGIN

    [statement_list]

END [end_label]

瀛樺偍瀛愮▼搴忓彲浠ヤ娇鐢�BEGIN ... END澶嶅悎璇�鍙ユ潵鍖呭惈澶氫釜璇�鍙ャ��statement_list 浠ｈ〃涓�涓�鎴栧�氫釜璇�鍙ョ殑鍒楄〃銆�statement_list涔嬪唴姣忎釜璇�鍙ラ兘蹇呴』鐢ㄥ垎鍙凤紙锛涳級鏉ョ粨灏俱��

澶嶅悎璇�鍙ュ彲浠ヨ��鏍囪�般�傞櫎闈�begin_label瀛樺湪,鍚﹀垯end_label涓嶈兘琚�缁欏嚭,骞朵笖濡傛灉浜岃�呴兘瀛樺湪,浠栦滑蹇呴』鏄�鍚屾牱鐨勩��

璇锋敞鎰忥紝鍙�閫夌殑[NOT] ATOMIC瀛愬彞鐜板湪杩樹笉琚�鏀�鎸併�傝繖鎰忓懗鐫�鍦ㄦ寚浠ゅ潡鐨勫紑濮嬫病鏈変氦浜掔殑瀛樺偍鐐硅��璁剧疆锛屽苟涓斿湪涓婁笅鏂囦腑鐢ㄥ埌鐨凚EGIN瀛愬彞瀵瑰綋鍓嶄氦浜掑姩浣滄病鏈夊奖鍝嶃��

浣跨敤澶氶噸璇�鍙ラ渶瑕佸�㈡埛绔�鑳藉彂閫佸寘鍚�璇�鍙ュ畾鐣岀��;鐨勬煡璇㈠瓧绗︿覆銆傝繖涓�绗﹀彿鍦ㄥ懡浠よ�屽�㈡埛绔�琚�鐢╠elimiter鍛戒护鏉ュ�勭悊銆傛敼鍙樻煡璇㈢粨灏惧畾鐣岀��;锛堟瘮濡傛敼鍙樹负//锛変娇寰�; 鍙�琚�鐢ㄥ湪瀛愮▼搴忎綋涓�銆�

DECLARE璇�鍙ヨ��鐢ㄦ潵鎶婁笉鍚岄」鐩�灞�鍩熷埌涓�涓� 瀛愮▼搴忥細灞�閮ㄥ彉閲忥紙璇峰弬闃�20.2.9鑺傦紝鈥�瀛樺偍绋嬪簭涓�鐨勫彉閲�”)锛屾潯浠跺拰 澶勭悊绋嬪簭锛堣�峰弬闃�20.2.10鑺傦紝鈥�鏉′欢鍜屽�勭悊绋嬪簭”) 鍙婂厜鏍囷紙璇峰弬闃�20.2.11鑺傦紝鈥�鍏夋爣”)銆係IGNAL鍜孯ESIGNAL璇�鍙ュ綋鍓嶈繕涓嶈��鏀�鎸併��

DECLARE浠呰��鐢ㄥ湪BEGIN ... END澶嶅悎璇�鍙ラ噷锛屽苟涓斿繀椤诲湪澶嶅悎璇�鍙ョ殑寮�澶达紝鍦ㄤ换浣曞叾瀹冭��鍙ヤ箣鍓嶃��

鍏夋爣蹇呴』鍦ㄥ０鏄庡�勭悊绋嬪簭涔嬪墠琚�澹版槑锛屽苟涓斿彉閲忓拰鏉′欢蹇呴』鍦ㄥ０鏄庡厜鏍囨垨澶勭悊绋嬪簭涔嬪墠琚�澹版槑銆�

浣犲彲浠ュ湪瀛愮▼搴忎腑澹版槑骞朵娇鐢ㄥ彉閲忋��

DECLARE var_name[,...] type [DEFAULT value]

SET var_name = expr [, var_name = expr] ...

SELECT col_name[,...] INTO var_name[,...] table_expr

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

CREATE PROCEDURE sp1 (x VARCHAR(5))

  BEGIN

    DECLARE xname VARCHAR(5) DEFAULT 'bob';

    DECLARE newname VARCHAR(5);

    DECLARE xid INT;

    SELECT xname,id INTO newname,xid 

      FROM table1 WHERE xname = xname;

    SELECT newname;

  END;

鐗瑰畾鏉′欢闇�瑕佺壒瀹氬�勭悊銆傝繖浜涙潯浠跺彲浠ヨ仈绯诲埌閿欒��锛屼互鍙婂瓙绋嬪簭涓�鐨勪竴鑸�娴佺▼鎺у埗銆�

DECLARE condition_name CONDITION FOR condition_value

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | mysql_error_code

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:

    CONTINUE

  | EXIT

  | UNDO

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | condition_name

  | SQLWARNING

  | NOT FOUND

  | SQLEXCEPTION

  | mysql_error_code

mysql> CREATE TABLE test.t (s1 int,primary key (s1));

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()

    -> BEGIN

    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

    ->   SET @x = 1;

    ->   INSERT INTO test.t VALUES (1);

    ->   SET @x = 2;

    ->   INSERT INTO test.t VALUES (1);

    ->   SET @x = 3;

    -> END;

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//

    +------+

    | @x   |

    +------+

    | 3    |

    +------+

    1 row in set (0.00 sec)

绠�鍗曞厜鏍囧湪瀛樺偍绋嬪簭鍜屽嚱鏁板唴琚�鏀�鎸併�傝��娉曞�傚悓鍦ㄥ祵鍏ョ殑SQL涓�銆傚厜鏍囧綋鍓嶆槸涓嶆晱鎰熺殑锛屽彧璇荤殑鍙婁笉婊氬姩鐨勩�備笉鏁忔劅鎰忎负鏈嶅姟鍣ㄥ彲浠ユ椿涓嶅彲浠ュ�嶅埗瀹冪殑缁撴灉琛ㄣ��

鍏夋爣蹇呴』鍦ㄥ０鏄庡�勭悊绋嬪簭涔嬪墠琚�澹版槑锛屽苟涓斿彉閲忓拰鏉′欢蹇呴』鍦ㄥ０鏄庡厜鏍囨垨澶勭悊绋嬪簭涔嬪墠琚�澹版槑銆�

渚嬪�傦細

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE a CHAR(16);

  DECLARE b,c INT;

  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;

  OPEN cur2;

  REPEAT

    FETCH cur1 INTO a, b;

    FETCH cur2 INTO c;

    IF NOT done THEN

       IF b < c THEN

          INSERT INTO test.t3 VALUES (a,b);

       ELSE

          INSERT INTO test.t3 VALUES (a,c);

       END IF;

    END IF;

  UNTIL done END REPEAT;

  CLOSE cur1;

  CLOSE cur2;

END

DECLARE cursor_name CURSOR FOR select_statement

OPEN cursor_name

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor_name

IF, CASE, LOOP, WHILE, ITERATE, 鍙� LEAVE 鏋勯�犺��瀹屽叏瀹炵幇銆�

杩欎簺鏋勯�犲彲鑳芥瘡涓�鍖呭惈瑕佷箞涓�涓�鍗曠嫭璇�鍙ワ紝瑕佷箞鏄�浣跨敤BEGIN ... END澶嶅悎璇�鍙ョ殑涓�鍧楄��鍙ャ�傛瀯閫犲彲浠ヨ��宓屽�椼��

鐩�鍓嶈繕涓嶆敮鎸丗OR寰�鐜�銆�

IF search_condition THEN statement_list

    [ELSEIF search_condition THEN statement_list] ...

    [ELSE statement_list]

END IF

CASE case_value

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list] ...

    [ELSE statement_list]

END CASE

CASE

    WHEN search_condition THEN statement_list

    [WHEN search_condition THEN statement_list] ...

    [ELSE statement_list]

END CASE

[begin_label:] LOOP

    statement_list

END LOOP [end_label]

LEAVE label

ITERATE label

CREATE PROCEDURE doiterate(p1 INT)

BEGIN

  label1: LOOP

    SET p1 = p1 + 1;

    IF p1 < 10 THEN ITERATE label1; END IF;

    LEAVE label1;

  END LOOP label1;

  SET @x = p1;

END

[begin_label:] REPEAT

    statement_list

UNTIL search_condition

END REPEAT [end_label]

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)

    -> BEGIN

    ->   SET @x = 0;

    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

    -> END

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//

+------+

| @x   |

+------+

| 1001 |

+------+

1 row in set (0.00 sec)

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

CREATE PROCEDURE dowhile()

BEGIN

  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO

    ...

    SET v1 = v1 - 1;

  END WHILE;

END

• MySQL 5.1瀛樺偍绋嬪簭鍜屽嚱鏁板�瑰�嶅埗璧蜂綔鐢ㄥ悧锛� 

  鏄�鐨勶紝鍦ㄥ瓨鍌ㄧ▼搴忓拰鍑芥暟涓�琚�鎵ц�屾爣鍑嗚�屼负琚�浠庝富MySQL鏈嶅姟鍣ㄥ�嶅埗鍒颁粠鏈嶅姟鍣ㄣ�傛湁灏戞暟闄愬埗锛屽畠浠�鍦�20.4鑺傦紝鈥�瀛樺偍瀛愮▼搴忓拰 瑙﹀彂绋嬪簭浜岃繘鍒舵棩蹇楀姛鑳解��涓�璇﹁堪銆�

• 鍦ㄤ富鏈嶅姟鍣ㄤ笂鍒涘缓鐨勫瓨鍌ㄧ▼搴忓拰鍑芥暟鍙�浠ヨ��澶嶅埗鍒颁粠鏈嶅姟鍣ㄤ笂涔堬紵

  鏄�鐨勶紝閫氳繃涓�鑸珼DL璇�鍙ユ墽琛岀殑瀛樺偍绋嬪簭鍜屽嚱鏁帮紝鍏跺湪涓绘湇鍔″櫒涓婄殑鍒涘缓琚�澶嶅埗鍒颁粠鏈嶅姟鍣�锛屾墍浠ョ洰鏍囧皢瀛樺湪涓や釜鏈嶅姟鍣ㄤ笂銆傚�瑰瓨鍌ㄧ▼搴忓拰鍑芥暟鐨凙LTER 鍜孌ROP璇�鍙ヤ篃琚�澶嶅埗銆�

• 琛屼负濡備綍鍦ㄥ凡澶嶅埗鐨勫瓨鍌ㄧ▼搴忓拰鍑芥暟閲屽彂鐢燂紵

  MySQL绾�褰曟瘡涓�鍙戠敓鍦ㄥ瓨鍌ㄧ▼搴忓拰鍑芥暟閲岀殑DML浜嬩欢锛屽苟澶嶅埗杩欎簺鍗曠嫭鐨勮�屼负鍒颁粠鏈嶅姟鍣ㄣ�傛墽琛屽瓨鍌ㄧ▼搴忓拰鍑芥暟鐨勫垏瀹炶皟鐢ㄤ笉琚�澶嶅埗銆�

• 瀵逛竴璧蜂娇鐢ㄥ瓨鍌ㄧ▼搴忥紝鍑芥暟鍜屽�嶅埗鏈変粈涔堢壒鍒�鐨勫畨鍏ㄨ�佹眰涔堬紵

  鏄�鐨勶紝鍥犱负涓�涓�浠庢湇鍔″櫒鏈夋潈闄愭潵鎵ц�屼换浣曡�昏嚜涓绘湇鍔″櫒鐨勪簩杩涘埗鏃ュ織鐨勮��鍙ワ紝鎸囧畾鐨勫畨鍏ㄧ害鏉熷洜涓庡�嶅埗涓�璧蜂娇鐢ㄧ殑瀛樺偍绋嬪簭鍜屽嚱鏁拌�屽瓨鍦ㄣ�傚�傛灉澶嶅埗鎴栦簩杩涘埗鏃ュ織澶т綋涓婃槸婵�娲荤殑锛堜负point-in-time鎭㈠�嶇殑鐩�鐨勶級锛岄偅涔圡ySQL DBA 鏈変袱涓�瀹夊叏閫夐」鍙�閫夛細

  • 浠讳綍鎯冲垱寤哄瓨鍌ㄧ▼搴忕殑鐢ㄦ埛蹇呴』琚�璧嬩簣SUPER鏉冮檺銆�
  • 浣滀负閫夋嫨锛屼竴涓狣BA鍙�浠ヨ�剧疆log_bin_trust_routine_creators绯荤粺鍙橀噺涓�1锛屽畠灏嗕細鍏佽�告湁鏍囧噯CREATE ROUTINE鏉冮檺鐨勪汉鏉ュ垱寤轰竴涓�瀛樺偍绋嬪簭鍜屽嚱鏁般��
   
• 瀵瑰�嶅埗瀛樺偍绋嬪簭鍜屽嚱鏁扮殑琛屼负鏈変粈涔堥檺鍒讹紵

  宓屽叆鍒板瓨鍌ㄧ▼搴忎腑鐨勪笉纭�瀹氾紙闅忔満锛夋垨鏃跺熀琛屼笉鑳介�傚綋鍦板�嶅埗銆傞殢鏈轰骇鐢熺殑缁撴灉锛屼粎鍥犲叾鏈�鎬э紝鏄�浣犲彲棰勬祴鐨勫拰涓嶈兘琚�纭�瀹炲厠闅嗙殑銆傚洜姝わ紝澶嶅埗鍒颁粠鏈嶅姟鍣ㄧ殑闅忔満琛屼负灏嗕笉浼氶暅鍍忛偅浜涗骇鐢熷湪涓绘湇鍔″櫒涓婄殑銆傛敞鎰忥紝 澹版槑瀛樺偍绋嬪簭鎴栧嚱鏁颁负DETERMINISTIC鎴栬�呭湪log_bin_trust_routine_creators涓�璁剧疆绯荤粺鍙橀噺涓�0 灏嗕細鍏佽�搁殢鍗冲�兼搷浣滆��璋冪敤銆�

  姝ゅ�栵紝鏃跺熀琛屼负涓嶈兘鍦ㄤ粠鏈嶅姟鍣ㄤ笂閲嶆柊浜х敓锛屽洜涓哄湪瀛樺偍绋嬪簭涓�閫氳繃瀵瑰�嶅埗浣跨敤鐨勪簩杩涘埗鏃ュ織鏉ヨ�℃椂杩欐牱鐨勬椂鍩鸿�屼负鏄�涓嶅彲閲嶆柊浜х敓鐨勶紝鍥犱负璇ヤ簩杩涘埗鏃ュ織浠呯邯褰旸ML浜嬩欢涓斾笉鍖呮嫭璁℃椂绾︽潫銆�

  鏈�鍚庯紝鍦ㄥぇ鍨婦ML琛屼负锛堝�傚ぇ鎵规彃鍏ワ級涓�闈炰氦浜掕〃鍙戠敓閿欒��锛岃�ラ潪浜や簰琛ㄥ彲鑳界粡鍘嗗�嶅埗锛屽湪澶嶅埗鐗堢殑闈炰氦浜掕〃涓�涓绘湇鍔″櫒鍙�浠ヨ��閮ㄥ垎鍦颁粠DML琛屼负鏇存柊銆備絾鏄�鍥犱负鍙戠敓鐨勯偅涓�閿欒��锛屽�逛粠鏈嶅姟鍣ㄦ病鏈夋洿鏂般�� 瀵瑰嚱鏁扮殑DML琛屼负锛屽伐浣滃尯灏嗚��鐢↖GNORE鍏抽敭璇嶆潵鎵ц�岋紝浠ヤ究浜庡湪涓绘湇鍔″櫒涓婂�艰嚧閿欒��鐨勬洿鏂拌��蹇界暐锛屽苟涓斾笉浼氬�艰嚧閿欒��鐨勬洿鏂拌��澶嶅埗鍒颁粠鏈嶅姟鍣ㄣ��

   

• 涓婅堪鐨勯檺鍒朵細褰卞搷MySQL浣� point-in-time鎭㈠�嶇殑鑳藉姏鍚楋紵

  褰卞搷澶嶅埗鐨勫悓涓�闄愬埗浼氬奖鍝峱oint-in-time鎭㈠�嶃��

•  MySQL瑕佸仛浠�涔堟潵鏀规�ｅ墠杩扮殑闄愬埗鍛�锛�

  灏嗘潵鍙戣�岀殑MySQL棰勬湡鏈変竴涓�鍔熻兘鍘婚�夋嫨澶嶅埗璇ュ�備綍琚�澶勭悊锛�

  •  鍩轰簬璇�鍙ョ殑澶嶅埗锛堝綋鍓嶅疄鐜帮級銆�
  • 琛岀骇鍒�澶嶅埗锛堝畠灏嗚В鍐虫墍鏈夋棭鍏堟弿杩扮殑闄愬埗锛夈��
• 瑙﹀彂绋嬪簭瀵瑰�嶅埗璧蜂綔鐢ㄤ箞锛�

  MySQL 5.1涓�鐨勮Е鍙戠▼搴忓拰澶嶅埗璞″湪澶у�氭暟鍏跺畠鏁版嵁搴撳紩鎿庝腑涓�鏍峰伐浣滐紝鍦ㄩ偅浜涘紩鎿庝腑锛岄�氳繃瑙﹀彂绋嬪簭鍦ㄤ富鏈嶅姟鍣ㄤ笂鎵ц�岀殑琛屼负涓嶈��澶嶅埗鍒颁粠鏈嶅姟鍣ㄣ�傚彇鑰屼唬涔嬬殑鏄�锛屼綅浜庝富MySQL鏈嶅姟鍣ㄧ殑琛ㄤ腑鐨� 瑙﹀彂绋嬪簭闇�瑕佸湪閭ｄ簺瀛樺湪浜庝换浣昅ySQL浠庢湇鍔″櫒涓婄殑琛ㄥ唴琚�鍒涘缓锛屼互渚夸簬瑙﹀彂绋嬪簭鍙�浠ヤ篃鍙�浠ュ湪浠庢湇鍔″櫒涓婅��婵�娲汇��

   

•  涓�涓�琛屼负濡備綍閫氳繃浠庝富鏈嶅姟鍣ㄤ笂澶嶅埗鍒颁粠鏈嶅姟鍣ㄤ笂鐨勮Е鍙戠▼搴忔潵鎵ц�屽憿锛�

  棣栧厛锛屼富鏈嶅姟鍣ㄤ笂鐨勮Е鍙戠▼搴忓繀椤诲湪浠庢湇鍔″櫒涓婇噸寤恒�備竴鏃﹂噸寤轰簡锛屽�嶅埗娴佺▼灏辫薄鍏跺畠鍙備笌鍒板�嶅埗涓�鐨勬爣鍑咲ML璇�鍙ヤ竴鏍峰伐浣溿�備緥濡傦細鑰冭檻涓�涓�宸茬粡鎻掑叆瑙﹀彂绋嬪簭AFTER鐨凟MP琛�锛屽畠浣嶄簬涓籑ySQL鏈嶅姟鍣ㄤ笂銆傚悓鏍风殑EMP琛ㄥ拰AFTER鎻掑叆 瑙﹀彂绋嬪簭涔熷瓨鍦ㄤ簬浠庢湇鍔″櫒涓娿�傚�嶅埗娴佺▼鍙�鑳芥槸锛�

1.    瀵笶MP鍋氫竴涓狪NSERT璇�鍙ャ��

2.   EMP涓婄殑AFTER瑙﹀彂绋嬪簭婵�娲汇��

3.    INSERT璇�鍙ヨ��鍐欒繘浜岃繘鍒舵棩蹇椼��

4.    浠庢湇鍔″櫒涓婄殑澶嶅埗鎷捐捣INSERT璇�鍙ョ粰EMP琛�锛屽苟鍦ㄤ粠鏈嶅姟鍣ㄤ笂鎵ц�屽畠銆�

5.    浣嶄簬浠庢湇鍔″櫒EMP涓婄殑AFTER瑙﹀彂绋嬪簭婵�娲汇��

·         澶嶅埗鐨勫熀纭�鏄�涓绘湇鍔″櫒鍙戦�佸寘鍚�鍦ㄤ簩杩涘埗鏃ュ織閲岀殑浜嬩欢鍒颁粠鏈嶅姟鍣�锛屼粠鏈嶅姟鍣ㄦ墽琛岃繖浜涗簨浠舵潵閫犳垚涓庡�逛富鏈嶅姟鍣ㄩ�犳垚鐨勫悓鏍风殑鏁版嵁鏀瑰彉锛岃�峰弬闃�6.2鑺傦紝鈥�澶嶅埗姒傝堪”銆�

·         鐗瑰畾鐨勬暟鎹�鎭㈠�嶆搷浣滆�歌�佷娇鐢ㄤ簩杩涘埗鏃ュ織銆傚�囦唤鐨勬枃浠惰��鎭㈠�嶄箣鍚庯紝澶囦唤鍚庣邯褰曠殑浜岃繘鍒舵棩蹇楅噷鐨勪簨浠惰��閲嶆柊鎵ц�屻�傝繖浜涗簨浠舵妸鏁版嵁搴撳甫浠庡�囦唤鐐圭殑鏃ュ瓙甯﹀埌褰撳墠銆傝�峰弬闃�5.9.2.2鑺傦紝鈥�浣跨敤澶囦唤鎭㈠��”銆�

·         CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,鍜孉LTER FUNCTION 璇�鍙ヨ��鍐欒繘浜岃繘鍒舵棩蹇楋紝CALL, DROP PROCEDURE, 鍜孌ROP FUNCTION 涔熶竴鏍枫��

灏界�″�傛�わ紝瀵瑰�嶅埗鏈変竴涓�瀹夊叏鏆楃ず锛氳�佸垱寤轰竴涓�瀛愮▼搴忥紝鐢ㄦ埛蹇呴』鏈塁REATE ROUTINE鏉冮檺,浣嗘湁杩欎釜鏉冮檺鐨勭敤鎴蜂笉鑳藉啓涓�涓� 瀛愮▼搴忓湪浠庢湇鍔″櫒涓婃墽琛屼换浣曟搷浣溿�傚洜涓哄湪浠庢湇鍔″櫒涓婄殑SQL绾跨▼鐢ㄥ畬鍏ㄦ潈闄愭潵杩愯�屻�備緥濡傦紝濡傛灉涓绘湇鍔″櫒鍜屼粠鏈嶅姟鍣ㄥ垎鍒�鏈夋湇鍔″櫒ID鍊�1鍜�2锛屽湪涓绘湇鍔″櫒涓婄殑鐢ㄦ埛鍙�鑳藉垱寤哄苟璋冪敤濡備笅涓�涓� 绋嬪簭锛�

mysql> delimiter //

mysql> CREATE PROCEDURE mysp ()

    -> BEGIN

    ->   IF @@server_id=2 THEN DROP DATABASE accounting; END IF;

    -> END;

    -> //

mysql> delimiter ;

mysql> CALL mysp();

CREATE PROCEDURE鍜孋ALL璇�鍙ュ皢琚�鍐欒繘浜岃繘鍒舵棩蹇楋紝鎵�浠ヤ粠鏈嶅姟鍣ㄥ皢鎵ц�屽畠浠�銆傚洜涓轰粠SQL绾跨▼鏈夊畬鍏ㄦ潈闄愶紝瀹冨皢绉婚櫎accounting鏁版嵁搴撱��

瑕佷娇鍏佽�镐簩杩涘埗鏃ュ織鍔熻兘鐨勬湇鍔″櫒閬垮厤杩欎釜鍗遍櫓锛孧ySQL 5.1宸茬粡瑕佹眰 瀛樺偍绋嬪簭鍜屽嚱鏁扮殑鍒涘缓鑰呴櫎浜嗛�氬父闇�瑕佺殑CREATE ROUTINE鐨勬潈闄愬�栵紝杩樺繀椤绘湁SUPER 鏉冮檺銆傜被浼煎湴锛岃�佷娇鐢ˋLTER PROCEDURE鎴朅LTER FUNCTION锛岄櫎浜咥LTER ROUTINE鏉冮檺澶栦綘蹇呴』鏈塖UPER鏉冮檺銆傛病鏈塖UPER鏉冮檺锛屽皢浼氬彂鐢熶竴涓�閿欒��锛�

ERROR 1419 (HY000): You do not have the SUPER privilege and

binary logging is enabled (you *might* want to use the less safe

log_bin_trust_routine_creators variable)

浣犲彲鑳戒笉鎯冲己鍒惰�佹眰瀛愮▼搴忓垱寤鸿�呭繀椤绘湁SUPER鏉冮檺銆備緥濡傦紝浣犵郴缁熶笂鎵�鏈夋湁CREATE ROUTINE鏉冮檺鐨勭敤鎴峰彲鑳芥槸鏈夌粡楠岀殑搴旂敤绋嬪簭寮�鍙戣�呫�傝�佺�佹�㈡帀瀵筍UPER鏉冮檺鐨勮�佹眰锛岃�剧疆log_bin_trust_routine_creators 鍏ㄥ眬绯荤粺鍙橀噺涓�1銆傞粯璁ゅ湴锛岃繖涓�鍙橀噺鍊间负0锛屼絾浣犲彲浠ヨ薄杩欐牱鏀瑰彉杩欐牱锛�

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;

浣犱篃鍙�浠ュ湪鍚�鍔ㄦ湇鍔″櫒涔嬫椂鐢�--log-bin-trust-routine-creators閫夐」鏉ヨ�剧疆鍏佽�歌繖涓�鍙橀噺銆�

濡傛灉浜岃繘鍒舵棩蹇楀姛鑳戒笉琚�鍏佽�革紝log_bin_trust_routine_creators 娌℃湁琚�鐢ㄤ笂锛屽瓙绋嬪簭鍒涘缓闇�瑕丼UPER鏉冮檺銆�

·         涓�涓�鎵ц�屾洿鏂扮殑闈炵‘瀹氬瓙绋嬪簭鏄�涓嶅彲閲嶅�嶇殑锛屽畠鑳芥湁涓や釜涓嶅�傛剰鐨勫奖鍝嶏細

瑕佽В鍐宠繖浜涢棶棰橈紝MySQL寮哄埗鍋氫笅闈㈣�佹眰锛氬湪涓绘湇鍔″櫒涓婏紝闄ら潪瀛愮▼搴忚��澹版槑涓虹‘瀹氭�х殑鎴栬�呬笉鏇存敼鏁版嵁锛屽惁鍒欏垱寤烘垨鑰呮浛鎹㈠瓙绋嬪簭灏嗚��鎷掔粷銆傝繖鎰忓懗鐫�褰撲綘鍒涘缓涓�涓�瀛愮▼搴忕殑鏃跺�欙紝浣犲繀椤昏�佷箞澹版槑瀹冩槸纭�瀹氭�х殑锛岃�佷箞瀹冧笉鏀瑰彉鏁版嵁銆備袱濂楀瓙绋嬪簭鐗瑰緛鍦ㄨ繖閲岄�傜敤锛�

榛樿�ゅ湴锛岃�佷竴涓狢REATE PROCEDURE 鎴� CREATE FUNCTION 璇�鍙ヨ��鎺ュ彈锛孌ETERMINISTIC 鎴� NO SQL涓嶳EADS SQL DATA 涓�鐨勪竴涓�蹇呴』鏄庣櫧鍦版寚瀹氾紝鍚﹀垯浼氫骇鐢熷�備笅閿欒��锛�

ERROR 1418 (HY000): This routine has none of DETERMINISTIC, NO SQL,

or READS SQL DATA in its declaration and binary logging is enabled

(you *might* want to use the less safe log_bin_trust_routine_creators

variable)

濡傛灉璁剧疆log_bin_trust_routine_creators 涓�1, 绉婚櫎瀵瑰瓙绋嬪簭蹇呴』鏄�纭�瀹氱殑鎴栦笉淇�鏀规暟鎹�鐨勮�佹眰銆�

娉ㄦ剰锛屽瓙绋嬪簭鏈�鎬х殑璇勪及鏄�鍩轰簬鍒涘缓鑰呯殑“璇氬疄搴�” 锛歁ySQL涓嶆��鏌ュ０鏄庝负纭�瀹氭�х殑瀛愮▼搴忔槸鍚︿笉鍚�浜х敓闈炵‘瀹氭�х粨鏋滅殑璇�鍙ャ��

·         濡傛灉瀛愮▼搴忚繑鍥炴棤閿欙紝CALL璇�鍙ヨ��鍐欒繘浜岃繘鍒舵棩蹇楋紝鍚﹀垯灏变笉鍐欍�傚綋涓�涓�瀛愮▼搴忎慨鏀规暟鎹�澶辫触浜嗭紝浣犱細寰楀埌杩欐牱鐨勮�﹀憡锛�

路                ERROR 1417 (HY000): A routine failed and has neither NO SQL nor

路                READS SQL DATA in its declaration and binary logging is enabled; if

路                non-transactional tables were updated, the binary log will miss their

路                changes

杩欎釜璁版棩蹇楄�屼负娼滃湪鍦板�艰嚧闂�棰�.濡傛灉涓�涓�瀛愮▼搴忛儴鍒嗗湴淇�鏀逛竴涓�闈炰氦浜掕〃锛堟瘮濡備竴涓狹yISAM琛╝ble锛夊苟涓旇繑鍥炰竴涓�閿欒��锛屼簩杩涘埗鏃ュ織灏嗗弽鏄犺繖浜涘彉鍖栥�傝�侀槻姝㈣繖绉嶆儏鍐碉紝浣犲簲璇ュ湪 瀛愮▼搴忎腑浣跨敤浜や簰琛ㄥ苟涓斿湪浜や簰鍔ㄤ綔鍐呬慨鏀硅〃銆�

鍦ㄤ竴涓�瀛愮▼搴忓唴锛屽�傛灉浣犲湪INSERT, DELETE, 鎴栬�匲PDATE閲屼娇鐢↖GNORE鍏抽敭璇嶆潵蹇界暐閿欒��锛屽彲鑳藉彂鐢熶竴涓�閮ㄥ垎鏇存柊锛屼絾娌℃湁閿欒��浜х敓銆傝繖鏍风殑璇�鍙ヨ��璁板綍鏃ュ織锛屼笖姝ｅ父澶嶅埗銆�

·         濡傛灉涓�涓�瀛樺偍鍑芥暟鍦ㄤ竴涓�濡係ELECT杩欐牱涓嶄慨鏀规暟鎹�鐨勮��鍙ュ唴琚�璋冪敤锛屽嵆浣垮嚱鏁版湰韬�鏇存敼鏁版嵁锛屽嚱鏁扮殑鎵ц�屼篃灏嗕笉琚�鍐欒繘浜岃繘鍒舵棩蹇楅噷銆傝繖涓�璁板綍鏃ュ織鐨勮�屼负娼滃湪鍦板�艰嚧闂�棰樸�傚亣璁惧嚱鏁癿yfunc()濡備笅瀹氫箟锛�

路                CREATE FUNCTION myfunc () RETURNS INT

路                BEGIN

路                  INSERT INTO t (i) VALUES(1);

路                  RETURN 0;

路                END;

鎸夌収涓婇潰瀹氫箟锛�涓嬮潰鐨勮��鍙ヤ慨鏀�琛╰锛屽洜涓簃yfunc()淇�鏀硅〃t, 浣嗘槸璇�鍙ヤ笉琚�鍐欒繘浜岃繘鍒舵棩蹇楋紝鍥犱负瀹冩槸涓�涓猄ELECT璇�鍙ワ細

SELECT myfunc();

瀵硅繖涓�闂�棰樼殑宸ヤ綔鍖哄皢璋冪敤鍦ㄥ仛鏇存柊鐨勮��鍙ラ噷鍋氭洿鏂扮殑鍑芥暟銆傛敞鎰忥紝铏界劧DO璇�鍙ユ湁鏃朵负浜嗗叾浼扮畻琛ㄨ揪寮忕殑鍓�鏁堝簲鑰岃��鎵ц�岋紝DO鍦ㄨ繖閲屼笉鏄�涓�涓�宸ヤ綔鍖猴紝鍥犱负瀹冧笉琚�鍐欒繘浜岃繘鍒舵棩蹇椼��

·         鍦ㄤ竴涓�瀛愮▼搴忓唴鎵ц�岀殑璇�鍙ヤ笉琚�鍐欒繘浜岃繘鍒舵棩蹇椼�傚亣濡備綘鍙戝竷涓嬪垪璇�鍙ワ細

路                CREATE PROCEDURE mysp INSERT INTO t VALUES(1);

路                CALL mysp;

瀵逛簬杩欎釜渚嬪瓙鏉ヨ�达紝CREATE PROCEDURE 鍜孋ALL璇�鍙ュ嚭鐜板湪浜岃繘鍒舵棩蹇楅噷锛屼絾INSERT璇�鍙ュ苟鏈�鍑虹幇銆�

·         鍦ㄤ粠鏈嶅姟鍣ㄤ笂锛屽綋鍐冲畾澶嶅埗鍝�涓�鏉ヨ嚜涓绘湇鍔″櫒鐨勪簨浠舵椂锛屼笅鍒楅檺鍒惰��搴旂敤锛�--replicate-*-table瑙勫垯涓嶉�傜敤浜嶤ALL璇�鍙ユ垨瀛愮▼搴忓唴鐨勮��鍙ワ細鍦ㄨ繖浜涙儏鍐典笅锛屾�绘槸杩斿洖鈥滃�嶅埗锛�”