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

这篇具有很好参考价值的文章主要介绍了玩转Mysql系列 - 第20篇:异常捕获及处理详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

这是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的效果文章来源地址https://www.toymoban.com/news/detail-706493.html

到了这里,关于玩转Mysql系列 - 第20篇:异常捕获及处理详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 玩转Mysql系列 - 第17篇:存储过程&自定义函数详解

    这是Mysql系列第17篇。 环境:mysql5.7.25,cmd命令中进行演示。 代码中被[]包含的表示可选,|符号分开的表示可选其一。 需求背景介绍 线上程序有时候出现问题导致数据错误的时候,如果比较紧急,我们可以写一个存储来快速修复这块的数据,然后再去修复程序,这种方式我们

    2024年02月09日
    浏览(43)
  • 爬虫异常捕获与处理方法详解

    Hey!作为一名专业的爬虫代理供应商,我今天要和大家分享一些关于爬虫异常捕获与处理的方法。在进行爬虫操作时,我们经常会遇到各种异常情况,例如网络连接错误、请求超时、数据解析错误等等。这些异常情况可能会导致程序崩溃或数据丢失,因此,我们需要学会如何

    2024年02月11日
    浏览(43)
  • 玩转Mysql系列 - 第8篇:详解排序和分页(order by & limit),及存在的坑

    这是Mysql系列第7篇。 环境:mysql5.7.25,cmd命令中进行演示。 代码中被[]包含的表示可选,|符号分开的表示可选其一。 本章内容 详解排序查询 详解limit limit存在的坑 分页查询中的坑 排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此

    2024年02月11日
    浏览(43)
  • 玩转Mysql系列 - 第7篇:玩转select条件查询,避免采坑

    这是Mysql系列第7篇。 环境:mysql5.7.25,cmd命令中进行演示。 电商中:我们想查看某个用户所有的订单,或者想查看某个用户在某个时间段内所有的订单,此时我们需要对订单表数据进行筛选,按照用户、时间进行过滤,得到我们期望的结果。 此时我们需要使用条件查询来对

    2024年02月11日
    浏览(32)
  • 【C++】异常处理 ① ( 异常概念引入 | 抛出异常语法 | 捕获异常语法 | 异常捕获流程 | 异常处理代码示例 )

    异常是一种 特殊的程序流控制机制 , 用于处理程序中可能出现的错误或异常情况 ; 当程序执行错误时 , 由 throw 抛出异常 , 并即跳转到相应的异常处理程序中 ; 如果没有适当的异常处理程序处理该异常 , 程序会崩溃终止 ; 异常与函数对比 : 函数 是一种 以 栈结构 展开的

    2024年02月04日
    浏览(49)
  • 玩转Mysql系列 - 第6篇:select查询基础篇

    这是Mysql系列第6篇。 环境:mysql5.7.25,cmd命令中进行演示。 DQL(Data QueryLanguage):数据查询语言,通俗点讲就是从数据库获取数据的,按照DQL的语法给数据库发送一条指令,数据库将按需求返回数据。 DQL分多篇来说,本文属于第1篇。 基本语法 注意: select语句中不区分大小写

    2024年02月11日
    浏览(32)
  • 玩转MYSQL数据库之--视图详解

    从今天开始本系列文章就带各位小伙伴学习数据库技术。 数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。 非常适合零基础的小伙伴来学习。 全文大约 【1297】字 ,不说废话,只讲可以让你学到技术、

    2024年02月05日
    浏览(101)
  • java异常处理机制(二)之异常处理与捕获

    1 Error(错误): 是指程序无法处理的错误,表示运行应用程序时比较严重的问题。大多数错误与代码编写者执行的操作无关,而表示代码运行时JVM(Java 虚拟机)出现的问题。 2 异常(Exception): 是指在程序执行时由于程序处理逻辑上的错误而导致程序中断的一种指令流。通俗的

    2024年02月05日
    浏览(105)
  • Java 异常处理以及如何捕获和处理多个异常

    在Java中,我们使用异常处理程序组件try,catch和finally块来处理异常。 为了捕获和处理异常,我们将try...catch...finally代码块放置在可能产生异常的代码周围。finally块是可选的。 try...catch...finally的语法为: 可能会生成异常的代码放在try块中。 每个try块后面应紧跟着catch 或 fi

    2024年02月14日
    浏览(41)
  • 爬虫异常处理:异常捕获与容错机制设计

    作为一名专业的爬虫程序员,每天使用爬虫IP面对各种异常情况是我们每天都会遇到的事情。 在爬取数据的过程中,我们经常会遇到网络错误、页面结构变化、被反爬虫机制拦截等问题。在这篇文章中,我将和大家分享一些关于如何处理爬虫异常情况的经验和技巧。通过异常

    2024年02月11日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包