MySQL异常处理浅析
MySQL的异常处理分析如下:
标准格式
DECLAREhandler_typeHANDLERFORcondition_value[,...]statement handler_type: CONTINUE |EXIT |UNDO--这个暂时不支持 condition_value: SQLSTATE[VALUE]sqlstate_value |condition_name |SQLWARNING |NOTFOUND |SQLEXCEPTION |mysql_error_code condition_value细节
1、常用MYSQLERRORCODE列表
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这种格式是专门为ANSISQL和ODBC以及其他的标准.
并不是所有的MySQLERRORCODE都映射到SQLSTATE。
2、如果你不想插ERRORCODE的话,就用速记条件来代替
SQLWARNING代表所有以01开头的错误代码
NOTFOUND代表所有以02开头的错误代码,当然也可以代表一个游标到达数据集的末尾。
SQLEXCEPTION代表除了SQLWARNING和NOTFOUND的所有错误代码
3、我们现在就用手册上的例子
CREATETABLEt(s1int,primarykey(s1)); mysql>uset_girl Databasechanged mysql>CREATETABLEt(s1int,primarykey(s1)); QueryOK,0rowsaffected(0.00sec) mysql> mysql> mysql>DELIMITER|| mysql>CREATEPROCEDUREhandlerdemo() ->BEGIN ->DECLAREEXITHANDLERFORSQLSTATE'23000'BEGINEND;--遇到重复键值就退出 ->SET@x=1; ->INSERTINTOtVALUES(1); ->SET@x=2; ->INSERTINTOtVALUES(1); ->SET@x=3; ->END|| QueryOK,0rowsaffected(0.00sec) mysql>DELIMITER; mysql>callhandlerdemo(); QueryOK,0rowsaffected(0.00sec) mysql>select@x; +------+ |@x| +------+ |2| +------+ 1rowinset(0.00sec) mysql>callhandlerdemo(); QueryOK,0rowsaffected(0.00sec) mysql>select@x; +------+ |@x| +------+ |1| +------+ 1rowinset(0.00sec) mysql>
现在来看一下遇到错误继续的情况
mysql>truncatetablet; QueryOK,0rowsaffected(0.01sec) mysql>DELIMITER$$ mysql>DROPPROCEDUREIFEXISTS`t_girl`.`handlerdemo`$$ QueryOK,0rowsaffected(0.00sec) mysql>CREATEDEFINER=`root`@`localhost`PROCEDURE`handlerdemo`() ->BEGIN ->DECLARECONTINUEHANDLERFORSQLSTATE'23000'BEGINEND; ->SET@x=1; ->INSERTINTOtVALUES(1); ->SET@x=2; ->INSERTINTOtVALUES(1); ->SET@x=3; ->END$$ QueryOK,0rowsaffected(0.01sec) mysql>DELIMITER; mysql>callhandlerdemo(); QueryOK,0rowsaffected(0.00sec) mysql>select@x; +------+ |@x| +------+ |3| +------+ 1rowinset(0.00sec) mysql>callhandlerdemo(); QueryOK,0rowsaffected(0.00sec) mysql>select@x; +------+ |@x| +------+ |3| +------+ 1rowinset(0.00sec) mysql>
可以看到,始终执行到最后。
当然,上面的SQLSTATE'23000'可以替换为1062
我们来看一下警告。
mysql>altertabletadds2intnotnull; QueryOK,0rowsaffected(0.01sec) Records:0Duplicates:0Warnings:0
此列没有默认值,插入的时候会出现警告或者1364错误提示。
mysql>DELIMITER$$ mysql>DROPPROCEDUREIFEXISTS`t_girl`.`handlerdemo`$$ QueryOK,0rowsaffected,1warning(0.00sec) mysql>CREATEDEFINER=`root`@`localhost`PROCEDURE`handlerdemo`() ->BEGIN ->DECLARECONTINUEHANDLERFOR1062BEGINEND; ->DECLARECONTINUEHANDLERFORSQLWARNING ->BEGIN ->updatetsets2=2; ->END; ->DECLARECONTINUEHANDLERFOR1364 ->BEGIN ->INSERTINTOt(s1,s2)VALUES(1,3); ->END; ->SET@x=1; ->INSERTINTOt(s1)VALUES(1); ->SET@x=2; ->INSERTINTOt(s1)VALUES(1); ->SET@x=3; ->END$$ QueryOK,0rowsaffected(0.00sec) mysql>DELIMITER; mysql>callhandlerdemo(); QueryOK,0rowsaffected(0.00sec) mysql>select*fromt; +----+----+ |s1|s2| +----+----+ |1|3| +----+----+ 1rowinset(0.00sec)
遇到错误的时候插入的新记录。
mysql>select@x; +------+ |@x| +------+ |3| +------+ 1rowinset(0.00sec)