Home > Database > Mysql Tutorial > MySQL异常处理浅析_MySQL

MySQL异常处理浅析_MySQL

WBOY
Release: 2016-06-01 13:07:31
Original
979 people have browsed it

MySQL的异常处理分析如下:

标准格式

DECLARE handler_type HANDLER FOR condition_value[,...] statementhandler_type:  CONTINUE | EXIT | UNDO --这个暂时不支持condition_value:  SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_codecondition_value细节
Copy after login

1、常用MYSQL ERROR CODE 列表

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这种格式是专门为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_girlDatabase changedmysql> 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> 
Copy after login

现在来看一下遇到错误继续的情况

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> 
Copy after login

可以看到,始终执行到最后。
当然,上面的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
Copy after login

此列没有默认值,插入的时候会出现警告或者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)
Copy after login

遇到错误的时候插入的新记录。

mysql> select @x;+------+| @x |+------+| 3 | +------+1 row in set (0.00 sec)
Copy after login
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