mysql 让一个存储过程定时作业的代码 分享 - 全知网mysql 让一个存储过程定时作业的代码点击复制内容1、在mysql 中建立一个数据库 test1 语句：create database test1 2、创建表examinfo create table examinfo( id int auto_increment not null, endtime datetime, primary key(id) ); 3 插入数据： insert into examinfo values('1',' 23:26:50'); 4 创建存储过程test CREATE PROCEDURE test () BEGIN update examinfo SET endtime = now() WHERE id = 1; END; 5、 创建event e_test
CREATE EVENT if not exists e_test on schedule every 30 second on completion preserve do call test();
6、查看event是否开启: show variables like '%sche%'; 将事件计划开启: set global event_scheduler=1; 关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE; 开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE; 7、运行查询结果即可出现想要的结果。结果显示如下： 原始数据：过一段时间查询后的数据： 来源：全知网mysql存储过程，不知道哪的语法错了？
DELIMITER $$
USE `mstgwnew`$$
DROP PROCEDURE IF EXISTS `UPDATE_RANKING_MIDDLE_BYDAY`$$
CREATE DEFINER=`root`@`%` PROCEDURE `UPDATE_RANKING_MIDDLE_BYDAY`(
#传入的前多少条数据
IN topFlag INT(10)
DECLARE NOWTIMEFORMAT VARCHAR(20);
DECLARE NOWHOUR VARCHAR(10);
DECLARE stmt VARCHAR(2000);
DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE websiteidV VARCHAR(300);
DECLARE parentidV VARCHAR(300);
DECLARE timesV DATETIME;
DECLARE urlV LONGTEXT;
DECLARE countnumV INT(10);
DECLARE isfirstpage CHAR(1);#是否为第一页1标识第一页
DECLARE titleV VARCHAR(500);
DECLARE stmt2 VARCHAR(2000);
#取当前时间YYMMDD
SET NOWTIMEFORMAT = CURDATE()+0;
#取当前小时HH
SET NOWHOUR = HOUR(NOW());
#创建日志临时表
DROP TABLE IF EXISTS `temp_apache`;
CREATE TEMPORARY TABLE `temp_apache` (
`id` INT NOT NULL AUTO_INCREMENT,
`website_id` VARCHAR(300) NOT NULL,
`parent_id` VARCHAR(300) NULL,
`times` DATETIME NOT NULL,
`url` LONGTEXT NOT NULL,
`countnum` INT(10) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
SET @sqlstring=CONCAT('INSERT INTO temp_apache (website_id,parent_id,times,url,countnum) SELECT APACHE.WEBSITE_ID WEBSITE_ID,APACHE.WEBROOT_ID PARENT_ID,APACHE.ACCESS_TIME TIMES,APACHE.URL URL,COUNT(APACHE.URL) COUNTNUM FROM apache_',NOWTIMEFORMAT,'_',NOWHOUR,' APACHE WHERE APACHE.STATU_CODE=200 GROUP BY APACHE.URL) ');
PREPARE stmt FROM @
SELECT MIN(`id`) INTO i FROM temp_
SELECT MAX(`id`) INTO maxCnt FROM temp_
#创建排行榜中间临时表
DROP TABLE IF EXISTS `temp_ranking_middle`;
CREATE TEMPORARY TABLE `temp_ranking_middle` (
`title` VARCHAR(500) NULL,
`website_id` VARCHAR(300) NOT NULL,
`parent_id` VARCHAR(300) NULL,
`times` DATETIME NOT NULL,
`url` LONGTEXT NOT NULL,
`countvisit` INT(10) NOT NULL
WHILE i &= maxCnt DO
SELECT website_id,parent_id,times,url,countnum INTO websiteidV,parentidV,timesV,urlV,countnumV FROM temp_apache WHERE id=i;
SET @sqlstr=CONCAT(' SELECT
DATA.IS_FIRST_PAGE into @isfirstpage,DATA.TITLE into @titleV FROM DATA_',parentidV,' DATA WHERE DATA.URL = ',urlV);
PREPARE stmt2 FROM @
EXECUTE stmt2;
#如果是第一页则插入到排行榜中间临时表中
IF isfirstpage && 0 THEN
INSERT INTO temp_ranking_middle(titile,website_id,parent_id,times,url,countvisit) VALUES(titleV,websiteidV,parentidV,timesV,urlV,countnumV);
SET i = i + 1;
END WHILE;
INSERT INTO RANKING_MIDDLE(TITLE,WEBSITE_ID,PARENT_ID,TIMES,URL,`COUNT`) SELECT temp.title title,temp.website_id websiteid,temp.parent_id parent_id,temp.times times,temp.url url,temp.countvisit countnum
FROM temp_ranking_middle temp
ORDER BY countnum DESC LIMIT topF
DELIMITER ;
错误码: 1064 You have an error in your SQL check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE i &= maxCnt DO&
& SELECT website_id,parent_id,times,url,countnum INTO web' at line 64
给你调试了下，但是上面提到的那张表我这边没有，所以最后几句话你需要自己检查检查。代码贴出来给你。
DELIMITER $$
USE xqjp$$
DROP PROCEDURE IF EXISTS UPDATE_RANKING_MIDDLE_BY_DAY$$
CREATE PROCEDURE UPDATE_RANKING_MIDDLE_BY_DAY(IN TOPFLAG INT(10))
DECLARE NOWTIMEFORMAT VARCHAR(20);
DECLARE NOWHOUR VARCHAR(10);
DECLARE stmt VARCHAR(2000);
DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE websiteidV VARCHAR(300);
DECLARE parentidV VARCHAR(300);
DECLARE timesV DATETIME;
DECLARE urlV LONGTEXT;
DECLARE countnumV INT(10);
DECLARE isfirstpage CHAR(1);
DECLARE titleV VARCHAR(500);
DECLARE stmt2 VARCHAR(2000);
SET NOWTIMEFORMAT = CURDATE()+0;
SET NOWHOUR = HOUR(NOW());
DROP TABLE IF EXISTS temp_
CREATE TEMPORARY TABLE temp_apache (
id INT NOT NULL AUTO_INCREMENT,
website_id VARCHAR(300) NOT NULL,
parent_id VARCHAR(300) NULL,
times DATETIME NOT NULL,
url LONGTEXT NOT NULL,
countnum INT(10) NOT NULL,
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=UTF8;
SET @sqlstring=CONCAT('INSERT INTO temp_apache (website_id,parent_id,times,url,countnum) SELECT APACHE.WEBSITE_ID WEBSITE_ID,APACHE.WEBROOT_ID PARENT_ID,APACHE.ACCESS_TIME TIMES,APACHE.URL URL,COUNT(APACHE.URL) COUNTNUM FROM apache_',NOWTIMEFORMAT,'_',NOWHOUR,' APACHE WHERE APACHE.STATU_CODE=200 GROUP BY APACHE.URL) ');
PREPARE stmt FROM @
SELECT MIN(id) INTO i FROM temp_
SELECT MAX(id) INTO maxCnt FROM temp_
DROP TABLE IF EXISTS temp_ranking_
CREATE TEMPORARY TABLE temp_ranking_middle (
title VARCHAR(500) NULL,
website_id VARCHAR(300) NOT NULL,
parent_id VARCHAR(300) NULL,
times DATETIME NOT NULL,
url LONGTEXT NOT NULL,
countvisit INT(10) NOT NULL
WHILE i &= maxCnt DO
SELECT website_id,parent_id,times,url,countnum INTO websiteidV,parentidV,timesV,urlV,countnumV FROM temp_apache WHERE id=i;
SET @sqlstr=CONCAT('SELECT DATA.IS_FIRST_PAGE into @isfirstpage,DATA.TITLE into @titleV FROM DATA_',parentidV,' DATA WHERE DATA.URL = ',urlV);
PREPARE stmt2 FROM @
EXECUTE stmt2;
IF isfirstpage && 0 THEN
INSERT INTO temp_ranking_middle(titile,website_id,parent_id,times,url,countvisit) VALUES(titleV,websiteidV,parentidV,timesV,urlV,countnumV);
SET i = i + 1;
END WHILE;
INSERT INTO RANKING_MIDDLE(TITLE,WEBSITE_ID,PARENT_ID,TIMES,URL,COUNT)
SELECT temp.title title,temp.website_id websiteid,temp.parent_id parent_id,temp.times times,temp.url url,temp.countvisit countnum
FROM temp_ranking_middle temp
ORDER BY countnum DESC LIMIT topF
--- 共有 1 条评论 ---
多谢前辈???
你这个存储过程中，RANKING_MIDDLE是另外壹张表的表名吗？我在上下文中找不到相应的定义。目前我已经发现的你这个存储过程中有语法错误如下：84行的BEGIN是不需要的，88行应该是END IF;
不可否认，调试 MySQL 的存储过程是件非常痛苦的事情，有时候需要靠猜
--- 共有 1 条评论 ---
是啊，我折腾了好久，有个；露了??ORZ