mysql-定時呼叫儲存過程

weixin_34377065發表於2015-03-18
MySql儲存過程

mysql定時呼叫儲存過程，對錶資料集表結構進行備份

儲存過程例項：

BEGIN
	DECLARE tname varchar(64);	
	set @tname = CONCAT('RENAME TABLE sms_accpet TO sms_accpet',DATE_FORMAT(NOW(),'%Y%m'));	
	PREPARE pre_tname from @tname;
	EXECUTE pre_tname;

	set @newtable = CONCAT('create table sms_accpet select * from sms_accpet_template where 1=2');
	PREPARE pre_newtable from @newtable;
	EXECUTE pre_newtable;
	
	alter table sms_accpet add primary key (id);
	alter table sms_accpet modify column id int unsigned not null auto_increment;
END


定時器例項

CREATE EVENT EVENT_SMS    
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO CALL backUpSms();

參考站點：

儲存過程：

http://blog.csdn.net/youngqj/article/details/6936632
http://blog.csdn.net/sun886/article/details/7992935

定時器：

http://www.cnblogs.com/gaizai/archive/2012/12/24/2831315.html

http://lobert.iteye.com/blog/1953827


1.複製表結構及資料到新表

CREATE TABLE 新表
SELECT * FROM 舊錶

2.僅僅複製表結構到新表

CREATE TABLE 新表
SELECT * FROM 舊錶 WHERE 1=2
即:讓WHERE條件不成立.
方法二:(低版本號的mysql不支援，mysql4.0.25 不支援，mysql5已經支援了)
CREATE TABLE 新表
LIKE 舊錶

3.複製舊錶的資料到新表(如果兩個表結構一樣)

INSERT INTO 新表
SELECT * FROM 舊錶

4.複製舊錶的資料到新表(如果兩個表結構不一樣)

INSERT INTO 新表(欄位1,欄位2,…….)
SELECT 欄位1,欄位2,…… FROM 舊錶

例子抽選 

每天凌晨三點執行  
create event event_call_defer     
on schedule every 1 day starts date_add(date(curdate() + 1),interval 3 hour)  
on completion preserve enable  
do  
begin  
    call test.warn();  
end  
  
每一個月的一號凌晨1 點執行  
CREATE EVENT EVENT2    
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO  
 BEGIN  
     CALL STAT();  
 END  
   
每一個季度一號的凌晨2點執行  
CREATE EVENT TOTAL_SEASON_EVENT  
ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2  
HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO  
 BEGIN  
     CALL SEASON_STAT();  
 END  
   
  
每年1月1號凌晨四點執行  
CREATE EVENT TOTAL_YEAR_EVENT  
ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO  
 BEGIN  
     CALL YEAR_STAT();  
 END




相關文章