版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 本文链接：https://blog.csdn.net/likun557/article/details/102383367 Mysql系列的目标是：通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。
欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。
这是Mysql系列第20篇。
环境：mysql5.7.25，cmd命令中进行演示。
代码中被[]包含的表示可选，|符号分开的表示可选其一。
需求背景 我们在写存储过程的时候，可能会出现下列一些情况：
插入的数据违反唯一约束，导致插入失败
插入或者更新数据超过字段最大长度，导致操作失败
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) 也正常。
总结 异常分为Mysql内部异常和外部异常
内部异常由mysql内部触发，外部异常是sql的执行结果和期望结果不一致导致的错误
sql内部异常捕获方式
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE; ROW_COUNT()可以获取mysql中insert或者update影响的行数
掌握使用乐观锁（添加版本号）来解决并发修改数据可能出错的问题
begin end前面可以加标签，LEAVE 标签可以退出对应的begin end，可以使用这个来实现return的效果
Mysql系列目录 第1篇：mysql基础知识
第2篇：详解mysql数据类型（重点）
第3篇：管理员必备技能(必须掌握)
第4篇：DDL常见操作
第5篇：DML操作汇总（insert,update,delete）
第6篇：select查询基础篇
第7篇：玩转select条件查询，避免采坑
第8篇：详解排序和分页(order by & limit)
第9篇：分组查询详解（group by & having）
第10篇：常用的几十个函数详解
第11篇：深入了解连接查询及原理
第12篇：子查询
第13篇：细说NULL导致的神坑，让人防不胜防
第14篇：详解事务
第15篇：详解视图
第16篇：变量详解
第17篇：存储过程&自定义函数详解
第18篇：流程控制语句
第19篇：游标详解
mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!
路人甲java
▲长按图片识别二维码关注
路人甲java：工作10年的前阿里P7分享Java、算法、数据库方面的技术干货！坚信用技术改变命运，让家人过上更体面的生活 ———————————————— 版权声明：本文为博主「路人甲Java」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/likun557/article/details/102383367
