這是Mysql系列第20篇。

環(huán)境：mysql5.7.25，cmd命令中進(jìn)行演示。

代碼中被[]包含的表示可選，|符號(hào)分開(kāi)的表示可選其一。

需求背景

我們?cè)趯?xiě)存儲(chǔ)過(guò)程的時(shí)候，可能會(huì)出現(xiàn)下列一些情況：

1. 插入的數(shù)據(jù)違反唯一約束，導(dǎo)致插入失敗

2. 插入或者更新數(shù)據(jù)超過(guò)字段最大長(zhǎng)度，導(dǎo)致操作失敗

3. update影響行數(shù)和期望結(jié)果不一致

遇到上面各種異常情況的時(shí)，可能需要我們能夠捕獲，然后可能需要回滾當(dāng)前事務(wù)。

本文主要圍繞異常處理這塊做詳細(xì)的介紹。

此時(shí)我們需要使用游標(biāo)，通過(guò)游標(biāo)的方式來(lái)遍歷select查詢(xún)的結(jié)果集，然后對(duì)每行數(shù)據(jù)進(jìn)行處理。

本篇內(nèi)容

• 異常分類(lèi)詳解

• 內(nèi)部異常詳解

• 外部異常詳解

• 掌握樂(lè)觀鎖解決并發(fā)修改數(shù)據(jù)出錯(cuò)的問(wèn)題

• update影響行數(shù)和期望結(jié)果不一致時(shí)的處理

準(zhǔn)備數(shù)據(jù)

創(chuàng)建庫(kù)：javacode2018

創(chuàng)建表：test1，test1表中的a字段為主鍵。

/*建庫(kù)javacode2018*/
drop?database?if?exists?javacode2018;
create?database?javacode2018;/*切換到j(luò)avacode2018庫(kù)*/
use?javacode2018;DROP?TABLE?IF?EXISTS?test1;
CREATE?TABLE?test1(a?int?PRIMARY?KEY);

異常分類(lèi)

我們將異常分為mysql內(nèi)部異常和外部異常

mysql內(nèi)部異常

當(dāng)我們執(zhí)行一些sql的時(shí)候，可能違反了mysql的一些約束，導(dǎo)致mysql內(nèi)部報(bào)錯(cuò)，如插入數(shù)據(jù)違反唯一約束，更新數(shù)據(jù)超時(shí)等，此時(shí)異常是由mysql內(nèi)部拋出的，我們將這些由mysql拋出的異常統(tǒng)稱(chēng)為內(nèi)部異常。

外部異常

當(dāng)我們執(zhí)行一個(gè)update的時(shí)候，可能我們期望影響1行，但是實(shí)際上影響的不是1行數(shù)據(jù)，這種情況：sql的執(zhí)行結(jié)果和期望的結(jié)果不一致，這種情況也我們也把他作為外部異常處理，我們將sql執(zhí)行結(jié)果和期望結(jié)果不一致的情況統(tǒng)稱(chēng)為外部異常。

Mysql內(nèi)部異常

示例1

test1表中的a字段為主鍵，我們向test1表同時(shí)插入2條數(shù)據(jù)，并且放在一個(gè)事務(wù)中執(zhí)行，最終要么都插入成功，要么都失敗。

創(chuàng)建存儲(chǔ)過(guò)程：

/*刪除存儲(chǔ)過(guò)程*/
DROP?PROCEDURE?IF?EXISTS?proc1;
/*聲明結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過(guò)程*/
CREATE?PROCEDURE?proc1(a1?int,a2?int)BEGINSTART?TRANSACTION;INSERT?INTO?test1(a)?VALUES?(a1);INSERT?INTO?test1(a)?VALUES?(a2);COMMIT;END?$
/*結(jié)束符置為;*/
DELIMITER?;

上面存儲(chǔ)過(guò)程插入了兩條數(shù)據(jù)，a的值都是1。

驗(yàn)證結(jié)果：

mysql>?DELETE?FROM?test1;
Query?OK,?0?rows?affected?(0.00?sec)mysql>?CALL?proc1(1,1);
ERROR?1062?(23000):?Duplicate?entry?'1'?for?key?'PRIMARY'
mysql>?SELECT?*?from?test1;
+---+
|?a?|
+---+
|?1?|
+---+
1?row?in?set?(0.00?sec)

上面先刪除了test1表中的數(shù)據(jù)，然后調(diào)用存儲(chǔ)過(guò)程proc1，由于test1表中的a字段是主鍵，插入第二條數(shù)據(jù)時(shí)違反了a字段的主鍵約束，mysql內(nèi)部拋出了異常，導(dǎo)致第二條數(shù)據(jù)插入失敗，最終只有第一條數(shù)據(jù)插入成功了。

上面的結(jié)果和我們期望的不一致，我們希望要么都插入成功，要么失敗。

那我們?cè)趺醋瞿?#xff1f;我們需要捕獲上面的主鍵約束異常，然后發(fā)現(xiàn)有異常的時(shí)候執(zhí)行rollback回滾操作，改進(jìn)上面的代碼，看下面示例2。

示例2

我們對(duì)上面示例進(jìn)行改進(jìn)，捕獲上面主鍵約束異常，然后進(jìn)行回滾處理，如下：

創(chuàng)建存儲(chǔ)過(guò)程：

/*刪除存儲(chǔ)過(guò)程*/
DROP?PROCEDURE?IF?EXISTS?proc2;
/*聲明結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過(guò)程*/
CREATE?PROCEDURE?proc2(a1?int,a2?int)BEGIN/*聲明一個(gè)變量，標(biāo)識(shí)是否有sql異常*/DECLARE?hasSqlError?int?DEFAULT?FALSE;/*在執(zhí)行過(guò)程中出任何異常設(shè)置hasSqlError為T(mén)RUE*/DECLARE?CONTINUE?HANDLER?FOR?SQLEXCEPTION?SET?hasSqlError=TRUE;/*開(kāi)啟事務(wù)*/START?TRANSACTION;INSERT?INTO?test1(a)?VALUES?(a1);INSERT?INTO?test1(a)?VALUES?(a2);/*根據(jù)hasSqlError判斷是否有異常，做回滾和提交操作*/IF?hasSqlError?THENROLLBACK;ELSECOMMIT;END?IF;END?$
/*結(jié)束符置為;*/
DELIMITER?;

上面重點(diǎn)是這句：

DECLARE?CONTINUE?HANDLER?FOR?SQLEXCEPTION?SET?hasSqlError=TRUE;

當(dāng)有sql異常的時(shí)候，會(huì)將變量hasSqlError的值置為TRUE。

模擬異常情況：

mysql>?DELETE?FROM?test1;
Query?OK,?2?rows?affected?(0.00?sec)mysql>?CALL?proc2(1,1);
Query?OK,?0?rows?affected?(0.00?sec)mysql>?SELECT?*?from?test1;
Empty?set?(0.00?sec)

上面插入了2條一樣的數(shù)據(jù)，插入失敗，可以看到上面test1表無(wú)數(shù)據(jù)，和期望結(jié)果一致，插入被回滾了。

模擬正常情況：

mysql>?DELETE?FROM?test1;
Query?OK,?0?rows?affected?(0.00?sec)mysql>?CALL?proc2(1,2);
Query?OK,?0?rows?affected?(0.00?sec)mysql>?SELECT?*?from?test1;
+---+
|?a?|
+---+
|?1?|
|?2?|
+---+
2?rows?in?set?(0.00?sec)

上面插入了2條不同的數(shù)據(jù)，最終插入成功。

外部異常

外部異常不是由mysql內(nèi)部拋出的錯(cuò)誤，而是由于sql的執(zhí)行結(jié)果和我們期望的結(jié)果不一致的時(shí)候，我們需要對(duì)這種情況做一些處理，如回滾操作。

示例1

我們來(lái)模擬電商中下單操作，按照上面的步驟來(lái)更新賬戶(hù)余額。

電商中有個(gè)賬戶(hù)表和訂單表，如下：

DROP?TABLE?IF?EXISTS?t_funds;
CREATE?TABLE?t_funds(user_id?INT?PRIMARY?KEY?COMMENT?'用戶(hù)id',available?DECIMAL(10,2)?NOT?NULL?DEFAULT?0?COMMENT?'賬戶(hù)余額'
)?COMMENT?'用戶(hù)賬戶(hù)表';
DROP?TABLE?IF?EXISTS?t_order;
CREATE?TABLE?t_order(id?int?PRIMARY?KEY?AUTO_INCREMENT?COMMENT?'訂單id',price?DECIMAL(10,2)?NOT?NULL?DEFAULT?0?COMMENT?'訂單金額'
)?COMMENT?'訂單表';
delete?from?t_funds;
/*插入一條數(shù)據(jù)，用戶(hù)id為1001，余額為1000*/
INSERT?INTO?t_funds?(user_id,available)?VALUES?(1001,1000);

下單操作涉及到操作上面的賬戶(hù)表，我們用存儲(chǔ)過(guò)程來(lái)模擬實(shí)現(xiàn)：

/*刪除存儲(chǔ)過(guò)程*/
DROP?PROCEDURE?IF?EXISTS?proc3;
/*聲明結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過(guò)程*/
CREATE?PROCEDURE?proc3(v_user_id?int,v_price?decimal(10,2),OUT?v_msg?varchar(64))a:BEGINDECLARE?v_available?DECIMAL(10,2);/*1.查詢(xún)余額，判斷余額是否夠*/select?a.available?into?v_available?from?t_funds?a?where?a.user_id?=?v_user_id;if?v_available<=v_price?THENSET?v_msg='賬戶(hù)余額不足!';/*退出*/LEAVE?a;END?IF;/*模擬耗時(shí)5秒*/SELECT?sleep(5);/*2.余額減去price*/SET?v_available?=?v_available?-?v_price;/*3.更新余額*/START?TRANSACTION;UPDATE?t_funds?SET?available?=?v_available?WHERE?user_id?=?v_user_id;/*插入訂單明細(xì)*/INSERT?INTO?t_order?(price)?VALUES?(v_price);/*提交事務(wù)*/COMMIT;SET?v_msg='下單成功!';END?$
/*結(jié)束符置為;*/
DELIMITER?;

上面過(guò)程主要分為3步驟：驗(yàn)證余額、修改余額變量、更新余額。

開(kāi)啟2個(gè)cmd窗口，連接mysql，同時(shí)執(zhí)行下面操作：

USE?javacode2018;
CALL?proc3(1001,100,@v_msg);
select?@v_msg;

然后執(zhí)行：

mysql>?SELECT?*?FROM?t_funds;
+---------+-----------+
|?user_id?|?available?|
+---------+-----------+
|????1001?|????900.00?|
+---------+-----------+
1?row?in?set?(0.00?sec)mysql>?SELECT?*?FROM?t_order;
+----+--------+
|?id?|?price??|
+----+--------+
|??1?|?100.00?|
|??2?|?100.00?|
+----+--------+
2?rows?in?set?(0.00?sec)

上面出現(xiàn)了非常嚴(yán)重的錯(cuò)誤：下單成功了2次，但是賬戶(hù)只扣了100。

上面過(guò)程是由于2個(gè)操作并發(fā)導(dǎo)致的，2個(gè)窗口同時(shí)執(zhí)行第一步的時(shí)候看到了一樣的數(shù)據(jù)（看到的余額都是1000），然后繼續(xù)向下執(zhí)行，最終導(dǎo)致結(jié)果出問(wèn)題了。

上面操作我們可以使用樂(lè)觀鎖來(lái)優(yōu)化。

樂(lè)觀鎖的過(guò)程：用期望的值和目標(biāo)值進(jìn)行比較，如果相同，則更新目標(biāo)值，否則什么也不做。

樂(lè)觀鎖類(lèi)似于java中的cas操作，這塊需要了解的可以點(diǎn)擊：詳解CAS

我們可以在資金表t_funds添加一個(gè)version字段，表示版本號(hào)，每次更新數(shù)據(jù)的時(shí)候+1，更新數(shù)據(jù)的時(shí)候?qū)ersion作為條件去執(zhí)行update，根據(jù)update影響行數(shù)來(lái)判斷執(zhí)行是否成功，優(yōu)化上面的代碼，見(jiàn)示例2。

示例2

對(duì)示例1進(jìn)行優(yōu)化。

創(chuàng)建表：

DROP?TABLE?IF?EXISTS?t_funds;
CREATE?TABLE?t_funds(user_id?INT?PRIMARY?KEY?COMMENT?'用戶(hù)id',available?DECIMAL(10,2)?NOT?NULL?DEFAULT?0?COMMENT?'賬戶(hù)余額',version?INT?DEFAULT?0?COMMENT?'版本號(hào)，每次更新+1'
)?COMMENT?'用戶(hù)賬戶(hù)表';DROP?TABLE?IF?EXISTS?t_order;
CREATE?TABLE?t_order(id?int?PRIMARY?KEY?AUTO_INCREMENT?COMMENT?'訂單id',price?DECIMAL(10,2)?NOT?NULL?DEFAULT?0?COMMENT?'訂單金額'
)COMMENT?'訂單表';
delete?from?t_funds;
/*插入一條數(shù)據(jù)，用戶(hù)id為1001，余額為1000*/
INSERT?INTO?t_funds?(user_id,available)?VALUES?(1001,1000);

創(chuàng)建存儲(chǔ)過(guò)程：

/*刪除存儲(chǔ)過(guò)程*/
DROP?PROCEDURE?IF?EXISTS?proc4;
/*聲明結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過(guò)程*/
CREATE?PROCEDURE?proc4(v_user_id?int,v_price?decimal(10,2),OUT?v_msg?varchar(64))a:BEGIN/*保存當(dāng)前余額*/DECLARE?v_available?DECIMAL(10,2);/*保存版本號(hào)*/DECLARE?v_version?INT?DEFAULT?0;/*保存影響的行數(shù)*/DECLARE?v_update_count?INT?DEFAULT?0;/*1.查詢(xún)余額，判斷余額是否夠*/select?a.available,a.version?into?v_available,v_version?from?t_funds?a?where?a.user_id?=?v_user_id;if?v_available<=v_price?THENSET?v_msg='賬戶(hù)余額不足!';/*退出*/LEAVE?a;END?IF;/*模擬耗時(shí)5秒*/SELECT?sleep(5);/*2.余額減去price*/SET?v_available?=?v_available?-?v_price;/*3.更新余額*/START?TRANSACTION;UPDATE?t_funds?SET?available?=?v_available?WHERE?user_id?=?v_user_id?AND?version?=?v_version;/*獲取上面update影響行數(shù)*/select?ROW_COUNT()?INTO?v_update_count;IF?v_update_count=1?THEN/*插入訂單明細(xì)*/INSERT?INTO?t_order?(price)?VALUES?(v_price);SET?v_msg='下單成功!';/*提交事務(wù)*/COMMIT;ELSESET?v_msg='下單失敗,請(qǐng)重試!';/*回滾事務(wù)*/ROLLBACK;END?IF;END?$
/*結(jié)束符置為;*/
DELIMITER?;

ROW_COUNT()可以獲取更新或插入后獲取受影響行數(shù)。將受影響行數(shù)放在v_update_count中。

然后根據(jù)v_update_count是否等于1判斷更新是否成功，如果成功則記錄訂單信息并提交事務(wù)，否則回滾事務(wù)。

驗(yàn)證結(jié)果：開(kāi)啟2個(gè)cmd窗口，連接mysql，執(zhí)行下面操作：

use?javacode2018;
CALL?proc4(1001,100,@v_msg);
select?@v_msg;

窗口1結(jié)果：

mysql>?CALL?proc4(1001,100,@v_msg);
+----------+
|?sleep(5)?|
+----------+
|????????0?|
+----------+
1?row?in?set?(5.00?sec)Query?OK,?0?rows?affected?(5.00?sec)mysql>?select?@v_msg;
+---------------+
|?@v_msg????????|
+---------------+
|?下單成功!?????|
+---------------+
1?row?in?set?(0.00?sec)

窗口2結(jié)果：

mysql>?CALL?proc4(1001,100,@v_msg);
+----------+
|?sleep(5)?|
+----------+
|????????0?|
+----------+
1?row?in?set?(5.00?sec)Query?OK,?0?rows?affected?(5.01?sec)mysql>?select?@v_msg;
+-------------------------+
|?@v_msg??????????????????|
+-------------------------+
|?下單失敗,請(qǐng)重試!????????|
+-------------------------+
1?row?in?set?(0.00?sec)

可以看到第一個(gè)窗口下單成功了，窗口2下單失敗了。

再看一下2個(gè)表的數(shù)據(jù)：

mysql>?SELECT?*?FROM?t_funds;
+---------+-----------+---------+
|?user_id?|?available?|?version?|
+---------+-----------+---------+
|????1001?|????900.00?|???????0?|
+---------+-----------+---------+
1?row?in?set?(0.00?sec)mysql>?SELECT?*?FROM?t_order;
+----+--------+
|?id?|?price??|
+----+--------+
|??1?|?100.00?|
+----+--------+
1?row?in?set?(0.00?sec)

也正常。

總結(jié)

1. 異常分為Mysql內(nèi)部異常和外部異常

2. 內(nèi)部異常由mysql內(nèi)部觸發(fā)，外部異常是sql的執(zhí)行結(jié)果和期望結(jié)果不一致導(dǎo)致的錯(cuò)誤

3. sql內(nèi)部異常捕獲方式

   DECLARE?CONTINUE?HANDLER?FOR?SQLEXCEPTION?SET?hasSqlError=TRUE;
   

4. ROW_COUNT()可以獲取mysql中insert或者update影響的行數(shù)

5. 掌握使用樂(lè)觀鎖（添加版本號(hào)）來(lái)解決并發(fā)修改數(shù)據(jù)可能出錯(cuò)的問(wèn)題

6. begin end前面可以加標(biāo)簽，LEAVE 標(biāo)簽可以退出對(duì)應(yīng)的begin end，可以使用這個(gè)來(lái)實(shí)現(xiàn)return的效果