mysql-定時調用預存程序，mysql-預存程序

mysql-定時調用預存程序，mysql-預存程序

mysql定時調用預存程序，對錶資料集表結構進行備份

預存程序執行個體：

CREATE PROCEDURE backUpSms()BEGINDECLARE tname varchar(64);set @tname = CONCAT('sms_accpet',DATE_FORMAT(NOW(),'%Y%m'));set @rname = CONCAT('create table ',@tname,' select * from sms_accpet');PREPARE create_table from @rname;EXECUTE create_table;delete from sms_accpet;

定時器執行個體

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