mysql - 在 phpMyAdmin 中创建事件时启动事务导致错误
问题描述
无论出于何种原因,phpMyAdmin 都不允许我使用 Start 事务创建事件。这就是我正在使用的:
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
START TRANSACTION、INSERT、DELETE、UPDATE 和 COMMIT 在事件之外运行时都会起作用。这是我不断收到的错误:
#1064 - 您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以在第 8 行的 '' 附近使用正确的语法
希望你能提供帮助。谢谢你。
解决方案
您需要将 DELIMITER 定义为$$
除 ( ;
) 之外的其他内容(例如:),并在最后将其重新定义回 ( ;
)。
基本上,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 ;
推荐阅读
- python - 使用不区分大小写的字符串访问python字典
- mysql - 如何使用 Ansible 为 MySQL/MariaDB root 用户设置和保存随机密码?
- c# - 突出显示 textbox-winform c# 中的错误行
- angular - Angular 4+ Dependency Injection - Getting the closest host component out of more possibilities
- mysql - mysql join the same table multiple times with group by
- php - SilverStripe 4 自定义错误页面未显示
- node.js - Cannot destructure propery error - googleapi
- regex - 正则表达式:从双引号中提取值
- magento - Magento 1.9:观察者不工作。有什么方法可以测试观察者是否工作?
- angular - Angular/Ionic 模块中对 ModalController 的依赖导致服务“无法解析所有参数”