示例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的效果
