MySQL数据库----事务
事务
-- 事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,
-- 即可回滚到原来的状态,从而保证数据库数据完整性。
-- 事务也就是要么都成功,要么都不成功
-- 事务就是由一堆sql语句组成的
create table user(
id int primary key auto_increment,
name char(32),
balance int #用户余额
);
insert into user(name,balance) values('海燕',200),
('哪吒',200),
('小哈',200);
-- 如果都成功就执行commit,,,如果不成功就执行rollback。
start transaction #开启事务
update user set balance = 100 where name = '海燕';
update user set balance = 210 where name = '哪吒';
update user set balance = 290 where name = '小哈'; #sql语句错误就会报错了
commit; #如果所有的sql语句都没有出现异常,应该执行commit
start transaction
update user set balance = 100 where name = '海燕';
update user set balance = 210 where name = '哪吒';
updatezzzz user set balance = 290 where name = '小哈'; #sql语句错误就会报错了
rollback; #如果任意一条sql出现异常,都应该回归到初始状态
上面的两种情况我们可以用异常处理捕捉一下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // create PROCEDURE p6( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; update user set balance = 100 where name = '海燕'; update user set balance = 210 where name = '哪吒'; update user11 set balance = 290 where name = '小哈'; COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功 END // delimiter ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
============捕捉异常+事务============== delimiter // create PROCEDURE p6( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; insert into test(username,dep_id) values('egon',1); DELETE from tb1111111; #如果执行失败,就不会执行commit了 COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功 END // delimiter ; #调用 set @res = 111 #相当于定义一个全局变量 call p6(@res) select * from test; select @res
![](https://images2017.cnblogs.com/blog/1184802/201709/1184802-20170919125416181-1642547018.png)
![](https://images2017.cnblogs.com/blog/1184802/201709/1184802-20170919125424946-1471976656.png)
其实也就相当于python中的try.....except
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#用python模拟 try: START TRANSACTION; DELETE from tb1; #执行失败 insert into blog(name,sub_time) values('yyy',now()); COMMIT; set p_return_code = 0; #0代表执行成功 except sqlexception: set p_return_code = 1; rollback; except sqlwaring: set p_return_code = 2; rollback;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步