如何在程序或触发器中创建mysql事件？ [英] How to create mysql event inside a procedure or trigger?
问题描述
我有mysql表结构：
pre>
CREATE TABLE IF NOT EXISTS`battles`（
`id` int（11）NOT NULL AUTO_INCREMENT，
`active` tinyint（1）NOT NULL ，
`created` datetime NOT NULL，
`modified` datetime NOT NULL，
`begindate` datetime NOT NULL，
`enddate` datetime NOT NULL，
PRIMARY KEY（`id`），
）ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;
每场战斗都有争议和结束。 Begindate是插入的datetime，通常enddate是三天后。
我想在战斗结束时创建一个停止战斗（设置活动= 0）的mysql事件。我希望这个事件是在战斗表中的插入触发器上创建的。
有很少的答案有一个相关的问题（其中）。
他们建议：
您应该可以使用触发器和事件调度程序：
- 在每次更新/插入时触发的表上创建一个触发器
- 此触发器创建在行的日期时间发生的计划事件，更新>你的第二个表
我试图创建这样一个查询，但没有成功。
DELIMITER |
DROP TRIGGER IF EXISTS battle_create_end |
CREATE TRIGGER battle_create_end打开后的战斗
为每个ROW BEGIN
创建事件如果不存在CONCAT（'battle_end _'，NEW.id）
在新的时间表.enddate
DO
更新战斗SET battles.active = 0 WHERE battles.id = NEW.id;
END |
DELIMITER;
我得到的错误是
1576 - 当身体存在时，EVENT DDL语句的递归被禁止
我尝试使用不同的分隔符
如果有人可以帮忙，请指教。
BR，
Ilko
对不起，兄弟，但从我一直在读什么，你提出的是不可能的。我不认为你可以创建一个触发器的事件。这是一个很大的障碍，因为它对我也是有用的。
当创建每个战斗的行时，创建事件会更容易。听到的是我从一个家伙发现的一些代码解释事件如何工作的例子。
// ...
//插入博客帖子与待处理状态，获取id分配发布
$ query =INSERT INTO blog_posts（id，title ，post_text，status）
VALUES（NULL，：title，：postText，'pending'）;
$ stm = $ db-> prepare（$ query）;
$ stm-> execute（array（：title=> $ title，：postText=> $ text））;
$ id = $ db-> lastInsertId（）;
//这是未来的帖子吗？
if（isset（$ _ POST [schedule]，$ _POST [time]））{
$ scheduleDate = strtotime（$ _ POST [time]）;
$ query =CREATE EVENT publish_：id
在FROM_UNIXTIME（：scheduleDate）上的日程表
DO
BEGIN
更新blog_posts SET status ='published 'WHERE id =：id;
END;
$ stm = $ db-> prepare（$ query）;
$ stm-> execute（array（：id=> $ id，：scheduleDate=> $ scheduleDate））;
}
//这不是将来的帖子，现在发布
else {
$ query =UPDATE blog_posts SET status ='published'WHERE id =：id;
$ stm = $ db-> prepare（$ query）;
$ stm-> execute（array（：id=> $ id））;
}
所以，当你向桌面添加战斗时，基本上创建了这个事件。 p>
recently i've been searching for a solution to the following situation:
I have mysql table with structure:
CREATE TABLE IF NOT EXISTS `battles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`active` tinyint(1) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`begindate` datetime NOT NULL,
`enddate` datetime NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
Every battle has begindate and enddate. Begindate is datetime of insert and usually enddate is three days later.
I would like to create a mysql event that stops the battle (sets active = 0) at the battle enddate. And i would like this event to be created on insert trigger in the battles table.
There is an related issue with very few answers (here).
They advise:
You should be able to do it using a trigger and the event scheduler:
- create a trigger on the table that is fired on every update / insert
- this trigger creates a scheduled event that occurs at the datetime of the row and updates >your second table
I've tried to create such a query but with no success.
DELIMITER |
DROP TRIGGER IF EXISTS battle_create_end|
CREATE TRIGGER battle_create_end AFTER INSERT ON battles
FOR EACH ROW BEGIN
CREATE EVENT IF NOT EXISTS CONCAT('battle_end_',NEW.id)
ON SCHEDULE AT NEW.enddate
DO
UPDATE battles SET battles.active = 0 WHERE battles.id = NEW.id;
END|
DELIMITER ;
The error i get is
1576 - Recursion of EVENT DDL statements is forbidden when body is present
I've tried with different delimiters in the for each row structure with no success either.
If someone can help, please advise.
BR, Ilko
Sorry brother but from what I have been reading what you are proposing is not possible. I don't think you can create an event with a trigger. Which is a bummer because it would be useful for me as well.
It would however be easier to create the event when the row for each battle is created. Hear is an example of some code I found from a guy explaining how events work.
<?php
// establish database connection and filter incoming data
// ...
// insert blog post with pending status, get id assigned to post
$query = "INSERT INTO blog_posts (id, title, post_text, status)
VALUES (NULL, :title, :postText, 'pending')";
$stm = $db->prepare($query);
$stm->execute(array(":title" => $title, ":postText" => $text));
$id = $db->lastInsertId();
// is this a future post?
if (isset($_POST["schedule"], $_POST["time"])) {
$scheduleDate = strtotime($_POST["time"]);
$query = "CREATE EVENT publish_:id
ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate)
DO
BEGIN
UPDATE blog_posts SET status = 'published' WHERE id = :id;
END";
$stm = $db->prepare($query);
$stm->execute(array(":id" => $id, ":scheduleDate" => $scheduleDate));
}
// this is not a future post, publish now
else {
$query = "UPDATE blog_posts SET status = 'published' WHERE id = :id";
$stm = $db->prepare($query);
$stm->execute(array(":id" => $id));
}
So basically create the event when you add a battle to the table.
这篇关于如何在程序或触发器中创建mysql事件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
