MySQL如何自定義函數及觸發器

今天小編給大家分享一下MySQL如何自定義函數及觸發器的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

1. 存儲函數(自定義函數)

• 自定義函數是一種對MySQL擴展的途徑，其用法與內置的函數相同。

• 定義函數的兩個必要條件：參數、返回值。函數可以返回任意類型的值，同樣可以接收這些類型的參數。

關于函數體：

• 函數體是由合法的SQL語句構成。

• 函數體可以是簡單的SELECT或INSERT語句。

• 函數體如果為符合結構則使用BEGIN....AND語句包裹。

• 復合結構可以包含聲明、循環、控制結構等等。

重點：自定義的函數不能重名，類似于定義了一個全局變量，變量名不能一致。

1.1 定義存儲函數

語法格式：

create function 函數名(參數列表)
returns type(返回值類型)
begin
    --SQL語句
end;

需求：定義一個存儲過程的函數，獲取滿足條件的總記錄條數

實現：

delimiter $
create function fun(countryId int)
returns int 
begin
    # 定義一個存儲總數據條數的變量
    declare cum int default 0;
    # 查詢等于傳遞參數的全部的數據數，然后將其賦值給定義的變量
    select count(*) into cum from city where country_id = countryId;
    # 返回結果值。存儲函數必須有返回值
    return cum;
end $

delimiter ;

1.2 調用存儲函數

語法格式：

select 函數名(參數列表);

注意：調用存儲過程的時候使用的是call關鍵字，但是在調用存儲函數的時候直接使用select即可，就和調用MySQL一個普通的聚合函數的方式一樣即可。

select fun(1);
# 這里和存儲過程一樣，調用的時候需要加小括號和參數，但是在刪除的時候指定函數名即可

1.3 刪除存儲函數

語法格式

drop [if exists] function fun;

2. 觸發器

2.1 觸發器介紹

• 觸發器是與表有關的數據庫對象，指在 insert/update/delete 之前或之后，觸發并執行觸發器中定義的SQL集合。觸發器的這種特性可以協助應用在數據庫端確保數據的完整性、日志記錄以及數據校驗等操作。

• 只有增加、刪除、修改的時候才可以使用觸發器，查詢的時候不可以使用觸發器。

• 使用別名OLD和NEW來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似對的。MySQL觸發器還只支持行級觸發，不支持語句級觸發。oracle支持行級和語句級觸發器都支持。

• OLD、NEW這兩個變量又叫做行記錄變量。可以通過這個兩個變量來獲取即將要操作的數據表中的數據。

2.2 創建觸發器

語法格式：

create trigger(觸發器) trigger_name(觸發器名稱)
before/after insert/update/delete
on tab_name(表名)
[for each row](行級觸發器)
begin
    trigger_stmt;(觸發器的邏輯)
end;

需求:通過觸發器記錄 emp 表的數據變更日志 emp_logs ，其中包含增加、修改、刪除

實現:

分析：一個觸發器只能操作一種數據的操作類型，不可以同時完成增加、修改、刪除的操作。所以此時需要定義多個觸發器來完成這個日志記錄的任務。

因為 MySQL中是行級操作的觸發器，所以 new 以及 old 中存儲的都是一整行數據。

創建執行 insert 的觸發器:

• 使用 new 關鍵字可以獲取到操作的數據，在insert模式下，new變量中存儲的就是即將插入的數據

• 使用的是 after ，在執行完表 emp 的新增之后執行這個觸發器記錄日志。

• 這個觸發器什么時候執行與兩點有關：

• 必須操作的是 emp 這個表，也就是on后面聲明的這個表。

• 必須執行的 insert 操作。

創建執行 update 的觸發器:

此時 old 變量中存儲的是被修改前的數據，new 變量中存儲的是修改之后的數據

創建執行 delete 的觸發器:

此時的 old 變量中存儲的即將刪除的數據

測試：測試都必須是操作的 emp 表，這樣才會觸發上邊定義的觸發器。

2.3 刪除觸發器

語法結構：

drop trigger [schema_name.](數據庫名)trigger_name(觸發器名);

如果沒有指定 schema_name(數據庫名)，默認為當前數據庫。

2.4 查看觸發器

可以通過執行 show triggers 命令查看觸發器的狀態、語法等信息。

語法結構：

show triggers;

以上就是“MySQL如何自定義函數及觸發器”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。