首先创建一个过程
CREATE DEFINER = 'root'@'localhost' PROCEDURE `test_proc`( IN create_time VARCHAR(19) ) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN declare v_sql varchar(5000); declare v_cdate varchar(6); declare v_alertsql varchar(500); declare v_count integer; set v_cdate = create_time; /* set v_sql=concat('create table if not exists internet_records_',v_cdate,'(id int(11) NOT NULL AUTO_INCREMENT,website_name varchar(512) DEFAULT NULL,host varchar(512) DEFAULT NULL,website_url varchar(1024) DEFAULT NULL,create_date datetime DEFAULT NULL ,net_date datetime DEFAULT NULL,website_type tinyint(4) NOT NULL DEFAULT 0,client_num varchar(125) DEFAULT NULL,PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;'); */ set v_sql=concat('insert into log (totalcount,subcount,time) values(0,0,',v_cdate,');'); set @v_sql=v_sql; prepare stmt from @v_sql; EXECUTE stmt; deallocate prepare stmt; END;
然后创建一个event
CREATE EVENT `event_save_minute` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-08-19 11:14:36' ON COMPLETION NOT PRESERVE ENABLE COMMENT '' DO BEGIN call test_proc(date_format(now(),'%m-%d-%Y')); END;
如果没有启动的话,那么输入
show variables like '%sche%'
设置事件启动。同事保证函数输入的参数类型一致。保证开始时间>当前时间。