或許你曾經(jīng)去面試的時(shí)候被問(wèn)到過(guò)關(guān)于mysql數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程和觸發(fā)器的問(wèn)題，如果你還不懂可以看下這篇關(guān)于存儲(chǔ)過(guò)程和觸發(fā)器的文章，希望能幫助到有需要的朋友。

[[255513]]

Mysql存儲(chǔ)過(guò)程與觸發(fā)器

本篇文章主要是簡(jiǎn)單解釋mysql中存儲(chǔ)過(guò)程的創(chuàng)建、調(diào)用以及介紹觸發(fā)器和如何創(chuàng)建觸發(fā)器。那么關(guān)于存儲(chǔ)過(guò)程和觸發(fā)器那些官方理論的介紹我就不在這里啰嗦了。

1數(shù)據(jù)表的準(zhǔn)備

下面所有例子中用到的表的創(chuàng)建腳本。tb_user是下面例子中的用戶(hù)表，tb_blog是博客表，tb_user_log是用戶(hù)信息更新日記表。

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默認(rèn)以';'作為語(yǔ)句結(jié)束符。我們都知道，在mysql命令行模式下，當(dāng)輸入一條語(yǔ)句時(shí)，如果不加‘;’回車(chē)是不會(huì)執(zhí)行輸入的sql語(yǔ)句的。如：

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的作用就是修改語(yǔ)句結(jié)束符，如delimiter &就是將sql語(yǔ)句的結(jié)束為定義為'&'符號(hào),當(dāng)遇到'&'符號(hào)時(shí)，mysql判斷為語(yǔ)句輸入完成就會(huì)執(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 //的作用是將'//'作為語(yǔ)句的結(jié)束符，'//'可以是其他的字符，比如上面例子中使用'&';

那么為什么編寫(xiě)存儲(chǔ)過(guò)程和觸發(fā)器我們需要將默認(rèn)的';'修改為'//'作為sql語(yǔ)句結(jié)束符呢?因?yàn)槲覀円诖鎯?chǔ)過(guò)程或觸發(fā)器中執(zhí)行sql語(yǔ)句，所以會(huì)用到';'，如果不改其它符號(hào)而使用';'作為語(yǔ)句結(jié)束符的話(huà)，mysql遇到';'就當(dāng)作一條語(yǔ)句完成了，而存儲(chǔ)過(guò)程或觸發(fā)器的sql語(yǔ)句都沒(méi)寫(xiě)完全呢，這樣只會(huì)ERROR。

注意，在使用delimiter //將sql語(yǔ)句結(jié)束符改為'//'用完后(如完成創(chuàng)建存儲(chǔ)過(guò)程)記得要使用delimiter ;將sql語(yǔ)句結(jié)束符改回為默認(rèn)。

3存儲(chǔ)過(guò)程

先來(lái)看兩個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程實(shí)例，對(duì)存儲(chǔ)過(guò)程的創(chuàng)建和調(diào)用有一個(gè)模糊的印象。

#實(shí)例一：創(chuàng)建查詢(xún)所有博客的存儲(chǔ)過(guò)程 
drop procedure if exists select_procedure 
delimiter // 
create procedure select_procedure() 
    begin 
        select * from tb_blog; 
    end // 
delimiter ; 
#調(diào)用 
call select_procedure; 
 
 
#實(shí)例二：更新博客修改時(shí)間的存儲(chǔ)過(guò)程 
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()獲取當(dāng)前日期+時(shí)間字符串（24小時(shí)格式） 
    end // 
delimiter ; 
#調(diào)用 
call update_blog_updatedate(2); 

好，下面我通過(guò)一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程實(shí)例來(lái)分析如何創(chuàng)建一個(gè)存儲(chǔ)過(guò)程。先看例子：

#創(chuàng)建更新博客標(biāo)題的存儲(chǔ)過(guò)程 
drop procedure if exists update_blog;#如果存在該存儲(chǔ)過(guò)程先刪除 
delimiter // 
create procedure update_blog(blogid int(11)) 
    begin 
        start transaction;#開(kāi)啟事務(wù) 
        update tb_blog set title='dsssss' where id=blogid;#要做的事情 
        commit;#提交事務(wù) 
    end // 
delimiter ; 

上面實(shí)際創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句為

create procedure update_blog(blogid int(11))#(參數(shù)1 參數(shù)類(lèi)型(長(zhǎng)度),參數(shù)2 參數(shù)類(lèi)型(長(zhǎng)度),...) 
    begin 
        start transaction;#開(kāi)啟事務(wù) 
        update tb_blog set title='dsssss' where id=blogid;#要做的事情 
        commit;#提交事務(wù) 
    end // 

end后面的'//'是sql語(yǔ)句結(jié)束符，就是前面用delimiter //修改的sql語(yǔ)句結(jié)束符，所以從create到//就是一條完整的創(chuàng)建存儲(chǔ)過(guò)程的sql語(yǔ)句。那么為什么還要在前面加一條drop procedure if exists update_blog?其實(shí)你可以不加的，這條語(yǔ)句的作用只是當(dāng)要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程已經(jīng)存在同名的存儲(chǔ)過(guò)程時(shí)將已經(jīng)存在的存儲(chǔ)過(guò)程刪除。

現(xiàn)在再來(lái)解析創(chuàng)建存儲(chǔ)過(guò)程的這條語(yǔ)句，其中，update_blog時(shí)存儲(chǔ)過(guò)程的名稱(chēng)，()內(nèi)是調(diào)用該存儲(chǔ)過(guò)程時(shí)要傳遞的參數(shù)，參數(shù)個(gè)數(shù)不限制，參數(shù)間用','分割，參數(shù)要聲明類(lèi)型，如blogid int(11)，blogid就是參數(shù)名，int是類(lèi)型，如果要指定長(zhǎng)度則在類(lèi)型后面加'(長(zhǎng)度)'。

begin和end之間就是存儲(chǔ)過(guò)程要做的事情。

使用call+存儲(chǔ)過(guò)程名稱(chēng)來(lái)調(diào)用存儲(chǔ)過(guò)程，如果存儲(chǔ)過(guò)程定義了參數(shù)，那么需要在調(diào)用的時(shí)候傳入?yún)?shù)，否則調(diào)用失敗。

call update_blog(2);#調(diào)用存儲(chǔ)過(guò)程 

下面來(lái)看一個(gè)稍微成型點(diǎn)的存儲(chǔ)過(guò)程。

# 創(chuàng)建批量更新的存儲(chǔ)過(guò)程 
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，類(lèi)型為int，默認(rèn)值為0 
        declare blog_count int default 0; 
        declare bid int; 
        select count(*) into blog_count from tb_blog;#into blog_count 將查詢(xún)結(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)鍵字。定義變量必須是在存儲(chǔ)過(guò)程的內(nèi)部，即begin和end之間。變量的定義方式是declare關(guān)鍵字加變量名加變量類(lèi)型，如果想指定默認(rèn)值就在類(lèi)型后面加上“default 默認(rèn)值”。

select count(*) into blog_count from tb_blog語(yǔ)句是獲取tb_blog表的總數(shù)賦值給blog_count，將查詢(xún)結(jié)果賦值給某個(gè)變量使用into關(guān)鍵字。

set關(guān)鍵字是修改變量的值，將一個(gè)新的值寫(xiě)給set指定的變量。其它的就不做解釋了，看不懂就需要學(xué)一下mysql的條件語(yǔ)句與循環(huán)語(yǔ)句了。

4Mysql中的觸發(fā)器

觸發(fā)器是什么?

觸發(fā)器就是一個(gè)函數(shù)，當(dāng)滿(mǎn)足某種條件時(shí)才會(huì)觸發(fā)其執(zhí)行。

什么情況下使用觸發(fā)器?

比如我們要為用戶(hù)所做的個(gè)人信息修改記錄一條變更日記，那么是不是需要在修改完用戶(hù)信息之后添加一條日記記錄?如果不使用觸發(fā)器我們就需要執(zhí)行兩條sql語(yǔ)句，***條是修改用戶(hù)信息的sql語(yǔ)句，第二條是添加一個(gè)日記記錄的sql語(yǔ)句。我們?cè)趯?xiě)業(yè)務(wù)邏輯代碼的時(shí)候如果在多處地方可能對(duì)用戶(hù)信息修改，在某處忘記了寫(xiě)日記記錄也不奇怪。而如果使用觸發(fā)器，當(dāng)用戶(hù)信息修改時(shí)觸發(fā)觸發(fā)器執(zhí)行添加一條日記記錄，這樣也會(huì)比在業(yè)務(wù)代碼中執(zhí)行兩條sql語(yǔ)句效率要高。

那么如果創(chuàng)建一個(gè)觸發(fā)器呢?

create trigger 觸發(fā)器名稱(chēng) after|before insert|delete|update on 表名 for each row 
begin 
    #觸發(fā)器要做的事情 
end 

• 表名：將改觸發(fā)器的觸發(fā)條件掛載在哪張表上，也就是指定哪張表的操作滿(mǎn)足條件時(shí)觸發(fā)該觸發(fā)器。
• 觸發(fā)器執(zhí)行時(shí)機(jī)：after或者before，即之前還是之后。
• 觸發(fā)的條件：insert|delete|update 即可選增刪改時(shí)觸發(fā);比如alter insert，就是在添加完成之后觸發(fā)，執(zhí)行時(shí)機(jī)與觸發(fā)條件可隨意組合使用，即

before insert

before delete

before update

after insert

after delete

after update

• for each row表示任何一條記錄的操作滿(mǎn)足觸發(fā)條件都會(huì)觸發(fā)觸發(fā)器執(zhí)行。

下面來(lái)看一個(gè)實(shí)例：在用戶(hù)信息表tb_user中的記錄被修改之后添加一條日記記錄，記錄修改時(shí)間和修改內(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是修改前的舊的紀(jì)錄
• sysdate函數(shù)是獲取當(dāng)前系統(tǒng)日期時(shí)間字符串

下面我們執(zhí)行一條sql來(lái)觸發(fā)該觸發(fā)器

update tb_user set uname='new_name' where id = 1; 

查看日記表中是否添加了一條記錄。