欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

理解MySQL存儲過程和函數(shù)

 更新時間:2016年03月23日 14:28:42   作者:pursuer.chen  
這篇文章主要幫助大家學(xué)習(xí)理解MySQL存儲過程和函數(shù),感興趣的小伙伴們可以參考一下

一、概述 

一提到存儲過程可能就會引出另一個話題就是存儲過程的優(yōu)缺點,這里也不做討論,一般別人問我我就這樣回答你覺得它好你就用它。因為mysql中存儲過程和函數(shù)的語法非常接近所以就放在一起,主要區(qū)別就是函數(shù)必須有返回值(return),并且函數(shù)的參數(shù)只有IN類型而存儲過程有IN、OUT、INOUT這三種類型。

二、語法 

 創(chuàng)建存儲過程和函數(shù)語法

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語法塊是需要注意的地方,先用一個例子來介紹。

例子:

#創(chuàng)建數(shù)據(jù)庫
DROP DATABASE IF EXISTS Dpro;
CREATE DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#創(chuàng)建部門表
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'
);

#插入測試數(shù)據(jù)
INSERT INTO Employee(id,name,depid) VALUES(1,'陳',100),(2,'王',101),(3,'張',101),(4,'李',102),(5,'郭',103);

#創(chuàng)建存儲過程
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 ;

#執(zhí)行存儲過程
CALL Pro_Employee(101,@pcount);

SELECT @pcount;


語法解釋:

在創(chuàng)建存儲過程的時候一般都會用DELIMITER$$.....END$$ DELIMITER ;放在開頭和結(jié)束,目的就是避免mysql把存儲過程內(nèi)部的";"解釋成結(jié)束符號,最后通過“DELIMITER ;”來告知存儲過程結(jié)束。

主要解釋characteristic部分:

LANGUAGE SQL:用來說明語句部分是SQL語句,未來可能會支持其它類型的語句。

[NOT] DETERMINISTIC:如果程序或線程總是對同樣的輸入?yún)?shù)產(chǎn)生同樣的結(jié)果,則被認為它是“確定的”,否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC,默認的就是NOT DETERMINISTIC(非確定的)CONTAINS SQL:表示子程序不包含讀或?qū)憯?shù)據(jù)的語句。

NO SQL:表示子程序不包含SQL語句。

READS SQL DATA:表示子程序包含讀數(shù)據(jù)的語句,但不包含寫數(shù)據(jù)的語句。

MODIFIES SQL DATA:表示子程序包含寫數(shù)據(jù)的語句。

SQL SECURITY DEFINER:表示執(zhí)行存儲過程中的程序是由創(chuàng)建該存儲過程的用戶的權(quán)限來執(zhí)行。

SQL SECURITY INVOKER:表示執(zhí)行存儲過程中的程序是由調(diào)用該存儲過程的用戶的權(quán)限來執(zhí)行。(例如上面的存儲過程我寫的是由調(diào)用該存儲過程的用戶的權(quán)限來執(zhí)行,當(dāng)前存儲過程是用來查詢Employee表,如果我當(dāng)前執(zhí)行存儲過程的用戶沒有查詢Employee表的權(quán)限那么就會返回權(quán)限不足的錯誤,如果換成DEFINER如果存儲過程是由ROOT用戶創(chuàng)建那么任何一個用戶登入調(diào)用存儲過程都可以執(zhí)行,因為執(zhí)行存儲過程的權(quán)限變成了root)

COMMENT 'string':備注,和創(chuàng)建表的字段備注一樣。

注意:在編寫存儲過程和函數(shù)時建議明確指定上面characteristic部分的狀態(tài),特別是存在復(fù)制的環(huán)境中,如果創(chuàng)建函數(shù)不明確指定這些狀態(tài)會報錯,從一個非復(fù)制環(huán)境將帶函數(shù)的數(shù)據(jù)庫遷移到復(fù)制環(huán)境的機器上如果沒有明確指定DETERMINISTIC, NO SQL, or READS SQL DATA該三個狀態(tài)也會報錯。

報錯示例

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)
這個報錯就是上面注意部分說的問題。原來是因為在主從復(fù)制的兩臺MySQL服務(wù)器中開啟了二進制日志選項log-bin,slave會從master復(fù)制數(shù)據(jù),而一些操作,比如function所得的結(jié)果在master和slave上可能不同,所以存在潛在的安全隱患。因此,在默認情況下回阻止function的創(chuàng)建。

解決辦法有兩種:

1.將log_bin_trust_function_creators參數(shù)設(shè)置為ON,這樣一來開啟了log-bin的MySQL Server便可以隨意創(chuàng)建function。這里存在潛在的數(shù)據(jù)安全問題,除非明確的知道創(chuàng)建的function在master和slave上的行為完全一致。
  設(shè)置該參數(shù)可以用動態(tài)的方式或者指定該參數(shù)來啟動數(shù)據(jù)庫服務(wù)器或者修改配置文件后重啟服務(wù)器。需注意的是,動態(tài)設(shè)置的方式會在服務(wù)器重啟后失效。

mysql> show variables like 'log_bin_trust_function_creators';
 mysql> set global log_bin_trust_function_creators=1;

  另外如果是在master上創(chuàng)建函數(shù),想通過主從復(fù)制的方式將函數(shù)復(fù)制到slave上則也需在開啟了log-bin的slave中設(shè)置上述變量的值為ON(變量的設(shè)置不會從master復(fù)制到slave上,這點需要注意),否則主從復(fù)制會報錯。

2.明確指明函數(shù)的類型
  1 )、DETERMINISTIC 不確定的
  2 )、NO SQL 沒有SQl語句,當(dāng)然也不會修改數(shù)據(jù)
  3 )、READS SQL DATA 只是讀取數(shù)據(jù),當(dāng)然也不會修改數(shù)據(jù)
比如:CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
這樣一來相當(dāng)于明確的告知MySQL服務(wù)器這個函數(shù)不會修改數(shù)據(jù),因此可以在開啟了log-bin的服務(wù)器上安全的創(chuàng)建并被復(fù)制到開啟了log-bin的slave上。

修改存儲過程函數(shù)語法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 
characteristic:
  { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'

刪除存儲過程函數(shù)語法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存儲過程和函數(shù)

1.查看存儲過程狀態(tài)

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
show procedure status like 'Pro_Employee' \G

2.查看存儲過程和函數(shù)的創(chuàng)建語法

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;

3.查看存儲過程和函數(shù)詳細信息

復(fù)制代碼 代碼如下:
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' \G;

總結(jié) 

 存儲過程和函數(shù)語法不難理解,但是往往存儲過程中不單單只包含這種簡單的查詢語法,還會嵌套循環(huán)語句、變量、報錯處理、事務(wù)等,下一篇文章會單獨講變量,將變量的知識加入到存儲過程,包括變量的聲明和報錯處理,歡迎關(guān)注。

相關(guān)文章

  • mysql如何將id重新修改為遞增

    mysql如何將id重新修改為遞增

    這篇文章主要介紹了mysql如何將id重新修改為遞增,本文模擬場景結(jié)合實例代碼給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2023-08-08
  • mysql定時自動備份數(shù)據(jù)庫的方法步驟

    mysql定時自動備份數(shù)據(jù)庫的方法步驟

    我們都知道數(shù)據(jù)是無價,如果不對數(shù)據(jù)進行備份,相當(dāng)是讓數(shù)據(jù)在裸跑,本文就介紹一下如何給mysql定時自動備份數(shù)據(jù),感興趣的小伙伴們可以參考一下
    2021-07-07
  • MySQL v5.7.18 解壓版本安裝詳細教程

    MySQL v5.7.18 解壓版本安裝詳細教程

    這篇文章主要介紹了MySQL v5.7.18 解壓版本安裝詳細教程,非常不錯,具有參考借鑒價值,需要的朋友可以參考下
    2017-04-04
  • MySQL事務(wù)日志(redo?log和undo?log)的詳細分析

    MySQL事務(wù)日志(redo?log和undo?log)的詳細分析

    innodb事務(wù)日志包括redo?log和undo?log,redo?log是重做日志,提供前滾操作,undo?log是回滾日志,提供回滾操作,下面這篇文章主要給大家介紹了關(guān)于MySQL事務(wù)日志(redo?log和undo?log)的詳細分析,需要的朋友可以參考下
    2022-04-04
  • Mac 安裝和卸載 Mysql5.7.11 的方法

    Mac 安裝和卸載 Mysql5.7.11 的方法

    本文給大家介紹Mac 安裝和卸載 Mysql5.7.11 的方法,本文介紹的非常詳細,具有參考借鑒價值,感興趣的朋友一起學(xué)習(xí)吧
    2016-03-03
  • MySQL 兩種恢復(fù)數(shù)據(jù)的方法

    MySQL 兩種恢復(fù)數(shù)據(jù)的方法

    這篇文章主要介紹了MySQL 兩種恢復(fù)數(shù)據(jù)的方法,幫助恢復(fù)線上數(shù)據(jù),保證數(shù)據(jù)完整,感興趣的朋友可以了解下
    2020-10-10
  • mysql升級到5.7時,wordpress導(dǎo)數(shù)據(jù)報錯1067的問題

    mysql升級到5.7時,wordpress導(dǎo)數(shù)據(jù)報錯1067的問題

    小編最近把mysql升級到5.7了,wordpress導(dǎo)數(shù)據(jù)報錯,導(dǎo)入數(shù)據(jù)庫時報1067 – Invalid default value for ‘字段名’的問題,怎么解決這個問題,下面小編把我的解決方案分享到腳本之家平臺供大家參考,希望對大家有所幫助
    2021-05-05
  • MySQL索引詳細解析

    MySQL索引詳細解析

    索引是有雙面性的,合理的建立索引可以提高數(shù)據(jù)庫的效率。但是如果沒有合理的構(gòu)建索引和使用索引,可能會導(dǎo)致索引失效或者影響數(shù)據(jù)庫性能,這篇文章主要介紹了MySql索引原理與操作
    2022-10-10
  • MySQL索引類型Normal、Unique和Full Text的講解

    MySQL索引類型Normal、Unique和Full Text的講解

    今天小編就為大家分享一篇關(guān)于MySQL索引類型Normal、Unique和Full Text的講解,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
    2019-03-03
  • log引起的mysql不能啟動的解決方法

    log引起的mysql不能啟動的解決方法

    今天服務(wù)器掛了原來服務(wù)器的硬盤進行了數(shù)據(jù)轉(zhuǎn)移 弄到mysql的時候發(fā)現(xiàn)里面log日志文件高達900MB
    2008-07-07

最新評論