我现需要在存储过程中创建一个事件,该如何做呢?如下面的代码,是创建事务,在五天后清空表test的数据。。
创建时报错:
Recursion of EVENT DDL statements is forbidden when body is present-------------存储过程创建语句-------------
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`sp_create_event`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.test;
END$$DELIMITER ;
创建时报错:
Recursion of EVENT DDL statements is forbidden when body is present-------------存储过程创建语句-------------
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`sp_create_event`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.test;
END$$DELIMITER ;
好像目前的5.1版本不支持,在存储过程里面直接创建事件event的。
不过你要是有这样的业务,直接建事件event也是可以达到你的效果的。
而且事件event里面也可以调用存储过程的。
-- mysql建立定时任务
1 开启定时任务参数
mysql> SET GLOBAL event_scheduler = ON;2 建立定时任务 event
DELIMITER $$
use `test `$$create EVENT `insert_itemized_hist_fail_blocked` ON SCHEDULE EVERY 1 MINUTE STARTS '2010-09-27 17:12:00' ON COMPLETION NOT PRESERVE ENABLE
DO
BEGIN
call procedurename1(); --调用业务存储过程。
insert into test.t(n) select 1;
commit;
END$$
DELIMITER ;3 验证
select * from test.t;
BEGIN
SET @create_event_stmt='CREATE EVENT IF NOT EXISTS e_test_insert
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO vip_equity.aaa VALUES (CURRENT_TIMESTAMP)';
PREPARE stmt FROM @create_event_stmt;
EXECUTE stmt;
END$$DELIMITER ;但在调用存储过程的时候,mysql错误信息为:
This command is not supported in the prepared statement protocol yet
mysql难道就真的不支持在储过程中创建事件么?
CREATE EVENT IF NOT EXISTS e_test_insert
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO vip_equity.aaa VALUES (CURRENT_TIMESTAMP)
这一段单独运行是没有错的