可以做视频推广的网站,选择大连网站建设,上海大型网站设计公司,用手机做兼职的网站这是Mysql系列第20篇。
环境#xff1a;mysql5.7.25#xff0c;cmd命令中进行演示。
代码中被[]包含的表示可选#xff0c;|符号分开的表示可选其一。
需求背景
我们在写存储过程的时候#xff0c;可能会出现下列一些情况#xff1a; 插入的数据违反唯一约束#xff…这是Mysql系列第20篇。
环境mysql5.7.25cmd命令中进行演示。
代码中被[]包含的表示可选|符号分开的表示可选其一。
需求背景
我们在写存储过程的时候可能会出现下列一些情况 插入的数据违反唯一约束导致插入失败 插入或者更新数据超过字段最大长度导致操作失败 update影响行数和期望结果不一致
遇到上面各种异常情况的时可能需要我们能够捕获然后可能需要回滚当前事务。
本文主要围绕异常处理这块做详细的介绍。
此时我们需要使用游标通过游标的方式来遍历select查询的结果集然后对每行数据进行处理。
本篇内容 异常分类详解 内部异常详解 外部异常详解 掌握乐观锁解决并发修改数据出错的问题 update影响行数和期望结果不一致时的处理
准备数据
创建库javacode2018
创建表test1test1表中的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)BEGINSTART 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 hasSqlErrorTRUE;/*开启事务*/START TRANSACTION;INSERT INTO test1(a) VALUES (a1);INSERT INTO test1(a) VALUES (a2);/*根据hasSqlError判断是否有异常做回滚和提交操作*/IF hasSqlError THENROLLBACK;ELSECOMMIT;END IF;END $
/*结束符置为;*/
DELIMITER ;上面重点是这句
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlErrorTRUE;当有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:BEGINDECLARE 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_availablev_price THENSET 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_availablev_price THENSET 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_count1 THEN/*插入订单明细*/INSERT INTO t_order (price) VALUES (v_price);SET v_msg下单成功!;/*提交事务*/COMMIT;ELSESET 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 hasSqlErrorTRUE;ROW_COUNT()可以获取mysql中insert或者update影响的行数 掌握使用乐观锁添加版本号来解决并发修改数据可能出错的问题 begin end前面可以加标签LEAVE 标签可以退出对应的begin end可以使用这个来实现return的效果
