查看定时是否开启:
查看event是否开启 : SHOW VARIABLES LIKE '%event_sche%';
将事件计划开启 : SET GLOBAL event_scheduler = 1;
将事件计划关闭 : SET GLOBAL event_scheduler = 0;
代码:
BEGIN
-- 保存表名
DECLARE v_tableName varchar(40) DEFAULT 0;
-- 时间
-- DECLARE v_datetime DATETIME;
-- DECLARE v_datetime TIMESTAMP;
-- 遍历结束标记
DECLARE done INT DEFAULT FALSE;
-- 找出要删除的表名
DECLARE cursor_table CURSOR FOR
SELECT TABLE_NAME FROM information_schema.`TABLES`
WHERE TABLE_NAME like 'info_illegal_web_%' AND TABLE_SCHEMA = 'ydb_illegal_data_his' AND LENGTH(TABLE_NAME) = 25;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 设置时间
-- SET v_datetime = UNIX_TIMESTAMP(DATE_SUB(SYSDATE(),INTERVAL 3 DAY));
-- 打开游标
OPEN cursor_table;
-- 开始循环
read_loop: LOOP
FETCH cursor_table INTO v_tableName;
-- 如果没有数据,退出
IF done THEN
LEAVE read_loop;
END IF;
-- 删除数据
IF DATE(SUBSTR(v_tableName, 18)) <= DATE_SUB(CURDATE(),INTERVAL 6 MONTH) THEN
SET @STMT :=CONCAT("DROP TABLE ",v_tableName);
PREPARE STMT FROM @STMT;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END LOOP;
END