实战：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 '' 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

巴特西

实战：mysql写存储过程并定时调用

最新文章

热门文章