Analysis on MySQL Exception Handling

The exception handling Analysis of MySQL is as follows:

Standard Format

DECLARE handler_type handler for condition_value [,...] statementhandler_type: CONTINUE | EXIT | UNDO -- condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_codecondition_value

1. List of common mysql error codes

Http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
For more error lists, see the MySQL installation path.
For example, my/usr/local/mysql/share/mysql/errmsg.txt
Description: SQLSTATE [VALUE] sqlstate_value is specially used for ansi SQL, ODBC, and other standards.
Not all MySQL ERROR codes are mapped to SQLSTATE.

2. If you do not want to insert error code, replace it with a stenographer condition.

SQLWARNING indicates all error codes starting with 01.
Not found indicates all error codes starting with 02. Of course, a cursor can also be reached at the end of the dataset.
SQLEXCEPTION indicates all error codes except SQLWARNING and not found.

3. Now we will use the example in the manual.

Create table t (s1 int, primary key (s1); mysql> use t_girlDatabase changedmysql> create table t (s1 int, primary key (s1); Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER | mysql> create procedure handlerdemo ()-> BEGIN-> declare exit handler for sqlstate '100' begin end; -- in case of duplicate key VALUES, exit-> 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>

Now let's take a 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`.`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> 

As you can see, it is always executed until the end.
Of course, the above SQLSTATE '200' can be replaced with 23000
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

This column does not have a default value. A warning or 1364 error message is displayed during insertion.

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)

The new record inserted when an error occurs.

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