Detailed explanation of MySQL exception definition and exception handling

In MySQL, specific exceptions require specific handling. These exceptions can be linked to errors, as well as general flow control within the subroutine. Defining exceptions is to define in advance the problems encountered during program execution. Exception handling defines the handling method that should be adopted when problems are encountered, and ensures that stored procedures or functions can continue to execute when encountering errors or warnings.

1 Exception definition

1.1 Syntax

DECLARE condition_name CONDITION FOR [condition_type];

1.2 Description

condition_name parameter represents the name of the exception;
condition_type parameter represents the type of condition, condition_type is determined by SQLSTATE [VALUE] sqlstate_value|mysql_error_code consists of:

sqlstate_value and mysql_error_code can both represent MySQL errors;
sqlstate_value is a string type error code with a length of 5;
mysql_error_code is a numeric type error code;

1.3 Example
Definition of "ERROR 1148(42000)" error, the name is command_not_allowed. There are two methods:

//方法一：使用sqlstate_value 
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000′; 
//方法二：使用mysql_error_code 
DECLARE command_not_allowed CONDITION FOR 1148;

2 Custom exception handling

2.1 Exception handling syntax

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

2.2 Parameter description

handler_type : CONTINUE|EXIT|UNDO

handler_type is the error handling method, and the parameter is one of 3 values;
CONTINUE means not to handle the error and continue execution;
EXIT means to exit immediately when an error is encountered;
UNDO means that it encounters an error After undoing the previous operation, MySQL does not support rollback operation yet;

condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

condition_value indicates the error type;
SQLSTATE [VALUE] sqlstate_value is a string error value containing 5 characters;
condition_name means DECLARE The error condition name defined by CONDITION;
SQLWARNING matches all SQLSTATE error codes starting with 01;
NOT FOUND matches all SQLSTATE error codes starting with 02;
SQLEXCEPTION matches all SQLSTATE error codes that are not SQLWARNING or NOT The SQLSTATE error code captured by Found;
mysql_error_code match the value type error code; See which step is executed without defining exception handling and defining exception handling.

/方法一：捕获sqlstate_value异常 
//这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为”42S02″，执行CONTINUE操作，并输出”NO_SUCH_TABLE”信息 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02′ SET @info='NO_SUCH_TABLE'; 
  
//方法二：捕获mysql_error_code异常 
//这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出”NO_SUCH_TABLE”信息； 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE'; 
 
//方法三：先定义条件，然后捕获异常 
DECLARE no_such_table CONDITION FOR 1146; 
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';  
  
//方法四：使用SQLWARNING捕获异常 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
  
//方法五：使用NOT FOUND捕获异常 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
  
//方法六：使用SQLEXCEPTION捕获异常 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

Example 1: Without defining exceptions

show databases;
use wms;
create table location
(
location_id int primary key,
location_name varchar(50)
);

Call the stored procedure and the result:

DELIMITER //
CREATE PROCEDURE handlerInsertNoException()
BEGIN
 /*DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;*/
 SET @x=1;
 INSERT INTO location VALUES (1,'Beijing');
 SET @x=2;
 INSERT INTO location VALUES (1,'Wuxi');
 SET @x=3;
END;
//
DELIMITER ;

Note: Before operating Example 2, you must clear the data in the table, log out and log in again to avoid client The influence of variable @x, please refer to the first point in the conclusion for details.

mysql> call handlerInsertNoException();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select @x;
+------+
| @x  |
+------+
|  2 |
+------+
1 row in set (0.00 sec)
  
mysql> select * from location;
+-------------+---------------+
| location_id | location_name |
+-------------+---------------+
|      1 | Beijing    |
+-------------+---------------+
1 row in set (0.00 sec)

Example 2: When defining exception handling:

mysql> truncate table location;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from location;
Empty set (0.00 sec)
mysql> exit;
Bye
  
david@Louis:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)
  
mysql> use wms;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
  
Database changed
mysql> select * from location;
Empty set (0.00 sec)
  
mysql> select @x;
+------+
| @x  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Calling stored procedures and results:

DELIMITER //
CREATE PROCEDURE handlerInsertWithException()
BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
 SET @x=1;
 INSERT INTO location VALUES (1,'Beijing');
 SET @x=2;
 INSERT INTO location VALUES (1,'Wuxi');
 SET @x=3;
END;
//
DELIMITER ;

Explanation and conclusion:

1. In MySQL, @var_name represents a user variable, and uses the SET statement for it Assignment, user variables are related to the connection. Variables defined by one client cannot be seen or used by other clients. When the client exits, all variables for that client connection are automatically released.

2. In Example 1, due to the annotated exception statement "", inserting the same primary key into the table will trigger an exception and take the default (EXIT) path; and check the @x at this time to return 2 , indicating that the following INSERT statement exited without being executed.

3. Exception handling is defined. If an error is encountered at this time, execution will continue according to the exception definition; but only the first piece of data is inserted into the table. This The user variable @x=3 indicates that the execution has reached the end;

The above is the detailed explanation of MySQL exception definition and exception handling. I hope it will be helpful to everyone's learning. For more related articles, please pay attention to the PHP Chinese website (www.php .cn)!