start事务在phpmyadmin中创建事件时导致错误

busg9geu  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(382)

不管出于什么原因,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服务器版本对应的手册,以获取要使用的正确语法

希望你能帮忙。谢谢您。

cwxwcias

cwxwcias1#

您需要将分隔符定义为其他内容(例如: $$ )除( ; )重新定义为( ; )最后。
基本上,phpmyadmin解析器将解释 ; 作为执行触发器,它将尝试触发查询,而不是将其作为一个整体 Create 活动声明。因此,重新定义分隔符有助于绕过执行。
执行以下操作:

DELIMITER $$
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 $$
DELIMITER ;

相关问题