MySQL中的存儲過程和觸發(fā)器詳解

更新時間：2025年09月08日 09:55:25 作者：BUG召喚師

本文系統(tǒng)介紹了MySQL存儲過程的創(chuàng)建與使用,包括變量定義、條件判斷、循環(huán)控制、游標(biāo)操作等核心知識,詳細(xì)講解了存儲過程的優(yōu)缺點分析,以及存儲函數(shù)與觸發(fā)器的區(qū)別與應(yīng)用場景,通過具體示例演示了如何實現(xiàn)參數(shù)傳遞、條件處理、日志記錄等功能

一、存儲過程

1. 存儲過程介紹

MySQL 的存儲過程是為了完成特定功能的一組 sql 語句集，用戶通過存儲過程的名字和參數(shù)進(jìn)行調(diào)用，類似于 java 中的方法。

將業(yè)務(wù)邏輯封裝在了數(shù)據(jù)庫內(nèi)部，減少了應(yīng)用程序的復(fù)雜性；
集中管理數(shù)據(jù)庫的操作，便于維護(hù)和更新；
可以被多次調(diào)用，代碼重用性高；

2. 優(yōu)缺點分析

存儲過程的優(yōu)點：

1. 存儲過程在創(chuàng)建時，編譯并保存在數(shù)據(jù)庫中，執(zhí)行速度比單個的 sql 要快；
2. 存儲過程可以重復(fù)調(diào)用，減少重復(fù)代碼，提高代碼的可維護(hù)性；
3. 可以限制用于直接訪問數(shù)據(jù)庫，通過存儲過程間接訪問，從而保證系統(tǒng)的安全性；
4. 可以在存儲過程中實現(xiàn)復(fù)雜的事務(wù)邏輯；
5. 當(dāng)表結(jié)構(gòu)發(fā)生變化，只需要響應(yīng)修改存儲過程，應(yīng)用程序只需要關(guān)注存儲過程的接口，代碼改動較小，因此存儲過程降低了代碼的耦合性；

存儲過程的缺點：

1. 存儲過程的可移植性差，更換數(shù)據(jù)庫后，需要重新編寫存儲過程；
2. 只有少數(shù)數(shù)據(jù)庫支持存儲過程的代碼調(diào)試，因此維護(hù)困難；
3. 數(shù)據(jù)庫的主要功能是保存和查詢數(shù)據(jù)，數(shù)據(jù)是保存在硬盤中，存儲和查詢數(shù)據(jù)都涉及到硬盤 IO，效率不高，數(shù)據(jù)庫常常稱為系統(tǒng)的效率瓶頸。對于高并發(fā)場景，將業(yè)務(wù)邏輯放在數(shù)據(jù)庫層面執(zhí)行，會增加數(shù)據(jù)庫的壓力，進(jìn)一步導(dǎo)致效率降低。

3. 存儲過程的語法

delimiter 關(guān)鍵字用于定義語句的結(jié)束標(biāo)識符，例如 "delimiter //" 就是將結(jié)束標(biāo)識符定義為 "//"；

-- 修改 sql 語句結(jié)束標(biāo)識符為 //
delimiter //

-- 創(chuàng)建存儲過程
create procedure 存儲過程名(參數(shù), 參數(shù)...)
begin
    select 列名, 列名... from exam;
end//

-- 修改 sql 語句結(jié)束標(biāo)識符為 ;
delimiter;

-- 查看 topoc01 中的所有存儲過程
select * from information_schema.routines where routine_schema = 數(shù)據(jù)庫名;

-- 查看存儲過程的定義
show create procedure 存儲過程名;

-- 調(diào)用存儲過程
call 存儲過程名(參數(shù), 參數(shù)...);

-- 刪除存儲過程
drop procedure if exists 存儲過程名;

注意：

創(chuàng)建存儲過程中，begin 后面的 sql 語句，是需要寫分號的；
如果不使用 delimiter 改變語句的結(jié)束標(biāo)識符，在命令行中遇到分號就會認(rèn)為語句結(jié)束了，存儲過程就會創(chuàng)建失??；
改變語句的結(jié)束標(biāo)識符為 "//" 后，命令行中遇到 "//" 后才會認(rèn)為語句結(jié)束，因此能夠創(chuàng)建成功；
創(chuàng)建成功后，記得將結(jié)束表示符修改為分號，避免后續(xù)寫其他 sql 時報錯；

二、變量

1. 系統(tǒng)變量

系統(tǒng)變量分為全局變量和會話變量；

查詢系統(tǒng)變量：

-- 查看系統(tǒng)變量
show global variables;

-- 查看會話變量
show session variables;

-- 搭配模糊查詢 - 全局變量
show global variables like 'xxx%';

-- 搭配模糊查詢 - 會話變量
show session variables like '%xxx%';

-- 使用 select 進(jìn)行精確查詢 - 全局變量
select @@global.系統(tǒng)變量名;

-- 使用 select 進(jìn)行精確查詢 - 會話變量
select @@session.系統(tǒng)變量名;

注意：

如果不指定時全局變量或者會話變量，默認(rèn)是會話變量；

設(shè)置系統(tǒng)變量：

-- 設(shè)置系統(tǒng)變量 - 全局變量
set global 系統(tǒng)變量名 = 值;

-- 設(shè)置系統(tǒng)變量 - 會話變量
set session 系統(tǒng)變量名 = 值;

-- 設(shè)置系統(tǒng)變量 - 全局變量
set @@global.全局變量名 = 值;

-- 設(shè)置系統(tǒng)變量 - 會話變量
set @@session.會話變量名 = 值;

注意：

如果沒有指定 global 或者 session，修改的默認(rèn)為會話變量；
會話關(guān)閉后，設(shè)置的會話變量失效；新建的會話默認(rèn)讀全局系統(tǒng)變量的值為初始值；
MySQL 服務(wù)器重啟后，設(shè)置的全局變量失效；如果想使系統(tǒng)變量永久生效，需要修改配置文件；

2. 用戶自定義變量

用戶自定義變量是 sql 會話中定義的變量，不用提前申明，作用域為當(dāng)前會話；

-- 申明 + 賦值
set @var_name := 表達(dá)式;

select @var_name :=  表達(dá)式;

select 列名 into @var_name from 表名;

-- 使用自定義變量
select @var_name;

3. 局部變量

局部變量只在存儲過程，函數(shù)或者觸發(fā)器的范圍內(nèi)有效；

需要使用關(guān)鍵字 declare 申明，作用域為 begin 和 end 中間的代碼塊內(nèi)；

-- 局部變量申明的語法
declare 局部變量名 類型 default 默認(rèn)值;

-- 局部變量在存儲過程中的應(yīng)用

delimiter//

-- 定義存儲過程
create procedure p1()
begin 
    -- 定義局部變量
    declare stu_count int default 0;
    -- 給局部變量賦值
    select count(*) into stu_count from student;
    -- 使用局部變量
    select stu_count;
end//

delimiter ;

三、SQL 編程

1. 條件判斷語句

-- 條件判斷語句
if 條件1 then
    具體執(zhí)行邏輯;
elseif 條件2 then
    具體執(zhí)行邏輯;
...
else
    具體執(zhí)行邏輯;
end if;

示例：

-- 練習(xí)
-- 分?jǐn)?shù)>=90分等級為優(yōu)秀
-- 分?jǐn)?shù)>=80且分?jǐn)?shù)<90分等級為良好
-- 分?jǐn)?shù)>=60分且分?jǐn)?shù)<80分等級為及格
-- 分?jǐn)?shù)<60分等級為不及格

delimiter //

create procedure p1()
begin
    declare score int default 86;
    declare result varchar(20);

    if score >= 90 then
        set result := '優(yōu)秀';
    elseif score >=80 then
        set result := '良好';
    elseif score >= 60 then 
        set result := '及格';
    else
        set result := '不及格';
    end if;

    select result;
end//

delimiter ;

call p1();

2. 參數(shù)

參數(shù)類型有三種：in（輸入型），out（輸出型），inout（輸入輸出型）；

示例：

-- 練習(xí)
-- 傳??個分?jǐn)?shù)的值，判定當(dāng)前分?jǐn)?shù)對應(yīng)的等級 
-- 分?jǐn)?shù)>=90分等級為優(yōu)秀
-- 分?jǐn)?shù)>=80且分?jǐn)?shù)<90分等級為良好
-- 分?jǐn)?shù)>=60分且分?jǐn)?shù)<80分等級為及格
-- 分?jǐn)?shù)<60分等級為不及格

delimiter //

create procedure p2(in score int, out result varchar(20))
begin
    if score >= 90 then
        set result := '優(yōu)秀';
    elseif score >=80 then
        set result := '良好';
    elseif score >= 60 then 
        set result := '及格';
    else
        set result := '不及格';
    end if;
end//

delimiter ;

-- 調(diào)用
call p2(86, @result);
select @result;

-- 傳??個分?jǐn)?shù)的值，在傳?分?jǐn)?shù)的基礎(chǔ)上加10分，然后返回
delimiter //

create procedure p3(inout score int)
begin
    set score = score + 10;
end//

delimiter ;

set @score := 50;
call p3(@score);
select @score;

3. case 判斷語句

-- 語法?
 
CASE case_value
 WHEN when_value THEN statement_list
 [WHEN when_value THEN statement_list] ...
 [ELSE statement_list]
END CASE

 
-- 語法?
 
statement_list 
(如果存在
CASE
 WHEN search_condition THEN statement_list
 [WHEN search_condition THEN statement_list] ...
 [ELSE statement_list]
END CASE

示例：

-- ?例?：傳??個狀態(tài)碼，輸出該狀態(tài)碼表?的含義
-- 0：成功
-- 10001：??名或密碼錯誤
-- 10002：您沒有對應(yīng)的權(quán)限，請聯(lián)系管理員
-- 20001：你傳?的參數(shù)有誤
-- 20002：沒有找到相應(yīng)的結(jié)果

delimiter //

create procedure p4(in code int, out result varchar(20))
begin
    case code
        when 0 then 
            set result := '成功';
        when 10001 then 
            set result := '用戶名或密碼錯誤';
        when 10002 then 
            set result := '您沒有對應(yīng)的權(quán)限，請聯(lián)系管理員';
        when 20001 then 
            set result := '你傳?的參數(shù)有誤';
        when 20002 then 
            set result := '沒有找到相應(yīng)的結(jié)果';
        else 
            set result = '服務(wù)器錯誤，請聯(lián)系管理員';
    end case;
end//

delimiter ;

call p4(10001, @result);
select @result;


-- ?例?：根據(jù)傳?的?份，輸出該?份屬于哪個季度
-- 1~3?為第?季度
-- 4~6?為第二季度
-- 7~9?為第三季度
-- 1~12?為第四季度

delimiter //

create procedure p5(in month int, out result varchar(20))
begin 
    case
        when month >= 1 and month <= 3 then 
            set result := '第?季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then 
            set result := '第四季度';
        else 
            set result := '非法參數(shù)';
    end case;
end//

delimiter ;

call p5(6, @result);
select @result;

4. 循環(huán)

while 循環(huán)：

WHILE search_condition Do
    statement_list
END WHILE;

示例：

-- 傳??個數(shù)n，計算從1累加到n的值
delimiter //

create procedure p6(in n int, out result int)
begin 
  declare num int default 1;
  set result := 0;
  while num <= n do 
   set result := result + num;
   set num := num + 1;
  end while;
end//

delimiter ;

call p6(3, @result);
select @result;

repeat 循環(huán)：

REPEAT
 statement_list
UNTIL search_condition END REPEAT;

示例：

delimiter //

create procedure p7(in n int, out sum int)
begin
  set sum := 0;
  repeat 
    set sum := sum + n;
    set n := n - 1;
  until n = 0 end repeat;
end//

delimiter ;

call p7(5, @sum);
select @sum;

loop 循環(huán)：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

示例：

-- 傳??個數(shù)n，累加從1累加到n之間偶數(shù)的值
delimiter //

create procedure p8(in n int)
begin
  declare sum int default 0; 
  sum_label: LOOP
    IF n <= 0 THEN
      LEAVE sum_label; 
    END IF; 
    set sum := sum + n;
    set n := n - 1;
  END LOOP sum_label;
  
  select sum;

end//

delimiter ;

call p8(100);

-- 傳??個數(shù)n，累加從1累加到n之間偶數(shù)的值
delimiter //

create procedure p9(in n int)
BEGIN
  declare sum int default 0;
  sum_label: LOOP
    IF n <= 0 THEN
      LEAVE sum_label; 
    END IF; 
    IF n % 2 != 0 then 
      set n := n - 1;
      ITERATE sum_label;
    END IF;
    set sum := sum + n;
    set n := n - 1;
  END LOOP sum_label;
  
  select sum;

END//

delimiter ;

call p9(10);

5. 游標(biāo)和條件處理程序

游標(biāo)是一種數(shù)據(jù)庫對象，允許在存儲過程和函數(shù)中對查詢到的結(jié)果進(jìn)行逐行檢索。

使用游標(biāo)之前，必須聲明游標(biāo)，之后使用 open，fetch，close 語句來開啟游標(biāo)，獲取游標(biāo)記錄和關(guān)閉游標(biāo)；
游標(biāo)必須在條件處理程序之前被聲明，并且變量必須要在游標(biāo)和條件處理程序之前被聲明；
通過查詢語句獲取的記錄通常是多行，因此游標(biāo)通常會和循環(huán)搭配使用；
打開游標(biāo)的時候，才會真正執(zhí)行 sql 查詢語句；

-- 聲明游標(biāo)
declare 游標(biāo)名 cursor for 查詢語句;

-- 打開游標(biāo)
open 游標(biāo)名;

-- 獲取游標(biāo)記錄
fetch 游標(biāo)名 into 變量名, 變量名...;

-- 關(guān)閉游標(biāo)
close 游標(biāo)名;

條件處理程序的條件是程序執(zhí)行過程中可能遇到的問題，類似 java 中的異常；
處理程序是遇到問題應(yīng)該采取的處理方式；
使用條件處理程序的作用是保證存儲過程或者函數(shù)在遇到錯誤時能繼續(xù)執(zhí)行，可以增強程序處理問題的能力，避免程序異常停止運行；

-- 聲明條件處理程序
declare handler_action handler for condition_value, condition_value...
    statement;

handler_action:{
    continue, - 繼續(xù)執(zhí)行當(dāng)前程序
    exit - 退出當(dāng)前程序
}

condition_value:{
     mysql_error_code  -- MYSQL錯誤碼
        
    | SQLSTATE [VALUE] sqlstate_value  -- 狀態(tài)碼
 
    | SQLWARNING  -- 所有以 01開頭的 SQLSTATE 代碼
 
    | NOT FOUND   -- 所有以 02開頭的 SQLSTATE 代碼
 
    | SQLEXCEPTION -- 所有沒有被 SQLWARNING 或 NOT FOUND 捕獲的 SQLSTATE 代碼
}

示例：

-- 示例：傳入班級編號，查詢學(xué)生表中屬于該班級的學(xué)生信息
-- 并將符合條件的學(xué)生信息寫入到?張新表中

-- 新表及字段 t_student_class(id,student_name,class_name)

-- 實現(xiàn)邏輯
-- 定義變量?于接收查詢結(jié)果集中每??中列的值
-- 聲明游標(biāo)，?于接收查詢結(jié)果集
-- 創(chuàng)建新表
-- 開啟游標(biāo)
-- 從游標(biāo)中獲取結(jié)果集中的記錄
-- 向新表中寫?數(shù)據(jù)
-- 關(guān)閉游標(biāo)

delimiter //

create procedure p10(in class_id int)
begin
    -- 定義變量?于接收查詢結(jié)果集中每??中列的值
    declare student_name varchar(20);
    declare class_name varchar(20);
    -- 聲明游標(biāo)，?于接收查詢結(jié)果集
    declare s_cursor cursor for 
        select student.name, class.name from student, class 
        where student.class_id = class.id and class.id = class_id;
    -- 創(chuàng)建新表
    create table if not exists t_student_class (
        id int primary key auto_increment,
        student_name varchar(20) not null, 
        class_name varchar(20) not null
    );
    -- 開啟游標(biāo)
    open s_cursor;
    -- 從游標(biāo)中獲取結(jié)果集中的記錄
    while true do
        fetch s_cursor into student_name, class_name;
         -- 向新表中寫?數(shù)據(jù)
        insert into t_student_class values (student_name, class_name);
    -- 關(guān)閉游標(biāo)
    close s_cursor;
end//

delimiter ;

注意：

因為查詢語句獲取的數(shù)據(jù)是有限的，因此當(dāng)使用游標(biāo)遍歷到最后一行的下一行后，應(yīng)當(dāng)退出循環(huán)，因此代碼需要改進(jìn)，做法是使用條件處理程序退出；

delimiter //

create procedure p10(in class_id int)
begin
    -- 定義變量?于接收查詢結(jié)果集中每??中列的值
    declare student_name varchar(20);
    declare class_name varchar(20);
    -- 定義循環(huán)退出條件
    declare is_done bool default false;
    -- 聲明游標(biāo)，?于接收查詢結(jié)果集
    declare s_cursor cursor for 
        select student.name, class.name from student, class 
        where student.class_id = class.id and class.id = class_id;
    -- 聲明條件處理程序
    declare continue handler for not found
        set is_done := true;
    -- 創(chuàng)建新表
    create table if not exists t_student_class (
        id int primary key auto_increment,
        student_name varchar(20) not null, 
        class_name varchar(20) not null
    );
    -- 開啟游標(biāo)
    open s_cursor;
    -- 從游標(biāo)中獲取結(jié)果集中的記錄
    while not is_done do 
        fetch s_cursor into student_name, class_name;
         -- 向新表中寫?數(shù)據(jù)
        insert into t_student_class values (null, student_name, class_name);
    end while;
    -- 關(guān)閉游標(biāo)
    close s_cursor;
end//

delimiter ;

注意，上述程序的條件處理程序的問題是：

當(dāng)游標(biāo)（fetch 語句）讀取到最后一行的下一行時，程序發(fā)生錯誤；
條件處理程序?qū)⑸鲜龅?is_done 變量置為 true 后，又繼續(xù)執(zhí)行 while 循環(huán) fetch 后面的語句；
上面的 student_name 和 class_name 繼續(xù)和上次保持一致，因此又多插入了一行上次的數(shù)據(jù)；
因此我們必須要在 t_student_class 插入數(shù)據(jù)之前，退出循環(huán)。

while 循環(huán)沒有退出方式，因此 while 循環(huán)并不適合這里的需求，而 loop 循環(huán)是能夠滿足需求的方式，因此使用 loop 循環(huán)代替 while 循環(huán)，將代碼更改為：

delimiter //

create procedure p10(in class_id int)
begin
    -- 定義變量?于接收查詢結(jié)果集中每??中列的值
    declare student_name varchar(20);
    declare class_name varchar(20);
    -- 定義循環(huán)退出條件
    declare is_done bool default false;
    -- 聲明游標(biāo)，?于接收查詢結(jié)果集
    declare s_cursor cursor for 
        select student.name, class.name from student, class 
        where student.class_id = class.id and class.id = class_id;
    -- 聲明條件處理程序
    declare continue handler for not found
        set is_done := true;
    -- 創(chuàng)建新表
    create table t_student_class(id,student_name,class_name);
    -- 開啟游標(biāo)
    open s_cursor;
    -- 從游標(biāo)中獲取結(jié)果集中的記錄
    read_loop: loop
        fetch s_cursor into student_name, class_name;
        if is_done then
            leave read_loop;
        end if;
        insert into t_student_class values (null, student_name, class_name);
    end loop read_loop;
    -- 關(guān)閉游標(biāo)
    close s_cursor;
end//

delimiter ;

6. 存儲函數(shù)

MySQL 的存儲函數(shù)是返回值的存儲過程，參數(shù)只能是 in 類型，因此 in 也可以省略；

存儲函數(shù)和存儲過程的主要區(qū)別是：存儲函數(shù)必須有返回值，存儲過程沒有返回值，只能用輸出型或者輸入輸出型參數(shù)作為輸出的結(jié)果。

-- 定義存儲函數(shù)
delimiter //

create function 存儲函數(shù)名(參數(shù)列表) returns 類型 characterristic
begin
    SQL 語句...
    return 返回值;
end//

delimiter ;

characteristic:
    deterministic | not deterministic | no sql | reads sql data | modifies sql data 

-- 使用存儲函數(shù)
select 存儲函數(shù)名(參數(shù)列表);

示例：傳入一個數(shù) n，計算從 1 累加到 n 的值；

delimiter //

create function func1(n int) returns int deterministic
begin

    declare total int default 0;
    while n >= 1 do
        set total := total + n;
        set n := n - 1;
    end while;

    return total;

end//

delimiter ;


select func1(100);

四、觸發(fā)器

1. 觸發(fā)器的定義和類型

觸發(fā)器是一個與表相關(guān)聯(lián)的數(shù)據(jù)庫對象；

在對表進(jìn)行 insert，update，delete 操作時，觸發(fā)并執(zhí)行觸發(fā)器中定義的 sql 語句；

觸發(fā)器可以在對表操作之前或者之后執(zhí)行，這被稱為觸發(fā)時間；
觸發(fā)器在對表操作之前執(zhí)行，典型的應(yīng)用場景是對 sql 語句參數(shù)進(jìn)行校驗；
觸發(fā)器在對表操作之后執(zhí)行，典型的應(yīng)用場景是記錄對表操作的日志；

MySQL 支持 3 中類型的觸發(fā)器：insert 觸發(fā)器， update 觸發(fā)器，delete 觸發(fā)器；

可以使用 OLD 和 NEW 關(guān)鍵字引用觸發(fā)器中發(fā)生變化的數(shù)據(jù)被容：

insert 觸發(fā)器：只能使用 NEW 來引用新插入的數(shù)據(jù)內(nèi)容；
update 觸發(fā)器：可以使用 OLD 引用修改前的數(shù)據(jù)內(nèi)容，NEW 引用修改后的數(shù)據(jù)內(nèi)容；
delete 觸發(fā)器：只能使用 OLD 引用刪除前的數(shù)據(jù)內(nèi)容；

觸發(fā)器也可以分為行級觸發(fā)器和語句級觸發(fā)器：

行級觸發(fā)器指的是對表中的每一行進(jìn)行 insert，update，delete 操作時，行級觸發(fā)器都會被觸發(fā)；如果一個語句影響了多行數(shù)據(jù)，每一行數(shù)據(jù)都會觸發(fā)一次行級觸發(fā)器；
語句級觸發(fā)器指的是對表中的數(shù)據(jù)進(jìn)行 insert， update，delete 操作時，只觸發(fā)一次。無論該操作影響了多少行數(shù)據(jù)，語句級觸發(fā)器都只觸發(fā)一次；
MySQL 中只支持行級觸發(fā)器，不支持語句級觸發(fā)器；

2. 觸發(fā)器的語法及實現(xiàn)

-- 定義觸發(fā)器
delimiter //

create trigger if not exists 觸發(fā)器名 
    觸發(fā)時間 觸發(fā)類型 on 表名 for each row
begin
    觸發(fā)器邏輯;
end

delimiter ;


trigger time: before | after

trigger event: insert / update / delete

-- 查看觸發(fā)器
show triggers;

-- 刪除觸發(fā)器
drop trigger if exists 觸發(fā)器名;

示例：通過觸發(fā)器記錄學(xué)生表的變更日志，將變更日志寫入到表 student_log 中，包含增加，修改和刪除操作。

1. 創(chuàng)建 student_log 表

-- 創(chuàng)建 student_log 表
create table student_log (
    id bigint primary key auto_increment,
    operation_type varchar(10) not null comment '操作類型：insert, update, delete',
    operation_time datetime not null comment '操作時間',
    operation_id bigint not null comment '操作記錄 id',
    operation_data varchar(500) not null comment '操作數(shù)據(jù)'
);

2. 創(chuàng)建 isnert 觸發(fā)器

-- 創(chuàng)建 insert 觸發(fā)器
delimiter //

create trigger tgr_student_insert
    after insert on student for each row
begin
    insert into student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    ) values (
        'insert',
        now(),
        new.id,
        concat(new.id, ',' new.name, ',' new.sno, ',' new.age, ',' new.gender, ',' new.enroll_date, ',' new.class_id )
    );
end//

delimiter ;

-- 插入數(shù)據(jù)記錄
insert into student values (null, '曹操', '300001', 28, 1, '2024-9-1', 3);

3. 創(chuàng)建 update 觸發(fā)器

-- 創(chuàng)建 update 觸發(fā)器
delimiter //

create trigger tgr_student_update
    after update on student for each row
begin
    insert into student_log (
        operation_type,
        operation_date,
        operation_id,
        operation_data
    ) values (
        'update',
        now(),
        new.id,
        concat(old.id, ', ', old.name, ', ', old.sno, ', ', old.age, ', ', old.gender, ', ', old.enroll_date, ', ', old.class_id, ' | ', 
               new.id, ', ', new.name, ', ', new.sno, ', ', new.age, ', ', new.gender, ', ', new.enroll_date, ', ', new.class_id)
    );
end//

delimiter ;

-- 更新學(xué)生表
update student set age = 20 where name = '曹操'; 
   
update student set class_id = 3 where id >= 7;

4. 創(chuàng)建 delete 觸發(fā)器

-- 創(chuàng)建 delete 觸發(fā)器
delimiter //

create trigger tgr_student_delete
    after delete on student for each row
begin
    insert into student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    ) values (
        'delete',
        now(),
        old.id,
        concat(old.id, ', ', old.name, ', ',old.sno, ', ',old.age, ', ',old.gender, ', ',old.enroll_date, ', ',old.class_id)
    );
end//

delimiter ;

-- 刪除數(shù)據(jù)記錄
delete from student where name = '曹操';

五、總結(jié)

1. 存儲過程的作用是什么？

存儲過程的作用是通過一個 sql 語句集，在數(shù)據(jù)庫層面實現(xiàn)特定的業(yè)務(wù)邏輯；類似于 java 中的方法。

優(yōu)點是：

業(yè)務(wù)邏輯編譯完成后可以保存在數(shù)據(jù)庫中，實現(xiàn)一次編寫，重復(fù)調(diào)用；
在數(shù)據(jù)表和應(yīng)用程序之間增加一個存儲過程，避免應(yīng)用程序直接訪問數(shù)據(jù)庫，避免不安全的問題；
應(yīng)用程序訪問數(shù)據(jù)庫只需要關(guān)心存儲過程的名字和參數(shù)，數(shù)據(jù)表發(fā)生變化的時候只需要修改存儲過程，不需要修改應(yīng)用程序；

缺點是：

MySQL 是一個客戶端-服務(wù)器結(jié)構(gòu)的程序，一個服務(wù)器給多個客戶端提供服務(wù)。
如果并發(fā)量比較高的情況下，存儲過程會給服務(wù)器帶來較大的負(fù)擔(dān)，降低服務(wù)器的效率；

2. 如何創(chuàng)建一個存儲過程？

使用 create 關(guān)鍵字：create procedure 存儲過程名字(參數(shù)列表)；

begin 后面編寫 sql 語句集，end 結(jié)束語句集的編寫；

需要注意的點是：要使用 delimiter 來修改 sql 語句終止符，默認(rèn)終止符是分號，避免在命令行中執(zhí)行的時候，服務(wù)器認(rèn)為語句中的分號為終止符，導(dǎo)致創(chuàng)建失??；

3. MySQL 中的變量有哪幾種？

有三種，分別是：系統(tǒng)變量，用戶自定義變量，局部變量；

系統(tǒng)變量又分為全局變量和會話變量，全局變量修改成功后對所有會話都生效，但是如果服務(wù)器重啟，修改的效果就會失效；會話變量修改后只在當(dāng)前會話生效，會話關(guān)閉后，就會失效；

用戶自定義變量：不需要提前聲明，用的時候直接寫 @變量名即可；

局部變量用于存儲過程，存儲函數(shù)，觸發(fā)器中，需要提前使用關(guān)鍵字 declare 聲明；

4. 如何定義一個變量？

系統(tǒng)變量是不可以自定義的；

用戶自定義變量可以通過 set @變量名定義；

局部變量通過 declare 變量名變量類型默認(rèn)值的方式定義；

5. MySQL中使用變量是否需要提前聲明？

如果是用戶自定義變量，不需要提前聲明；

如果在存儲過程，存儲函數(shù)，觸發(fā)器中使用局部變量，需要使用 declare 關(guān)鍵字提前申明；

6. MySQL中的參數(shù)分為哪幾種？

MuSQL 中參數(shù)分為 3 種：in - 輸入型參數(shù)， out - 輸出型參數(shù)，inout - 輸入輸出型參數(shù)；

存儲過程，存儲函數(shù)都可以使用輸入型參數(shù)，但是存儲函數(shù)只能使用輸入型參數(shù)，因此 in 可以省略；存儲過程中可以使用輸出型參數(shù)或者輸入輸出型參數(shù)代替返回值；

7. 用過游標(biāo)嗎？游標(biāo)的作用是什么？

用過游標(biāo)，游標(biāo)的作用是遍歷使用查詢語句獲得的結(jié)果集；游標(biāo)默認(rèn)指向的是查詢結(jié)果集的前一行，使用 fetch 游標(biāo)名 into 變量名的方式將結(jié)果集中的列保存到變量中，fetch 操作后，游標(biāo)會向結(jié)果集的下一行移動；

因此游標(biāo)通常會和循環(huán)搭配使用，由于游標(biāo)走到結(jié)果集到最后一行會發(fā)生越界，sql 會報錯，通?；睾蠗l件處理程序配合使用；

8. 了解條件處理程序嗎？介紹?下如何使用？

條件處理程序需要注意兩點：

第一點是條件，條件可以是 MySQL 的錯誤碼，或者 SQL 語言的狀態(tài)碼；

第二點是處理程序：滿足條件后才會執(zhí)行處理程序，處理程序的邏輯有兩種，continue 和 exit，滿足條件時，continue 會繼續(xù)讓程序執(zhí)行，增強程序處理問題的能力；exit 則會退出程序；

局部變量和游標(biāo)必須要在條件處理程序之前先定義；

定義條件處理程序的方式是 declare handler_action hander condition_value，...；

9. 存儲函數(shù)與存儲過程的區(qū)別是什么？

存儲函數(shù)必須要求有返回值，存儲過程不需要有返回值；

存儲函數(shù)的參數(shù)列表只能有 in 類型的參數(shù)，因此 in 也可以省略；

存儲函數(shù)聲明的時候必須要寫 returns，后面跟上返回值類型，在 MySQL 8 中，開啟 binlog 的前提條件下，返回值類型后面必須寫 deterministic，或者 not deterministic，不寫會報錯；

deterministic 的意思是給定一個固定的輸入，輸出總是相同的；
not deterministic 的意思是給定一個固定的輸入，有可能輸出是不同的；

10. 如何查看數(shù)據(jù)庫中創(chuàng)建的存儲過程？

select * from information_schema.routines where routine_schema = '數(shù)據(jù)庫名';

也可以：

show create procedure 存儲過程名;

11. 什么是觸發(fā)器？

觸發(fā)器是一個與表相關(guān)聯(lián)的數(shù)據(jù)庫對象；

在進(jìn)行 insert, update, delete 操作時，觸發(fā)執(zhí)行觸發(fā)器定義的 sql 語句；

12. MySQL中觸發(fā)器分為幾種類型？

MySQL 中觸發(fā)器分為三種類型：insert 觸發(fā)器，update 觸發(fā)器，delete 觸發(fā)器；

觸發(fā)器的觸發(fā)時間分為 before 和 after；

before 是在 sql 執(zhí)行前觸發(fā)，可以用于參數(shù)校驗等；
after 是在 sql 執(zhí)行后觸發(fā)，可以用于記錄日志等；

13. 行級觸發(fā)器與語句級觸器的區(qū)別是什么？

行級觸發(fā)器是在執(zhí)行 insert, update, delete 時，受到影響的每一行數(shù)據(jù)都會觸發(fā)一次；如果一個 sql 語句影響了多行數(shù)據(jù)，每一行數(shù)據(jù)都會觸發(fā)一次觸發(fā)器；
語句集觸發(fā)器是在執(zhí)行 insert, update, delete 時，只執(zhí)行一次觸發(fā)器，即使一個 sql 語句影響到了多行數(shù)據(jù)，也只會執(zhí)行一次；

14. 說?下了解的觸發(fā)器使用場景都有哪些？

根據(jù)觸發(fā)時間分為 before 和 after；

如果 sql 語句執(zhí)行前觸發(fā)，這種通常用于參數(shù)校驗，參數(shù)類型約束，檢查傳入的參數(shù)是否滿足數(shù)據(jù)庫的要求；
如果 sql 語句執(zhí)行后觸發(fā)，這種通常用于日志的記錄；

15. 如果對?表中的數(shù)據(jù)進(jìn)行更新，要在日志表中記錄該條記錄更新前與更新后的值，如何實現(xiàn)？

首先要創(chuàng)建日志表；

第二要定義一個 update 觸發(fā)器，觸發(fā)器時間設(shè)置為 after；

觸發(fā)器的語句集中寫 insert 語句，記錄發(fā)生變化時的操作類型，操作時間，操作數(shù)據(jù)記錄的 id，還有針對列的修改；、

16. 如何查看數(shù)據(jù)庫中創(chuàng)建的觸發(fā)器？

show triggers;

可以查看數(shù)據(jù)庫中定義的所有觸發(fā)器；

也可以

show create trigger 觸發(fā)器名;

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL中的存儲過程和觸發(fā)器詳解

目錄

一、存儲過程

1. 存儲過程介紹

2. 優(yōu)缺點分析

3. 存儲過程的語法

二、變量

1. 系統(tǒng)變量

2. 用戶自定義變量

3. 局部變量

三、SQL 編程

1. 條件判斷語句

2. 參數(shù)

3. case 判斷語句

4. 循環(huán)

5. 游標(biāo)和條件處理程序

6. 存儲函數(shù)

四、觸發(fā)器

1. 觸發(fā)器的定義和類型

2. 觸發(fā)器的語法及實現(xiàn)

五、總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL中的存儲過程和觸發(fā)器詳解

目錄

一、存儲過程

1. 存儲過程介紹

2. 優(yōu)缺點分析

3. 存儲過程的語法

二、變量

1. 系統(tǒng)變量

2. 用戶自定義變量

3. 局部變量

三、SQL 編程

1. 條件判斷語句

2. 參數(shù)

3. case 判斷語句

4. 循環(huán)

5. 游標(biāo)和條件處理程序

6. 存儲函數(shù)

四、觸發(fā)器

1. 觸發(fā)器的定義和類型

2. 觸發(fā)器的語法及實現(xiàn)

五、總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、存儲過程

二、變量

三、SQL 編程

四、觸發(fā)器

五、總結(jié)