MySQL异常处理浅析

mysql的异常处理分析如下：

标准格式

declare handler_type handler for condition_value[,...] statement
handler_type:
  continue
 | exit
 | undo --这个暂时不支持
condition_value:
  sqlstate [value] sqlstate_value
 | condition_name
 | sqlwarning
 | not found
 | sqlexception
 | mysql_error_code
condition_value细节

1、常用mysql error code 列表

http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
更多错误列表见mysql安装路径下
比如我的/usr/local/mysql/share/mysql/errmsg.txt
说明一下：sqlstate [value] sqlstate_value这种格式是专门为ansi sql 和 odbc以及其他的标准.
并不是所有的mysql error code 都映射到sqlstate。

2、如果你不想插error code的话，就用速记条件来代替

sqlwarning 代表所有以01开头的错误代码
not found 代表所有以02开头的错误代码，当然也可以代表一个游标到达数据集的末尾。
sqlexception 代表除了sqlwarning和not found 的所有错误代码

3、我们现在就用手册上的例子

create table t (s1 int,primary key (s1));
mysql> use t_girl
database changed
mysql> create table t (s1 int,primary key (s1));
query ok, 0 rows affected (0.00 sec)
mysql> 
mysql> 
mysql> delimiter ||
mysql> create procedure handlerdemo ()
  -> begin
  -> declare exit handler for sqlstate '23000' begin end; -- 遇到重复键值就退出
  -> set @x = 1;
  -> insert into t values (1);
  -> set @x = 2;
  -> insert into t values (1);
  -> set @x = 3;
  -> end||
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call handlerdemo();
query ok, 0 rows affected (0.00 sec)
mysql> select @x;
+------+
| @x |
+------+
| 2 | 
+------+
1 row in set (0.00 sec)
mysql> call handlerdemo();
query ok, 0 rows affected (0.00 sec)
mysql> select @x;
+------+
| @x |
+------+
| 1 | 
+------+
1 row in set (0.00 sec)
mysql> 

现在来看一下遇到错误继续的情况

mysql> truncate table t;
query ok, 0 rows affected (0.01 sec)
mysql> delimiter $$
mysql> drop procedure if exists `t_girl`.`handlerdemo`$$
query ok, 0 rows affected (0.00 sec)
mysql> create definer=`root`@`localhost` procedure `handlerdemo`()
  -> begin
  -> declare continue handler for sqlstate '23000' begin end;
  -> set @x = 1;
  -> insert into t values (1);
  -> set @x = 2;
  -> insert into t values (1);
  -> set @x = 3;
  -> end$$
query ok, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call handlerdemo();
query ok, 0 rows affected (0.00 sec)
mysql> select @x;
+------+
| @x |
+------+
| 3 | 
+------+
1 row in set (0.00 sec)
mysql> call handlerdemo();
query ok, 0 rows affected (0.00 sec)
mysql> select @x;
+------+
| @x |
+------+
| 3 | 
+------+
1 row in set (0.00 sec)
mysql> 

可以看到，始终执行到最后。
当然，上面的sqlstate '23000'可以替换为1062
我们来看一下警告。

mysql> alter table t add s2 int not null;
query ok, 0 rows affected (0.01 sec)
records: 0 duplicates: 0 warnings: 0

此列没有默认值，插入的时候会出现警告或者1364错误提示。

mysql> delimiter $$
mysql> drop procedure if exists `t_girl`.`handlerdemo`$$
query ok, 0 rows affected, 1 warning (0.00 sec)
mysql> create definer=`root`@`localhost` procedure `handlerdemo`()
  -> begin
  -> declare continue handler for 1062 begin end;
  -> declare continue handler for sqlwarning
  -> begin
  -> update t set s2 = 2;
  -> end;
  -> declare continue handler for 1364
  -> begin
  -> insert into t(s1,s2) values (1,3);
  -> end; 
  -> set @x = 1;
  -> insert into t(s1) values (1);
  -> set @x = 2;
  -> insert into t(s1) values (1);
  -> set @x = 3;
  -> end$$
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call handlerdemo();
query ok, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+----+
| s1 | s2 |
+----+----+
| 1 | 3 | 
+----+----+
1 row in set (0.00 sec)

遇到错误的时候插入的新记录。

mysql> select @x;
+------+
| @x |
+------+
| 3 | 
+------+
1 row in set (0.00 sec)