作者：python與資料分析
連結：
本文為讀者投稿

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

文章大綱

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

一、儲存過程

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

過程：將若干條 SQL 語句封裝起來，起個名字
儲存過程 : 我們將此過程儲存在資料庫中，有點類似於程式設計中用到的函式，區別是函式有返回值，而過程沒有返回值，相同點是將程式碼封裝可複用，可傳參，呼叫即可執行。
好處：① 程式碼封裝可複用 ② 可以接收、返回引數 ③ 減少網路互動、提升效率

2、儲存過程如何使用

建立

create procedure 名稱()
begin
  sql語句;
end

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

檢視

show procedure status;

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

呼叫

call 名稱();

刪除

drop procedure if exists 名稱;

3、儲存過程中的變數

變數的種類和定義

在 SQL 中變數分為兩種：

① 系統變數：@@
② 自定義變數：@

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

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

格式：declare 變數名 變數型別 【default 預設值】

變數運算與控制結構

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

① set 變數名 = 值
② set 變數名 := 值

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

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 之和

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

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

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

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

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

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

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

二、定時器

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 表插⼊⼀條語句"事件啟動了"

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

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

週期時間定時執行：

every子句：格式是“數字+時間單位”，表示時間週期，例如：1 hour / 2 minute / 3 second
starts子句：可選，跟上 timestamp 值，表示事件開始的時間點，如果沒有指定就是當前時間
ends子句：可選，跟上 timesatamp 值，表示停止執行的時間，如果沒有ends表示無限執行

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

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

注意事項

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 —— 其它——事件，檢視到當前事件的定義，計劃，當然也可以手動完成上述操作。

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

三、觸發器

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

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

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

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

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

2、觸發器如何使用

建立

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

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

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);