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

/开启事务/

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）

自我介绍一下，小编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)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！