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

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ù)傳遞、條件處理、日志記錄等功能

本文系統(tǒng)介紹了MySQL存儲過程的創(chuàng)建與使用，包括變量定義、條件判斷、循環(huán)控制、游標(biāo)操作等核心知識。

詳細(xì)講解了存儲過程的優(yōu)缺點分析，以及存儲函數(shù)與觸發(fā)器的區(qū)別與應(yīng)用場景。

通過具體示例演示了如何實現(xiàn)參數(shù)傳遞、條件處理、日志記錄等功能，并深入解析了行級觸發(fā)器的實現(xiàn)機制。

文章還總結(jié)了存儲過程在實際開發(fā)中的常見問題與解決方案，為數(shù)據(jù)庫編程提供了全面指導(dǎo)。

一、存儲過程

1. 存儲過程介紹

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

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

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

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

  • 1. 存儲過程在創(chuàng)建時，編譯并保存在數(shù)據(jù)庫中，執(zhí)行速度比單個的 sql 要快；
  • 2. 存儲過程可以重復(fù)調(diào)用，減少重復(fù)代碼，提高代碼的可維護性；
  • 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)試，因此維護困難；
  • 3. 數(shù)據(jù)庫的主要功能是保存和查詢數(shù)據(jù)，數(shù)據(jù)是保存在硬盤中，存儲和查詢數(shù)據(jù)都涉及到硬盤 IO，效率不高，數(shù)據(jù)庫常常稱為系統(tǒng)的效率瓶頸。對于高并發(fā)場景，將業(yè)務(wù)邏輯放在數(shù)據(jù)庫層面執(zhí)行，會增加數(shù)據(jù)庫的壓力，進一步導(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 進行精確查詢 - 全局變量
select @@global.系統(tǒng)變量名;

-- 使用 select 進行精確查詢 - 會話變量
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é)果進行逐行檢索。

  • 使用游標(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)，因此代碼需要改進，做法是使用條件處理程序退出；
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ù)庫對象；

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

  • 觸發(fā)器可以在對表操作之前或者之后執(zhí)行，這被稱為觸發(fā)時間；
  • 觸發(fā)器在對表操作之前執(zhí)行，典型的應(yīng)用場景是對 sql 語句參數(shù)進行校驗；
  • 觸發(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ā)器指的是對表中的每一行進行 insert，update，delete 操作時，行級觸發(fā)器都會被觸發(fā)；如果一個語句影響了多行數(shù)據(jù)，每一行數(shù)據(jù)都會觸發(fā)一次行級觸發(fā)器；
  • 語句級觸發(fā)器指的是對表中的數(shù)據(jù)進行 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ù)庫對象；

在進行 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ù)進行更新，要在日志表中記錄該條記錄更新前與更新后的值，如何實現(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)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

相關(guān)文章

最新評論