无法处理MYSQL函数中的异常 [英] Unable to handle exception in MYSQL Function

问题描述

我对MYSQL函数&不太熟悉。异常处理。在完成所有研究之后，我可以在下面进行介绍，但没有收获。

I am not very familiar with MYSQL functions & exception handling. After all the research I could come up with below, but to no gain.

如果插入语句执行失败，我将尝试返回0，否则返回1。异常被引发而不是被处理。我在哪里错了？

I am trying to return 0 if insert statement execution fails and 1 otherwise. Exception is getting raised instead of being handled. Where am i going wrong?

    CREATE DEFINER=`myusr`@`localhost` FUNCTION `func1`(p1 varchar(50), p2 varchar(6)) RETURNS int(1)
        READS SQL DATA
        DETERMINISTIC
    BEGIN
        DECLARE EXP DATETIME;
        DECLARE RINT INT(1);
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
           RETURN  0;
        END;
        SET exp = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
        INSERT INTO `mydb`.`my_tbl`
            (`C1`,
            `C2`,
            `C3`)
            VALUES
            (p1, p2, exp);
        SET RINT = 1;
        RETURN RINT;
    END

表-my_tbl

成功案例-func1（'ABC '，'123456'）

例外情况-func1（'ABC'，'123456789'）

编辑-添加了屏幕截图

推荐答案

看来mysql 8.0.19无法捕获所有错误并正确处理它们。

It seems that mysql 8.0.19 can't catch all errors and handle them properly.

错误代码：1406。 o长到第1行的列'p2'

Error Code: 1406. Data too long for column 'p2' at row 1

错误代码：1049。未知数据库'mydb'

Error Code: 1049. Unknown database 'mydb'

这是我测试过的两个示例，但均不起作用，其他示例也是如此，我认为这对于mysql论坛更适用。

Are two examples that i tested and didn't work, others do so i think this is more a case for the mysql Forum.

DELIMITER $$
CREATE DEFINER=`mydb`@`localhost` FUNCTION `func1`(p1 varchar(50), p2 varchar(6)) RETURNS int
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE EXP DATETIME;
    DECLARE RINT INT(1);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
    BEGIn
       RETURN  0;
    END;
    SET exp = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
    INSERT INTO `mydb`.`func1`
        (`C1`,
        `C2`,
        `C3`)
        VALUES
        (p1, p2, exp);
    SET RINT = 1;
    RETURN RINT;
END;
DE§LIMITER ;

此方案有效。我为引用变量p2增加了大小以适合输入的数据，并为错误1265添加了退出处理程序，该错误是您尝试插入长文本时遇到的错误。

This Scenario works. I increased for the referenced variable p2 the size to fit the data entered, and i added a exit handler for the error 1265 , which is the error you get, when you try to insert the long text.

错误代码：1265。数据在第1行的列'c2'被截断

Error Code: 1265. Data truncated for column 'c2' at row 1

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `func1`(p1 varchar(50), p2 varchar(20)) RETURNS int
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE EXP DATETIME;
    DECLARE RINT INT(1);
    BEGIN
    DECLARE EXIT HANDLER FOR 1265 RETURN 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN 0;


    SET exp = DATE_ADD(NOW(), INTERVAL 15 MINUTE);
    INSERT INTO `mydb`.`my_tbl`
        (`C1`,
        `C2`,
        `C3`)
        VALUES
        (p1, p2, exp);
        SET RINT = 1;
        RETURN RINT;
    END;    
END$$
DELIMITER ;

您收到的错误，甚至在添加处理程序之前都会传递错误消息

The error you got, delivers the error message before even adding a HANDLER

这篇关于无法处理MYSQL函数中的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！