mysql定时任务

-- 数据库中表的信息查询  
##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 continue handler for not found set done = true;   -- 将结束标志绑定到游标  
declare rs cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bdata' and TABLE_ROWS<100; -- 声明游标来实现遍历记录  
open rs; -- 打开游标  
  
-- 开始循环  
read_loop: LOOP  
   -- 提取游标里的数据，这里只有一个，多个的话也一样；  
    FETCH cur INTO a;  
    SELECT a;  
  
    -- 声明结束的时候  
    IF done THEN  
      LEAVE read_loop;  
    END IF;  
    -- 这里做你想做的循环的事件     
  
  END LOOP ;  
  -- 关闭游标  
  CLOSE cur;  
  
  
  
end $$  
DELIMITER ;  
  
call deleteTables;  
  
*/  
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 THEN  
        LEAVE 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 day  
on completion preserve    
do call deletetables();  
2. 事件查看
 
 
#查看是否开启事件
show variables like '%sche%';

##开启事件
set global event_scheduler = 1;

/*

create event if not exists e_deletetables_rowsless100
on schedule every 1 day
on completion preserve
do call deletetables();
*/



##注意分隔符的使用
/*
DELIMITER $$
CREATE PROCEDURE test ()
begin
update student set name = now() where NO = '2012001';
end $$
DELIMITER ;
*/
##创建事件
/*
create event if not exists e_test
on schedule every 5 second
on completion preserve
do call test();

*/
##关闭事件任务  定时任务
#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;

##注意分隔符的使用
/*
DELIMITER $$
CREATE PROCEDURE test ()
begin
update student set name = now() where NO = '2012001';
end $$
DELIMITER ;
*/
##创建事件
/*
create event if not exists e_test
on schedule every 5 second
on completion preserve
do call test();

*/
##关闭事件任务  定时任务
#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;
/*
DELIMITER $$

#drop procedure if exists new_procedure $$

CREATE PROCEDURE `bdata`.`new_procedure` ()
BEGIN
-- 需要定义接收游标数据的变量
  DECLARE a VARCHAR(255);

  -- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;


   -- 游标
  DECLARE cur CURSOR FOR SELECT name FROM bdata.student;
 -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 打开游标
  OPEN cur;

  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据，这里只有一个，多个的话也一样；
    FETCH cur INTO a;
    SELECT a;

    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件

  END LOOP ;
  -- 关闭游标
  CLOSE cur;

END $$
DELIMITER ;

call new_procedure;
*/


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 THEN
        LEAVE READ_LOOP;
      END IF;
      -- 一些敏感操作

      SELECT tablename;

/*    ## 动态sql语句的构建
      SET @sqlstr=concat("drop table ", tablename);
      prepare stmt from @sqlstr;
      EXECUTE stmt;
      ddeletetableseallocate prepare stmt;
*/

   END LOOP ;
   -- 关闭游标
   CLOSE cur;
END $$
delimiter ;




-- CALL deletetables;
-- 数据库中表的信息查询
##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 continue handler for not found set done = true;   -- 将结束标志绑定到游标
declare rs cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bdata' and TABLE_ROWS<100; -- 声明游标来实现遍历记录
open rs; -- 打开游标

-- 开始循环
read_loop: LOOP
   -- 提取游标里的数据，这里只有一个，多个的话也一样；
    FETCH cur INTO a;
    SELECT a;

    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件

  END LOOP ;
  -- 关闭游标
  CLOSE cur;



end $$
DELIMITER ;

call deleteTables;

*/
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 THEN
        LEAVE 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 day
on completion preserve
do call deletetables();