mysql 存储过程 每天的凌晨1点执行定时任务 mysql定时任务调用存储过程

有表：cap_meter_detail 

字段：recordtime

情景：recordtime每半个小时记录一次，故一天会产生很很多数据，我们要做的是，每天00:00:00对cap_meter_detail 按时间拆分，比如，今天是2018年09月10号，那就将所有recordtime的日期为2018-09-10的拆分到表cap_meter_detail _20180610

思路：1、建表 cap_meter_detail _20180610

           2、从cap_meter_detail 找出date(recordtime)="2018-09-10"的所有数据，插入到cap_meter_detail _20180610

           3、将插入的数据从cap_meter_detail 里面删除

          由于可能某个时间段数据库出问题，没有及时删除，所以，我们需要做个循环，用存储过程的思路解决，如下图，找出来有11天的，那就要循环11次

 

步骤：

 

1、写存储过程myFun_cap_meter_detail 

 

BEGIN  

-- 定义变量 
    DECLARE tableName_bl VARCHAR(50); 
-- 定义done 
    DECLARE done INT;


-- 定义表名（tableName）游标
    DECLARE rs_tableName CURSOR FOR  
    
-- 得到游标集合
        SELECT
            -- recordtime,
            -- GROUP_CONCAT(id) ammeterid_group,
            -- DATE_FORMAT(recordtime, '%Y%m%d'),
            CONCAT('cap_meter_detail_',DATE_FORMAT(recordtime, '%Y%m%d')) tableName
        FROM
            `cap_meter_detail`
        WHERE
            recordtime < DATE(NOW())
        GROUP BY
            DATE_FORMAT(recordtime, '%Y%m%d');    
        
 

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 

-- 初始化done，为0，false
    set done = 0;

    open rs_tableName; 
    REPEAT 
    
        FETCH  rs_tableName into tableName_bl;
  
         -- 将类似 2018-03-19 00:22:00的数据插入到 cap_meter_20180319
                if done<>1 then 

       SET @var= tableName_bl;

-- 建表


    SET @createTableSqlStr=CONCAT("CREATE TABLE ",tableName_bl,"
     (
        id int(11) NOT NULL AUTO_INCREMENT,
        ammeterid int(11) DEFAULT NULL,
        a_voltage double(11,2) DEFAULT NULL,
        b_voltage double(11,2) DEFAULT NULL,
        c_voltage double(11,2) DEFAULT NULL,
        frequence double(11,2) DEFAULT NULL,
        a_current double(11,2) DEFAULT NULL,
        b_current double(11,2) DEFAULT NULL,
        c_current double(11,2) DEFAULT NULL,
        total_power_factor double(11,2) DEFAULT NULL,
        a_power_factor double(11,2) DEFAULT NULL,
        b_power_factor double(11,2) DEFAULT NULL,
        c_power_factor double(11,2) DEFAULT NULL,
        total_active_power double(11,2) DEFAULT NULL,
        a_active_power double(11,2) DEFAULT NULL,
        b_active_power double(11,2) DEFAULT NULL,
        c_active_power double(11,2) DEFAULT NULL,
        total_reactive_power double(11,2) DEFAULT NULL,
        a_reactive_power double(11,2) DEFAULT NULL,
        b_reactive_power double(11,2) DEFAULT NULL,
        c_reactive_power double(11,2) DEFAULT NULL,
        recordtime datetime DEFAULT NULL,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=51927530 DEFAULT CHARSET=utf8");


     -- 查看函数
        SELECT @createTableSqlStr; 

     -- 执行函数
        PREPARE createTableSqlStr FROM @createTableSqlStr;
        EXECUTE createTableSqlStr;


-- 插入


     SET @insertSqlStr=CONCAT("INSERT INTO ",tableName_bl,
                              " SELECT * FROM cap_meter_detail WHERE recordtime < DATE(NOW()) AND DATE_FORMAT(recordtime, '%Y%m%d')=right('",@var,"',8)");

     -- 查看函数
     SELECT @insertSqlStr; 

     -- 执行函数
     PREPARE insertSqlStr FROM @insertSqlStr;
     EXECUTE insertSqlStr;



-- 删除

        SET @deleteSqlStr=CONCAT("DELETE FROM cap_meter_detail WHERE recordtime < DATE(NOW()) AND DATE_FORMAT(recordtime, '%Y%m%d')=right('",@var,"',8)");

        -- 查看函数
        SELECT @deleteSqlStr; 
        -- 执行函数
        PREPARE deleteSqlStr FROM @deleteSqlStr;
        EXECUTE deleteSqlStr;
 
end if;
      


-- 直到done变为true结束循环
        UNTIL done END 
     REPEAT; 
    close rs_tableName;  
END

 

2、写事件：myEvent_cap_meter_detail 

CREATE EVENT
IF NOT EXISTS myEvent_cap_meter_detail ON SCHEDULE EVERY 24 HOUR ON COMPLETION PRESERVE DO
	CALL myFun_cap_meter_detail();

3、启用事件

-- 查看事件状态
SHOW VARIABLES LIKE '%event_scheduler%'; 

-- 启用事件
SET GLOBAL event_scheduler =1