Mysql - 获取错误值

Question

我正在使用 mysql 5.7.23，我正在尝试在我的存储过程中记录错误我在获取特定错误以便将其写入日志表时遇到了一些困难。我想做这样的事情:

BEGIN
DECLARE v_error varchar(50);
DECLARE v_err_no varchar(50) ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
set v_err_no= mysql_errno ;
#set v_error= mysql_sqlstate();

call pcd_log ('ERROR', v_err_no ); #my custom procedure to log
end;


insert into temp values (1);#some stuff that might give errors

end

但这行不通，我怎样才能获取错误的值(数字或描述)并将其放入变量中？

谢谢

Answer 1

继续阅读 https://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

drop procedure if exists p;

delimiter $$
create procedure p()
BEGIN
DECLARE v_error varchar(50);
DECLARE v_err_no varchar(50) ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
    get diagnostics condition 1
     v_err_no = returned_sqlstate;

    #call pcd_log ('ERROR', v_err_no ); #my custom procedure to log

    select v_err_no;
end;


insert into t values ('zzz');#some stuff that might give errors

end $$

delimiter ;

call p();

+----------+
| v_err_no |
+----------+
| 22007    |
+----------+
1 row in set (0.00 sec)