MySQL触发器的详细教学与实战分析

所有知识系统文章,GitHub已收录,迎接老板们前来Star!

GitHub地址: https://github.com/Ziphtracks/JavaLearningmanual

MySQL触发器

一、什么是触发器

触发器(trigger)是MySQL提供给程序员和数据剖析员来保证数据完整性的一种方式,它是与表事宜相关的特殊的存储历程,它的执行不是由程序挪用,也不是手工启动,而是由事宜来触发,好比当对一个表举行操作(insert,delete, update)时就会激活它执行。简朴理解为:你执行一条sql语句,这条sql语句的执行会自动去触发执行其他的sql语句。

二、触发器的作用

  • 可在写入数据表前,强制磨练或转换数据。
  • 触发器发生错误时,异动的效果会被打消。
  • 部门数据库治理系统可以针对数据界说语言(DDL)使用触发器,称为DDL触发器。
  • 可遵照特定的情形,替换异动的指令 (INSTEAD OF)。

三、触发器建立的四要素

  • 监视地址(table)
  • 监视事宜(insert、update、delete)
  • 触发时间(after、before)
  • 触发事宜(insert、update、delete)

四、触发器的使用语法

语法:

before/after: 触发器是在增删改之前执行,照样之后执行

delete/insert/update: 触发器由哪些行为触发(增、删、改)

on 表名: 触发器监视哪张表的(增、删、改)操作

触发SQL代码块: 执行触发器包罗的SQL语句

1CREATE TRIGGER 触发器名
2BEFORE|AFTER DELETE|INSERT|UPDATE
3ON 表名 FOR EACH ROW
4BEGIN
5触发SQL代码块;
6END;

注重: 触发器也是存储历程程序的一种,而触发器内部的执行SQL语句是可以多行操作的,以是在MySQL的存储历程程序中,要界说竣事符。

若是MySQL存储历程不领会的小伙伴,可以参考此文面向MySQL存储历程编程,文章中详细解说了MySQL存储历程的优势和语法等等,信赖你会在其中得以收获。

1# 设置MySQL执行竣事标志,默以为;
2delimiter //

五、触发器的基本使用

5.1 基本使用步骤

首先,我先展示一下建立的两张表,由于建立的表很简朴,这里我没有提供库表操作的SQL下令。

tb_class

MySQL触发器的详细教学与实战分析 image-20200611205404311

employee

MySQL触发器的详细教学与实战分析 image-20200611205435284

其次,建立了一个含有update操作的存储历程

1delimiter //
2create procedure update_emp(in i intin p int)
3begin
4    update employee set phone = p where id = i;
5end //

再建立一个触发器

剖析: 触发器名称为t1,触发时间为after,监视动作为update,监视表为employee表。汇总一起注释这个触发器就是:建立一个触发器名称为t1的触发器,触发器监视employee表执行update(更新)操作后,就最先执行触发器内部SQL语句update tb_class set num = num + 1 where id = 1;

简朴来说就是一个监视一个表的增、删、改操作并设置操作前后时间,在设置时间的范围内对另外一个表举行其他操作。

若是你学到这里照样一知半解,后面我会解说一个订单与库存的数据关系,到那时刻你就会明了了!

 1delimiter //
2# 建立触发器,触发器名称为t1
3create trigger t1
4    # 触发器执行在update操作之后
5    after update
6    # 监视employee表
7    on employee
8    for each row
9begin
10    # 触发执行的SQL语句
11    update tb_class set num = num + 1 where id = 1;
12end //

最后挪用函数,并查看、剖析效果

1call update_emp(2110);

触发器在此场景的作用剖析

当employee表发生update操作时,触发器就对tb_class表中的num值做修改。

执行效果发现,我们在使用函数将employee表中id为2员工的phone修改为110后,触发器监视到employee表中发生了update更新操作,就执行了内部SQL语句,也就是将tb_class表中id为1的num值自增1。

MySQL触发器的详细教学与实战分析 image-20200611213411229
MySQL触发器的详细教学与实战分析 image-20200611213432459

5.2 查看和删除已有的触发器

查看已有触发器: show triggers

删除已有触发器: drop trigger 触发器名称

5.3 for each row

这里扩展,在oracle触发器中,触发器分为行触发器和语句触发器。也就是说,假设你监视一个修改操作,它修改了1000行代码,在Oracle中触发器会触发1000次。

在oracle中,for each row若是不写,无论update语句一次影响了若干行,都只执行一次触发事宜。

而MySQL中,不支持语句级触发器,以是在MySQL中并不需要在意。

六、订单与库存关系场景

订单与库存的关系: 用户下订单,意味着建立该商品订单,该商品订单中的商品数目为1,库存中的该商品数目-1。往往订单表和库存表中的数目是同时操作的,以是我们这里可以用触发器。

触发器应用: 关于订单表,下订单肯定是涉及到insert插入数据数目的操作。我们可以建立一个监视订单表insert操作后执行库存表数目-1的触发器来完成订单与库存表的同时修改。

建立表,并在表中添加几条数据:

 1create table goods(
2  gid int,
3  name varchar(20),
4  num smallint
5);
6create table ord(
7  oid int,
8  gid int,
9  much smallint
10);
11insert into goods values(1,'cat',40);
12insert into goods values(2,'dog',63);
13insert into goods values(3,'pig',87);

建立触发器

1create trigger t1 
2after
3insert
4on ord
5for each row
6begin
7 update goods set num = num - 1 where gid = 1;
8end$

该触发器意为,用户不管下什么订单,都会把商品编号为1的商品的库存减去1。

一网打尽枚举操作 .net core

七、触发器中引用行变量

7.1 old和new工具语法

  • 在触发目标上执行insert操作后会有一个新行,若是在触发事宜中需要用到这个新行的变量,可以用new关键字示意
  • 在触发目标上执行delete操作后会有一个旧行,若是在触发事宜中需要用到这个旧行的变量,可以用old关键字示意
  • 在触发目标上执行update操作后原纪录是旧行,新纪录是新行,可以使用new和old关键字来划分操作
触发语句 old new
insert 所有字段都为空 将要插入的数据
update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都为空

7.2 old和new工具应用

关于old和new工具的应用,我在这里没有睁开演示。只是将第八章的综合案例连系了old和new工具实现。综合案例中详细解说了MySQL触发器的使用!

八、综合案例

8.1 建立表、插入表数据

tb_class为幼儿园班级表,其中cid为唯一主键,cname为大、中、小班班级尺度,stuNo为班级尺度内的学生个数。插入大、中、小班尺度,初始化两名学生在买办。

tb_stu为幼儿园学生表,其中sid为唯一主键,sname为学生性名,cno为所在班级尺度的外键。插入两条数据并初始化这两名学生在买办,由于我们在班级表中初始化了两名学生在买办嘛,以是要做此操作。

 1create table tb_class
2(
3    cid   int auto_increment
4        primary key,
5    cname varchar(32not null,
6    stuNo int         not null
7);
8
9INSERT INTO temp.tb_class (cname, stuNo) VALUES ('买办'2)
10INSERT INTO temp.tb_class (cname, stuNo) VALUES ('中班'0)
11INSERT INTO temp.tb_class (cname, stuNo) VALUES ('小班'0)
12
13create table tb_stu
14(
15    sid   int auto_increment
16        primary key,
17    sname varchar(32not null,
18    cno   int         not null
19);
20
21INSERT INTO temp.tb_stu (sname, cno) VALUES ('Ziph'1)
22INSERT INTO temp.tb_stu (sname, cno) VALUES ('Join'1)

8.2 添加学生案例

在此表结构中,若是一位新同砚来到学校学习,意味着某一个班级中会多出一名学生。假设Marry同砚去小班学习,其表结构的转变为:tb_stu表中添加一条Marry的纪录(注:cno = 3),tb_class表中小班纪录的stuNo = 0修改为stuNo = 1

先建立一个添加学生的存储历程

1# 添加学生函数
2delimiter //
3# 建立存储历程,传入学生性名和班级参数
4create procedure add_stu(in in_sname varchar(32), in in_cno int)
5begin
6    # 插入纪录
7    insert into tb_stu (sname, cno) values (in_sname, in_cno);
8end //

建立触发器

注重: 在更新学生数目SQL语句中,有一段cid = new.cno的SQL语句。这里我注释一下,new代表发生的新工具,将cid主键与添加Marry纪录后发生的新纪录工具的cno外键关联。(由于insert后发生的是新纪录工具嘛,以是用new)

 1# 触发器
2# 建立名称为t_add_stu的触发器
3create trigger t_add_stu
4    # 设置在insert操作之后触发
5    after
6        insert
7    # 监视tb_stu的insert操作
8    on tb_stu
9    for each row
10begin
11    # 更新学生数目(cid为tb_class表中主键,cno为tb_stu表中外键)
12    update tb_class set stuNo = stuNo + 1 where cid = new.cno;
13end //

声明回竣事符

1delimiter ;

插入Marry学生纪录到数据库表中

1call add_stu('Marry'3);

执行效果就是当插入Marry学生纪录的同时也修改了班级表中的小班学生数目。

8.3 删除学生案例

删除学生与添加学生十分相似,删除学生相当于是添加学生的逆历程。若是以为学生退学了或者读完了幼儿园脱离学校了,就意味着班级中少了一位学生。假设Join同砚读完了买办竣事了幼儿园阶段的学习将要幼儿园去上小学,其表结构转变为:tb_stu删除Join这条纪录(注:sid = 2),tb_class将修改Join所在买办班级级别的stuNo,即stuNo = stuNo – 1

先建立一个删除学生的存储历程

1# 删除学生
2delimiter //
3create procedure delete_stu(in in_sid int)
4begin
5    delete from tb_stu where sid = in_sid;
6end //

建立触发器

注重: 在更新学生数目的时刻,书写了此段SQL语句cid = OLD.cno。该语句使用old工具,意为Join学生的纪录没有了,然则使用触发器同步修改tb_class表中的买办学生数目还需要用到关联Join学生所在纪录的外键cno,使用old来句点出来的cno就是删除之前Join那一条学生纪录的cno。(若是我们用new,该纪录还存在吗?该纪录的cno还存在吗?谜底是都不存在了!)

 1# 触发器
2# 建立触发器名称为t_delete_stu的触发器
3create trigger t_delete_stu
4    # 设置在delete操作之后触发
5    after
6        delete
7    # 监视tb_stu表的delete操作
8    on tb_stu
9    for each row
10begin
11    # 更新学生数目(cid为tb_class表中主键,cno为tb_stu表中外键)
12    update tb_class set stuNo = stuNo - 1 where cid = OLD.cno;
13end //

声明回竣事符

1delimiter ;

删除Jion学生纪录

1call delete_stu(2);

执行效果为Join纪录在数据库的表中消逝了,而买办的学生数目也减掉了1。

8.4 删除班级案例

由于我已经详细解说了添加学生与删除学生,以是删除班级我就不再作过多的赘述了。那就直接说核心内容吧。删除一个班级级别好比:删除小班之前要把小班内的所有学生也被删除了,由于两个表是主外键关联的。若是只删除了小班,而没有删除小班内的所有学生,那么原小班内的所有学生现在属于哪个班级呢,就不知道了吧!以是要在删除小班之前删除小班内的所有学生。

 1# 建立删除班级的存储历程
2delimiter //
3create procedure delete_class(in in_cid int)
4begin
5    delete from tb_class where cid = in_cid;
6end //
7
8# 建立触发器名称为t_delete_class的触发器
9create trigger t_delete_class
10    # 作用在delete操作之前
11    before
12        delete
13    # 监视tb_class表中的delete操作
14    on tb_class
15    for each row
16begin
17    # 同时删除所有该原班级cid的所有学生
18    delete from tb_stu where cno = OLD.cid;
19end //
20
21# 将竣事符声明为;
22delimiter ;
23
24# 删除小班班级别
25call delete_class(3);

执行效果为既删除了小班,又删除小班内的所有学生。

8.5 触发器冲突问题

触发器冲突问题实在就是关联问题。为什么这么说呢?就说以下适才这三个案例中泛起的触发器冲突问题。

若是我们在写触发器的时刻,将添加学生、删除学生和删除班级的触发器都写在一个查询模板中。你会发现当你在删除班级的时刻,会报错。显示如下信息:

MySQL触发器的详细教学与实战分析 image-20200612004546204

这是为什么呢?

仔细想想,我们将在案例中有两个是同一个表中的删除触发器。删除班级的触发器中界说的是删除班级时触发删除学生,而删除学生的触发器中界说的是班级人数减一。你发现了没,触发器被连着触发了。如下转变:

MySQL触发器的详细教学与实战分析 image-20200612005312835

我们通过删除班级案例领会了,删除班级之前需要把班级内所有学生删除掉。正由于如此,我们在删除班级之前已经把所有学生都删除了,导致在删除学生的时刻触发了班级人数减一的触发器,该触发器在执行历程中修改了已经被删除班级的学生人数。这问题就出在这里了,班级已经删除了,怎么修改一个本就没有的班级内的人数呢?对吧!

解决触发器冲突

为解决这个场景的触发器冲突问题,我们只能取舍一个触发器。于是,就通过下令删除了删除学生案例中使用的谁人触发器,删除后删除班级就可以乐成执行触发了!

1drop trigger t_delete_stu;

注重: 由于存在触发器冲突问题,我们在现实开发中需要认真考量界说触发器!

九、触发器性能和使用剖析(必读)

各大论坛等等,信赖在人人的文章中都不推荐使用触发器,而是推荐使用存储历程程序,这是为什么呢?

首先,存储历程程序分为存储历程、储存历程函数和触发器。也就是说这三种都是存储历程的使用都是存储历程的表现形式。

若是场景在数据量和并发量都很大的情形下,使用触发器、存储历程再加上几个事务等等,很容易泛起死锁。而且在使用触发器的时刻,也会泛起冲突,泛起问题时,我们需要追溯的代码就需要从一个触发器到另一个触发器……从而影响开发效率。从性能上看,触发器也是存储历程程序的一种,它也并没有展现出若干性能上的优势。由于触发器写起来对照隐藏,容易被开发人员忽略,而且隐式挪用触发器不易于清扫依赖,对后期维护不是很友好!

以是在开发中,触发器是很少用到的。那为什么我还花时间大篇幅的解说MySQL触发器呢?缘故原由很简朴,是由于需要扩展自己的知识贮备。开发中的使用问题和是否被人人摒弃,不是你拒绝学习知识的理由。之以是存在就有它存在的原理,我们在学习的门路中不停扩充自己的知识贮备即可。

假如有一天你的同事聊起触发器,你也能和他们聊聊你对触发器的看法是哈?若是你凭据从未领会过此知识呢?那性子就不一样了,信赖人人都懂吧!

原创文章,作者:28ru新闻网,如若转载,请注明出处:https://www.28ru.com/archives/10220.html