今天同事跟我说, mysql存储过程中 发生异常了,怎么把异常的相关信息记录到另外一张表中,
他说网上找了 ,基本上没找到相关资料
通过看官方文档 发现, 其实还是能够处理的, 我们可以分别定义异常 然后在做相应的处理
1、 首先看官方文档上的说明
mysql> help declare handler
Name: 'DECLARE HANDLER'
Description:
Syntax:
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action:
CONTINUE --表示出现异常继续执行
| EXIT -- 表示出现异常退出执行
| UNDO -- 不支持
condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
The DECLARE ... HANDLER statement specifies a handler that deals with
one or more conditions. If one of these conditions occurs, the
specified statement executes. statement can be a simple statement such
as SET var_name = value, or a compound statement written using BEGIN
and END (see [HELP BEGIN END]).
下面是举例 主键冲突的 例子,如果这个主键冲突 那么就把冲突的主键给记录下来
create table test_table(
id int primary key,
name varchar(30),
password varchar(30)
);
create table record_repeat(
id int,
occ_time datetime default now()
);
delimiter //
create procedure test_conflict_data(inid int)
begin
declare t_id int;
declare continue handler for SQLEXCEPTION
begin
insert into record_repeat(id) values(t_id);
end;
set t_id=inid;
insert into test_table(id) values(inid);
end;
//
delimiter ;
下面测试数据
mysql> call test_conflict_data(234234);
Query OK, 1 row affected (0.05 sec)
mysql> call test_conflict_data(234234);
Query OK, 1 row affected (0.05 sec)
mysql> call test_conflict_data(234236);
Query OK, 1 row affected (0.05 sec)
mysql> call test_conflict_data(234237);
Query OK, 1 row affected (0.03 sec)
mysql> call test_conflict_data(234237);
Query OK, 1 row affected (0.05 sec)
mysql> select * from test_table;
+--------+------+----------+
| id | name | password |
+--------+------+----------+
| 234234 | NULL | NULL |
| 234236 | NULL | NULL |
| 234237 | NULL | NULL |
+--------+------+----------+
3 rows in set (0.00 sec)
mysql> select * from record_repeat;
+--------+---------------------+
| id | occ_time |
+--------+---------------------+
| 234234 | 2016-06-02 11:23:35 |
| 234237 | 2016-06-02 11:23:49 |
+--------+---------------------+
2 rows in set (0.00 sec)
mysql>
发生重复插入的数据 都 记录到 record_repeat 表中了