mysql任务调度器_mysql存储过程跟任务调度器

mysql存储过程和任务调度器 -- 创建月表和日表的存储过程delimiter $$use `devicitydb1`$$drop procedure if exists `createTable`$$create definer=`devicityusr01`@`%` procedure `createTable`()begin declare sql_str varchar(20000); declare tname varc

mysql存储过程和任务调度器

-- 创建月表和日表的存储过程

delimiter $$

use `devicitydb1`$$

drop procedure if exists `createTable`$$

create definer=`devicityusr01`@`%` procedure `createTable`()

begin

declare sql_str varchar(20000);

declare tname varchar(200);

declare dataStr varchar(2000);

declare i int;

set i=0;

while i<=7 do

-- 创建日表

set sql_str=' create table if not exists tableName ( MGMT_INFO_VALUE_ID VARCHAR(60) NOT NULL,MGMT_ENTITY_ID VARCHAR(512) DEFAULT NULL,MGMT_INFO_ID VARCHAR(255) DEFAULT NULL, FROM_DATE DATETIME DEFAULT NULL, THRU_DATE DATETIME DEFAULT NULL, ITEM_VALUE0 VARCHAR(255) DEFAULT NULL, ITEM_VALUE1 VARCHAR(255) DEFAULT NULL, ITEM_VALUE2 VARCHAR(255) DEFAULT NULL, ITEM_VALUE3 VARCHAR(255) DEFAULT NULL, ITEM_VALUE4 VARCHAR(255) DEFAULT NULL, ITEM_VALUE5 VARCHAR(255) DEFAULT NULL, ITEM_VALUE6 VARCHAR(255) DEFAULT NULL, ITEM_VALUE7 VARCHAR(255) DEFAULT NULL, ITEM_VALUE8 VARCHAR(255) DEFAULT NULL, ITEM_VALUE9 VARCHAR(255) DEFAULT NULL, ITEM_DIM0 VARCHAR(255) DEFAULT NULL, ITEM_DIM1 VARCHAR(255) DEFAULT NULL, ITEM_DIM2 VARCHAR(255) DEFAULT NULL, ITEM_DIM3 VARCHAR(255) DEFAULT NULL, ITEM_DIM4 VARCHAR(255) DEFAULT NULL, ITEM_DIM5 VARCHAR(255) DEFAULT NULL, ITEM_DIM6 VARCHAR(255) DEFAULT NULL, ITEM_DIM7 VARCHAR(255) DEFAULT NULL, ITEM_DIM8 VARCHAR(255) DEFAULT NULL, ITEM_DIM9 VARCHAR(255) DEFAULT NULL, STATUS_ID VARCHAR(20) DEFAULT NULL, PERIOD_DATE DATE DEFAULT NULL, PERIOD_TIME DATE DEFAULT NULL, MEDIATION_OBJECT_ID VARCHAR(20) DEFAULT NULL, MEDIATION_DATE DATETIME DEFAULT NULL, EVENT_LEVEL VARCHAR(10) DEFAULT NULL, INFO_COUNT DECIMAL(19,2) DEFAULT NULL, MEDIATION_DELAY DECIMAL(19,2) DEFAULT NULL, DATA_BASE_DELAY DECIMAL(19,2) DEFAULT NULL, SUM_DELAY DECIMAL(19,2) DEFAULT NULL, COMMENTS VARCHAR(400) DEFAULT NULL, INFO_VALUE_UID VARCHAR(255) DEFAULT NULL, LAST_UPDATED_STAMP DATETIME DEFAULT NULL, LAST_UPDATED_TX_STAMP DATETIME DEFAULT NULL, CREATED_STAMP DATETIME DEFAULT NULL, CREATED_TX_STAMP DATETIME DEFAULT NULL, VALUE_DESC VARCHAR(255) DEFAULT NULL, NATIVE_CODE VARCHAR(255) DEFAULT NULL, RESOURCE_ID VARCHAR(20) DEFAULT NULL, SERVICE_ID VARCHAR(20) DEFAULT NULL, BUSINESS_ID VARCHAR(20) DEFAULT NULL, PARTY_ID VARCHAR(20) DEFAULT NULL, FACILITY_ID VARCHAR(20) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8';

-- set dataStr=curdate();

set dataStr=date_sub(curdate(),interval -i day);

set dataStr=replace(dataStr, '-','');

if i=7 then

-- 创建月表

set dataStr=substring(dataStr,1,length(dataStr)-2); -- 精确到月 创建月表

end if;

set tname=concat('mgmt_info_value_',dataStr) ;

set sql_str=replace(sql_str, 'tableName',tname) ;

-- select sql_str from dual;

set @delSql = sql_str;

prepare stmt from @delSql;

execute stmt;

select sql_str from dual;

set i=i+1;

end while;

end$$

delimiter ;

-- 事件调度器-----------------------------

delimiter $$

-- SET GLOBAL event_scheduler = ON$$ -- required for event to execute but not create

createEVENT `devicitydb1`.`event_call_procedure`

on schedule

every 1 day

-- EVERY 1 MINUTE

-- STARTS '2014-05-26?16:11:00'

-- AT TIMESTAMP '2014-05-26 16:27:00'

on completion preserve

enable

do

begin

call createTable();

end$$

delimiter ;

?

本文原创发布php中文网，转载请注明出处，感谢您的尊重！