mysql - 如果在一个EVENT中查询失败，那么这个EVENT是否失败了？

Question

假设如果 EVENT 中的查询未返回 OK，EVENT 将立即失败，这是否安全？

DELETE 是破坏性的，所以我想确保在(相同的记录)被 DELETEd 之前之前的查询成功。

use argus_dnsdb;
DELIMITER |
CREATE EVENT `dnsdb_rotator`
ON SCHEDULE
EVERY 1 DAY
STARTS date_format(now(), '%Y-%m-%d 00:00:05')
ON COMPLETION NOT PRESERVE
ENABLE
DO BEGIN
set @target_table_name=CONCAT('`argus_dnsdb`.`',date_format(date_sub(now(),interval 1 day), '%Y%m%d'),'`');
set @create_table_stmt_str = CONCAT('CREATE TABLE ',@target_table_name,' like `argus_dnsdb`.`main`;');
PREPARE create_table_stmt FROM @create_table_stmt_str;
EXECUTE create_table_stmt;
DEALLOCATE PREPARE create_table_stmt;
set @a=unix_timestamp(date_format(now(), '%Y-%m-%d 00:00:00'));
set @insert_stmt_str = CONCAT('INSERT INTO ',@target_table_name,' SELECT * FROM `argus_dnsdb`.`main` WHERE qtime < ',@a,' ;');
PREPARE insert_stmt FROM @insert_stmt_str;
EXECUTE insert_stmt;
DEALLOCATE PREPARE insert_stmt;
DELETE FROM `argus_dnsdb`.`main` WHERE qtime < @a ;
END;
|
DELIMITER ;

在这种情况下，如果 EXECUTE insert_stmt; 失败，EVENT 是否会停止执行 DO block ？

Answer 1

这取决于错误的类型。 As stated in the manual :

If [an error] condition occurs for which no handler has been declared, the action taken depends on the condition class:

For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler [i.e. the program returns]. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.

For SQLWARNING conditions, the program continues executing, as if there were a CONTINUE handler.

For NOT FOUND conditions, if the condition was raised normally, the action is CONTINUE. If it was raised by SIGNAL or RESIGNAL, the action is EXIT.

插入失败会引发 SQLEXCEPTION，因此程序终止(或触发自定义错误处理程序，如果存在此类处理程序)。

(SQLEXCEPTION、SQLWARNING 和 NOT FOUND 的定义在同一手册页中提供)