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 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;