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

帶你輕松接觸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)
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有