Mysql高手系列 – 第20篇：異常捕獲及處理詳解（實戰經驗）

  • 2019 年 10 月 10 日
  • 筆記

Mysql系列的目標是：通過這個系列從入門到全面掌握一個高級開發所需要的全部技能。

這是Mysql系列第20篇。

環境：mysql5.7.25，cmd命令中進行演示。

代碼中被[]包含的表示可選，|符號分開的表示可選其一。

需求背景

我們在寫存儲過程的時候，可能會出現下列一些情況：

  1. 插入的數據違反唯一約束，導致插入失敗
  2. 插入或者更新數據超過字段最大長度，導致操作失敗
  3. update影響行數和期望結果不一致

遇到上面各種異常情況的時，可能需要我們能夠捕獲，然後可能需要回滾當前事務。

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

此時我們需要使用游標，通過游標的方式來遍歷select查詢的結果集，然後對每行數據進行處理。

本篇內容

  • 異常分類詳解
  • 內部異常詳解
  • 外部異常詳解
  • 掌握樂觀鎖解決並發修改數據出錯的問題
  • update影響行數和期望結果不一致時的處理

準備數據

創建庫：javacode2018

創建表：test1，test1表中的a字段為主鍵。

/*建庫javacode2018*/  drop database if exists javacode2018;  create database javacode2018;    /*切換到javacode2018庫*/  use javacode2018;    DROP TABLE IF EXISTS test1;  CREATE TABLE test1(a int PRIMARY KEY);

異常分類

我們將異常分為mysql內部異常和外部異常

mysql內部異常

當我們執行一些sql的時候，可能違反了mysql的一些約束，導致mysql內部報錯，如插入數據違反唯一約束，更新數據超時等，此時異常是由mysql內部拋出的，我們將這些由mysql拋出的異常統稱為內部異常。

外部異常

當我們執行一個update的時候，可能我們期望影響1行，但是實際上影響的不是1行數據，這種情況：sql的執行結果和期望的結果不一致，這種情況也我們也把他作為外部異常處理，我們將sql執行結果和期望結果不一致的情況統稱為外部異常。

Mysql內部異常

示例1

test1表中的a字段為主鍵，我們向test1表同時插入2條數據，並且放在一個事務中執行，最終要麼都插入成功，要麼都失敗。

創建存儲過程：
/*刪除存儲過程*/  DROP PROCEDURE IF EXISTS proc1;  /*聲明結束符為$*/  DELIMITER $  /*創建存儲過程*/  CREATE PROCEDURE proc1(a1 int,a2 int)    BEGIN      START TRANSACTION;      INSERT INTO test1(a) VALUES (a1);      INSERT INTO test1(a) VALUES (a2);      COMMIT;    END $  /*結束符置為;*/  DELIMITER ;

上面存儲過程插入了兩條數據，a的值都是1。

驗證結果：
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表中的數據，然後調用存儲過程proc1，由於test1表中的a字段是主鍵，插入第二條數據時違反了a字段的主鍵約束，mysql內部拋出了異常，導致第二條數據插入失敗，最終只有第一條數據插入成功了。

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

那我們怎麼做呢？我們需要捕獲上面的主鍵約束異常，然後發現有異常的時候執行rollback回滾操作，改進上面的代碼，看下面示例2。

示例2

我們對上面示例進行改進，捕獲上面主鍵約束異常，然後進行回滾處理，如下：

創建存儲過程：
/*刪除存儲過程*/  DROP PROCEDURE IF EXISTS proc2;  /*聲明結束符為$*/  DELIMITER $  /*創建存儲過程*/  CREATE PROCEDURE proc2(a1 int,a2 int)    BEGIN      /*聲明一個變量，標識是否有sql異常*/      DECLARE hasSqlError int DEFAULT FALSE;      /*在執行過程中出任何異常設置hasSqlError為TRUE*/      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;        /*開啟事務*/      START TRANSACTION;      INSERT INTO test1(a) VALUES (a1);      INSERT INTO test1(a) VALUES (a2);        /*根據hasSqlError判斷是否有異常，做回滾和提交操作*/      IF hasSqlError THEN        ROLLBACK;      ELSE        COMMIT;      END IF;    END $  /*結束符置為;*/  DELIMITER ;
上面重點是這句：
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

當有sql異常的時候，會將變量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條一樣的數據，插入失敗，可以看到上面test1表無數據，和期望結果一致，插入被回滾了。

模擬正常情況：
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條不同的數據，最終插入成功。

外部異常

外部異常不是由mysql內部拋出的錯誤，而是由於sql的執行結果和我們期望的結果不一致的時候，我們需要對這種情況做一些處理，如回滾操作。

示例1

我們來模擬電商中下單操作，按照上面的步驟來更新賬戶餘額。

電商中有個賬戶表和訂單表，如下：
DROP TABLE IF EXISTS t_funds;  CREATE TABLE t_funds(    user_id INT PRIMARY KEY COMMENT '用戶id',    available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '賬戶餘額'  ) COMMENT '用戶賬戶表';  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;  /*插入一條數據，用戶id為1001，餘額為1000*/  INSERT INTO t_funds (user_id,available) VALUES (1001,1000);
下單操作涉及到操作上面的賬戶表，我們用存儲過程來模擬實現：
/*刪除存儲過程*/  DROP PROCEDURE IF EXISTS proc3;  /*聲明結束符為$*/  DELIMITER $  /*創建存儲過程*/  CREATE PROCEDURE proc3(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64))    a:BEGIN      DECLARE v_available DECIMAL(10,2);        /*1.查詢餘額，判斷餘額是否夠*/      select a.available into v_available from t_funds a where a.user_id = v_user_id;      if v_available<=v_price THEN        SET v_msg='賬戶餘額不足!';        /*退出*/        LEAVE a;      END IF;        /*模擬耗時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;        /*插入訂單明細*/      INSERT INTO t_order (price) VALUES (v_price);        /*提交事務*/      COMMIT;      SET v_msg='下單成功!';    END $  /*結束符置為;*/  DELIMITER ;

上面過程主要分為3步驟：驗證餘額、修改餘額變量、更新餘額。

開啟2個cmd窗口，連接mysql，同時執行下面操作：
USE javacode2018;  CALL proc3(1001,100,@v_msg);  select @v_msg;
然後執行：
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)

上面出現了非常嚴重的錯誤：下單成功了2次，但是賬戶只扣了100。

上面過程是由於2個操作並發導致的，2個窗口同時執行第一步的時候看到了一樣的數據（看到的餘額都是1000），然後繼續向下執行，最終導致結果出問題了。

上面操作我們可以使用樂觀鎖來優化。

樂觀鎖的過程：用期望的值和目標值進行比較，如果相同，則更新目標值，否則什麼也不做。

樂觀鎖類似於java中的cas操作，這塊需要了解的可以點擊：詳解CAS

我們可以在資金錶t_funds添加一個version字段，表示版本號，每次更新數據的時候+1，更新數據的時候將version作為條件去執行update，根據update影響行數來判斷執行是否成功，優化上面的代碼，見示例2。

示例2

對示例1進行優化。

創建表：
DROP TABLE IF EXISTS t_funds;  CREATE TABLE t_funds(    user_id INT PRIMARY KEY COMMENT '用戶id',    available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '賬戶餘額',    version INT DEFAULT 0 COMMENT '版本號，每次更新+1'  ) COMMENT '用戶賬戶表';    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;  /*插入一條數據，用戶id為1001，餘額為1000*/  INSERT INTO t_funds (user_id,available) VALUES (1001,1000);
創建存儲過程：
/*刪除存儲過程*/  DROP PROCEDURE IF EXISTS proc4;  /*聲明結束符為$*/  DELIMITER $  /*創建存儲過程*/  CREATE PROCEDURE proc4(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64))      a:BEGIN      /*保存當前餘額*/      DECLARE v_available DECIMAL(10,2);      /*保存版本號*/      DECLARE v_version INT DEFAULT 0;      /*保存影響的行數*/      DECLARE v_update_count INT DEFAULT 0;          /*1.查詢餘額，判斷餘額是否夠*/      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 THEN        SET v_msg='賬戶餘額不足!';        /*退出*/        LEAVE a;      END IF;        /*模擬耗時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影響行數*/      select ROW_COUNT() INTO v_update_count;        IF v_update_count=1 THEN        /*插入訂單明細*/        INSERT INTO t_order (price) VALUES (v_price);        SET v_msg='下單成功!';        /*提交事務*/        COMMIT;      ELSE        SET v_msg='下單失敗,請重試!';        /*回滾事務*/        ROLLBACK;      END IF;    END $  /*結束符置為;*/  DELIMITER ;

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

然後根據v_update_count是否等於1判斷更新是否成功，如果成功則記錄訂單信息並提交事務，否則回滾事務。

驗證結果：開啟2個cmd窗口，連接mysql，執行下面操作：
use javacode2018;  CALL proc4(1001,100,@v_msg);  select @v_msg;
窗口1結果：
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結果：
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                  |  +-------------------------+  | 下單失敗,請重試!        |  +-------------------------+  1 row in set (0.00 sec)

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

再看一下2個表的數據：

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)

也正常。

總結

  1. 異常分為Mysql內部異常和外部異常

  2. 內部異常由mysql內部觸發，外部異常是sql的執行結果和期望結果不一致導致的錯誤

  3. sql內部異常捕獲方式

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

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

  5. 掌握使用樂觀鎖（添加版本號）來解決並發修改數據可能出錯的問題

  6. begin end前面可以加標籤，LEAVE 標籤可以退出對應的begin end，可以使用這個來實現return的效果

Mysql系列目錄

  1. 第1篇：mysql基礎知識
  2. 第2篇：詳解mysql數據類型（重點）
  3. 第3篇：管理員必備技能(必須掌握)
  4. 第4篇：DDL常見操作
  5. 第5篇：DML操作匯總（insert,update,delete）
  6. 第6篇：select查詢基礎篇
  7. 第7篇：玩轉select條件查詢，避免采坑
  8. 第8篇：詳解排序和分頁(order by & limit)
  9. 第9篇：分組查詢詳解（group by & having）
  10. 第10篇：常用的幾十個函數詳解
  11. 第11篇：深入了解連接查詢及原理
  12. 第12篇：子查詢
  13. 第13篇：細說NULL導致的神坑，讓人防不勝防
  14. 第14篇：詳解事務
  15. 第15篇：詳解視圖
  16. 第16篇：變量詳解
  17. 第17篇：存儲過程&自定義函數詳解
  18. 第18篇：流程控制語句
  19. 第19篇：游標詳解
  20. 第20篇：異常捕獲及處理詳解
  21. 第21篇：什麼是索引？

mysql系列大概有20多篇，喜歡的請關注一下，歡迎大家加我微信itsoku或者留言交流mysql相關技術!

VirMach 便宜 VPS

QNews

QNews