- 浏览: 253738 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
我在路上看风景:
反复看了几多遍,才明白。
Spring Security3.1例子 -
ganbo:
...
CXF的三种发布方式和客户端调用方法 -
ganbo:
...
CXF的三种发布方式和客户端调用方法 -
蓝aiq冰:
非常感谢,解决了大问题
Description Resource Path Location Type ...project facets version -
安东源:
...
spring3.0多文件上传
查看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;
简单实例.
创建表 CREATE TABLE test(endtime DATETIME);
创建存储过程test
CREATE PROCEDURE test ()
BEGIN
update examinfo SET endtime = now() WHERE id = 14;
END;
创建event e_test
CREATE EVENT if not exists e_test
on schedule every 30 second
on completion preserve
do call test();
触发器
delimiter //
CREATE TRIGGER trigger_htmlcache BEFORE INSERT ON t_model
FOR EACH ROW BEGIN
if CURDATE()<NEW.time then
INSERT INTO t_htmlcache(id,url) value(NEW.id,NEW.url);
end if;
END;
//
通过建表->Insert的方式测试.
DELIMITER $$
DROP PROCEDURE IF EXISTS `njfyedepartment`.`sp_ireport` $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_ireport`(IN qureyType VARCHAR(20),IN daytime VARCHAR(20),IN p_ids VARCHAR(50),IN c_ids VARCHAR(50),IN ct1_ids VARCHAR(50),IN ct2_ids VARCHAR(50),IN ku VARCHAR(50),IN ireport_chart varchar(50))
BEGIN
DECLARE i INT DEFAULT 1;
IF qureyType = 'insert' OR qureyType = 'INSERT' THEN
INSERT INTO ireport
(pid,cid,ct1id,ct2id,creatTime,crawlerNumber,WEEK)
SELECT province AS pid,
cityid AS cid,
category1id AS ct1id,
category2id AS ct2id,
(CURRENT_DATE) AS creatTime,COUNT(*) AS crawlerNumber,
(FLOOR(DAYOFMONTH(CURRENT_DATE)/8)+1) AS WEEK
FROM t_model t
WHERE TIME > (CURRENT_DATE-1) AND TIME < (CURRENT_DATE)
AND province IS NOT NULL
AND cityid IS NOT NULL
GROUP BY province,cityid,category1id,category2id;
END IF;
IF qureyType = 'month' OR qureyType = 'MONTH' THEN
IF EXISTS(SELECT * FROM information_schema.`TABLES` T WHERE TABLE_NAME = 'tmp_result' AND TABLE_SCHEMA = ku) THEN
DROP TABLE tmp_result;
END IF;
CREATE TABLE tmp_result
(pid VARCHAR(50),pName VARCHAR(50),cid VARCHAR(50),cName VARCHAR(50),ct1id VARCHAR(50),ct1Name VARCHAR(50),
ct2id VARCHAR(50),ct2Name VARCHAR(50),month1 INTEGER,month2 INTEGER,month3 INTEGER,month4 INTEGER,month5 INTEGER,
month6 INTEGER,month7 INTEGER,month8 INTEGER,month9 INTEGER,month10 INTEGER,month11 INTEGER,month12 INTEGER,heji INTEGER);
lable_exit: BEGIN
SET @SqlCmd = ' INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name)
SELECT pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name FROM
(SELECT ia.pid,a.name AS pname,ia.cid,b.name AS cname,ia.ct1id,c.name AS ct1name,ia.ct2id,d.name AS ct2name
FROM ireport ia
LEFT JOIN province a ON ia.pid=a.id
LEFT JOIN city b ON ia.cid=b.id
LEFT JOIN t_category1 c ON ia.ct1id=c.id
LEFT JOIN t_category2 d ON ia.ct2id=d.id
WHERE YEAR(ia.creatTime)=YEAR(?) ';
IF p_ids IS NOT NULL AND p_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.pid in (');
SET @SqlCmd = CONCAT(@SqlCmd, p_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF c_ids IS NOT NULL AND c_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.cid in (');
SET @SqlCmd = CONCAT(@SqlCmd, c_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct1_ids IS NOT NULL AND ct1_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct1id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct1_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct2_ids IS NOT NULL AND ct2_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct2id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct2_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
SET @SqlCmd = CONCAT(@SqlCmd , ') AS ir GROUP BY pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name;');
PREPARE stmt1 FROM @SqlCmd;
SET @a = daytime;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
WHILE i <= 12 DO
lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result AS a,
(
SELECT pid,cid,ct1id,ct2id,SUM(crawlerNumber) AS crawlerNumber FROM
(SELECT pid,cid,ct1id,ct2id,crawlerNumber FROM ireport WHERE MONTH(creatTime)=? AND YEAR(creatTime)=YEAR(?)) AS ir
GROUP BY pid,cid,ct1id,ct2id
) AS b
SET a.month';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , '=b.crawlerNumber ');
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE a.pid=b.pid AND a.cid=b.cid AND a.ct1id=b.ct1id AND a.ct2id=b.ct2id; ');
PREPARE stmt1 FROM @SqlCmd;
SET @a = i;
SET @b = daytime;
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result SET month';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' = 0 WHERE month');
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' IS NULL');
PREPARE stmt1 FROM @SqlCmd;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
SET i = i + 1;
END WHILE;
UPDATE tmp_result SET heji=month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12;
INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12,heji)
SELECT '' AS pid,'--' AS pname,'' AS cid,'总合计:' AS cname,'' AS ct1id,'--' AS ct1name,'' AS ct2id,'--' AS ct2name,SUM(month1) AS month1,SUM(month2) AS month2,SUM(month3) AS month3,SUM(month4) AS month4,
SUM(month5) AS month5,SUM(month6) AS month6,SUM(month7) AS month7,SUM(month8) AS month8,SUM(month9) AS month9,SUM(month10) AS month10,SUM(month11) AS month11,SUM(month12) AS month12,SUM(heji) AS heji
FROM tmp_result;
IF ireport_chart = 'report' OR ireport_chart = 'REPORT' THEN
SELECT pid,pName,cid,cName,ct1id,ct1Name,ct2id,ct2Name,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12,heji FROM tmp_result;
END IF;
IF ireport_chart = 'chart' OR ireport_chart = 'CHART' THEN
SELECT '' AS pid,pName,'' AS cid,'' AS cName,'' AS ct1id,'' AS ct1Name,'' AS ct2id,'' AS ct2Name,SUM(month1) as month1,SUM(month2) as month2,SUM(month3) as month3,SUM(month4) as month4,
SUM(month5) as month5,SUM(month6) as month6,SUM(month7) as month7,SUM(month8) as month8,SUM(month9) as month9,SUM(month10) as month10,SUM(month11) as month11,SUM(month12) as month12,'' AS heji
FROM (SELECT pid,pName,cid,cName,ct1id,ct1Name,ct2id,ct2Name,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12 FROM tmp_result WHERE pname <> '--') AS ir
GROUP BY pid;
END IF;
END IF;
IF qureyType = 'week' OR qureyType = 'WEEK' THEN
IF EXISTS(SELECT * FROM information_schema.`TABLES` T WHERE TABLE_NAME = 'tmp_result' AND TABLE_SCHEMA = ku) THEN
DROP TABLE tmp_result;
END IF;
CREATE TABLE tmp_result
(pid VARCHAR(50),pName VARCHAR(50),cid VARCHAR(50),cName VARCHAR(50),ct1id VARCHAR(50),ct1Name VARCHAR(50),ct2id VARCHAR(50),ct2Name VARCHAR(50),week1 INTEGER,week2 INTEGER,week3 INTEGER,week4 INTEGER,heji INTEGER);
lable_exit: BEGIN
SET @SqlCmd = ' INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name)
SELECT pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name FROM
(SELECT ia.pid,a.name AS pname,ia.cid,b.name AS cname,ia.ct1id,c.name AS ct1name,ia.ct2id,d.name AS ct2name
FROM ireport ia
LEFT JOIN province a ON ia.pid=a.id
LEFT JOIN city b ON ia.cid=b.id
LEFT JOIN t_category1 c ON ia.ct1id=c.id
LEFT JOIN t_category2 d ON ia.ct2id=d.id
WHERE YEAR(ia.creatTime)=YEAR(?) And MONTH(ia.creatTime)=MONTH(?) ';
IF p_ids IS NOT NULL AND p_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.pid in (');
SET @SqlCmd = CONCAT(@SqlCmd, p_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF c_ids IS NOT NULL AND c_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.cid in (');
SET @SqlCmd = CONCAT(@SqlCmd, c_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct1_ids IS NOT NULL AND ct1_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct1id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct1_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
IF ct2_ids IS NOT NULL AND ct2_ids <> '' THEN
SET @SqlCmd = CONCAT(@SqlCmd , 'and ia.ct2id in (');
SET @SqlCmd = CONCAT(@SqlCmd, ct2_ids);
SET @SqlCmd = CONCAT(@SqlCmd, ')');
END IF;
SET @SqlCmd = CONCAT(@SqlCmd , ') AS ir GROUP BY pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name;');
PREPARE stmt1 FROM @SqlCmd;
SET @a = daytime;
EXECUTE stmt1 USING @a,@a;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
WHILE i <= 4 DO
lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result AS a,
(
SELECT pid,cid,ct1id,ct2id,SUM(crawlerNumber) AS crawlerNumber FROM
(SELECT pid,cid,ct1id,ct2id,crawlerNumber FROM ireport WHERE WEEK=? AND MONTH(creatTime)=MONTH(?)) AS ir
GROUP BY pid,cid,ct1id,ct2id
) AS b
SET a.week';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , '=b.crawlerNumber ');
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE a.pid=b.pid AND a.cid=b.cid AND a.ct1id=b.ct1id AND a.ct2id=b.ct2id; ');
PREPARE stmt1 FROM @SqlCmd;
SET @a = i;
SET @b = daytime;
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
lable_exit: BEGIN
SET @SqlCmd = 'UPDATE tmp_result SET week';
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' = 0 WHERE week');
SET @SqlCmd = CONCAT(@SqlCmd , i);
SET @SqlCmd = CONCAT(@SqlCmd , ' IS NULL');
PREPARE stmt1 FROM @SqlCmd;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
LEAVE lable_exit;
END lable_exit;
SET i = i + 1;
END WHILE;
UPDATE tmp_result SET heji=week1+week2+week3+week4;
INSERT INTO tmp_result (pid,pname,cid,cname,ct1id,ct1name,ct2id,ct2name,week1,week2,week3,week4,heji)
SELECT '' AS pid,'--' AS pname,'' AS cid,'总合计:' AS cname,'' AS ct1id,'--' AS ct1name,'' AS ct2id,'--' AS ct2name, SUM(week1) AS week1,SUM(week2) AS week2,SUM(week3) AS week3,SUM(week4) AS week4,SUM(heji) AS heji
FROM tmp_result;
IF ireport_chart = 'report' OR ireport_chart = 'REPORT' THEN
SELECT pid,pName,cid,cName,ct1id,ct1Name,ct2id,ct2Name,week1,week2,week3,week4,heji FROM tmp_result;
END IF;
IF ireport_chart = 'chart' OR ireport_chart = 'CHART' THEN
SELECT '' as pid,pName,'' as cid,'' as cName,'' as ct1id,'' as ct1Name,'' as ct2id,'' as ct2Name,SUM(week1) AS week1,SUM(week2) AS week2,SUM(week3) AS week3,SUM(week4) AS week4,'' as heji
FROM (SELECT pid,cid,pname,cname,week1,week2,week3,week4 FROM tmp_result WHERE pname <> '--') AS ir
GROUP BY pid;
END IF;
END IF;
END $$
DELIMITER ;
发表评论
-
mongo Reduce
2014-01-09 17:58 0MapReduce是一种编程模型,用于大规模数 ... -
更新表的结构和冗余数据
2012-08-14 16:00 876/*添加表字段*/ALTER TABLE `hplus_sta ... -
mysql 更改自动增长字段值的重新设定
2011-10-21 10:37 1029ALTER TABLE `testTAB` auto_incr ... -
安装mysql出现The security setting could not be applied问题
2011-09-17 00:45 1343解决方法: 1, 卸载MySQL 2, 删除目录 ... -
Oracle分区详解
2011-09-08 21:36 1086关键字:Oracle分区详解 ... -
oracle数据库基本操作
2011-09-01 10:36 941可以通过dbca命令再次创建数据库 http://IP地址: ... -
mysql数据库的备份和恢复
2011-06-20 17:25 975cd mysql安装目录\bin 备份:mysqldump - ... -
Oracle10逻辑备份及还原
2011-04-03 17:28 1022附近是oracle备份自动化脚本,将其增添到windows任 ... -
jdbc连接数据库
2011-03-21 17:44 9681、Oracle8/8i/9i数据库(thin模式) ... -
mySql安装问题解决方案
2011-03-21 16:38 988安装mysql出现The security setting c ...
相关推荐
mysql定时任务脚本
数据库定时任务执行脚本,数据库脚本,定时任务,为了避免写大量的Java代码和减轻应用服务器压力
生成sql文件备份和还原,经过本人测试,可以使用。实现定时功能
主要介绍了mysql定时任务(event事件)实现详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
主要介绍了Linux下如何实现Mysql定时任务,需要的朋友可以参考下
实现mysql 每天定时自动给数据库表追加分区,包含存储计划和存储过程
本文实例讲述了mysql 定时任务的实现与使用方法。分享给大家供大家参考,具体如下: mysql5.1.6增加了一个事件调度器(Event Scheduler),可以做定时任务(定时删除记录,定时数据统计),取代之前系统的计划任务。...
windows定时任务备份mysql数据库脚本
navicat创建MySql 定时任务详解 事件(event)是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次,也可周期性的启动,它由一个特定的线程来管理的,也就是所谓的“事件调度器”。事件和触发器类似,都...
SpringBoot定时任务实现Oracle和mysql数据同步
查看event是否开启 代码如下:show variables like ‘%sche%’; 将事件计划开启 代码如下:set global event_scheduler =1; 创建存储过程test 代码如下:CREATE PROCEDURE test () BEGIN update examinfo SET ...
本文实例讲述了mysql实现设置定时任务的方法。分享给大家供大家参考,具体如下: 今天遇到了个需要每天定时执行的任务,在mysql数据库里面提供了这样的功能,正好整理下分享出来。 1.首先检查是否开启了定时任务 ...
springmvc+spring+mysql+maven定时任务
当安装完成操作系统之后,默认便会启动此任务调度命令。下面这篇文章主要给大家介绍了关于在Linux下Mysql定时任务备份数据的实现方法,文中通过示例代码介绍的非常详细,需要的朋友可以参考下
analyData这是一个java定时任务,定时调用mysql的存储过程
Python定时任务,将xml报文解释入mysql数据库