mysql 捕捉最新更新的数据_MySQL异常捕获及处理详情

最新推荐文章于 2024-04-22 11:25:51 发布

weixin_40007804

最新推荐文章于 2024-04-22 11:25:51 发布

阅读量305

点赞数

文章标签： mysql 捕捉最新更新的数据

本文链接：https://blog.csdn.net/weixin_40007804/article/details/114334487

版权

代码中被[]包含的表示可选，|符号分开的表示可选其一。

应用场景

写存储过程中，可能出现

插入数据违反唯一约束，导致插入失败

插入或者更新数据超过字段最大长度，导致操作失败

update影响行数和期望结果不一致

异常分类

mysql内部异常和外部异常

mysql内部异常

当我们执行些sql时候，可能违反mysql的一些约束，导致mysql内部报错。插入数据违反唯一约束，更新数据超时等，mysql内部抛出的，这些由mysql抛出的异常称为内部异常。

外部异常

执行update时候，可能我们期望影响1行，但实际影响的不是1行数据。sql执行结果和期望的结果不一致，我们称为外部异常处理，将sql执行结果和期望结果不一致称为外部异常。

Mysql内部异常

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 ;

-- 调用存储函数

CALL procl(1,1);

由于test1表中的a字段是主键，插入第二条数据时违反了a字段的主键约束,mysql内部抛出了异常，导致第二条数据插入失败，最终只有第一条数据插入成功了。

/*删除存储过程*/

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 。

CALL proc2(1,1); -- 数据会回滚

CALL proc2(1,2); -- 执行成功

外部异常

外部异常，由于sql的执行结果和我们期望的结果不一致时候，我们需要回滚

-- 电商中有个账户表和订单表，如下：

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 ;

-- 开启2个cmd窗⼝，连接mysql，同时执行下面操作：

USE javacode2018;

CALL proc3(1001,100,@v_msg);

select @v_msg;

-- 然后执行：

SELECT * FROM t_funds;

+---------+-----------+

| user_id | available |

+---------+-----------+

| 1001 | 900.00 |

+---------+-----------+

1 row in set (0.00 sec)

SELECT * FROM t_order;

+----+--------+

| id | price |

+----+--------+

| 1 | 100.00 |

| 2 | 100.00 |

+----+--------+

2 rows in set (0.00 sec)

-- 上面出现了非常严重的错误：下单成功了2次，但是账户只扣了100。

-- 上面过程是由于2个操作并发导致的，2个窗⼜同时执行第一步的时候看到了一样的数据(看到的余额都是1000)，然后继续向下执行，最终导致结果出问题了。

乐观锁优化：用期望的值和目标值进行比较，如果相同，则更新目标值，否则什么也不做。

在资金表 t_funds 添加一个 version 字段，表示版本号，每次更新数据的时候+1，更新数据的时候将version作为条件去执行update，根据update影响行数来判断执行是否成功

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结果

CALL proc4(1001,100,@v_msg);

+----------+

| sleep(5) |

+----------+

| 0 |

+----------+

1 row in set (5.00 sec)

select @v_msg;

+---------------+

| @v_msg |

+---------------+

| 下单成功! |

+---------------+

1 row in set (0.00 sec)

-- 窗口2

CALL proc4(1001,100,@v_msg);

+----------+

| sleep(5) |

+----------+

| 0 |

+----------+

1 row in set (5.00 sec)

select @v_msg;

+-------------------------+

| @v_msg |

+-------------------------+

| 下单失败,请重试! |

+-------------------------+

1 row in set (0.00 sec)

-- 可以看到第一个窗口下单成功了，窗口2下单失败了。

SELECT * FROM t_funds;

+---------+-----------+---------+

| user_id | available | version |

+---------+-----------+---------+

| 1001 | 900.00 | 0 |

+---------+-----------+---------+

1 row in set (0.00 sec)

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

来源：【公众号】大侠学JAVA

weixin_40007804

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 捕捉最新更新的数据_MySQL异常捕获及处理详情

代码中被[]包含的表示可选，|符号分开的表示可选其一。应用场景写存储过程中，可能出现插入数据违反唯一约束，导致插入失败插入或者更新数据超过字段最大长度，导致操作失败update影响行数和期望结果不一致异常分类mysql内部异常和外部异常mysql内部异常当我们执行些sql时候，可能违反mysql的一些约束，导致mysql内部报错。插入数据违反唯一约束，更新数据超时等，mysql内部抛出的，这些由m...
复制链接

扫一扫