下面的示例是使用存储过程来统计产品的各个品牌的产品个数,并保存到新表中,并判断新表中是否已有该品牌的统计数量,若有则更新数量,若没有则插入该品牌的统计数据。
不了解MySQL存储过程与游标的可以参考此地址
存储过程的使用
-- 删除存储过程
-- DROP PROCEDURE IF EXISTS product_brand_count;
CREATE PROCEDURE product_brand_count()
BEGIN
DECLARE tmp_num int DEFAULT 0; -- 用于判断是否已存在数据,若存在则更新
DECLARE done int DEFAULT false; -- 用于判断是否结束
-- 声明变量接收游标指向的数据
DECLARE brand_id_var BIGINT DEFAULT 0;
DECLARE total_var int DEFAULT 0;
-- 声明游标
DECLARE brand_count_cursor CURSOR FOR
SELECT brand_id,count(1) as total FROM pms_product GROUP BY brand_id;
DECLARE CONTINUE HANDLER FOR not found SET done = true;
-- 打开游标
OPEN brand_count_cursor;
-- 循环遍历查询处理的数据
brand_loop:LOOP
-- 获取游标指向的数据
FETCH brand_count_cursor INTO brand_id_var,total_var;
if done THEN
LEAVE brand_loop;
END IF;
-- 先查询是否已有该品牌id的数据
SELECT count(1) INTO tmp_num FROM pms_product_brand_count WHERE brand_id = brand_id_var;
-- 如果大于0 则更新数据,否则插入数据
IF tmp_num > 0 THEN
UPDATE pms_product_brand_count SET total = total_var,create_time = NOW() WHERE brand_id = brand_id_var;
ELSE
INSERT INTO pms_product_brand_count(brand_id,total,create_time) VALUES (brand_id_var,total_var,NOW());
END IF;
END LOOP brand_loop;
CLOSE brand_count_cursor;
END
-- 调用存储过程
-- CALL product_brand_count();
触发器的使用
查看触发器
SHOW TRIGGERs;
-- 删除触发器
DROP TRIGGER IF EXISTS brand_count_trigger;
-- 创建触发器
CREATE TRIGGER brand_count_trigger AFTER UPDATE
ON pms_product FOR EACH ROW
-- 如果只有一条语句的时候,begin end 可不要
BEGIN
-- 此处我调用的是上面的存储过程
CALL product_brand_count();
END;
定时任务
定时执行某一存储过程
查看已创建的存储过程:
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE product_brand_count;
查看是否开启定时器,若未开启则设置开启,步骤如下:
查看定时器是否开启
show VARIABLES LIKE '%event_sche%';
开启定时器
SET GLOBAL event_scheduler =1;
若未开启的话,每次重启电脑。或重启mysql服务后,会发现,事件自动关闭(event_scheduler=OFF),所以想让事件一直保持开启,最好修改配置文件,让mysql服务启动的时候开启时间,只需要在my.ini配置文件的[mysqld]部分加上event_scheduler=ON 即可
#默认开启事件
event_scheduler=ON
定时事件参数说明参考文档
创建事件product_brand_event,从2021-02-18 16:44:00到2021-02-18 16:46:00这个时间段内,每1分钟执行一次存储过程product_brand_count
CREATE EVENT `product_brand_event`
ON SCHEDULE EVERY 1 MINUTE STARTS '2021-02-18 16:44:00' ENDS '2021-02-18 16:46:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
CALL product_brand_count();
修改事件处理内容
ALTER EVENT `product_brand_event`
DO CALL product_brand_count();
提示:可以使用Navicat工具来新建事件。制定定时任务比较方便