《Mysql應用理解MySQL存儲過程和函數》要點：
本文介紹了Mysql應用理解MySQL存儲過程和函數，希望對您有用。如果有疑問，可以聯系我們。
一、概述? MYSQL數據庫
一提到存儲過程可能就會引出另一個話題就是存儲過程的優缺點,這里也不做討論,一般別人問我我就這樣回答你覺得它好你就用它.因為mysql中存儲過程和函數的語法非常接近所以就放在一起,主要區別就是函數必須有返回值(return),并且函數的參數只有IN類型而存儲過程有IN、OUT、INOUT這三種類型.MYSQL數據庫
二、語法? MYSQL數據庫
?創建存儲過程和函數語法MYSQL數據庫
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
語法來自官方自帶的參考手冊,characteristic語法塊是需要注意的地方,先用一個例子來介紹.MYSQL數據庫
例子：MYSQL數據庫
#創建數據庫 DROP DATABASE IF EXISTS Dpro; CREATE DATABASE Dpro CHARACTER SET utf8 ; USE Dpro; #創建部門表 DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (id INT NOT NULL PRIMARY KEY COMMENT '主鍵', name VARCHAR(20) NOT NULL COMMENT '人名', depid INT NOT NULL COMMENT '部門id' ); #插入測試數據 INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100),(2,'王',101),(3,'張',101),(4,'李',102),(5,'郭',103); #創建存儲過程 DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid; END$$ DELIMITER ; #執行存儲過程 CALL Pro_Employee(101,@pcount); SELECT @pcount;
MYSQL數據庫
語法解釋：MYSQL數據庫
在創建存儲過程的時候一般都會用DELIMITER$$.....END$$ DELIMITER ;放在開頭和結束,目的就是避免mysql把存儲過程內部的";"解釋成結束符號,最后通過“DELIMITER ;”來告知存儲過程結束.MYSQL數據庫
主要解釋characteristic部分：MYSQL數據庫
LANGUAGE SQL：用來說明語句部分是SQL語句,未來可能會支持其它類型的語句.MYSQL數據庫
[NOT] DETERMINISTIC：如果程序或線程總是對同樣的輸入參數產生同樣的結果,則被認為它是“確定的”,否則就是“非確定”的.如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC,默認的就是NOT DETERMINISTIC(非確定的)CONTAINS SQL：表示子程序不包括讀或寫數據的語句.MYSQL數據庫
NO SQL：表示子程序不包括SQL語句.MYSQL數據庫
READS SQL DATA：表示子程序包括讀數據的語句,但不包括寫數據的語句.MYSQL數據庫
MODIFIES SQL DATA：表示子程序包括寫數據的語句.MYSQL數據庫
SQL SECURITY DEFINER：表示執行存儲過程中的程序是由創建該存儲過程的用戶的權限來執行.MYSQL數據庫
SQL SECURITY INVOKER：表示執行存儲過程中的程序是由調用該存儲過程的用戶的權限來執行.(例如上面的存儲過程我寫的是由調用該存儲過程的用戶的權限來執行,當前存儲過程是用來查詢Employee表,如果我當前執行存儲過程的用戶沒有查詢Employee表的權限那么就會返回權限不足的錯誤,如果換成DEFINER如果存儲過程是由ROOT用戶創建那么任何一個用戶登入調用存儲過程都可以執行,因為執行存儲過程的權限變成了root)MYSQL數據庫
COMMENT 'string'：備注,和創建表的字段備注一樣.MYSQL數據庫
注意：在編寫存儲過程和函數時建議明確指定上面characteristic部分的狀態,特別是存在復制的環境中,如果創建函數不明確指定這些狀態會報錯,從一個非復制環境將帶函數的數據庫遷移到復制環境的機器上如果沒有明確指定DETERMINISTIC, NO SQL, or READS SQL DATA該三個狀態也會報錯.MYSQL數據庫
報錯示例MYSQL數據庫
Error Code: 1418. This function 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_function_creators variable)
這個報錯就是上面注意部分說的問題.原來是因為在主從復制的兩臺MySQL服務器中開啟了二進制日志選項log-bin,slave會從master復制數據,而一些操作,比如function所得的結果在master和slave上可能不同,所以存在潛在的平安隱患.因此,在默認情況下回阻止function的創建.MYSQL數據庫
解決方法有兩種：MYSQL數據庫
1.將log_bin_trust_function_creators參數設置為ON,這樣一來開啟了log-bin的MySQL Server便可以隨意創建function.這里存在潛在的數據平安問題,除非明確的知道創建的function在master和slave上的行為完全一致.
? 設置該參數可以用動態的方式或者指定該參數來啟動數據庫服務器或者修改配置文件后重啟服務器.需注意的是,動態設置的方式會在服務器重啟后失效.
MYSQL數據庫
mysql> show variables like 'log_bin_trust_function_creators'; mysql> set global log_bin_trust_function_creators=1;
? 另外如果是在master上創建函數,想通過主從復制的方式將函數復制到slave上則也需在開啟了log-bin的slave中設置上述變量的值為ON(變量的設置不會從master復制到slave上,這點需要注意),否則主從復制會報錯.MYSQL數據庫
2.明確指明函數的類型
? 1 )、DETERMINISTIC 不確定的
? 2 )、NO SQL 沒有SQl語句,當然也不會修改數據
? 3 )、READS SQL DATA 只是讀取數據,當然也不會修改數據
比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
這樣一來相當于明確的告知MySQL服務器這個函數不會修改數據,因此可以在開啟了log-bin的服務器上平安的創建并被復制到開啟了log-bin的slave上.MYSQL數據庫
修改存儲過程函數語法MYSQL數據庫
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
刪除存儲過程函數語法MYSQL數據庫
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
查看存儲過程和函數MYSQL數據庫
1.查看存儲過程狀態MYSQL數據庫
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
show procedure status like 'Pro_Employee' \G
MYSQL數據庫
2.查看存儲過程和函數的創建語法MYSQL數據庫
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
SHOW CREATE PROCEDURE Pro_Employee \G;
MYSQL數據庫
3.查看存儲過程和函數詳細信息MYSQL數據庫
MYSQL數據庫
總結? MYSQL數據庫
?存儲過程和函數語法不難理解,但是往往存儲過程中不單單只包括這種簡單的查詢語法,還會嵌套循環語句、變量、報錯處理、事務等,下一篇文章會單獨講變量,將變量的知識加入到存儲過程,包括變量的聲明和報錯處理,歡迎關注.MYSQL數據庫
歡迎參與《Mysql應用理解MySQL存儲過程和函數》討論，分享您的想法，維易PHP學院為您提供專業教程。
