MySQL数据库的异常处理

  • 来源: 天新网 作者: 若水   2008-05-10/17:06
  •      对于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(); #p#分页标题#e#
    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)

    评论 {{userinfo.comments}}

    {{money}}

    {{question.question}}

    A {{question.A}}
    B {{question.B}}
    C {{question.C}}
    D {{question.D}}
    提交

    驱动号 更多