MySQL5的异常处理
Error Handling 异常处理 1. Sample Problem: Log Of Failures 问题样例:故障记录 当INSERT失败时,我希望能将其记录在日志文件中我们用来展示出错处理的问题样例是很普通的。我希望得到错误的记录。当INSERT失败时,我想在另一个文件中记下这些错误的信息
Error Handling 异常处理1. Sample Problem: Log Of Failures 问题样例:故障记录
当INSERT失败时,我希望能将其记录在日志文件中我们用来展示出错处理的问题样例是很普通的。我希望得到错误的记录。当INSERT失败时,我想在另一个文件中记下这些错误的信息,例如出错时间,出错原因等。我对插入特别感兴趣的原因是它将违反外键关联的约束
2. Sample Problem: Log Of Failures (2)
mysql> CREATE TABLE t2
s1 INT, PRIMARY KEY (s1))
engine=innodb;//
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
FOREIGN KEY (s1) REFERENCES t2 (s1))
engine=innodb;//
mysql> INSERT INTO t3 VALUES (5);//
...
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails(这里显示的是系统的出错信息)
我开始要创建一个主键表,以及一个外键表。我们使用的是InnoDB,因此外键关联检查是打开的。然后当我向外键表中插入非主键表中的值时,动作将会失败。当然这种条件下可以很快找到错误号1216。
3. Sample Problem: Log Of Failures
CREATE TABLE error_log (error_message
CHAR(80))//
下一步就是建立一个在做插入动作出错时存储错误的表。
4. Sample Problem: Log Of Errors
CREATE PROCEDURE p22 (parameter1 INT)
BEGIN
DECLARE EXIT HANDLER FOR 1216
INSERT INTO error_log VALUES
(CONCAT('Time: ',current_date,
'. Foreign Key Reference Failure For
Value = ',parameter1));
INSERT INTO t3 VALUES (parameter1);
END;//
上面就是我们的程序。这里的第一个语句DECLARE EXIT HANDLER是用来处理异常的。意思是如果错误1215发生了,这个程序将会在错误记录表中插入一行。EXIT意思是当动作成功提交后退出这个复合语句。
5. Sample Problem: Log Of Errors
CALL p22 (5) //
调用这个存储过程会失败,这很正常,因为5值并没有在主键表中出现。但是没有错误信息返回因为出错处理已经包含在过程中了。t3表中没有增加任何东西,但是error_log表中记录下了一些信息,这就告诉我们INSERT into table t3动作失败。
DECLARE HANDLER syntax 声明异常处理的语法
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement
上面就是错误处理的用法,也就是一段当程序出错后自动触发的代码。MySQL允许两种处理器,一种是EXIT处理,我们刚才所用的就是这种。另一种就是我们将要演示的,CONTINUE处理,它跟EXIT处理类似,不同在于它执行后,原主程序仍然继续运行,那么这个复合语句就没有出口了。
1. DECLARE CONTINUE HANDLER example CONTINUE处理例子
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
这是MySQL参考手册上的CONTINUE处理的例子,这个例子十分好,所以我把它拷贝到这里。通过这个例子我们可以看出CONTINUE处理是如何工作的。
2. DECLARE CONTINUE HANDLER声明CONTINUE异常处理
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
这次我将为SQLSTATE值定义一个处理程序。还记得前面我们使用的MySQL错误代码1216吗?事实上这里的23000SQLSTATE是更常用的,当外键约束出错或主键约束出错就被调用了。
3. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1; INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
这个存储过程的第一个执行的语句是"SET @x = 1"。
4. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); SET @x = 3;
END;//
运行后值1被插入到主键表中。
5. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2; INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
然后@x的值变为2。
6. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); SET @x = 3;
END;//
然后程序尝试再次往主键表中插入数值,但失败了,因为主键有唯一性限制。
7. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
由于插入失败,错误处理程序被触发,开始进行错误处理。下一个执行的语句是错误处理的语句,@x2被设为2。
8. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3; END;//
到这里并没有结束,因为这是CONTINUE异常处理。所以执行返回到失败的插入语句之后,继续执行将@x设定为3动作。
9. DECLARE CONTINUE HANDLER example
mysql> CALL p23()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x, @x2//
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)
运行过程后我们观察@x的值,很确定的可以知道是3,观察@x2的值,为1。从这里可以判断程序运行无误,完全按照我们的思路进行。大家可以花点时间去调整错误处理器,让检查放在语句段的首部,而不是放在可能出现错误的地方,虽然那样看起来程序很紊乱,跳来跳去的感觉。但是这样的代码很安全也很清楚。
1. DECLARE CONDITION
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
这是另外一个错误处理的例子,在前面的基础上修改的。事实上你可给SQLSTATE或者错误代码其他的名字,你就可以在处理中使用自己定义的名字了。下面看它是怎么实现的:我把表t2定义为InnoDB表,所以对这个表的插入操作都会ROLLBACK(回滚),ROLLBACK(回滚事务)也是恰好会发生的。因为对主键插入两个同样的值会导致SQLSTATE 23000错误发生,这里SQLSTATE 23000是约束错误。
2. DECLARE CONDITION声明条件
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
这个约束错误会导致ROLLBACK(回滚事务)和SQLSTATE 23000错误发生。
3. DECLARE CONDITION
mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)
mysql> SELECT * FROM t2//
Empty set (0.00 sec)
我们调用这个存储过程看结果是什么,从上面结果我们看到表t2没有插入任何记录。全部事务都回滚了。这正是我们想要的。
4. DECLARE CONDITION
mysql> CREATE PROCEDURE p9 ()
-> BEGIN
-> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
-> END;//
Query OK, 0 rows affected (0.00 sec)
这里是三个预声明的条件:NOT FOUND (找不到行), SQLEXCEPTION (错误),SQLWARNING (警告或注释)。因为它们是预声明的,因此不需要声明条件就可以使用。不过如果你去做这样的声明:"DECLARE SQLEXCEPTION CONDITION ...",你将会得到错误信息提示。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

1. First, we right-click the blank space of the taskbar and select the [Task Manager] option, or right-click the start logo, and then select the [Task Manager] option. 2. In the opened Task Manager interface, we click the [Services] tab on the far right. 3. In the opened [Service] tab, click the [Open Service] option below. 4. In the [Services] window that opens, right-click the [InternetConnectionSharing(ICS)] service, and then select the [Properties] option. 5. In the properties window that opens, change [Open with] to [Disabled], click [Apply] and then click [OK]. 6. Click the start logo, then click the shutdown button, select [Restart], and complete the computer restart.

Today I would like to introduce to you an article published by MIT last week, using GPT-3.5-turbo to solve the problem of time series anomaly detection, and initially verifying the effectiveness of LLM in time series anomaly detection. There is no finetune in the whole process, and GPT-3.5-turbo is used directly for anomaly detection. The core of this article is how to convert time series into input that can be recognized by GPT-3.5-turbo, and how to design prompts or pipelines to let LLM solve the anomaly detection task. Let me introduce this work to you in detail. Image paper title: Largelanguagemodelscanbezero-shotanomalydete

The abnormality in the pool is a side task in the game. Many players want to know how to complete the abnormality in the pool task. It is actually very simple. First, we must master the technique of shooting in the water before we can accept the task and investigate the source of the stench. Later, we discovered It turns out that there are a lot of corpses under the pool. Let’s take a look at this graphic guide for the unusual tasks in the pool in Rise of Ronin. Guide to unusual missions in the Ronin Rise Pool: 1. Talk to Iizuka and learn the technique of shooting in the water. 2. Go to the location in the picture below to receive the abnormal task in the pool. 3. Go to the mission location and talk to the NPC, and learn that there is a foul smell in the nearby pool. 4. Go to the pool to investigate. 5. Swim to the location in the picture below, dive underwater, and you will find a lot of corpses. 6. Use a camera to take pictures of the corpse. 7

Quickly learn how to open and process CSV format files. With the continuous development of data analysis and processing, CSV format has become one of the widely used file formats. A CSV file is a simple and easy-to-read text file with different data fields separated by commas. Whether in academic research, business analysis or data processing, we often encounter situations where we need to open and process CSV files. The following guide will show you how to quickly learn to open and process CSV format files. Step 1: Understand the CSV file format First,

In the process of PHP development, dealing with special characters is a common problem, especially in string processing, special characters are often escaped. Among them, converting special characters into single quotes is a relatively common requirement, because in PHP, single quotes are a common way to wrap strings. In this article, we will explain how to handle special character conversion single quotes in PHP and provide specific code examples. In PHP, special characters include but are not limited to single quotes ('), double quotes ("), backslash (), etc. In strings

Some users suddenly find that their sound card driver is abnormal when using the computer. If this happens, you can update the driver from the device manager or roll back the driver to see if the problem is solved successfully. How to solve sound card driver abnormality 1. Right-click "This PC" and select "Manage" 2. Click "Device Manager", click "Sound" 3. Right-click the driver and select "Properties" 4. Click "Driver" at the top, and then click below You can choose "Update or rollback"

Exception handling and unit testing are important practices to ensure the soundness of C++ code. Exceptions are handled through try-catch blocks, and when the code throws an exception, it jumps to the catch block. Unit testing isolates code testing to verify that exception handling works as expected under different circumstances. Practical case: The sumArray function calculates the sum of array elements and throws an exception to handle an empty input array. Unit testing verifies the expected behavior of a function under abnormal circumstances, such as throwing an std::invalid_argument exception when an array is empty. Conclusion: By leveraging exception handling and unit testing, we can handle exceptions, prevent code from crashing, and ensure that the code behaves as expected under abnormal conditions.

Practical tips for efficiently resolving large file read exceptions in Java require specific code examples. Overview: When processing large files, Java may face problems such as memory overflow and performance degradation. This article will introduce several practical techniques to effectively solve Java large file reading exceptions, and provide specific code examples. Background: When processing large files, we may need to read the file contents into memory for processing, such as searching, analyzing, extracting and other operations. However, when the file is large, the following problems are often encountered: Memory overflow: trying to copy the entire file at once
