Home > Database > Mysql Tutorial > 带你轻松接触MySQL数据库的异常处理_MySQL

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

WBOY
Release: 2016-06-01 13:53:37
Original
863 people have browsed it

  对于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>

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template