MySQL高级 | 用存储过程、定时器、触发器来解决数据分析问题

很多做开发、数据库相关工作的小伙伴可能经常会用到 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定时器时，是用 pyhton 程序代码去操作数据表，再将py程序，放到服务器，跑定时任务。现在用定时器，完全可以在数据层面操作了，非常方便。

语法结构

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

查看商品表 创建触发器，并查看
发现这样写触发器，并不灵活

3、触发器引用行变量

使用别名old、new来引用触发器中发生变化的记录内容。注意：

引用行变量

要求：删除一个订单时，商品要退回，库存量要还原（删）

要求：订单表中的数量3 要求改到2，并且让商品表的库存量也变化（改）

要求：假如现在剩余 26 只pig，但是客户下订单买27只，能否预防，能否将buy_num > num 时，将buy_num 自动改为 num（深入理解before 和after的区别）

上面跟大家介绍了，如何数据分析工作中，应用 MySQL 的存储过程、定时器、触发器来实现自动化更新数据。当然，用 python 或其他编程语言也能实现，个人认为在数据层面操作，更加简单、高效、稳定。具体还要看你当下的业务场景。希望通过本文能为你提供一个解决问题的思路 。 欢迎点赞支持 ~