Mysql定时任务。

1. 存储过程的设计实验

-- 数据库中表的信息查询##SELECT TABLE_NAME,TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'bdata'  ORDER BY TABLE_ROWS DESC##select count(*) from (SELECT TABLE_NAME,TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'bdata'  ORDER BY TABLE_ROWS DESC) as temp;delimiter $$DROP PROCEDURE IF EXISTS deletetables $$ -- -- 实例 -- 存储过程名为：delateTables -- 参数:没有参数 -- CREATE PROCEDURE deletetables () BEGIN   DECLARE tablename VARCHAR(255);     -- 待删除的表的名称   DECLARE done INT DEFAULT FALSE;-- 遍历数据结束标志    DECLARE cur CURSOR FOR SELECT table_name FROM   information_schema.tables  WHERE  table_schema = 'bdata' AND table_rows<100; -- 声明游标来实现遍历记录  DECLARE ConTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 将结束标志绑定到游标    OPEN cur;   -- 打开游标 -- 下面是利用游标执行循环的部分  -- 开始循环   READ_LOOP: LOOP   -- 提取游标里的tb_myisam数据，这里只有一个，多个的话也一样；   FETCH cur INTO  tablename;      -- DROP TABLE tablename;   -- 声明结束的时候   IF done THENLEAVE READ_LOOP;   END IF;   -- 一些敏感操作       SELECT tablename;   SET @sqlstr=concat("drop table ", tablename);        prepare stmt from @sqlstr;        EXECUTE stmt;                 deallocate prepare stmt;    END LOOP ;    -- 关闭游标   CLOSE cur;END $$ delimiter ;-- CALL deletetables;create event if not exists event_deletetables_rowslessthan100  on schedule every 1 dayon completion preserve  do call deletetables();

2. 事件查看

#查看是否开启事件show variables like '%sche%'; ##开启事件 set global event_scheduler = 1;##注意分隔符的使用##创建事件##关闭事件任务  定时任务#alter event e_test ON COMPLETION PRESERVE DISABLE;##开启事件任务#alter event e_test ON COMPLETION PRESERVE ENABLE; 

3. 定时任务测试

##查看是否开启事件show variables like '%sche%'; ##开启事件 set global event_scheduler = 1;##注意分隔符的使用##创建事件##关闭事件任务  定时任务#alter event e_test ON COMPLETION PRESERVE DISABLE;##开户事件任务#alter event e_test ON COMPLETION PRESERVE ENABLE; #select table_name from information_schema.tables where table_schema='bdatadd'#select TABLE_NAME from (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bdata' and TABLE_ROWS<100) as temp limit 0,1;

4. 查看表

#show tables like 'mkk_data_%';#use ptimpdb;#select table_name,table_rows from information_schema.columns where table_schema = "ptimpdb" and table_name like'mkk_data_%' ORDER BY TABLE_ROWS DESC;#select table_name from information_schema.columns where table_schema = "ptimpdb" and table_name like'mkk_data_%'group by table_name；#select count(*) as res from  (select table_name from information_schema.columns where table_schema = "pdb" and table_name like'mkk_data_%'group by table_name)#select count(*) as res from mkk_data_10##数据库查询表的结果#SELECT TABLE_NAME,TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'pdb' AND TABLE_NAME LIKE'mkk_data_%' AND  TABLE_ROWS < 100 ORDER BY TABLE_ROWS DESC;#SELECT TABLE_NAME,TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'pdb' AND TABLE_NAME LIKE'mkk_data_%' AND  TABLE_ROWS < 100 ORDER BY TABLE_ROWS DESC;delimiter $$DROP PROCEDURE IF EXISTS deletetables $$ -- -- 实例 -- 存储过程名为：delateTables -- 参数:没有参数 deletetables-- CREATE PROCEDURE deletetables () BEGIN   DECLARE tablename VARCHAR(255);     -- 待删除的表的名称   DECLARE done INT DEFAULT FALSE;-- 遍历数据结束标志    DECLARE cur CURSOR FOR SELECT table_name FROM   information_schema.tables  WHERE  table_schema = 'pdb' AND table_rows<100; -- 声明游标来实现遍历记录  DECLARE ConTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 将结束标志绑定到游标    OPEN cur;   -- 打开游标 -- 下面是利用游标执行循环的部分  -- 开始循环   READ_LOOP: LOOP   -- 提取游标里的tb_myisam数据，这里只有一个，多个的话也一样；   FETCH cur INTO  tablename;      -- DROP TABLE tablename;   -- 声明结束的时候   IF done THENLEAVE READ_LOOP;   END IF;   -- 一些敏感操作       SELECT tablename;      END LOOP ;    -- 关闭游标   CLOSE cur;END $$ delimiter ;-- CALL deletetables;