在事件中创建处理程序时获取MySQL错误

Question

我正试图在MySQL 5.6中创建一个事件，每当我试图声明一些东西时，就会收到一个错误，比如参数或处理程序：

SQL错误[1064][42000]：您的SQL语法有错误；查看与您的MySQL服务器版本对应的手册，了解在'DECLARE CONTINUE HANDLER for SQLEXCEPTION SET@rollMeBack=TRUE附近使用的正确语法；

我是SQL Server的一员，语法让我很头疼。我试着像这个问题中那样声明一个变量，但在那一行我仍然得到了错误。帮帮我欧比-万·克诺比：你是我唯一的希望。

完整(模糊(代码：

DELIMITER ||
CREATE EVENT my_event
ON SCHEDULE EVERY 5 MINUTE
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Fixes status'
DO IF EXISTS (
SELECT NULL FROM [...problem records..]
) THEN
-- =================================================================================================
-- Get a complete list of bad records:
SET @rollMeBack = FALSE ;
SET @LogId = 0;
CREATE TEMPORARY TABLE updateable
SELECT DISTINCT e.id , (@LogId:=@LogId + 1) + (SELECT MAX(dsd.id) FROM LogTable dsd ) AS LogId
FROM Table1 e 
INNER JOIN Table2 r
ON r.e_id = e.id 
/*Waiting 5 minutes to not interfere with current operations:*/
AND r.rDateTime <= CURRENT_TIMESTAMP() - INTERVAL 5 MINUTE
WHERE e.Status = 'Sent'
AND NOT EXISTS(SELECT NULL FROM Table2 r2 WHERE r2.e_id = e.id and r2.rDateTime is null)
LIMIT 100 ;

-- =================================================================================================
-- =================================================================================================
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET @rollMeBack = TRUE;
START TRANSACTION
-- =================================================================================================
UPDATE Table1 as e1 
INNER JOIN updateable AS u
ON e1.d=id = u.id
SET e1.Status = 'Completed',
e1.cDateTime = COALESCE (e1.cDateTime, (select max(rDateTime) from Table2 r5 where r5.e_id = e1.id));
-- =================================================================================================
-- Log updates:
INSERT INTO Debug (ID, Message)
SELECT u.LogId,
'Record set to `Completed`'
FROM updateable AS u;
-- =================================================================================================
-- Cleanup:
DROP TABLE updateable;
IF @rollMeBack = TRUE THEN
ROLLBACK;
ELSE
COMMIT;
END IF
-- =================================================================================================
-- =================================================================================================
END IF; ||

分隔符；

Answer 1

一些修改使其可操作。

首先，必须在正确的位置声明处理程序。来自文档：

处理程序声明必须出现在变量或条件之后声明。

第二，DO IF EXISTS...END IF;应该包含一个BEGIN...END块，然后进行处理程序声明：

...
COMMENT 'Fixes status'
DO IF EXISTS (
SELECT NULL FROM [...problem records..]
) THEN
BEGIN -- Note the added begin
-- Moved the handler declaration to be the first statement of this block
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET @rollMeBack = TRUE;
-- =================================================================================================
-- Get a complete list of bad records:
SET @rollMeBack = FALSE ;
...

代码末尾：

...
-- =================================================================================================
END; -- Note the added END
END IF; ||
DELIMITER ;

顺便说一句，IF @rollMeBack = TRUE THEN...END IF块中也存在语法错误——END IF末尾缺少分号。