Analysis of Mysql exception handling _mysql

MySQL's exception handling analysis is as follows:

Standard format

DECLARE Handler_type handler for condition_value[,...] statement
handler_type:
  CONTINUE
 | EXIT
 | UNDO-This temporarily does not support
condition_value:
  SQLSTATE [value] sqlstate_value
 | condition_name
 | SQLWarning
 | Not FOUND
 | SQLEXCEPTION
 | Mysql_error_code
condition_value Details

1. Common MySQL ERROR CODE list

Http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
More error lists see 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 want to plug the error code, the use of 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, we now use the example in the manual

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
  -> SET @x = 1 When a duplicate key value is encountered;
  -> 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)

Now look at the situation where the error continues

mysql> truncate TABLE t;
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER $$ mysql>
DROP PROCEDURE IF EXISTS ' t_girl '. ' Handlerde Mo ' $$
Query OK, 0 rows Affected (0.00 sec)
mysql> CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' ' Handlerdemo ' ()
  -> begin
  -> DECLARE CONTINUE HANDLER for SQLSTATE ' 23000 ' the 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> 

Can be seen, always executed to the last.
Of course, the above sqlstate ' 23000 ' can be replaced with 1062
Let's take a look at the warning.

Mysql> ALTER TABLE t add s2 int not null;
Query OK, 0 rows affected (0.01 sec)
records:0 duplicates:0 warnings:0

There is no default value for this column, and a warning or 1364 error message appears when you insert it.

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS ' t_girl '. ' Handlerdemo ' $$
Query OK, 0 rows affected, 1 WA Rning (0.00 sec)
mysql> CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' Handlerdemo ' ()
  -> BEGIN
  -> DECLARE CONTINUE HANDLER for 1062 the BEGIN end;
  -> DECLARE CONTINUE HANDLER for sqlwarning
  -> BEGIN
  -> update t set s2 = 2;
  -> end;
  -> DECLARE CONTINUE HANDLER for 1364
  -> BEGIN
  -> inserts 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)

New record inserted when an error is encountered.

 mysql> select @x; +------+ | @x | +------+ | 3 | +------+ 1 row in Set (0.00 sec)