MySQL 高階 | 用儲存過程、定時器、觸發器來解決資料分析問題

11 min readSep 8, 2022

文章來於：傑哥的IT之旅

很多做開發、資料庫相關工作的小夥伴可能經常會用到 MySQL 的儲存過程、定時器、觸發器這些高階功能，但是做資料分析或者資料處理，我們也需要掌握這些技能，來解決特定的業務問題。比如：做自動化報表，如果資料需要每天實時更新（增量爬蟲）、定時計算某個業務指標、想要實時監控資料庫表中的資料增、刪、改情況等。

文章大綱

一、儲存過程

1、啥是儲存過程，有什麼用？

過程：將若干條 SQL 語句封裝起來，起個名字

儲存過程 : 我們將此過程儲存在資料庫中，有點類似於程式設計中用到的函式，區別是函式有返回值，而過程沒有返回值，相同點是將程式碼封裝可複用，可傳參，呼叫即可執行。

好處：① 程式碼封裝可複用 ② 可以接收、返回引數 ③ 減少網路互動、提升效率

2、儲存過程如何使用

建立

create procedure 名称()
begin
  sql语句;
end

檢視

show procedure status;

呼叫

call 名称();

刪除

drop procedure if exists 名称;

3、儲存過程中的變數

變數的種類和定義

在 SQL 中變數分為兩種：

① 系統變數：@@

② 自定義變數：@

儲存過程是可以程式設計的，意味著可以使用變數、表示式、控制結構，在儲存過程中，宣告變數用 declare

格式：declare 变量名 变量类型 【default 默认值】

變數運算與控制結構

變數的賦值，有兩種方式：

① set 變數名 = 值

② set 變數名 := 值

if | else 控制結構語法格式

if  条件1 then
  sql 语句；
else if 条件2 then
  sql 语句；
else
  sql语句；
end if

儲存過程中的引數傳遞

為了讓儲存過程更加靈活，可以傳遞引數，引數分為三種：

① in：引數作為輸入，呼叫時傳入

② out：引數作為輸出，可以作為返回值

③ inout：引數即可傳入又可輸入

格式: in|out|inout 参数名 参数类型

儲存過程中使用循環

while 循環格式：

while 条件 do
  sql 语句;
end while

列印 1–100 之和

帶輸入引數n，求1-n之和

要求帶輸入引數 n，和輸出引數 total ，求1-n 之和

要求 age 既是輸入又是輸出變數，傳入一個年齡，就增加 20

二、定時器

1、啥是定時器，怎麼用？

所謂定時器，就是定時地去執行指定的函式和程式碼，MySQL 的定時器就是 MySQL 的事件。

在開發過程中經常會遇到這樣一個問題：每天或每月需定時去執行一條 SQL 語句或更新或刪除資料。在我不瞭解 MySQL 定時器時，是用 Python 程式程式碼去操作資料表，再將 Python 程式，放到伺服器跑定時任務。現在用定時器，完全可以在資料層面操作了，非常方便。

語法結構

create event [if not exists] 事件名
[definer = user] 可选参数。执⾏事件的⽤⼾，不指定默认就是当前⽤⼾
on schedule 定时时间设置。定义事件执⾏的频率，可以指定具体时间也可以周期性执行
[on completion [not] preserve ] 可选参数。默认是not，表⽰时间过期后会⽴即删除(注意不是不激活)；on completion preserve 表⽰时间过期后会继续保留
[enable | disable | disable on slave] 可选参数。默认enable。事件激活、不激活、从服务上不激活（事件在主服务商创建并赋值到从服务器上，仅在主服务上执行）
[comment "注释"] 可选参数。
do 事件内容 定义事件的sql语句，如果语句有多⾏需要⽤ begin end 括起来

指定時間定時執行

at子句：這裡要求是timestamp時間格式，⼀般格式是“時間點 + interval 時間單位”。表示在什麼時間節點執行，例如：current_timestamp + interval 2 minute

要求：兩分鐘後往 event_test 表插⼊⼀條語句”事件啟動了”

注意：因為引數預設是 on completion not preserve，事件執行完成後會自動刪除

週期時間定時執行：

every子句：格式是“數字+時間單位”，表示時間週期，例如：1 hour / 2 minute / 3 second

starts子句：可選，跟上 timestamp 值，表示事件開始的時間點，如果沒有指定就是當前時間

ends子句：可選，跟上 timesatamp 值，表示停止執行的時間，如果沒有ends表示無限執行

要求：新建資料表 event_test，每分鐘往裡面插⼊⼀條資料，到 5 分鐘結束

注意事項

1、需要啟用事件，事件才會被執行，show events才可以檢視。⼀個是全域性引數開啟，⼀個是事件的開啟

SET GLOBAL event_scheduler = 1;
设置事件的状态为 enable：
ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE; 开启
ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE; 关闭

2、關掉了 navicat，事件不會關閉，關閉了 MySQL 伺服器才會被關閉
3、多語句執行的時候，可能需要修改結束分隔符，比如：delimiter $
4、如果事件的開始時間已經過去了，雖然建立語句不會報錯，但是事件不會被建立以及執行
5、事件⾥⾯不能巢狀事件，但是儲存過程裡面可以使用事件
6、在事件中使用 select、show 等返回結果語句沒有意義，但是可以用 select into、insert into 等儲存結果的語句
7、注意不要短週期內重複事件排程，不然資料會有問題。例如每分鐘執行 100w 行資料，那這個會有問題，如果實在是需要那這個時候可以使用行鎖、表鎖來進行
8、事件⽆法傳遞引數，但是可以用事件裡面的引數使用儲存過程

定時器可以結合儲存過程

現在用定時器，就可以在資料層面操作，定時去執行sql 語句或一組 sql 語句（儲存過程），設定好定時任務，可透過 navicat — — 其它 — — 事件，檢視到當前事件的定義，計劃，當然也可以手動完成上述操作。

三、觸發器

1、啥是觸發器，應用場景是?

觸發器是一類特殊的事務，可以監視資料操作（資料表的變更日誌），包括 insert | update | delete，並觸發相關操作 insert | update | delete，運用觸發器，不僅能簡化程式，又可以增加程式的靈活性。

應用場景①：當向一張表中新增或刪除資料時，需要在相關表中進行同步操作，比如：當一個訂單產生時，訂單所購的產品的庫存量相應減少。

應用場景②：當表中某列資料的值與其他表中的資料有聯絡時，比如：某客戶進行欠款消費，可以在生成訂單時，設計觸發器判斷該使用者的累計欠款是否超過最大限度。

應用場景③：跟蹤某張表時，比如當有新訂單產生時，需通知相關人員進行處理，這時可以在訂單表中新增觸發器加以實現。

2、觸發器如何使用

建立

觸發器只支援行級觸發（每一行受影響，觸發器都執行，叫作行級觸發器），不支援語句級觸發。

Create trigger 触发器名称
before/after  
insert/update/delete
on 表名 for each row #行级触发器
Begin 
    trigger_state;
end

檢視

Show triggers;

刪除

Drop trigger 数据库.触发器名称;

要求：現有商品表 goods，訂單表 orders，當下一個訂單時，商品要相應減少(買幾個商品，就少幾個庫存)，分析如下：

監視誰：orders

監視動作：insert

觸發時間：after

觸發事件：update

CREATE TABLE goods(gid INT,name VARCHAR(10),num SMALLINT);
CREATE TABLE ord(oid INT ,gid INT, buy_num SMALLINT)
INSERT INTO goods VALUES (1,'cat',20),(2,'dog',90),(3,'pig',26);