玩转Mysql系列 - 第20篇：异常捕获及处理详解_mysql存储过程异常捕获
赞
踩
/开启事务/
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)
也正常。
总结
-
异常分为Mysql内部异常和外部异常
-
内部异常由mysql内部触发，外部异常是sql的执行结果和期望结果不一致导致的错误
-
sql内部异常捕获方式
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;
-
ROW_COUNT()可以获取mysql中insert或者update影响的行数 -
掌握使用乐观锁（添加版本号）来解决并发修改数据可能出错的问题
-
begin end前面可以加标签，LEAVE 标签可以退出对应的begin end，可以使用这个来实现return的效果
Mysql系列目录
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）
总结
一般像这样的大企业都有好几轮面试，所以自己一定要花点时间去收集整理一下公司的背景，公司的企业文化，俗话说「知己知彼百战不殆」，不要盲目的去面试，还有很多人关心怎么去跟HR谈薪资。
这边给大家一个建议，如果你的理想薪资是30K，你完全可以跟HR谈33~35K，而不是一下子就把自己的底牌暴露了出来，不过肯定不能说的这么直接，比如原来你的公司是25K，你可以跟HR讲原来的薪资是多少，你们这边能给到我的是多少？你说我这边希望可以有一个20%涨薪。
最后再说几句关于招聘平台的，总之，简历投递给公司之前，请确认下这家公司到底咋样，先去百度了解下，别被坑了，每个平台都有一些居心不良的广告党等着你上钩，千万别上当！！！
Java架构学习资料，学习技术内容包含有：Spring，Dubbo，MyBatis, RPC, 源码分析，高并发、高性能、分布式,性能优化，微服务 高级架构开发等等。
还有Java核心知识点+全套架构师学习资料和视频+一线大厂面试宝典+面试简历模板可以领取+阿里美团网易腾讯小米爱奇艺快手哔哩哔哩面试题+Spring源码合集+Java架构实战电子书。
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
https://img-blog.csdnimg.cn/img_convert/e9bd0688dd403c466b91fc56b5d4eb03.jpeg" alt=“img” style=“zoom: 33%;” />
总结
一般像这样的大企业都有好几轮面试，所以自己一定要花点时间去收集整理一下公司的背景，公司的企业文化，俗话说「知己知彼百战不殆」，不要盲目的去面试，还有很多人关心怎么去跟HR谈薪资。
这边给大家一个建议，如果你的理想薪资是30K，你完全可以跟HR谈33~35K，而不是一下子就把自己的底牌暴露了出来，不过肯定不能说的这么直接，比如原来你的公司是25K，你可以跟HR讲原来的薪资是多少，你们这边能给到我的是多少？你说我这边希望可以有一个20%涨薪。
最后再说几句关于招聘平台的，总之，简历投递给公司之前，请确认下这家公司到底咋样，先去百度了解下，别被坑了，每个平台都有一些居心不良的广告党等着你上钩，千万别上当！！！
Java架构学习资料，学习技术内容包含有：Spring，Dubbo，MyBatis, RPC, 源码分析，高并发、高性能、分布式,性能优化，微服务 高级架构开发等等。
还有Java核心知识点+全套架构师学习资料和视频+一线大厂面试宝典+面试简历模板可以领取+阿里美团网易腾讯小米爱奇艺快手哔哩哔哩面试题+Spring源码合集+Java架构实战电子书。
[外链图片转存中…(img-T8hIGX4R-1713756335912)]
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
