Exception handling of MySQL database

For MySQL exception handling, I do not use. But I think there is a need to write it down.

Standard format

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 Details

1. MySQL ERROR CODE List

If you need to see more error lists, go directly to the MySQL installation path.

Like my/usr/local/mysql/share/mysql/errmsg.txt.

Description: SQLSTATE [VALUE] Sqlstate_value This format is specifically for ANSI SQL and ODBC and other standards.

Not all MySQL ERROR CODE is mapped to SQLState.

2, if you do not need to insert the error CODE, you can use shorthand conditions to replace

SQLWarning represents all error codes that start with 01

The not FOUND represents all error codes that start with 02, and of course it can represent a cursor to the end of the dataset.

SQLEXCEPTION represents all error codes except for sqlwarning and not FOUND.

3, specific examples:

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 ' the BEGIN end; --Exit if duplicate key values are encountered
-> 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>