[大数据]MySQL异常捕获简单示例

create table tbl1 (a int primary key);

create table error_log (
    id bigint(20) not null auto_increment,
    error_code varchar(50) default null,
    error_message varchar(500) default null,
    routine_name varchar(50) default null,
    create_time timestamp null default current_timestamp,
    primary key (id),
    key idx_routine_name (routine_name),
    key idx_create_time (create_time)
);

delimiter //
create procedure p_test (pa int)
begin
    declare exit handler for sqlexception
    begin
        get diagnostics condition 1
        @o_error_code = returned_sqlstate, @o_error_msg = message_text;
        insert into error_log (error_code, error_message, routine_name) values (@o_error_code, @o_error_msg, 'p_test');
    end;
    insert into tbl1 values(pa);
end;
//

delimiter ;
call p_test(1);
call p_test(1);
select * from tbl1;
select * from error_log;

调用结果：

?