| 導購 | 订阅 | 在线投稿
分享
 
 
 

带你轻松接触MySQL数据库的异常处理

2008-06-01 03:23:18  編輯來源:互聯網  简体版  手機版  評論  字體: ||
 
 
  对于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 列表

  如果需要查看更多的错误列表可以直接到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)
 
 
 
对于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 列表 如果需要查看更多的错误列表可以直接到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)
󰈣󰈤
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号 wangchaonetcn
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味著赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
© 2005- 王朝網路 版權所有