Mysql - 存儲過程 - 定時删表

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_cursor CURSOR FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA='tt_abc' and TABLE_NAME like 't_bk001_%';

/*遊标查詢時, 如果找不到下一個了, 會将done置為1, 用于跳出 REPEAT 循環*/           

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

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

/*打開遊标*/
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;

    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定時任務和存儲過程, 來實作定時删表操作.

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

*注:

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

原文位址

https://www.cnblogs.com/elvinle/p/11444781.html