MySQL事务及触发器

63 阅读 0 评论 42 点赞

我是靠谱客的博主畅快信封，最近开发中收集的这篇文章主要介绍MySQL事务及触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

事务

create table swpu(
id int primary key auto_increment,
money decimal(10,2) comment '学费'
);
insert into swpu values(null,5000);
create table swpu_stu(
id int primary key auto_increment,
stu_money decimal(10,2) comment '学生财产'
);
insert into swpu_stu values(null,7000);

select * from swpu;
select * from swpu_stu;

例如，学生交杂物费100元给学校

update swpu_stu set stu_money=stu_money-100 where id=1;
update swpu set money=money+100 where id=1;
select * from swpu;
select * from swpu_stu;

事务：如果整组成功，意味着全部sql都实现。如果其中任何一个失败，意味着整个操作都失败。失败，意味着整个过程都是没有意义的。应该使数据库回到操作前的初始状态。

如何处理？
1、失败后，可以回到开始位置。
2、没都成功之前，别的用户（进程，会话）是不能看到操作内的数据修改的。

具体思路：
就是在一组操作之前，设计一个记号（备份点）。
如果执行成功，让别人看到数据的修改。
如果执行失败，别人不能看到，应该回到记号位置。

实现：
使用innodb存储引擎的事务日志功能。
如果成功，则将结果提交到数据库内。意味着就更改了数据库内容。

SQL的执行的2个阶段：
1、执行阶段
2、将执行结果，提交到数据库阶段，其中我们的事务日志就是保存执行阶段的结果。如果用于选择提交，则才将执行结果提交到数据库中。

默认的执行方式叫自动提交。执行完毕，自动完成提交工作。因此需要关闭自动提交功能。

存在一个系统的变量autocommit，可以对自动提交进行配置。

set autocommit=0;
show variables like 'autocommit';

关闭后再次执行相应的更新语句，发现在其他的连接中查看数据时，没有发生变化，因为结果没有提交。

提交或回滚

在此基础上，执行完所有的sql语句。判断是否成功（出现错误，包括语法错误和逻辑错误，服务器错误）。
成功：将结果提交。利用commit。
失败：回到开始位置。利用rollback。

update swpu_stu set stu_money=stu_money-100 where id=1;
update swpu set money=money+100 where id=1;
select * from swpu;
select * from swpu_stu;
commit;
select * from swpu;
select * from swpu_stu;

提交之前：

提交之后：

需要手动改为自动提交。

set autocommit=1;

常见的事务指令

开启事务
start transcation;
关闭自动提交，如果事务结束了（成功或失败），都会回到自动提交机制，回到start时的状态。
成功：commit;
失败：rollback;

start transcation;
update swpu_stu set stu_money=stu_money-100 where id=1;
update swpu set money=money+100 where id=1;
select * from swpu;
select * from swpu_stu;
commit;
select * from swpu;
select * from swpu_stu;

事务限定：在innodb(DBD)存储引擎下生效。

注意：要求start transcation;开启事务功能。而不用set autocommit=0;开启事务功能。

事务的特点ACID：

1、原子性
2、一致性（从开始到结束，数据不会受到事务之外其他语句的改变。）
3、隔离性（一个事务的操作不会影响另一个事务）
4、持久性（修改永久生效）

触发器

触发器：监听数据进行操作。
在当前表上，设置一个对每行数据的一个监听器，监听相关事件。
每当事件发生时，会执行一段由sql完成的一段功能代码。

触发器的元素：事件，执行代码。

创建触发器

create trigger 名字事件执行性代码

事件：
插入 insert
删除 delete
修改 update

时间的时机：执行之前和执行之后（after before）。
由时机和事件在一起形成了六种事件。

before insert，before delete，before update
after insert，after delete，after update

事件规定：在那个表上的什么时机的什么动作上。

可执行代码：
SQL语句组成的代码。

create trigger name_jiaoxuefei after update  on swpu_stu 
for each row
update swpu set money=money+150;

select * from swpu;
select * from swpu_stu;

触发，触发程序：特定时间发生，即触发。

update swpu_stu set stu_money=stu_money-150 where id=1;

注意：
触发器不能同名。
目前mysql只支持一类事件，设置一个触发器。

create trigger name_jiaoxuefei2 after update  on swpu_stu 
for each row
update swpu set money=money-150;

管理触发器：

删除：drop

drop trigger trigger_name;

drop trigger name_jiaoxuefei;

查看：show

show create trigger trigger_name;

show create trigger name_jiaoxuefei;

在触发器内，获得触发给触发程序时的数据。利用触发程序内的new和old来完成。

Old：监听事件所在表上的数据，在事件发生之前时的数据，旧的数据。

New：监听表上，事件发生之后，新处理完毕的数据。数据就是触发该事件的记录。

事件update可以使用new和old

update swpu_stu set stu_money=stu_money-150 where id=1;

//1000 950
(old.stu_money-new.stu_money)

drop trigger name_jiaoxuefei;
create trigger name_jiaoxuefei after update  on swpu_stu 
for each row
update swpu set money=money+(old.stu_money-new.stu_money);

update swpu_stu set stu_money=stu_money-1500 where id=1;

事件是insert呢？不能使用old
alter table swpu add stu_count int default 0;

create trigger name_kaixue after insert on swpu_stu
for each row
update swpu set stu_count = stu_count+1;

drop trigger name_kaixue;
create trigger name_kaixue after insert on swpu_stu
for each row
update swpu set stu_count = stu_count+new.id;

事件是delete呢？不能使用new

create trigger name_biye after insert on swpu_stu
for each row
delete swpu set stu_count = stu_count-old.id;

如果一个触发程序由多条sql语句组成。应该满足如下：
1、语句组成语句块（begin end）用来标识语句块。
2、语句块的语句需要独立的语句结束符，分号。

由于触发器程序内使用分号作为语句的结束符，那么当命令行客户端碰到分号时，就应该理解成触发程序内的子语句结束，而不是整个创建触发器的语句结束。
因此，应该通过修改命令行的语句结束符达到目的。
delimiter语句可以完成设置语句结束符。

别忘了修改完之后，再改回去。

drop trigger name_kaixue;
delimiter $$
create trigger name_kaixue after insert on swpu_stu
for each row
begin
update swpu set stu_count = stu_count+1;
update swpu set money = money+100;
end
$$

delimiter ;

insert into swpu_stu values(2,2000);
select * from swpu;