mysql中使用存储过程，游标，定时任务

最新推荐文章于 2022-06-21 09:50:24 发布

Zy.周瑜

最新推荐文章于 2022-06-21 09:50:24 发布

阅读量741

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql 存储过程游标

本文链接：https://blog.youkuaiyun.com/XiangZhengxian/article/details/68064985

MySQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何在MySQL中创建定时任务，包括创建表、定义存储过程并利用事件调度器实现每30秒更新表中记录的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

------ --一句不对就是创建个job，之前一直认为只有Oracle才可以完成这些，其实不然，我也是才知道mysql如此强大呀！！~

-- -- 0.创建一张表，加几条数据待会儿看效果=========
CREATE TABLE `t_business_jobflowcount` (
  `ID` int(11) DEFAULT NULL,
  `T_NAME` varchar(30) DEFAULT NULL,
  `T_BASE_VAL` double(10,4) DEFAULT NULL,
  `T_PID` int(11) DEFAULT NULL,
  `T_COUNT` double(32,4) DEFAULT '0.0000',
  `T_SPEED` double(10,4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `t_business_jobflowcount` VALUES ('136', '1500M90?', '52000.0000', '132', '352.3600', '7.6600');
INSERT INTO `t_business_jobflowcount` VALUES ('137', '420M60?', '50000.0000', '133', '322.0000', '7.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('138', '900M120?', '45000.0000', '133', '230.0000', '5.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('139', '3000M180?', '40000.0000', '133', '138.0000', '3.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('90', '?????', '999999.9999', '0', '15594.0000', '339.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('96', '????', '171999.8000', '0', '860.2000', '18.7000');
INSERT INTO `t_business_jobflowcount` VALUES ('111', '????/??', '999999.9999', '0', '27094.0000', '589.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('130', '?????', '161000.0000', '0', '828.0000', '18.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('132', '90?????', '153000.0000', '0', '781.0800', '16.9800');
INSERT INTO `t_business_jobflowcount` VALUES ('133', '180?????', '135000.0000', '0', '690.0000', '15.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('112', '30M5?/?', '999999.9999', '90', '5704.0000', '124.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('113', '70M10?/?', '843500.0000', '90', '4600.0000', '100.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('114', '100M10?/?', '743500.0000', '90', '2990.0000', '65.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('115', '150M20?/?', '343500.0000', '90', '2300.0000', '50.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('116', '500M6?/4??', '999999.9999', '111', '9533.5000', '207.2500');
INSERT INTO `t_business_jobflowcount` VALUES ('117', '1G9?/4??', '999999.9999', '111', '6773.5000', '147.2500');
INSERT INTO `t_business_jobflowcount` VALUES ('118', '500M10?/24??', '992250.0000', '111', '4933.5000', '107.2500');
INSERT INTO `t_business_jobflowcount` VALUES ('119', '1G15?/24??', '999999.9999', '111', '5853.5000', '127.2500');
INSERT INTO `t_business_jobflowcount` VALUES ('97', '300M20?/?', '27527.8000', '96', '110.4000', '2.4000');
INSERT INTO `t_business_jobflowcount` VALUES ('98', '500M30?/?', '17222.2000', '96', '92.0000', '2.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('99', '700M40?/?', '15222.2000', '96', '82.8000', '1.8000');
INSERT INTO `t_business_jobflowcount` VALUES ('122', '2G70?/?', '25527.8000', '96', '105.8000', '2.3000');
INSERT INTO `t_business_jobflowcount` VALUES ('123', '3G100?/?', '12222.2000', '96', '78.2000', '1.7000');
INSERT INTO `t_business_jobflowcount` VALUES ('124', '4G130?/?', '29527.8000', '96', '138.0000', '3.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('125', '6G180?/?', '34527.8000', '96', '207.0000', '4.5000');
INSERT INTO `t_business_jobflowcount` VALUES ('126', '11G280?/?', '10222.2000', '96', '46.0000', '1.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('131', '1G10?/?', '161000.0000', '130', '828.0000', '18.0000');
INSERT INTO `t_business_jobflowcount` VALUES ('134', '210M30?', '50000.0000', '132', '168.3600', '3.6600');
INSERT INTO `t_business_jobflowcount` VALUES ('135', '450M60?', '51000.0000', '132', '260.3600', '5.6600');



-- --1、看event是否开启====
show variables like '%sche%';

-- --2、将事件计划开启
set global event_scheduler =1;

-- --3、 =================在存储过程中使用游标执行update语句===
BEGIN
declare emp_id int;
declare stop int default 0;
declare 
   emp_cur cursor  for(
     select id from T_BUSINESS_JOBFLOWCOUNT)  ;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;

OPEN emp_cur;

    FETCH emp_cur INTO emp_id;
    WHILE ( stop is not null) DO
    update T_BUSINESS_JOBFLOWCOUNT set T_COUNT=T_COUNT+T_SPEED where id=emp_id;
    FETCH emp_cur INTO emp_id;
    END WHILE;

CLOSE emp_cur;

END
-----------------
-- --4、===================创建定时时间任务，每30秒执行一次该存储过程===
create event if not exists e_businesscount 
on schedule every 30 second 
on completion preserve 
do call E_TCOUNT(); 

-- --5、=================关闭事件任务======
alter event e_businesscount ON 
COMPLETION PRESERVE DISABLE; 

-- --6、==========开户事件任务======
alter event e_businesscount ON 
COMPLETION PRESERVE ENABLE;