MySQL定义异常和异常处理方法

<blockquote>在存储过程或函数中，一定要主要异常处理的位置。通常放在DECLARE的最后，若存储过程有返回值，可通过类似如下方式解决：
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN.....SET @info='ERROR';END;</blockquote>在MySQL中，特定异常需要特定处理。这些异常可以联系到错误，以及子程序中的一般流程控制。定义异常是事先定义程序执行过程中遇到的问题，异常处理定义了在遇到问题时对应当采取的处理方式，并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。<h1>1 异常定义</h1>1.1 语法DECLARE condition_name CONDITION FOR [condition_type];1.2 说明condition_name参数表示异常的名称；condition_type参数表示条件的类型，condition_type由SQLSTATE [VALUE] sqlstate_value|<a>mysql</a>_error_code组成：sqlstate_value和mysql_error_code都可以表示MySQL的错误；sqlstate_value为长度为5的字符串类型的错误代码；mysql_error_code为数值类型错误代码；1.3 示例定义“ERROR 1148(42000)”错误，名称为command_not_allowed。可以有以下两种方法：//方法一：使用sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';//方法二：使用mysql_error_codeDECLARE command_not_allowed CONDITION FOR 1148;<h1>2 自定义异常处理</h1>2.1 异常处理语法DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement2.2 参数说明handler_type: CONTINUE|EXIT|UNDOhandler_type为错误处理方式，参数为3个值之一；CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误时马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL暂不支持回滚操作；condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_codecondition_value表示错误类型；SQLSTATE [VALUE] sqlstate_value为包含5个字符的字符串错误值；
condition_name表示DECLARE CONDITION定义的错误条件名称；SQLWARNING匹配所有以01开头的SQLSTATE错误代码；NOT FOUND匹配所有以02开头的SQLSTATE错误代码；SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；mysql_error_code匹配数值类型错误代码；2.3 异常捕获方法//方法一：捕获sqlstate_value异常//这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';//方法二：捕获mysql_error_code异常//这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息；DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';//方法三：先定义条件，然后捕获异常DECLARE no_such_table CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';//方法四：使用SQLWARNING捕获异常DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五：使用NOT FOUND捕获异常DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';//方法六：使用SQLEXCEPTION捕获异常DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';<h1>3 综合示例</h1>创建一个表，设置该表的主键，在不定义异常处理和定义异常处理情况下看执行到哪一步。show databases;use wms;create table location(location_id int primary key,location_name varchar(50));示例1：不定义异常情况下DELIMITER //CREATE PROCEDURE handlerInsertNoException()BEGIN /DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;/ SET @x=1; INSERT INTO location VALUES (1,'Beijing'); SET @x=2; INSERT INTO location VALUES (1,'Wuxi'); SET @x=3;END;//DELIMITER ;调用存储过程与结果：mysql> call handlerInsertNoException();ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> select @x;+------+| @x |+------+| 2 |+------+1 row in set (0.00 sec) mysql> select * from location;+-------------+---------------+| location_id | location_name |+-------------+---------------+| 1 | Beijing |+-------------+---------------+1 row in set (0.00 sec)注意：操作示例2前要清空表中数据，并退出重新登录，以免客户端变量@x影响，详细说明参见结论中的第一点。mysql> truncate table location;Query OK, 0 rows affected (0.04 sec)mysql> select * from location;Empty set (0.00 sec)mysql> exit;Bye david@Louis:~$ mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 53Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu) mysql> use wms;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> select * from location;Empty set (0.00 sec) mysql> select @x;+------+| @x |+------+| NULL |+------+1 row in set (0.00 sec) 示例2：定义异常处理情况下：DELIMITER //CREATE PROCEDURE handlerInsertWithException()BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1; SET @x=1; INSERT INTO location VALUES (1,'Beijing'); SET @x=2; INSERT INTO location VALUES (1,'Wuxi'); SET @x=3;END;//DELIMITER ;
调用存储过程与结果：mysql> CALL handlerInsertWithException();Query OK, 0 rows affected (0.09 sec) mysql> select @x;+------+| @x |+------+| 3 |+------+1 row in set (0.00 sec) mysql> select @x,@x2//
+——+——+
| @x | @x2 |
+——+——+
| 3 | 1 |
+——+——+
1 row in set (0.00 sec)
以上显示结果是@x=3，@x2=1，和上面分析的情况相同，说明程序运行无误。说明与结论：一、MySQL中，@var_name表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或者使用。当客户端退出时，该客户端连接的所有变量将自动释放。二、在示例1中，由于注释了异常的声明"",此时向表中插入相同主键，就会触发异常，并且采取默认(EXIT)路径；且查看此时的@x返回2,表示下面的INSERT语句并没有执行就退出了.三、定义了异常处理，此时遇到错误也会按照异常定义那样继续执行；但只有第一条数据被插入到表中，此时用户变量@x=3说明已经执行到了结尾；DECLARE CONDITION环境声明
 在进行错误处理的时候，可以使用SQLSTATE或指定一个错误代码，实际上，也可以给他们定义一个名字，然后在进行处理的时候使用定义的名字。比如看下面这个例子：
mysql> create procedure pro18()
 -> begin
 -> declareconstraint violation condition for sqlstate ’23000′;#注意constraint violation两侧的是“”，即Tab键上边，1键左边的按键，不是单引号，写成单引号会报错     -> declare exit handler forconstraint violationrollback;#注意constraint violation两侧的是“”
 -> start transaction;
 -> insert into t2(s1) values(1);
 -> insert into t2(s1) values(1);
 -> commit;
 -> end;//
Query OK, 0 rows affected (0.00 sec)首先给sqlstate ’23000′定义了一个名字constraint violation，在进行操作的时候，就直接可以用这个名字了。t2表是一个innodb表，所以对这个表的插入操作都会ROLLBACK回滚，在这里例子中，由于主键插入两个同样的值导致sqlstate 23000错误发生，导致回滚事件发生。sqlstate 23000是约束错误。
 下面调用这个存储过程并查看运行结果：
mysql> call pro18()//
Query OK, 0 rows affected (0.04 sec)mysql> select * from t2//
Empty set (0.01 sec)
 可以看到t2中没有插入任何记录，全部事务都回滚了。 下面再来介绍几个声明条件，首先看例子：
mysql> create procedure pro19()
 -> begin
 -> declare exit handler for not found begin end;
 -> declare exit handler for sqlexception begin end;
 -> declare exit handler for sqlwarning begin end;
 -> end;//
Query OK, 0 rows affected (0.00 sec)
这个例子展示了三个预条件声明：NOT FOUNT是找不到行，SQLEXCEPTION是错误，SQLWARNING是警告或注释；这三个条件声明是预声明，所以不需要声明条件就可以使用。但是如果使用:declare sqlexception condition….这种格式的话，就会报错。 错误代码
1011 HY000 Error on delete of ''%s'' (errn %d)
1021 HY000 Disk full (%s); waiting for someone to free some space . . .
1022 23000 Can''t write; duplicate key in table ''%s''
1027 HY000 ''%s'' is locked against change
1036 HY000 Table ''%s'' is read only
1048 23000 Column ''%s'' cannot be null
1062 23000 Duplicate entry ''%s'' for key %d
1099 HY000 Table ''%s'' was locked with a READ lock and can''t be updated
1100 HY000 Table ''%s'' was not locked with LOCK TABLES
1104 42000 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
1106 42000 Incorrect parameters to procedure ''%s''
1114 HY000 The table ''%s'' is full
1150 HY000 Delayed insert thread couldn''t get requested lock for table %s
1165 HY000 INSERT DELAYED can''t be used with table ''%s'' because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row
1263 22004 Column set to default value; NULL supplied to NOT NULL column ''%s'' at row %ld
1264 22003 Out of range value adjusted for column ''%s'' at row %ld
1265 1000 Data truncated for column ''%s'' at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
1325 24000 Cursor is already open
1326 24000 Cursor is not open
1328 HY000 Incorrect number of FETCH variables
1329 2000 No data to FETCH
1336 42000 USE is not allowed in a stored program
1337 42000 Variable or condition declaration after cursor or handler declaration
1338 42000 Cursor declaration after handler declaration
1339 20000 Case not found for CASE statement
1348 HY000 Column ''%s'' is not updatable
1357 HY000 Can''t drop a %s from within another stored routine
1358 HY000 GOTO is not allowed in a stored program handler
1362 HY000 Updating of %s row is not allowed in %s trigger
1363 HY000 There is no %s row in %s trigger 存储过程是<a>mysql</a>高级编程的一大特色，当然存储过程包括了很多的知识，包括错误处理,预定义等，下面让我们首先看一下错误处理的部分。定义错误：为错误定义一个名称，语法为：<pre>DECLARE error_name CONDITION FOR condition_value；</pre><pre>declare 定义一个变量</pre><pre>error_name:自定义的错误的名字</pre><pre>condition_value可以是两种情况：</pre><pre>第一：直接写错误号，如 1305；</pre><pre>
</pre>第二：写sqlstate错误号：如<pre>SQLSTATE '42000';</pre><pre>
</pre>错误处理语法为：<pre>DECLARE handler_type HANDLER FOR condition_value</pre><pre>begin</pre><pre>...</pre><pre>end;</pre><pre>handler_type: 处理的过程。</pre><pre> CONTINUE 继续执行未完成的存储过程，直至结束。（常用，默认）</pre><pre> | EXIT 出现错误即自动跳出所在的begin不再执行后面的语句。</pre><pre>condition_value: 处理的触发条件</pre><pre> SQLSTATE [VALUE] sqlstate_value 不用说了，就是上面提到的第二中方法，也是最常用的错误定义，自己去查错误列表吧。</pre><pre> | condition_name 我们刚刚定义的那个名字errorname就是用在这里的。</pre><pre> | SQLWARNING 代表所有以01开头的错误代码</pre><pre> | NOT FOUND 表所有以02开头的错误代码，当然也可以代表一个游标到达数据集的末尾。</pre><pre> | SQLEXCEPTION 代表除了SQLWARNING和NOT FOUND 的所有错误代码。</pre><pre> | mysql_error_code 错误编号，上面的第一种方法，不过同样可以在错误列表从中查到，是我比较常用的。</pre><pre>
</pre><pre>例子：</pre><pre>create procedure error_test()</pre><pre>begin</pre><pre>#定义错误，1305是调用了错误的存储过程</pre><pre>declare errname condition for 1305;</pre><pre>declare continue handler for errname</pre><pre>begin</pre><pre>select 'no that procedure' as error;</pre><pre>end;</pre><pre>call aaa();end;</pre><pre>备注：</pre><pre>如果需要查看更多的错误列表可以直接到MySQL安装路径下。</pre><pre>比如我的/usr/local/mysql/share/mysql/errmsg.txt</pre><pre>说明：SQLSTATE [VALUE] sqlstate_value这种格式是专门为ANSI SQL 和 ODBC以及其他的标准.</pre>并不是所有的MySQL ERROR CODE 都映射到SQLSTATE。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 218,451评论 6赞 506
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 93,172评论 3赞 394
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,782评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,709评论 1赞 294
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,733评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,578评论 1赞 305
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,320评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,241评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,686评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,878评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,992评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,715评论 5赞 346
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,336评论 3赞 330
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,912评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 33,040评论 1赞 270
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,173评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,947评论 2赞 355

MySQL定义异常和异常处理方法

推荐阅读更多精彩内容