什么？還不了解MySQLl存儲過程與觸發(fā)器的創(chuàng)建使用？
或許你曾經(jīng)去面試的時候被問到過關(guān)于mysql數(shù)據(jù)庫的存儲過程和觸發(fā)器的問題，如果你還不懂可以看下這篇關(guān)于存儲過程和觸發(fā)器的文章，希望能幫助到有需要的朋友。
Mysql存儲過程與觸發(fā)器
本篇文章主要是簡單解釋mysql中存儲過程的創(chuàng)建、調(diào)用以及介紹觸發(fā)器和如何創(chuàng)建觸發(fā)器。那么關(guān)于存儲過程和觸發(fā)器那些官方理論的介紹我就不在這里啰嗦了。
1數(shù)據(jù)表的準備
下面所有例子中用到的表的創(chuàng)建腳本。tb_user是下面例子中的用戶表，tb_blog是博客表，tb_user_log是用戶信息更新日記表。
- use db_mybatis;
- create table tb_user(
- id int(11) unsigned not null auto_increment,
- uname varchar(50) not null,
- pwd varchar(50) not null,
- primary key (id)
- )engine=InnoDB default charset=utf8;
- create table tb_blog(
- id int(11) unsigned not null auto_increment,
- title varchar(50) not null,
- details varchar(50) not null,
- create_date datetime not null,
- update_date datetime not null,
- primary key (id)
- )engine=InnoDB default charset=utf8;
- create table tb_user_log(
- id int(11) unsigned not null auto_increment,
- create_date datetime not null,
- details varchar(255) not null,
- primary key (id)
- )engine=InnoDB default charset=utf8;
2“delimiter //”的解釋
mysql默認以';'作為語句結(jié)束符。我們都知道，在mysql命令行模式下，當輸入一條語句時，如果不加‘;’回車是不會執(zhí)行輸入的sql語句的。如：
- mysql> select * from tb_blog
- ->
- ->
- ->
- ->
- -> ;
- +----+--------+--------------+---------------------+---------------------+
- | id | title | details | create_date | update_date |
- +----+--------+--------------+---------------------+---------------------+
- | 2 | dsssss | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-15 16:39:16 |
- | 3 | new1 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- | 4 | new2 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- | 5 | new3 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- | 6 | new4 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- +----+--------+--------------+---------------------+---------------------+
- 5 rows in set (0.01 sec)
而delimiter的作用就是修改語句結(jié)束符，如delimiter &就是將sql語句的結(jié)束為定義為'&'符號,當遇到'&'符號時，mysql判斷為語句輸入完成就會執(zhí)行，看下面例子：
- mysql> delimiter &
- mysql> select * from tb_blog
- ->
- -> &
- +----+--------+--------------+---------------------+---------------------+
- | id | title | details | create_date | update_date |
- +----+--------+--------------+---------------------+---------------------+
- | 2 | dsssss | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-15 16:42:54 |
- | 3 | new1 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- | 4 | new2 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- | 5 | new3 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- | 6 | new4 | 這是內(nèi)容 | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 |
- +----+--------+--------------+---------------------+---------------------+
- 5 rows in set (0.00 sec)
所以，delimiter //的作用是將'//'作為語句的結(jié)束符，'//'可以是其他的字符，比如上面例子中使用'&';
那么為什么編寫存儲過程和觸發(fā)器我們需要將默認的';'修改為'//'作為sql語句結(jié)束符呢?因為我們要在存儲過程或觸發(fā)器中執(zhí)行sql語句，所以會用到';'，如果不改其它符號而使用';'作為語句結(jié)束符的話，mysql遇到';'就當作一條語句完成了，而存儲過程或觸發(fā)器的sql語句都沒寫完全呢，這樣只會ERROR。
注意，在使用delimiter //將sql語句結(jié)束符改為'//'用完后(如完成創(chuàng)建存儲過程)記得要使用delimiter ;將sql語句結(jié)束符改回為默認。
3存儲過程
先來看兩個簡單的存儲過程實例，對存儲過程的創(chuàng)建和調(diào)用有一個模糊的印象。
- #實例一：創(chuàng)建查詢所有博客的存儲過程
- drop procedure if exists select_procedure
- delimiter //
- create procedure select_procedure()
- begin
- select * from tb_blog;
- end //
- delimiter ;
- #調(diào)用
- call select_procedure;
- #實例二：更新博客修改時間的存儲過程
- drop procedure if exists update_blog_updatedate;
- delimiter //
- create procedure update_blog_updatedate(blogid int(11))
- begin
- update tb_blog set update_date = sysdate() where id = blogid;#sysdate()獲取當前日期+時間字符串（24小時格式）
- end //
- delimiter ;
- #調(diào)用
- call update_blog_updatedate(2);
好，下面我通過一個簡單的存儲過程實例來分析如何創(chuàng)建一個存儲過程。先看例子：
- #創(chuàng)建更新博客標題的存儲過程
- drop procedure if exists update_blog;#如果存在該存儲過程先刪除
- delimiter //
- create procedure update_blog(blogid int(11))
- begin
- start transaction;#開啟事務(wù)
- update tb_blog set title='dsssss' where id=blogid;#要做的事情
- commit;#提交事務(wù)
- end //
- delimiter ;
上面實際創(chuàng)建存儲過程的語句為
- create procedure update_blog(blogid int(11))#(參數(shù)1 參數(shù)類型(長度),參數(shù)2 參數(shù)類型(長度),...)
- begin
- start transaction;#開啟事務(wù)
- update tb_blog set title='dsssss' where id=blogid;#要做的事情
- commit;#提交事務(wù)
- end //
end后面的'//'是sql語句結(jié)束符，就是前面用delimiter //修改的sql語句結(jié)束符，所以從create到//就是一條完整的創(chuàng)建存儲過程的sql語句。那么為什么還要在前面加一條drop procedure if exists update_blog?其實你可以不加的，這條語句的作用只是當要創(chuàng)建的存儲過程已經(jīng)存在同名的存儲過程時將已經(jīng)存在的存儲過程刪除。
現(xiàn)在再來解析創(chuàng)建存儲過程的這條語句，其中，update_blog時存儲過程的名稱，()內(nèi)是調(diào)用該存儲過程時要傳遞的參數(shù)，參數(shù)個數(shù)不限制，參數(shù)間用','分割，參數(shù)要聲明類型，如blogid int(11)，blogid就是參數(shù)名，int是類型，如果要指定長度則在類型后面加'(長度)'。
begin和end之間就是存儲過程要做的事情。
使用call+存儲過程名稱來調(diào)用存儲過程，如果存儲過程定義了參數(shù)，那么需要在調(diào)用的時候傳入?yún)?shù)，否則調(diào)用失敗。
- call update_blog(2);#調(diào)用存儲過程
下面來看一個稍微成型點的存儲過程。
- # 創(chuàng)建批量更新的存儲過程
- drop procedure if exists update_all_blog_date;
- delimiter //
- create procedure update_all_blog_date()
- begin
- declare id_index int(11) default 0;#定義變量id_index，類型為int，默認值為0
- declare blog_count int default 0;
- declare bid int;
- select count(*) into blog_count from tb_blog;#into blog_count 將查詢結(jié)果賦值給blog_count變量
- if blog_count>0 then
- #start transaction;
- while id_index<=blog_count do
- #update tb_blog set update_date = sysdate() where id in
- #(select tb.id from (select id from tb_blog limit id_index,1) as tb);
- #set id_index=id_index+1;
- select id into bid from tb_blog limit id_index,1;
- update tb_blog set update_date = sysdate() where id = bid;
- set id_index=id_index+1;
- end while;
- #commit;
- end if;
- end //
- delimiter ;
- call update_all_blog_date;
解析：
declare是定義變量的關(guān)鍵字，可以理解為javascript中的var關(guān)鍵字。定義變量必須是在存儲過程的內(nèi)部，即begin和end之間。變量的定義方式是declare關(guān)鍵字加變量名加變量類型，如果想指定默認值就在類型后面加上“default 默認值”。
select count(*) into blog_count from tb_blog語句是獲取tb_blog表的總數(shù)賦值給blog_count，將查詢結(jié)果賦值給某個變量使用into關(guān)鍵字。
set關(guān)鍵字是修改變量的值，將一個新的值寫給set指定的變量。其它的就不做解釋了，看不懂就需要學(xué)一下mysql的條件語句與循環(huán)語句了。
4Mysql中的觸發(fā)器
觸發(fā)器是什么?
觸發(fā)器就是一個函數(shù)，當滿足某種條件時才會觸發(fā)其執(zhí)行。
什么情況下使用觸發(fā)器?
比如我們要為用戶所做的個人信息修改記錄一條變更日記，那么是不是需要在修改完用戶信息之后添加一條日記記錄?如果不使用觸發(fā)器我們就需要執(zhí)行兩條sql語句，***條是修改用戶信息的sql語句，第二條是添加一個日記記錄的sql語句。我們在寫業(yè)務(wù)邏輯代碼的時候如果在多處地方可能對用戶信息修改，在某處忘記了寫日記記錄也不奇怪。而如果使用觸發(fā)器，當用戶信息修改時觸發(fā)觸發(fā)器執(zhí)行添加一條日記記錄，這樣也會比在業(yè)務(wù)代碼中執(zhí)行兩條sql語句效率要高。
那么如果創(chuàng)建一個觸發(fā)器呢?
- create trigger 觸發(fā)器名稱 after|before insert|delete|update on 表名 for each row
- begin
- #觸發(fā)器要做的事情
- end
- 表名：將改觸發(fā)器的觸發(fā)條件掛載在哪張表上，也就是指定哪張表的操作滿足條件時觸發(fā)該觸發(fā)器。
- 觸發(fā)器執(zhí)行時機：after或者before，即之前還是之后。
- 觸發(fā)的條件：insert|delete|update 即可選增刪改時觸發(fā);比如alter insert，就是在添加完成之后觸發(fā)，執(zhí)行時機與觸發(fā)條件可隨意組合使用，即
before insert
before delete
before update
after insert
after delete
after update
- for each row表示任何一條記錄的操作滿足觸發(fā)條件都會觸發(fā)觸發(fā)器執(zhí)行。
下面來看一個實例：在用戶信息表tb_user中的記錄被修改之后添加一條日記記錄，記錄修改時間和修改內(nèi)容。
- drop trigger if exists on_user_info_chang_log;
- delimiter //
- create trigger on_user_info_chang_log after update on tb_user for each row
- begin
- declare info varchar(255) charset utf8 default '';
- set info = '修改之前的信息為：[';
- set info = concat(info,NEW.uname);
- set info = concat(info,',');
- set info = concat(info,New.pwd);
- set info = concat(info,'],修改之前的信息為：[');
- set info = concat(info,OLD.uname);
- set info = concat(info,',');
- set info = concat(info,OLD.pwd);
- insert into tb_user_log (create_date,details) value(sysdate(),info);
- end //
- delimiter ;
解析：
- concat函數(shù)是字符串拼接函數(shù)
- NEW是修改后的新的記錄
- OLD是修改前的舊的紀錄
- sysdate函數(shù)是獲取當前系統(tǒng)日期時間字符串
下面我們執(zhí)行一條sql來觸發(fā)該觸發(fā)器
- update tb_user set uname='new_name' where id = 1;
查看日記表中是否添加了一條記錄。
