Mysql - 存儲過程 - 定時刪表

在工業監控裡面, 需要對每天的數據, 進行記錄, 時間長了之後, 資料庫很容易撐爆. 這時候, 如果允許, 可以對之前的數據進行一次清除, 只記錄幾個月內的數據.

delimiter $
DROP PROCEDURE  if exists p_clearOldData;
/*
g_date_limit 時間限制, 如 2019_08 , 則刪除 該時間之前的表 
g_date_length 時間格式的長度, 如 尾碼時間是 2019_08, 則此處應該傳 7  
*/
CREATE  PROCEDURE `p_clearOldData`(in g_date_limit varchar(30) , in g_date_length int )
begin 
    /*查詢到的表名*/
    DECLARE g_table VARCHAR(100);

    /*查詢到的表名對應的尾碼時間*/
    DECLARE g_date VARCHAR(30) DEFAULT '';

    /*定義done, 用於跳出迴圈使用*/
    DECLARE done bit DEFAULT 0;

    ###DECLARE g_date_limit VARCHAR(30) DEFAULT '';

    ###DECLARE g_date_length int default 10;

    /*聲明游標*/
    DECLARE g_cursor CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA='abc' and TABLE_NAME like 'tbl_abc_%';

    /*游標查詢時, 如果找不到下一個了, 會將done置為1, 用於跳出 REPEAT 迴圈*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    /*待執行動態sql*/
    set @v_full_sql := '';

    ###set g_date_limit = DATE_FORMAT(date_add(NOW(), interval -90 day),'%Y_%m_%d');

    /*打開游標*/
    open g_cursor;

    /*迴圈*/
    REPEAT

        FETCH g_cursor into g_table;

        set g_date = right(g_table, g_date_length);

        if g_date < g_date_limit then 

            set @v_full_sql = CONCAT('drop table if exists ',g_table);

             /*預編譯此動態sql, 並存入stmt中*/
            PREPARE stmt from @v_full_sql;

            /*執行此動態sql, 此動態sql的作用, 是刪除表*/
            execute stmt;    

            /*釋放此資源*/
            DEALLOCATE PREPARE stmt;

            /*對執行情況進行記錄*/
            INSERT into tbl_event_log (name, time) values (g_table, NOW());

        end if;

    /*結束repeat迴圈*/
    UNTIL done END REPEAT; 

    /*關閉游標*/
    close g_cursor;

select 'OK';

end $
delimiter;

在資料庫中, 進行測試:

CREATE TABLE `t_bk001_2019_02` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

執行腳本:

call p_clearOldData('2019_03', 7);

會發現, 表確實被刪除了, 且別的表並未受到影響.

在不能發後臺包的情況下, 可以通過mysql定時任務和存儲過程, 來實現定時刪表操作. 

不過, 如果通過這種方式, 還需要對此存儲過程進行改動, 或者再創建一個存儲過程, 對此進行封裝成一個沒有參數的存儲過程.

對於這種定時任務的執行情況和執行時機, 最好能加入一個日誌進去: 

CREATE TABLE `tbl_event_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT '表名',
  `time` datetime DEFAULT NULL COMMENT '時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

*註:

以上操作, 不推薦在mysql中, 通過定時任務和存儲過程來實現此功能, 推薦通過後臺定時任務, 執行刪表操作.