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

Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。

这是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. /*切换到javacode2018库*/
  5. use javacode2018;
  6. DROP TABLE IF EXISTS test1;
  7. 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 OK0 rows affected (0.00 sec)
  3. mysql> CALL proc1(1,1);
  4. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  5. mysql> SELECT * from test1;
  6. +---+
  7. | a |
  8. +---+
  9. 1 |
  10. +---+
  11. 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.     START TRANSACTION;
  14.     INSERT INTO test1(a) VALUES (a1);
  15.     INSERT INTO test1(a) VALUES (a2);
  16.     /*根据hasSqlError判断是否有异常,做回滚和提交操作*/
  17.     IF hasSqlError THEN
  18.       ROLLBACK;
  19.     ELSE
  20.       COMMIT;
  21.     END IF;
  22.   END $
  23. /*结束符置为;*/
  24. DELIMITER ;
上面重点是这句:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

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

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

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

模拟正常情况:
  1. mysql> DELETE FROM test1;
  2. Query OK0 rows affected (0.00 sec)
  3. mysql> CALL proc2(1,2);
  4. Query OK0 rows affected (0.00 sec)
  5. mysql> SELECT * from test1;
  6. +---+
  7. | a |
  8. +---+
  9. 1 |
  10. 2 |
  11. +---+
  12. 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,2NOT 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,2NOT 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.     /*1.查询余额,判断余额是否够*/
  10.     select a.available into v_available from t_funds a where a.user_id = v_user_id;
  11.     if v_available<=v_price THEN
  12.       SET v_msg='账户余额不足!';
  13.       /*退出*/
  14.       LEAVE a;
  15.     END IF;
  16.     /*模拟耗时5秒*/
  17.     SELECT sleep(5);
  18.     /*2.余额减去price*/
  19.     SET v_available = v_available - v_price;
  20.     /*3.更新余额*/
  21.     START TRANSACTION;
  22.     UPDATE t_funds SET available = v_available WHERE user_id = v_user_id;
  23.     /*插入订单明细*/
  24.     INSERT INTO t_order (price) VALUES (v_price);
  25.     /*提交事务*/
  26.     COMMIT;
  27.     SET v_msg='下单成功!';
  28.   END $
  29. /*结束符置为;*/
  30. 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. mysql> SELECT * FROM t_order;
  9. +----+--------+
  10. | id | price  |
  11. +----+--------+
  12. |  1 | 100.00 |
  13. |  2 | 100.00 |
  14. +----+--------+
  15. 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,2NOT NULL DEFAULT 0 COMMENT '账户余额',
  5.   version INT DEFAULT 0 COMMENT '版本号,每次更新+1'
  6. COMMENT '用户账户表';
  7. DROP TABLE IF EXISTS t_order;
  8. CREATE TABLE t_order(
  9.   id int PRIMARY KEY AUTO_INCREMENT COMMENT '订单id',
  10.   price DECIMAL(10,2NOT NULL DEFAULT 0 COMMENT '订单金额'
  11. )COMMENT '订单表';
  12. delete from t_funds;
  13. /*插入一条数据,用户id为1001,余额为1000*/
  14. 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.     /*1.查询余额,判断余额是否够*/
  15.     select a.available,a.version into v_available,v_version from t_funds a where a.user_id = v_user_id;
  16.     if v_available<=v_price THEN
  17.       SET v_msg='账户余额不足!';
  18.       /*退出*/
  19.       LEAVE a;
  20.     END IF;
  21.     /*模拟耗时5秒*/
  22.     SELECT sleep(5);
  23.     /*2.余额减去price*/
  24.     SET v_available = v_available - v_price;
  25.     /*3.更新余额*/
  26.     START TRANSACTION;
  27.     UPDATE t_funds SET available = v_available WHERE user_id = v_user_id AND version = v_version;
  28.     /*获取上面update影响行数*/
  29.     select ROW_COUNT() INTO v_update_count;
  30.     IF v_update_count=1 THEN
  31.       /*插入订单明细*/
  32.       INSERT INTO t_order (price) VALUES (v_price);
  33.       SET v_msg='下单成功!';
  34.       /*提交事务*/
  35.       COMMIT;
  36.     ELSE
  37.       SET v_msg='下单失败,请重试!';
  38.       /*回滚事务*/
  39.       ROLLBACK;
  40.     END IF;
  41.   END $
  42. /*结束符置为;*/
  43. 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. Query OK0 rows affected (5.00 sec)
  9. mysql> select @v_msg;
  10. +---------------+
  11. | @v_msg        |
  12. +---------------+
  13. | 下单成功!     |
  14. +---------------+
  15. 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. Query OK0 rows affected (5.01 sec)
  9. mysql> select @v_msg;
  10. +-------------------------+
  11. | @v_msg                  |
  12. +-------------------------+
  13. | 下单失败,请重试!        |
  14. +-------------------------+
  15. 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. mysql> SELECT * FROM t_order;
  9. +----+--------+
  10. | id | price  |
  11. +----+--------+
  12. |  1 | 100.00 |
  13. +----+--------+
  14. 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篇:游标详解

mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!

路人甲java

640?wx_fmt=png

▲长按图片识别二维码关注

路人甲java:工作10年的前阿里P7分享Java、算法、数据库方面的技术干货!坚信用技术改变命运,让家人过上更体面的生活

来源:https://itsoku.blog.csdn.net/article/details/102383367
posted @ 2022-04-23 23:12  程序员小明1024  阅读(266)  评论(0编辑  收藏  举报