MySQL中的存儲(chǔ)過(guò)程異常處理

更新時(shí)間：2022年09月26日 09:51:11 作者：浮華塵夢(mèng)

這篇文章主要介紹了MySQL中的存儲(chǔ)過(guò)程異常處理方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

1. condition

存儲(chǔ)過(guò)程中出現(xiàn)的錯(cuò)誤事件也就是異常都可以被稱為condition。

declare condition語(yǔ)法：

DECLARE condition_name CONDITION FOR condition_value 
condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value

declare condition語(yǔ)句的作用是給需要進(jìn)行處理的condition定義一個(gè)名稱，并提供給后續(xù)的declare handler進(jìn)行調(diào)用，從而使代碼清晰化。

例如：定義一個(gè)名稱為"no_such_table"的condition，并在declare handler中調(diào)用該名稱。

declare condition中可以使用error code(報(bào)錯(cuò)的代碼)值或是sqlstate(5位的字符串)值。

使用error code值定義condition：

DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;

使用sqlstate值定義condition

DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;

開(kāi)頭為’0’的error code或是開(kāi)頭為’00’的sqlstate值不能用于定義condition，因?yàn)樗鼈兇淼氖浅晒?，而不是異常?/p>

在SIGNAL或者是RESIGNAL中引用的condition，必須是使用sqlstate定義的condition，不能使用error code定義的condition。

存儲(chǔ)過(guò)程中的declare condition語(yǔ)句，必須出現(xiàn)在declare cursor或是declare handler之前，否則會(huì)報(bào)錯(cuò)。

2.handler

handler就是用來(lái)處理condition的，當(dāng)定義的condition發(fā)生時(shí)，就執(zhí)行handler中定義的處理邏輯，handler可以處理多個(gè)condition。

declare handler語(yǔ)法：

    DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
    handler_action:
    CONTINUE
    | EXIT
    | UNDO
    condition_value:
    mysql_error_code
    | SQLSTATE [VALUE] sqlstate_value
    | condition_name
    | SQLWARNING
    | NOT FOUND
    | SQLEXCEPTION

declare handler的語(yǔ)句必須在declare condition語(yǔ)句和定義變量語(yǔ)句之后出現(xiàn)

當(dāng)handler中定義的condition觸發(fā)時(shí)，可以采取以下三種處理方式：

1.CONTINUE：發(fā)送錯(cuò)誤時(shí)繼續(xù)執(zhí)行后續(xù)代碼
2.EXIT：發(fā)生錯(cuò)誤時(shí)退出該handler定義所在的代碼塊（可能是子代碼塊或者main代碼塊）
3.UNDO：回滾所有的操作，目前還不支持，所以只有continue和exit可用。

示例：

1.使用error code定義handler

? ? DECLARE CONTINUE HANDLER FOR 1051
? ? BEGIN
? ? -- body of handler
? ? END;

2.使用sqlstate值定義handler

? ? DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
? ? BEGIN
? ? -- body of handler
? ? END;

3.SQLWARNING代表01開(kāi)頭的sqlstate值

? ? DECLARE CONTINUE HANDLER FOR SQLWARNING
? ? BEGIN
? ? -- body of handler
? ? END;

4.NOT FOUND代表02開(kāi)頭的sqlstate值，這通常用于具有游標(biāo)的上下文關(guān)系中，用來(lái)處理游標(biāo)走到數(shù)據(jù)集終點(diǎn)時(shí)的condition。

? ? DECLARE CONTINUE HANDLER FOR NOT FOUND
? ? BEGIN
? ? -- body of handler
? ? END;

5.SQLEXCEPTION代表所有其他不是以00,01,02開(kāi)頭的sqlstate值

? ? DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
? ? BEGIN
? ? -- body of handler
? ? END;

注意1：

在存儲(chǔ)過(guò)程中，如果出現(xiàn)了一個(gè)condition，但是此時(shí)沒(méi)有定義相關(guān)的handler，那么處理該condition的方法取決于該condition的類型

SQLEXCEPTION類型的condition
默認(rèn)使用EXIT handler來(lái)進(jìn)行處理，如果此時(shí)該存儲(chǔ)過(guò)程被另外一個(gè)存儲(chǔ)過(guò)程調(diào)用，那么將使用調(diào)用者中定義的handler來(lái)進(jìn)行處理。
SQLWARNING類型的condition
默認(rèn)使用CONTINUE handler來(lái)進(jìn)行處理，存儲(chǔ)過(guò)程繼續(xù)執(zhí)行。
NOT FOUND類型的condition
如果condition被正常拋出，那么存儲(chǔ)過(guò)程正常執(zhí)行，也就是continue的處理方式，如果是被SIGNAL或RESIGNAL拋出，那么存儲(chǔ)過(guò)程終止運(yùn)行，也就是exit的處理方式

來(lái)看官網(wǎng)的一個(gè)SQLSTATE '23000’主鍵沖突的例子：

    mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    -> SET @x = 1;
    -> INSERT INTO test.t VALUES (1);
    -> SET @x = 2;
    -> INSERT INTO test.t VALUES (1);
    -> SET @x = 3;
    -> END;
    -> //
    Query OK, 0 rows affected (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)

可以看到存儲(chǔ)過(guò)程是正常執(zhí)行的。

如果希望被handler對(duì)捕獲到condition不進(jìn)行任何處理，那么可以這樣定義handler：

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

注意2：

標(biāo)簽的代碼范圍不包括declare handler的代碼范圍，所以在declare handler中不能使用iterate和leave語(yǔ)句，即使標(biāo)簽的范圍包含了declare handler的范圍。

在下述例子中，標(biāo)簽retry的范圍是整個(gè)repeat循環(huán)的范圍，在這個(gè)范圍中使用了declare handler語(yǔ)句，表面上看retry包含了declare handler，但實(shí)際上retry的范圍只是IF語(yǔ)句的范圍，并不包括declare handler的范圍。

CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 3;
retry:
    REPEAT
     BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
         BEGIN
            ITERATE retry; # 我不屬于retry的作用范圍哦，所以我不能使用retry標(biāo)簽
         END;
        IF i < 0 THEN
         LEAVE retry; #我才屬于retry的范圍，我可以使用retry標(biāo)簽。
        END IF;
        SET i = i - 1;
    END;
    UNTIL FALSE END REPEAT;
END;

所以存儲(chǔ)過(guò)程執(zhí)行時(shí)會(huì)出現(xiàn)下述錯(cuò)誤：

ERROR 1308 (42000): LEAVE with no matching label: retry

所以為了避免在handler的中引用外部標(biāo)簽，可以使用下述方法：

1.定義exit類型的handler

如果存儲(chǔ)過(guò)程遇到異常停止運(yùn)行時(shí)，無(wú)需做一些cleanup操作，可以如下定義：

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

如果需要做一些cleanup操作，可以在begin…end中編寫(xiě)相應(yīng)處理邏輯:

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
block cleanup statements
END;

2.定義continue類型的handler，并使用一個(gè)狀態(tài)變量

CREATE PROCEDURE p ()
BEGIN
	DECLARE i INT DEFAULT 3;
	DECLARE done INT DEFAULT FALSE;
	retry:
	 REPEAT
		  BEGIN
			DECLARE CONTINUE HANDLER FOR SQLWARNING
			  BEGIN
				SET done = TRUE;
			  END;
			IF done OR i < 0 THEN
			LEAVE retry;
			END IF;
			SET i = i - 1;
		 END;
	 UNTIL FALSE END REPEAT;
END;

這里使用了一個(gè)名稱為done的變量，通過(guò)判斷這個(gè)變量的狀態(tài)，從而得知是否調(diào)用了continue handler。

3.diagnostics area

SQL語(yǔ)句的執(zhí)行會(huì)產(chǎn)生診斷信息，并存放于診斷區(qū)中

通過(guò)GET DIAGNOSTICS語(yǔ)句獲取診斷區(qū)中的內(nèi)容，該語(yǔ)句不需要特殊的權(quán)限。

診斷區(qū)分為當(dāng)前診斷區(qū)和堆棧診斷區(qū)，通過(guò)CURRENT關(guān)鍵字來(lái)獲取當(dāng)前診斷區(qū)中的內(nèi)容，通過(guò)STACKED獲取堆棧診斷區(qū)中的內(nèi)容，堆棧診斷區(qū)只有在上下文為condition handler的情況下才可以使用，如果不指定關(guān)鍵字默認(rèn)從當(dāng)前診斷區(qū)獲取信息

在客戶端獲取診斷區(qū)中的數(shù)據(jù)

DROP TABLE test.no_such_table;
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1, @p2;

此時(shí)并不能使用GET STACKED DIAGNOSTICS堆棧診斷區(qū)中的內(nèi)容，

因?yàn)镚ET STACKED DIAGNOSTICS只能在condition handler中使用

診斷區(qū)匯總包含2種信息：

1.語(yǔ)句信息，例如conditions的數(shù)量和影響的行數(shù)

2. Condition信息，包括錯(cuò)誤代碼和錯(cuò)誤消息，如果SQL語(yǔ)句拋出多個(gè) conditions，那么在這部分診斷區(qū)中，會(huì)為每一個(gè)condition分配一個(gè)condition區(qū)，如果沒(méi)有拋出condition那么就不會(huì)分配

如果語(yǔ)句產(chǎn)生了3個(gè)condition，那么診斷區(qū)包含的語(yǔ)句信息和condition信息類似：

Statement information:
row count
... other statement information items ...
Condition Handling
Condition area list:
Condition area 1:
error code for condition 1
error message for condition 1
... other condition information items ...
Condition area 2:
error code for condition 2:
error message for condition 2
... other condition information items ...
Condition area 3:
error code for condition 3
error message for condition 3
... other condition information items ...

GET DIAGNOSTICS語(yǔ)句可以獲取語(yǔ)句信息或者condition信息，但是一條GET DIAGNOSTICS無(wú)法同時(shí)獲取這2種信息。

獲取診斷區(qū)中語(yǔ)句信息并保存到p1和p2變量中，本例中獲取的是condition的數(shù)量和rows-affected數(shù)量

GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;

通過(guò)指定condition的編號(hào)獲取診斷區(qū)中相應(yīng)condition信息到p3和p4變量中，本例中獲取的是sqlstate值和錯(cuò)誤消息。

GET DIAGNOSTICS CONDITION 1
@p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;

在SQL標(biāo)準(zhǔn)中，如果出現(xiàn)多個(gè)condition，那么第一個(gè)condition是關(guān)于前一個(gè)SQL語(yǔ)句返回的sqlstate值的，但是在MySQL中，無(wú)法保證這一點(diǎn)，為了得到主要的錯(cuò)誤，不能使用下面的方法：

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

而是先取回condition的數(shù)量，然后使用該值來(lái)指定要查看的condition

正確的方法：

GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

關(guān)于診斷區(qū)，官網(wǎng)的例子：

CREATE PROCEDURE do_insert(value INT)
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE rows INT;
DECLARE result TEXT;
-- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
END;
-- Perform the insert
INSERT INTO t1 (int_col) VALUES(value);
-- Check whether the insert was successful
IF code = '00000' THEN
GET DIAGNOSTICS rows = ROW_COUNT;
SET result = CONCAT('insert succeeded, row count = ',rows);
ELSE
SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
END IF;
-- Say what happened
SELECT result;
END;

假設(shè)上述存儲(chǔ)過(guò)程中的t1表的字段類型int，并且not null,那么在進(jìn)行下述操作分別向表t1中插入非空值和空值，各自得到的結(jié)果如下：

#插入非空值
mysql> CALL do_insert(1);
+---------------------------------+
| result                          |
+---------------------------------+
| insert succeeded, row count = 1 |
+---------------------------------+
##插入null
mysql> CALL do_insert(NULL);
+---- ------------------------------------------------------------+
| result                                                          |
+-----------------------------------------------------------------+
insert failed, error = 23000, message = Column 'int_col' cannot be null
+-----------------------------------------------------------------+

當(dāng)存儲(chǔ)過(guò)程中的condition handler被激活時(shí)，會(huì)發(fā)生一個(gè)向診斷區(qū)堆棧推送的事件：

1.當(dāng)前診斷區(qū)(第一診斷區(qū))會(huì)變?yōu)槎褩Ｔ\斷區(qū)(第二診斷區(qū))，并且創(chuàng)建一個(gè)新的診斷區(qū)作為當(dāng)前診斷區(qū)。

2.在condition Handler中可以使用 GET [CURRENT] DIAGNOSTICS 和 GET STACKED DIAGNOSTICS來(lái)獲取當(dāng)前診斷區(qū)或堆棧診斷區(qū)中的內(nèi)容。

3.在開(kāi)始的時(shí)候，當(dāng)前診斷區(qū)和堆棧診斷區(qū)會(huì)返回相同的結(jié)果,所以有可能從當(dāng)前診斷區(qū)獲取到被激活的Handler的condition的相關(guān)信息，只要此時(shí)handler中沒(méi)有其他SQL語(yǔ)句去改變當(dāng)前診斷區(qū)中的內(nèi)容。

4.隨著Handler中語(yǔ)句的執(zhí)行，會(huì)根據(jù)一定的規(guī)則對(duì)當(dāng)前診斷區(qū)的內(nèi)容進(jìn)行清空或者修改。

所以更可靠地獲取被激活condition handler中信息的方法是從堆棧診斷區(qū)中獲取相關(guān)信息，因?yàn)槎褩Ｔ\斷區(qū)中的內(nèi)容不會(huì)被condition handler中的語(yǔ)句所修改，除了RESIGNAL語(yǔ)句。

通過(guò)下面例子來(lái)說(shuō)明，在condition中如何通過(guò) GET STACKED DIAGNOSTICS語(yǔ)句來(lái)獲取關(guān)于handler異常的信息，盡管此時(shí)當(dāng)前診斷區(qū)已經(jīng)被清空或修改。

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TEXT NOT NULL);
DROP PROCEDURE IF EXISTS p;
delimiter //
CREATE PROCEDURE p ()
BEGIN
-- Declare variables to hold diagnostics area information
    DECLARE errcount INT;
    DECLARE errno INT;
    DECLARE msg TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Here the current DA is nonempty because no prior statements
-- executing within the handler have cleared it
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
-- Map attempted NULL insert to empty string insert
INSERT INTO t1 (c1) VALUES('');
-- Here the current DA should be empty (if the INSERT succeeded),
-- so check whether there are conditions before attempting to
-- obtain condition information
GET CURRENT DIAGNOSTICS errcount = NUMBER;
IF errcount = 0
THEN
SELECT 'mapped insert succeeded, current DA is empty' AS op;
ELSE
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA after mapped insert' AS op, errno, msg;
END IF ;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA after mapped insert' AS op, errno, msg;
END;
INSERT INTO t1 (c1) VALUES('string 1');
INSERT INTO t1 (c1) VALUES(NULL);
END;
//
delimiter ;
CALL p();
SELECT * FROM t1;

在上述存儲(chǔ)過(guò)程中，定義了一個(gè)condition handler，在這個(gè)handler的開(kāi)頭處分別獲取當(dāng)前診斷區(qū)和堆棧診斷區(qū)中的內(nèi)容，然后執(zhí)行一條insert語(yǔ)句，之后再分別查詢當(dāng)前診斷區(qū)和堆棧診斷區(qū)的內(nèi)容。

在handler定義結(jié)束后，是這個(gè)存儲(chǔ)過(guò)程的主體，也就是2條insert語(yǔ)句，其中一條insert語(yǔ)句為非空值字符串，另外一條insert插入的值為null，

所以該存儲(chǔ)過(guò)程執(zhí)行順序如下：

1.首先成功執(zhí)行INSERT INTO t1 (c1) VALUES(‘string 1’);

2.執(zhí)行INSERT INTO t1 (c1) VALUES(NULL);因?yàn)閠1表中禁止插入空值，所以會(huì)拋出異常。

3.拋出的異常被condition handler捕獲，condition handler被激活從而觸發(fā)其中的處理邏輯,并打印condition handler中當(dāng)前診斷區(qū)和堆棧診斷區(qū)的內(nèi)容，二者內(nèi)容相同。

4.condition handler中的INSERT INTO t1 (c1) VALUES(’’);語(yǔ)句執(zhí)行，該語(yǔ)句的執(zhí)行會(huì)清空當(dāng)前診斷區(qū)中的內(nèi)容。

+---------------------------------+-------+----------------------------+
| op                              | errno |             msg            |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert | 1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+

5.再次打印condition handler中當(dāng)前診斷區(qū)和堆棧診斷區(qū)的內(nèi)容，此時(shí)因?yàn)楫?dāng)前診斷區(qū)的內(nèi)容被清空，所以打印’mapped insert succeeded, current DA is empty’，接著打印堆棧診斷區(qū)中內(nèi)容，因?yàn)槎褩Ｔ\斷區(qū)中的內(nèi)容不會(huì)隨著語(yǔ)句的執(zhí)行而被清空掉，所以堆棧診斷區(qū)顯示的內(nèi)容依舊是：

+--------------------------------+-------+----------------------------+
| op                             | errno |  msg                       |
+--------------------------------+-------+----------------------------+
| stacked DA after mapped insert | 1048 | Column 'c1' cannot be null |
+--------------------------------+-------+----------------------------+

需要注意的是

1.GET DIAGNOSTICS語(yǔ)句也會(huì)清空當(dāng)前診斷區(qū)中的內(nèi)容，所以上述代碼中把condition handler中的insert語(yǔ)句去掉，得到的結(jié)果也是一樣的

2.如果將上述存儲(chǔ)過(guò)程進(jìn)行如下修改，也就是將3條declare變量的語(yǔ)句放到declare handler中，實(shí)際的結(jié)果將取決于MySQL的版本，如果是在MySQL-5.7.2之前的版本，下述修改后并不會(huì)影響診斷區(qū)中的內(nèi)容，實(shí)際結(jié)果與上述結(jié)果相同，如果實(shí)在MySQL-5.7.2及之后的版本，declare變量語(yǔ)句會(huì)清空當(dāng)前診斷區(qū)中的內(nèi)容。

CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errcount INT;
DECLARE errno INT;
DECLARE msg TEXT;
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
...

所以在需要獲取診斷區(qū)中的內(nèi)容時(shí)，一定要從堆棧診斷區(qū)中獲取，而不是當(dāng)前診斷區(qū)。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: