不管出于什么原因,phpmyadmin都不允许我用start transaction创建事件。我用的是:
CREATE EVENT `set_history`
ON SCHEDULE
EVERY 1 DAY STARTS '2018-9-29 00:00:00'
ON COMPLETION PRESERVE
DISABLE ON SLAVE
DO BEGIN
START TRANSACTION;
INSERT INTO historical_transactions SELECT * FROM transactions WHERE domain IN (SELECT domain FROM domain JOIN accounts ON domain.accessAbility_ID = accounts.accessAbility_ID WHERE accounts.`renew_at` = DATE(NOW()));
DELETE FROM `transactions` WHERE domain IN (SELECT domain FROM domain JOIN accounts ON domain.accessAbility_ID = accounts.accessAbility_ID WHERE accounts.`renew_at` = DATE(NOW()));
UPDATE `accounts` SET `renew_at`= DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH) WHERE `renew_at` = DATE(NOW());
COMMIT;
END
在事件外部运行时,启动事务、插入、删除、更新和提交所有工作。这是我一直得到的错误:
1064-您的sql语法有错误;在第8行的“”附近,检查与mysql服务器版本对应的手册,以获取要使用的正确语法
希望你能帮忙。谢谢您。
1条答案
按热度按时间cwxwcias1#
您需要将分隔符定义为其他内容(例如:
$$
)除(;
)重新定义为(;
)最后。基本上,phpmyadmin解析器将解释
;
作为执行触发器,它将尝试触发查询,而不是将其作为一个整体Create
活动声明。因此,重新定义分隔符有助于绕过执行。执行以下操作: