Mysql入门MySQL异常处理浅析

概述介绍《Mysql入门MySQL异常处理浅析》开发教程，希望对您有用。

《MysqL入门MysqL异常处理浅析》要点：
本文介绍了MysqL入门MysqL异常处理浅析，希望对您有用。如果有疑问，可以联系我们。

MysqL的异常处理分析如下：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 CODE 列表MysqL教程

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.MysqL教程

2、如果你不想插ERROR CODE的话,就用速记条件来代替MysqL教程

sqlWARNING 代表所有以01开头的错误代码
NOT FOUND 代表所有以02开头的错误代码,当然也可以代表一个游标到达数据集的末尾.
sqlEXCEPTION 代表除了sqlWARNING和NOT FOUND 的所有错误代码MysqL教程

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

CREATE table t (s1 int,primary key (s1));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 = 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教程

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教程

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教程

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> select * from t;+----+----+| s1 | s2 |+----+----+| 1 | 3 | +----+----+1 row in set (0.00 sec)

遇到错误的时候插入的新记录.MysqL教程

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

总结

以上是内存溢出为你收集整理的Mysql入门MySQL异常处理浅析全部内容，希望文章能够帮你解决Mysql入门MySQL异常处理浅析所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。