前往顾页
以后地位: 主页 > 收集编程 > Php实例教程 >

谈谈MySQL的异常措置

时候:2010-11-05 00:50来源:知行网www.zhixing123.cn 编辑:麦田守望者

对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 列表
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_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)
4、如果有新的体味,我会再发出来共享。

 


 

------分开线----------------------------
标签(Tag):MYSQL 数据库 MySQL教程 Mysql5
------分开线----------------------------
保举内容
猜你感兴趣