《谈谈MySQL的异常处理》由会员分享，可在线阅读，更多相关《谈谈MySQL的异常处理（5页珍藏版）》请在金锄头文库上搜索。
1、谈谈MySQL的异常处理对于MySQL的异常处理，本人不常用。不过我觉得还是有写下来的必要。标准格式DECLARE handler_type HANDLER FOR condition_value,. statementhandler_type:CONTINUE| EXIT| UNDO -这个暂时不支持condition_value:SQLSTATE VALUE sqlstate_value| condition_name| SQLWARNING| NOT FOUND| SQLEXCEPTION| mysql_error_codecondition_value细节1、常用MYSQL ERROR
2、 CODE 列表http:/ 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
3、);mysql use t_girlDatabase changedmysql 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
4、 = 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;+-
5、+| 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=rootlocalhost PROCEDURE handlerdemo()- BEGIN- DECLARE C
6、ONTINUE 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 s
7、et (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:
8、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=rootlocalhost PROCEDURE handlerdemo()- BEGIN- DECLARE CONTINUE HANDLER FOR 1062 BEGIN END;- DECLARE CONTINUE HANDLER FOR SQLWARNING- BE
9、GIN- 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)4、如果有新的体会，我会再发出来共享。
