• 玩转Mysql系列 - 第20篇：异常捕获及处理详解

    这是Mysql系列第20篇。

    环境：mysql5.7.25，cmd命令中进行演示。

    代码中被[]包含的表示可选，|符号分开的表示可选其一。

    需求背景

    我们在写存储过程的时候，可能会出现下列一些情况：

    1. 插入的数据违反唯一约束，导致插入失败

    2. 插入或者更新数据超过字段最大长度，导致操作失败

    3. update影响行数和期望结果不一致

    遇到上面各种异常情况的时，可能需要我们能够捕获，然后可能需要回滚当前事务。

    本文主要围绕异常处理这块做详细的介绍。

    此时我们需要使用游标，通过游标的方式来遍历select查询的结果集，然后对每行数据进行处理。

    本篇内容

    • 异常分类详解

    • 内部异常详解

    • 外部异常详解

    • 掌握乐观锁解决并发修改数据出错的问题

    • update影响行数和期望结果不一致时的处理

    准备数据

    创建库：javacode2018

    创建表：test1，test1表中的a字段为主键。

    1. /*建库javacode2018*/
    2. drop database if exists javacode2018;
    3. create database javacode2018;
    4.  
    5. /*切换到javacode2018库*/
    6. use javacode2018;
    7.  
    8. DROP TABLE IF EXISTS test1;
    9. 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条数据，并且放在一个事务中执行，最终要么都插入成功，要么都失败。

    创建存储过程：
    1. /*删除存储过程*/
    2. DROP PROCEDURE IF EXISTS proc1;
    3. /*声明结束符为$*/
    4. DELIMITER $
    5. /*创建存储过程*/
    6. CREATE PROCEDURE proc1(a1 int,a2 int)
    7.   BEGIN
    8.     START TRANSACTION;
    9.     INSERT INTO test1(a) VALUES (a1);
    10.     INSERT INTO test1(a) VALUES (a2);
    11.     COMMIT;
    12.   END $
    13. /*结束符置为;*/
    14. DELIMITER ;

    上面存储过程插入了两条数据，a的值都是1。

    验证结果：
    1. mysql> DELETE FROM test1;
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> CALL proc1(1,1);
    5. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    6. mysql> SELECT * from test1;
    7. +---+
    8. | a |
    9. +---+
    10. | 1 |
    11. +---+
    12. 1 row in set (0.00 sec)

    上面先删除了test1表中的数据，然后调用存储过程proc1，由于test1表中的a字段是主键，插入第二条数据时违反了a字段的主键约束，mysql内部抛出了异常，导致第二条数据插入失败，最终只有第一条数据插入成功了。

    上面的结果和我们期望的不一致，我们希望要么都插入成功，要么失败。

    那我们怎么做呢？我们需要捕获上面的主键约束异常，然后发现有异常的时候执行rollback回滚操作，改进上面的代码，看下面示例2。

    示例2

    我们对上面示例进行改进，捕获上面主键约束异常，然后进行回滚处理，如下：

    创建存储过程：
    1. /*删除存储过程*/
    2. DROP PROCEDURE IF EXISTS proc2;
    3. /*声明结束符为$*/
    4. DELIMITER $
    5. /*创建存储过程*/
    6. CREATE PROCEDURE proc2(a1 int,a2 int)
    7.   BEGIN
    8.     /*声明一个变量，标识是否有sql异常*/
    9.     DECLARE hasSqlError int DEFAULT FALSE;
    10.     /*在执行过程中出任何异常设置hasSqlError为TRUE*/
    11.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;
    12.  
    13.     /*开启事务*/
    14.     START TRANSACTION;
    15.     INSERT INTO test1(a) VALUES (a1);
    16.     INSERT INTO test1(a) VALUES (a2);
    17.  
    18.     /*根据hasSqlError判断是否有异常，做回滚和提交操作*/
    19.     IF hasSqlError THEN
    20.       ROLLBACK;
    21.     ELSE
    22.       COMMIT;
    23.     END IF;
    24.   END $
    25. /*结束符置为;*/
    26. DELIMITER ;
    上面重点是这句：
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

    当有sql异常的时候，会将变量hasSqlError的值置为TRUE。

    模拟异常情况：
    1. mysql> DELETE FROM test1;
    2. Query OK, 2 rows affected (0.00 sec)
    3.  
    4. mysql> CALL proc2(1,1);
    5. Query OK, 0 rows affected (0.00 sec)
    6.  
    7. mysql> SELECT * from test1;
    8. Empty set (0.00 sec)

    上面插入了2条一样的数据，插入失败，可以看到上面test1表无数据，和期望结果一致，插入被回滚了。

    模拟正常情况：
    1. mysql> DELETE FROM test1;
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> CALL proc2(1,2);
    5. Query OK, 0 rows affected (0.00 sec)
    6.  
    7. mysql> SELECT * from test1;
    8. +---+
    9. | a |
    10. +---+
    11. | 1 |
    12. | 2 |
    13. +---+
    14. 2 rows in set (0.00 sec)

    上面插入了2条不同的数据，最终插入成功。

    外部异常

    外部异常不是由mysql内部抛出的错误，而是由于sql的执行结果和我们期望的结果不一致的时候，我们需要对这种情况做一些处理，如回滚操作。

    示例1

    我们来模拟电商中下单操作，按照上面的步骤来更新账户余额。

    电商中有个账户表和订单表，如下：
    1. DROP TABLE IF EXISTS t_funds;
    2. CREATE TABLE t_funds(
    3.   user_id INT PRIMARY KEY COMMENT '用户id',
    4.   available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '账户余额'
    5. ) COMMENT '用户账户表';
    6. DROP TABLE IF EXISTS t_order;
    7. CREATE TABLE t_order(
    8.   id int PRIMARY KEY AUTO_INCREMENT COMMENT '订单id',
    9.   price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '订单金额'
    10. ) COMMENT '订单表';
    11. delete from t_funds;
    12. /*插入一条数据，用户id为1001，余额为1000*/
    13. INSERT INTO t_funds (user_id,available) VALUES (1001,1000);
    下单操作涉及到操作上面的账户表，我们用存储过程来模拟实现：
    1. /*删除存储过程*/
    2. DROP PROCEDURE IF EXISTS proc3;
    3. /*声明结束符为$*/
    4. DELIMITER $
    5. /*创建存储过程*/
    6. CREATE PROCEDURE proc3(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64))
    7.   a:BEGIN
    8.     DECLARE v_available DECIMAL(10,2);
    9.  
    10.     /*1.查询余额，判断余额是否够*/
    11.     select a.available into v_available from t_funds a where a.user_id = v_user_id;
    12.     if v_available<=v_price THEN
    13.       SET v_msg='账户余额不足!';
    14.       /*退出*/
    15.       LEAVE a;
    16.     END IF;
    17.  
    18.     /*模拟耗时5秒*/
    19.     SELECT sleep(5);
    20.  
    21.     /*2.余额减去price*/
    22.     SET v_available = v_available - v_price;
    23.  
    24.     /*3.更新余额*/
    25.     START TRANSACTION;
    26.     UPDATE t_funds SET available = v_available WHERE user_id = v_user_id;
    27.  
    28.     /*插入订单明细*/
    29.     INSERT INTO t_order (price) VALUES (v_price);
    30.  
    31.     /*提交事务*/
    32.     COMMIT;
    33.     SET v_msg='下单成功!';
    34.   END $
    35. /*结束符置为;*/
    36. DELIMITER ;

    上面过程主要分为3步骤：验证余额、修改余额变量、更新余额。

    开启2个cmd窗口，连接mysql，同时执行下面操作：
    1. USE javacode2018;
    2. CALL proc3(1001,100,@v_msg);
    3. select @v_msg;
    然后执行：
    1. mysql> SELECT * FROM t_funds;
    2. +---------+-----------+
    3. | user_id | available |
    4. +---------+-----------+
    5. |    1001 |    900.00 |
    6. +---------+-----------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> SELECT * FROM t_order;
    10. +----+--------+
    11. | id | price  |
    12. +----+--------+
    13. |  1 | 100.00 |
    14. |  2 | 100.00 |
    15. +----+--------+
    16. 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进行优化。

    创建表：
    1. DROP TABLE IF EXISTS t_funds;
    2. CREATE TABLE t_funds(
    3.   user_id INT PRIMARY KEY COMMENT '用户id',
    4.   available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '账户余额',
    5.   version INT DEFAULT 0 COMMENT '版本号，每次更新+1'
    6. ) COMMENT '用户账户表';
    7.  
    8. DROP TABLE IF EXISTS t_order;
    9. CREATE TABLE t_order(
    10.   id int PRIMARY KEY AUTO_INCREMENT COMMENT '订单id',
    11.   price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '订单金额'
    12. )COMMENT '订单表';
    13. delete from t_funds;
    14. /*插入一条数据，用户id为1001，余额为1000*/
    15. INSERT INTO t_funds (user_id,available) VALUES (1001,1000);
    创建存储过程：
    1. /*删除存储过程*/
    2. DROP PROCEDURE IF EXISTS proc4;
    3. /*声明结束符为$*/
    4. DELIMITER $
    5. /*创建存储过程*/
    6. CREATE PROCEDURE proc4(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64))
    7.     a:BEGIN
    8.     /*保存当前余额*/
    9.     DECLARE v_available DECIMAL(10,2);
    10.     /*保存版本号*/
    11.     DECLARE v_version INT DEFAULT 0;
    12.     /*保存影响的行数*/
    13.     DECLARE v_update_count INT DEFAULT 0;
    14.  
    15.  
    16.     /*1.查询余额，判断余额是否够*/
    17.     select a.available,a.version into v_available,v_version from t_funds a where a.user_id = v_user_id;
    18.     if v_available<=v_price THEN
    19.       SET v_msg='账户余额不足!';
    20.       /*退出*/
    21.       LEAVE a;
    22.     END IF;
    23.  
    24.     /*模拟耗时5秒*/
    25.     SELECT sleep(5);
    26.  
    27.     /*2.余额减去price*/
    28.     SET v_available = v_available - v_price;
    29.  
    30.     /*3.更新余额*/
    31.     START TRANSACTION;
    32.     UPDATE t_funds SET available = v_available WHERE user_id = v_user_id AND version = v_version;
    33.     /*获取上面update影响行数*/
    34.     select ROW_COUNT() INTO v_update_count;
    35.  
    36.     IF v_update_count=1 THEN
    37.       /*插入订单明细*/
    38.       INSERT INTO t_order (price) VALUES (v_price);
    39.       SET v_msg='下单成功!';
    40.       /*提交事务*/
    41.       COMMIT;
    42.     ELSE
    43.       SET v_msg='下单失败,请重试!';
    44.       /*回滚事务*/
    45.       ROLLBACK;
    46.     END IF;
    47.   END $
    48. /*结束符置为;*/
    49. DELIMITER ;

    ROW_COUNT()可以获取更新或插入后获取受影响行数。将受影响行数放在v_update_count中。

    然后根据v_update_count是否等于1判断更新是否成功，如果成功则记录订单信息并提交事务，否则回滚事务。

    验证结果：开启2个cmd窗口，连接mysql，执行下面操作：
    1. use javacode2018;
    2. CALL proc4(1001,100,@v_msg);
    3. select @v_msg;
    窗口1结果：
    1. mysql> CALL proc4(1001,100,@v_msg);
    2. +----------+
    3. | sleep(5) |
    4. +----------+
    5. |        0 |
    6. +----------+
    7. 1 row in set (5.00 sec)
    8.  
    9. Query OK, 0 rows affected (5.00 sec)
    10.  
    11. mysql> select @v_msg;
    12. +---------------+
    13. | @v_msg        |
    14. +---------------+
    15. | 下单成功!     |
    16. +---------------+
    17. 1 row in set (0.00 sec)
    窗口2结果：
    1. mysql> CALL proc4(1001,100,@v_msg);
    2. +----------+
    3. | sleep(5) |
    4. +----------+
    5. |        0 |
    6. +----------+
    7. 1 row in set (5.00 sec)
    8.  
    9. Query OK, 0 rows affected (5.01 sec)
    10.  
    11. mysql> select @v_msg;
    12. +-------------------------+
    13. | @v_msg                  |
    14. +-------------------------+
    15. | 下单失败,请重试!        |
    16. +-------------------------+
    17. 1 row in set (0.00 sec)

    可以看到第一个窗口下单成功了，窗口2下单失败了。

    再看一下2个表的数据：

    1. mysql> SELECT * FROM t_funds;
    2. +---------+-----------+---------+
    3. | user_id | available | version |
    4. +---------+-----------+---------+
    5. |    1001 |    900.00 |       0 |
    6. +---------+-----------+---------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> SELECT * FROM t_order;
    10. +----+--------+
    11. | id | price  |
    12. +----+--------+
    13. |  1 | 100.00 |
    14. +----+--------+
    15. 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的效果

  • 相关阅读:
    Python利用OpenGL和GLFW显示纹理贴图的正方形（Win11）
    MyBatis 进阶
    计算机毕业设计ssm高校OA办公管理平台jnpxm系统+程序+源码+lw+远程部署
    入门力扣自学笔记204 C++ （题目编号：1742）
    Webpack面试题总结
    [Python学习篇] Python列表
    30天Python入门（第三天：深入了解Python中的运算符）
    23111707[含文档+PPT+源码等]计算机毕业设计基于javawebmysql的旅游网址前后台-全新项目
    计算机硬件的读写速度差异
    给一百万张图片批量添加水印
  • 原文地址：https://blog.csdn.net/weixin_46228112/article/details/132813180