什么？還不了解MySQLl存儲過程與觸發器的創建使用？

作者：吳就業 2019-01-14 14:41:27

或許你曾經去面試的時候被問到過關于mysql數據庫的存儲過程和觸發器的問題，如果你還不懂可以看下這篇關于存儲過程和觸發器的文章，希望能幫助到有需要的朋友。

Mysql存儲過程與觸發器

本篇文章主要是簡單解釋mysql中存儲過程的創建、調用以及介紹觸發器和如何創建觸發器。那么關于存儲過程和觸發器那些官方理論的介紹我就不在這里啰嗦了。

1數據表的準備

下面所有例子中用到的表的創建腳本。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默認以';'作為語句結束符。我們都知道，在mysql命令行模式下，當輸入一條語句時，如果不加‘;’回車是不會執行輸入的sql語句的。如：

mysql> select * from tb_blog  
    ->  
    ->  
    ->  
    ->  
    -> ; 
+----+--------+--------------+---------------------+---------------------+ 
| id | title  | details      | create_date         | update_date         | 
+----+--------+--------------+---------------------+---------------------+ 
|  2 | dsssss | 這是內容     | 2018-08-13 02:42:44 | 2018-08-15 16:39:16 | 
|  3 | new1   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
|  4 | new2   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
|  5 | new3   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
|  6 | new4   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
+----+--------+--------------+---------------------+---------------------+ 
5 rows in set (0.01 sec)

而delimiter的作用就是修改語句結束符，如delimiter &就是將sql語句的結束為定義為'&'符號,當遇到'&'符號時，mysql判斷為語句輸入完成就會執行，看下面例子：

mysql> delimiter & 
mysql> select * from tb_blog 
    ->  
    -> & 
+----+--------+--------------+---------------------+---------------------+ 
| id | title  | details      | create_date         | update_date         | 
+----+--------+--------------+---------------------+---------------------+ 
|  2 | dsssss | 這是內容     | 2018-08-13 02:42:44 | 2018-08-15 16:42:54 | 
|  3 | new1   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
|  4 | new2   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
|  5 | new3   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
|  6 | new4   | 這是內容     | 2018-08-13 02:42:44 | 2018-08-13 22:04:21 | 
+----+--------+--------------+---------------------+---------------------+ 
5 rows in set (0.00 sec)

所以，delimiter //的作用是將'//'作為語句的結束符，'//'可以是其他的字符，比如上面例子中使用'&';

那么為什么編寫存儲過程和觸發器我們需要將默認的';'修改為'//'作為sql語句結束符呢?因為我們要在存儲過程或觸發器中執行sql語句，所以會用到';'，如果不改其它符號而使用';'作為語句結束符的話，mysql遇到';'就當作一條語句完成了，而存儲過程或觸發器的sql語句都沒寫完全呢，這樣只會ERROR。

注意，在使用delimiter //將sql語句結束符改為'//'用完后(如完成創建存儲過程)記得要使用delimiter ;將sql語句結束符改回為默認。

3存儲過程

先來看兩個簡單的存儲過程實例，對存儲過程的創建和調用有一個模糊的印象。

#實例一：創建查詢所有博客的存儲過程 
drop procedure if exists select_procedure 
delimiter // 
create procedure select_procedure() 
    begin 
        select * from tb_blog; 
    end // 
delimiter ; 
#調用 
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 ; 
#調用 
call update_blog_updatedate(2);

好，下面我通過一個簡單的存儲過程實例來分析如何創建一個存儲過程。先看例子：

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

上面實際創建存儲過程的語句為

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

end后面的'//'是sql語句結束符，就是前面用delimiter //修改的sql語句結束符，所以從create到//就是一條完整的創建存儲過程的sql語句。那么為什么還要在前面加一條drop procedure if exists update_blog?其實你可以不加的，這條語句的作用只是當要創建的存儲過程已經存在同名的存儲過程時將已經存在的存儲過程刪除。

現在再來解析創建存儲過程的這條語句，其中，update_blog時存儲過程的名稱，()內是調用該存儲過程時要傳遞的參數，參數個數不限制，參數間用','分割，參數要聲明類型，如blogid int(11)，blogid就是參數名，int是類型，如果要指定長度則在類型后面加'(長度)'。

begin和end之間就是存儲過程要做的事情。

使用call+存儲過程名稱來調用存儲過程，如果存儲過程定義了參數，那么需要在調用的時候傳入參數，否則調用失敗。

call update_blog(2);#調用存儲過程

下面來看一個稍微成型點的存儲過程。

# 創建批量更新的存儲過程 
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 將查詢結果賦值給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是定義變量的關鍵字，可以理解為javascript中的var關鍵字。定義變量必須是在存儲過程的內部，即begin和end之間。變量的定義方式是declare關鍵字加變量名加變量類型，如果想指定默認值就在類型后面加上“default 默認值”。

select count(*) into blog_count from tb_blog語句是獲取tb_blog表的總數賦值給blog_count，將查詢結果賦值給某個變量使用into關鍵字。

set關鍵字是修改變量的值，將一個新的值寫給set指定的變量。其它的就不做解釋了，看不懂就需要學一下mysql的條件語句與循環語句了。

4Mysql中的觸發器

觸發器是什么?

觸發器就是一個函數，當滿足某種條件時才會觸發其執行。

什么情況下使用觸發器?

比如我們要為用戶所做的個人信息修改記錄一條變更日記，那么是不是需要在修改完用戶信息之后添加一條日記記錄?如果不使用觸發器我們就需要執行兩條sql語句，***條是修改用戶信息的sql語句，第二條是添加一個日記記錄的sql語句。我們在寫業務邏輯代碼的時候如果在多處地方可能對用戶信息修改，在某處忘記了寫日記記錄也不奇怪。而如果使用觸發器，當用戶信息修改時觸發觸發器執行添加一條日記記錄，這樣也會比在業務代碼中執行兩條sql語句效率要高。

那么如果創建一個觸發器呢?

create trigger 觸發器名稱 after|before insert|delete|update on 表名 for each row 
begin 
    #觸發器要做的事情 
end

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

before insert

before delete

before update

after insert

after delete

after update

for each row表示任何一條記錄的操作滿足觸發條件都會觸發觸發器執行。

下面來看一個實例：在用戶信息表tb_user中的記錄被修改之后添加一條日記記錄，記錄修改時間和修改內容。

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函數是字符串拼接函數
NEW是修改后的新的記錄
OLD是修改前的舊的紀錄
sysdate函數是獲取當前系統日期時間字符串

下面我們執行一條sql來觸發該觸發器

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

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

責任編輯：武曉燕來源： java藝術

Mysql 存儲觸發器