mysql 异常_mysql异常处理

你的位置:

问答吧

-> MySQL

-> 问题详情

mysql异常处理

mysql异常处理

mysql中有没有类似oracle中when others 的条件？

exception

when others then

****

在mysql中怎么处理

DECLARE exit HANDLER for SQLEXCEPTION

这么写对么？

作者: cyt2005

发布时间: 2007-07-03

http://www.itpub.net/showthread. ... 7960351#post7960351

作者: atgc

发布时间: 2007-07-03

谢谢楼上的回复，正需要此东西！

作者: hb_li_520

发布时间: 2007-07-04

多谢atgc！

作者: cyt2005

发布时间: 2007-07-04

20.2.10. 条件和处理程序

20.2.10.1. DECLARE条件

20.2.10.2. DECLARE处理程序

特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。

20.2.10.1. DECLARE条件

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在DECLARE HANDLER语句中。请参阅20.2.10.2节，“DECLARE处理程序”。

除了SQLSTATE值，也支持MySQL错误代码。

20.2.10.2. DECLARE处理程序

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:

CONTINUE

| EXIT

| UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

| mysql_error_code

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。

对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。

·         SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

·         NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

·         SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

除了SQLSTATE值，MySQL错误代码也不被支持。

例如:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()

-> BEGIN

->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

->   SET @x = 1;

->   INSERT INTO test.t VALUES (1);

->   SET @x = 2;

->   INSERT INTO test.t VALUES (1);

->   SET @x = 3;

-> END;

-> //

Query OK, 0 rows affected (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)

注意到，@x是3，这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 这一行不在，第二个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取默认(EXIT)路径，并且SELECT @x可能已经返回2。

作者: zhouwf0726

发布时间: 2007-07-04

17.2.8. Conditions and Handlers

Certain conditions may require specific handling. These conditions can relate to errors, as well as to general flow control inside a routine.

17.2.8.1. DECLARE Conditions

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

This statement specifies conditions that need specific handling. It associates a name with a specified error condition. The name can subsequently be used in a DECLARE HANDLER statement. See Section 17.2.8.2, “DECLARE Handlers”.

A condition_value can be an SQLSTATE value or a MySQL error code.

17.2.8.2. DECLARE Handlers

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

The DECLARE ... HANDLER statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed. statement can be a simple statement (for example, SET var_name = value), or it can be a compound statement written using BEGIN and END (see Section 17.2.5, “BEGIN ... END Compound Statement Syntax”).

For a CONTINUE handler, execution of the current routine continues after execution of the handler statement. For an EXIT handler, execution terminates for the BEGIN ... END compound statement in which the handler is declared. (This is true even if the condition occurs in an inner block.) The UNDO handler type statement is not yet supported.

If a condition occurs for which no handler has been declared, the default action is EXIT.

A condition_value can be any of the following values:

An SQLSTATE value or a MySQL error code.

A condition name previously specified with DECLARE ... CONDITION. See Section 17.2.8.1, “DECLARE Conditions”.

SQLWARNING is shorthand for all SQLSTATE codes that begin with 01.

NOT FOUND is shorthand for all SQLSTATE codes that begin with 02.

SQLEXCEPTION is shorthand for all SQLSTATE codes not caught by SQLWARNING or NOT FOUND.

Example:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()

-> BEGIN

->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

->   SET @x = 1;

->   INSERT INTO test.t VALUES (1);

->   SET @x = 2;

->   INSERT INTO test.t VALUES (1);

->   SET @x = 3;

-> END;

-> //

Query OK, 0 rows affected (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)

The example associates a handler with SQLSTATE 23000, which occurs for a duplicate-key error. Notice that @x is 3, which shows that MySQL executed to the end of the procedure. If the line DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; had not been present, MySQL would have taken the default path (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint, and SELECT @x would have returned 2.

If you want to ignore a condition, you can declare a CONTINUE handler for it and associate it with an empty block. For example:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

作者: zhouwf0726

发布时间: 2007-07-04

Appendix B. Error Codes and Messages

Table of Contents

B.1. Server Error Codes and Messages

B.2. Client Error Codes and Messages

This appendix lists the errors that may appear when you call MySQL from any host language. The first list displays server error messages. The second list displays client program messages.

B.1. Server Error Codes and Messages

MySQL programs have access to several types of error information when the server returns an error. For example, the mysql client program displays errors using the following format:

shell> SELECT * FROM no_such_table;

ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

The message displayed contains three types of information:

A numeric error value (1146). This number is MySQL-specific and is not portable to other database systems.

A five-character SQLSTATE value ('42S02'). The values are specified by ANSI SQL and ODBC and are more standardized. Not all MySQL error numbers are mapped to SQLSTATE error codes. The value 'HY000' (general error) is used for unmapped errors.

A string that provides a textual description of the error.

Server error information comes from the following source files. For details about the way that error information is defined, see the MySQL Internals manual, available at http://dev.mysql.com/doc/.

Error message information is listed in the share/errmsg.txt file. %d and %s represent numbers and strings, respectively, that are substituted into the Message values when they are displayed.

The Error values listed in share/errmsg.txt are used to generate the definitions in the include/mysqld_error.h and include/mysqld_ername.h MySQL source files.

The SQLSTATE values listed in share/errmsg.txt are used to generate the definitions in the include/sql_state.h MySQL source file.

Because updates are frequent, it is possible that those files will contain additional error information not listed here.

Error: 1000 SQLSTATE: HY000 (ER_HASHCHK)

.......................................................................

Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)

Message: Can't write; duplicate key in table '%s'

作者: zhouwf0726

发布时间: 2007-07-04

呵呵

多谢老大

问题解决了

作者: cyt2005

发布时间: 2007-07-04