Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to define and handle exceptions in MySQL. Many people may not know much about it. In order to let you know more, Xiaobian summarizes the following contents for you. I hope you can gain something according to this article.

1 Definition of exception

1.1 syntax

DECLARE condition_name CONDITION FOR [condition_type];

1.2 description

The condition_name parameter indicates the name of the exception;

condition_type parameter indicates the type of condition, condition_type is represented by SQLSTATE [VALUE] sqlstate_value| mysql_error_code consists of:

sqlstate_value and mysql_error_code can both represent MySQL errors;

sqlstate_value is an error code of type string of length 5;

mysql_error_code is a numeric type error code;

1.3 example

Define ERROR 1148(42000) error with command_not_allowed. There are two ways:

Method 1: Use sqlstate_value

DECLARE command_not_allowed CONDITION FOR SQLSTATE ’42000′;

Method 2: Use 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 no handling when encountering errors and continue execution;

EXIT means to exit immediately when an error is encountered;

UNDO means to withdraw the previous operation after encountering an error. MySQL does not support rollback operation for the time being;

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 indicates the error condition name defined by DECLARE CONDITION;

SQLWARNING matches all SQLSTATE error codes that start with 01;

NOT FOUND matches all SQLSTATE error codes that start with 02;

SQLEXCEPTION matches all SQLSTATE error codes that are not caught by SQLWARNING or NOT FOUND;

mysql_error_code matches numeric type error code;

2.3 Anomaly catching method

Method 1: Catch sqlstate_value exception

This method captures the sqlstate_value value. If sqlstate_value is "42S02", continue operation and output "NO_SUCH_TABLE" message

DECLARE CONTINUE HANDLER FOR SQLSTATE ’42S02′ SET @info=’NO_SUCH_TABLE’;

Method 2: Catch mysql_error_code exception

This method captures the mysql_error_code value. If the mysql_error_code value is 1146, perform CONTINUE operation and output "NO_SUCH_TABLE" message;

DECLARE CONTINUE HANDLER FOR 1146 SET @info=’NO_SUCH_TABLE’;

Method 3: Define conditions first, then catch exceptions

DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=’NO_SUCH_TABLE’;

Method 4: Using SQLWARNING to catch exceptions

DECLARE EXIT HANDLER FOR SQLWARNING SET @info=’ERROR’;

Method 5: Use NOT FOUND to catch exceptions

DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’NO_SUCH_TABLE’;

Method 6: Using SQLEXCEPTION to catch exceptions

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’ERROR’;

3 Comprehensive examples

Create a table, set the primary key of the table, and see how far you can go without defining exception handling or defining exception handling.

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

Example 1: Without defining exceptions

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 ;

Calling stored procedures and results:

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)

Note: Before operating Example 2, clear the data in the table and log out again to avoid the influence of the client variable @x. For details, see *** in the conclusion.

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 53Server 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)

Example 2: Defining exception handling cases:

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 ;

Calling stored procedures and results:

mysql> CALL handlerInsertWithException(); Query OK, 0 rows affected (0.09 sec) mysql> select @x; +------+| @x |+------+| 3 |+------+1 row in set (0.00 sec)

Description and conclusion:

In MySQL,@var_name represents a user variable and is assigned a value using SET statements. User variables are related to connections. A client-defined variable cannot be seen or used by other clients. When a client exits, all variables connected to that client are automatically released.

Second, in Example 1, because the exception declaration "" is commented, inserting the same primary key into the table at this time will trigger the exception, and take the default (EXIT) path; and looking at the @x at this time returns 2, indicating that the following INSERT statement has not been executed and exits.

3. Exception handling is defined. At this time, errors will continue to be executed according to the exception definition; however, only *** pieces of data are inserted into the table. At this time, the user variable @x=3 indicates that the execution has reached the end;

After reading the above, do you have any further understanding of how to define and handle exceptions in MySQL? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.