mysql-定时调用存储过程_MySQL

CREATE PROCEDURE backUpSms()
BEGIN
	DECLARE 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();

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