mysql 函数 异常处理_无法处理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.

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

TABLE - my_tbl

SUCCESS CASE - func1('ABC','123456')

EXCEPTION CASE - func1('ABC','123456789')

EDIT - Added screenshots

解决方案

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

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

Error Code: 1049. Unknown database 'mydb'

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 ;

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.

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