很多做開發(fā)辈双、數(shù)據(jù)庫相關(guān)工作的小伙伴可能經(jīng)常會(huì)用到 mysql 的存儲(chǔ)過程、定時(shí)器柜砾、觸發(fā)器這些高級(jí)功能湃望，但是做數(shù)據(jù)分析或者數(shù)據(jù)處理，我們也需要掌握這些技能痰驱，來解決特定的業(yè)務(wù)問題证芭。比如：做自動(dòng)化報(bào)表，如果數(shù)據(jù)需要每天實(shí)時(shí)更新（增量爬蟲）担映、定時(shí)計(jì)算某個(gè)業(yè)務(wù)指標(biāo) 废士、想要實(shí)時(shí)監(jiān)控?cái)?shù)據(jù)庫表中的數(shù)據(jù)增、刪蝇完、改情況等湃密。
文章大綱
一诅挑、存儲(chǔ)過程
1四敞、啥是存儲(chǔ)過程泛源，有什么用？
- 過程 ：將若干條 sql 語句封裝起來忿危，起個(gè)名字
- 存儲(chǔ)過程 : 我們將此過程存儲(chǔ)在數(shù)據(jù)庫中
有點(diǎn)類似于編程中用到的函數(shù)达箍，區(qū)別是函數(shù)有返回值，而過程沒有返回值铺厨，相同點(diǎn)是將代碼封裝可復(fù)用缎玫，可傳參，調(diào)用即可執(zhí)行解滓。 - 好處：① 代碼封裝可復(fù)用 ② 可以接收赃磨、返回參數(shù) ③ 減少網(wǎng)絡(luò)交互、提升效率
2洼裤、存儲(chǔ)過程如何使用
創(chuàng)建
create procedure 名稱()
begin
sql語句;
end
查看
show procedure status;
調(diào)用
call 名稱();
刪除
drop procedure if exists 名稱;
3邻辉、存儲(chǔ)過程中的變量
變量的種類和定義
在sql中變量分為兩種：① 系統(tǒng)變量：@@ ② 自定義變量：@
存儲(chǔ)過程是可以編程的，意味著可以使用變量腮鞍、表達(dá)式值骇、控制結(jié)構(gòu)，在存儲(chǔ)過程中移国，聲明變量用 declare
格式：declare 變量名 變量類型 【default 默認(rèn)值】
變量運(yùn)算與控制結(jié)構(gòu)
變量的賦值吱瘩，有兩種方式：① set 變量名 = 值 ② set 變量名 := 值
if|else 控制結(jié)構(gòu)語法格式
if 條件1 then
sql 語句；
else if 條件2 then
sql 語句迹缀；
else
sql語句使碾；
end if
存儲(chǔ)過程中的參數(shù)傳遞
為了讓存儲(chǔ)過程更加靈活，可以傳遞參數(shù)祝懂，參數(shù)分為三種：① in ：參數(shù)作為輸入票摇，調(diào)用時(shí)傳入
② out ： 參數(shù)作為輸出，可以作為返回值 ③ inout ： 參數(shù)即可傳入又可輸入
格式: in|out|inout 參數(shù)名 參數(shù)類型
存儲(chǔ)過程中使用循環(huán)
while 循環(huán)格式 ：
while 條件 do
sql 語句;
end while
打印 1 - 100 之和
帶輸入?yún)?shù)n嫂易，求1-n之和
要求帶輸入?yún)?shù)n兄朋，和輸出參數(shù) total ，求1-n之和
要求 age 既是輸入又是輸出變量怜械，傳入一個(gè)年齡颅和，就增加20
二、定時(shí)器
1缕允、啥是定時(shí)器峡扩，怎么用？
所謂定時(shí)器障本，就是定時(shí)地去運(yùn)行指定的函數(shù)和代碼教届，mysql 的定時(shí)器就是 mysql 的事件
在開發(fā)過程中經(jīng)常會(huì)遇到這樣一個(gè)問題：每天或每月須定時(shí)去執(zhí)行一條sql語句或更新或刪除除數(shù)據(jù)响鹃。在我不了解mysql定時(shí)器時(shí)，是用 pyhton 程序代碼去操作數(shù)據(jù)表案训，再將py程序买置，放到服務(wù)器，跑定時(shí)任務(wù)∏况現(xiàn)在用定時(shí)器忿项，完全可以在數(shù)據(jù)層面操作了，非常方便城舞。
語法結(jié)構(gòu)
create event [if not exists] 事件名
[definer = user] 可選參數(shù)轩触。執(zhí)?事件的??，不指定默認(rèn)就是當(dāng)前??
on schedule 定時(shí)時(shí)間設(shè)置家夺。定義事件執(zhí)?的頻率脱柱，可以指定具體時(shí)間也可以周期性執(zhí)行
[on completion [not] preserve ] 可選參數(shù)。默認(rèn)是not拉馋，表?時(shí)間過期后會(huì)?即刪除(注意不是不激活)榨为；on completion preserve 表?時(shí)間過期后會(huì)繼續(xù)保留
[enable | disable | disable on slave] 可選參數(shù)。默認(rèn)enable椅邓。事件激活柠逞、不激活、從服務(wù)上不激活（事件在主服務(wù)商創(chuàng)建并賦值到從服務(wù)器上景馁，僅在主服務(wù)上執(zhí)行）
[comment "注釋"] 可選參數(shù)板壮。
do 事件內(nèi)容 定義事件的sql語句，如果語句有多?需要? begin end 括起來
指定時(shí)間定時(shí)執(zhí)行
at子句：這里要求是timestamp時(shí)間格式合住，?般格式是“時(shí)間點(diǎn) + interval 時(shí)間單位”绰精。表示在什么時(shí)間節(jié)點(diǎn)執(zhí)行，例如：current_timestamp + interval 2 minute
要求：兩分鐘后往 event_test 表插??條語句" 事件啟動(dòng)了"
注意： 因?yàn)閰?shù)默認(rèn)是on completion not preserve透葛，事件運(yùn)行完成后會(huì)自動(dòng)刪除
周期時(shí)間定時(shí)執(zhí)行：
every子句：格式是“數(shù)字+時(shí)間單位”笨使，表示時(shí)間周期，例如：1 hour / 2 minute / 3 second
starts子句：可選僚害，跟上 timestamp 值硫椰，表示事件開始的時(shí)間點(diǎn)，如果沒有指定就是當(dāng)前時(shí)間
ends子句：可選萨蚕，跟上 timesatamp 值靶草，表示停止執(zhí)行的時(shí)間，如果沒有ends表示無限執(zhí)行
要求：新建數(shù)據(jù)表 event_test岳遥，每分鐘往里面插??條數(shù)據(jù)奕翔，到5分鐘結(jié)束
注意事項(xiàng)
1、需要激活事件浩蓉，事件才會(huì)被執(zhí)行派继，show events才可以查看宾袜。?個(gè)是全局參數(shù)開啟，?個(gè)是事件的開啟驾窟。
SET GLOBAL event_scheduler = 1;
設(shè)置事件的狀態(tài)為enable：
ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE; 開啟
ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE; 關(guān)閉
- 關(guān)掉了navicat庆猫，事件不會(huì)關(guān)閉，關(guān)閉了mysql服務(wù)器才會(huì)被關(guān)閉纫普。
- 多語句執(zhí)行的時(shí)候阅悍，可能需要修改結(jié)束分隔符，比如： delimiter $
- 如果事件的開始時(shí)間已經(jīng)過去了昨稼，雖然創(chuàng)建語句不會(huì)報(bào)錯(cuò)，但是事件不會(huì)被創(chuàng)建以及執(zhí)行拳锚。
- 事件??不能嵌套事件假栓，但是存儲(chǔ)過程里面可以使用事件
- 在事件中使用select、show等返回結(jié)果語句沒有意義霍掺，但是可以用select into匾荆、insert into等
存儲(chǔ)結(jié)果的語句 - 注意不要短周期內(nèi)重復(fù)事件調(diào)度，不然數(shù)據(jù)會(huì)有問題杆烁。例如每分鐘執(zhí)行100w行數(shù)據(jù)牙丽，那這個(gè)會(huì)有問題，如果實(shí)在是需要那這個(gè)時(shí)候可以使用行鎖兔魂、表鎖來進(jìn)行烤芦。
- 事件?法傳遞參數(shù)，但是可以用事件里面的參數(shù)使用存儲(chǔ)過程
定時(shí)器可以結(jié)合存儲(chǔ)過程
現(xiàn)在用定時(shí)器析校，就可以在數(shù)據(jù)層面操作构罗，定時(shí)去執(zhí)行sql 語句或一組 sql 語句（存儲(chǔ)過程），設(shè)置好定時(shí)任務(wù)智玻，可通過navicat —— 其它——事件遂唧，查看到當(dāng)前事件的定義，計(jì)劃吊奢，當(dāng)然也可以手動(dòng)完成上述操作盖彭。
三、觸發(fā)器
1页滚、啥是觸發(fā)器召边，應(yīng)用場(chǎng)景是 ？
觸發(fā)器是一類特殊的事務(wù)逻谦，可以監(jiān)視數(shù)據(jù)操作（數(shù)據(jù)表的變更日志）掌实，包括 insert | update | delete，并觸發(fā)相關(guān)操作 insert | update | delete邦马，運(yùn)用觸發(fā)器贱鼻，不僅能簡化程序宴卖，又可以增加程序的靈活性。
應(yīng)用場(chǎng)景① ：當(dāng)向一張表中添加或刪除數(shù)據(jù)時(shí)邻悬，需要在相關(guān)表中進(jìn)行同步操作症昏，比如：當(dāng)一個(gè)訂單產(chǎn)生時(shí)，訂單所購的產(chǎn)品的庫存量相應(yīng)減少父丰。
應(yīng)用場(chǎng)景② ：當(dāng)表中某列數(shù)據(jù)的值與其他表中的數(shù)據(jù)有聯(lián)系時(shí)肝谭，比如：某客戶進(jìn)行欠款消費(fèi)，可以在生成訂單時(shí)蛾扇，設(shè)計(jì)觸發(fā)器判斷該用戶的累計(jì)欠款是否超過最大限度攘烛。
應(yīng)用場(chǎng)景③ ：跟蹤某張表時(shí)，比如當(dāng)有新訂單產(chǎn)生時(shí)镀首，需通知相關(guān)人員進(jìn)行處理坟漱，這時(shí)可以在訂單表中添加觸發(fā)器加以實(shí)現(xiàn)。
2更哄、觸發(fā)器如何使用
創(chuàng)建
觸發(fā)器只支持行級(jí)觸發(fā)（每一行受影響芋齿，觸發(fā)器都執(zhí)行，叫作行級(jí)觸發(fā)器）成翩，不支持語句級(jí)觸發(fā)觅捆。
Create trigger 觸發(fā)器名稱
before/after
insert/update/delete
on 表名 for each row #行級(jí)觸發(fā)器
Begin
trigger_state;
end
查看
Show triggers;
刪除
Drop trigger 數(shù)據(jù)庫.觸發(fā)器名稱;
要求：現(xiàn)有商品表 goods，訂單表 orders麻敌，當(dāng)下一個(gè)訂單時(shí)栅炒，商品要相應(yīng)減少(買幾個(gè)商品，就少幾個(gè)庫存)庸论， 分析如下：
監(jiān)視誰：orders
監(jiān)視動(dòng)作：insert
觸發(fā)時(shí)間：after
觸發(fā)事件：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);
3聂示、觸發(fā)器引用行變量
使用別名old域携、new來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容。注意：
要求：刪除一個(gè)訂單時(shí)鱼喉，商品要退回秀鞭，庫存量要還原（刪）
要求：訂單表中的數(shù)量3 要求改到2，并且讓商品表的庫存量也變化（改）
要求：假如現(xiàn)在剩余 26 只pig扛禽，但是客戶下訂單買27只锋边，能否預(yù)防，能否將buy_num > num 時(shí)编曼，將buy_num 自動(dòng)改為 num（深入理解before 和after的區(qū)別）
上面跟大家介紹了豆巨，如何數(shù)據(jù)分析工作中，應(yīng)用 MySQL 的存儲(chǔ)過程掐场、定時(shí)器往扔、觸發(fā)器來實(shí)現(xiàn)自動(dòng)化更新數(shù)據(jù)贩猎。當(dāng)然，用 python 或其他編程語言也能實(shí)現(xiàn)萍膛，個(gè)人認(rèn)為在數(shù)據(jù)層面操作吭服，更加簡單、高效蝗罗、穩(wěn)定艇棕。具體還要看你當(dāng)下的業(yè)務(wù)場(chǎng)景。希望通過本文能為你提供一個(gè)解決問題的思路 串塑。 歡迎點(diǎn)贊支持 ~
