MySQL中的異常處理
1. 問題場景
先來看個場景:
執行一條插入語句,因為id是主鍵,沒有設定自增,是以在插入的時候我們必須要添加該字段的值,但是上面沒有添加就出現了
1364
的錯誤提示資訊,針對這種情況我們應該怎麼處理呢?或者看下面這個存儲過程。
# 建立存儲過程
CREATE PROCEDURE insertStudentData()
BEGIN
SET @x = 1;
insert into student(name)values('TOM');
SET @x = 2;
insert into student(name)values('James');
SET @x = 3;
END ;
# 調用存儲過程
call insertStudentData() ;
# 檢視變量
select @x ;
輸出的結果為:
可以看到存儲過程調用中也出現了問題,程式沒有執行完成,針對這種問題我們就需要通過
定義條件
和
處理程式
來解決了。
定義條件
是事先定義程式執行過程中可能遇到的問題
處理程式
定義了在遇到問題時應當采取的處理方式,并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。這樣可以增強存儲程式處理問題的能力,避免程式異常停止運作。類似于Java中的異常處理。
說明:定義條件和處理程式在存儲過程、存儲函數中都是支援的
2.定義條件
定義條件就是給MySQL中的錯誤碼命名,這有助于存儲的程式代碼更清晰。它将一個 錯誤名字 和 指定的 錯誤條件 關聯起來。這個名字可以随後被用在定義處理程式的 DECLARE HANDLER 語句中。
定義條件使用DECLARE語句,文法格式如下:
DECLARE 錯誤名稱 CONDITION FOR 錯誤碼(或錯誤條件)
錯誤碼的說明:
MySQL_error_code 和 sqlstate_value 都可以表示MySQL的錯誤。
上圖中的
1364
是
MySQL_error_code
,
HY000
是
sqlstate_value
錯誤碼 | 說明 |
MySQL_error_code | 是數值類型錯誤代碼,比如 1364 |
sqlstate_value | 是長度為5的字元串類型錯誤代碼,比如 HY000 |
案例:定義“Field_Not_Be_NULL”錯誤名與MySQL中違反非空限制的錯誤類型是“ERROR 1364 (HY000)”對應。
#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1364;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE 'HY000';
3.定義處理程式
可以為SQL執行過程中發生的某種類型的錯誤定義特殊的處理程式。定義處理程式時,使用DECLARE語句的文法如下:
DECLARE 處理方式 HANDLER FOR 錯誤類型 處理語句
文法說明
組成内容 | 說明 |
處理方式 | 處理方式有3個取值:CONTINUE、EXIT、UNDO CONTINUE :表示遇到錯誤不處理,繼續執行。 EXIT :表示遇到錯誤馬上退出。 UNDO :表示遇到錯誤後撤回之前的操作。MySQL中暫時不支援這樣的操作。 |
錯誤類型 | 也就是條件: SQLSTATE ‘字元串錯誤碼’ :表示長度為5的sqlstate_value類型的錯誤代碼; MySQL_error_code :比對數值類型錯誤代碼; 錯誤名稱 :表示DECLARE … CONDITION定義的錯誤條件名稱。 SQLWARNING :比對所有以01開頭的SQLSTATE錯誤代碼; NOT FOUND :比對所有以02開頭的SQLSTATE錯誤代碼; SQLEXCEPTION :比對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤代碼; |
處理語句 | 如果出現上述條件之一,則采用對應的處理方式,并執行指定的處理語句。 語句可以是像“ SET 變量 = 值 ”這樣的簡單語句,也可以是使用 BEGIN … END 編寫的複合語句。 |
定義處理程式的幾種方式,代碼如下:
#方法1:捕獲sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2:捕獲mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3:先定義條件,再調用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
4.案例解決
通過上面的介紹我們可以來解決前面出現的問題了,先删除之前的存儲過程
DROP PROCEDURE insertStudentData
然後開始重新建立存儲過程,并添加對應的處理程式
CREATE PROCEDURE insertStudentData()
BEGIN
#處理的方法一:
DECLARE CONTINUE HANDLER FOR 1364 SET @proc_value=-1;
#處理的方法二:
#DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' SET @proc_value=-1;
#處理的方法三
# 先定義條件
# DECLARE field_not_null CONDITION FOR SQLSTATE 'HY000'
# DECLARE CONTINUE HANDLER FOR field_not_null SET @proc_value=-1;
SET @x = 1;
insert into student(name)values('TOM');
SET @x = 2;
insert into student(name)values('James');
SET @x = 3;
END ;
調用存儲過程
CALL insertStudentData() ;
查詢變量
SELECT @x , @proc_value;