【MySQL进阶】MySQL触发器详解

时间:2023-01-29 13:52:11
序号 系列文章
7 【MySQL基础】运算符及相关函数详解
8 【MySQL基础】MySQL多表操作详解
9 【MySQL进阶】MySQL事务详解
10 【MySQL进阶】MySQL视图详解


前言

大家好,我是小杨!前面我已经为大家介绍了MySQL中的进阶知识->视图,那么今天我们就给大家讲解MySQL中的触发器这部分的相关内容,希望大家能够收获多多!


1,触发器

1.1,触发器概述

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样,都是嵌入到MySQL服务器的一段程序。

触发器是由事件来触发某个操作,这些事件包括 INSERT, UPDATE, DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序,当数据库执行这些语句的时候,就相当于事件发生了,就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作,需要自动执行一些数据库逻辑时,可以使用触发器来实现。

也可以简单理解为:触发器可以看作是一种特殊类型的存储过程,它与存储过程的区别在于存储过程中使用时需要调用,而触发器是在预先定义好的事件(如INSERT,DELETE,UPDATE操作)发生时,才会被MySQL自动调用。


1.2,触发器使用环境

在实际开发中,我们经常会遇到这样的情况:有 2 个或者多个相互关联的表,如 商品信息和库存信息分别存放在 2 个不同的数据表中,我们在添加一条新商品记录的时候,为了保证数据的完整性,必须同时在库存表中添加一条库存记录。

这样一来,我们就必须把这两个关联的操作步骤写到程序里面,而且要用事务包裹起来,确保这两个操作成为一个原子操作,要么全部执行,要么全部不执行。要是遇到特殊情况,可能还需要对数据进行手动维护,这样就很容易忘记其中的一步,导致数据缺失。

这个时候,咱们就可以使用触发器。你可以创建一个触发器,让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来,就不用担心因为忘记添加库存数据而导致的数据缺失的问题了。


2,触发器基本操作

2.1,触发器的创建

在创建触发器时需要指定触发器的操作对象–数据表,且该数据表不能是临时表或视图。触发器的创建语法如下:

CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名称 FOR EACH ROW 触发顺序
BEGIN 
	触发器操作的内容
END;

在上述语法中,指定数据库下的触发器名称必须唯一,也就是说不同的数据库下可以含有名称相同的触发器,而同一个数据库下不可以含有名称相同的触发器。ON 表名称 FOR EACH ROW 触发顺序表示指定触发器的操作对象。

触发时机表示数据表在发生变化前后的两种状态,而触发事件表示激活触发器的操作类型,一般为INSERT,UPADTE,DELETE这三种。触发顺序表示指定同一个表中多个触发器的执行顺序,默认情况下按创建顺序激活。

创建触发器的选项具体描述为:

选项 可选值 描述
触发时机 BEFORE 在表中数据发生改变前的状态
AFTER 在表中数据已经发生改变后的状态
触发事件 INSERT 插入操作
UPDATE 更新操作
DELETE 删除操作
触发顺序 FPLLOWS 新触发器在现有触发器之后激活
PRECEDES 新触发器在现有触发器之前激活

对于每张数据表来说,每个触发事件只允许创建一个触发器,因此,一张数据表根据触发时机的不同最多支持6个触发器。

为了更好地理解触发器的创建及使用,现写个日志表来记录数据的插入修改及删除操作。

1,日志表结构准备

create table stu_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

【MySQL进阶】MySQL触发器详解

2,插入数据触发器

当往student表中每添加一个数据记录之后,插入数据触发器就会自动触发,记录数据添加记录,插入数据触发器创建的基本语法为:

create trigger stu_insert_trigger
	after insert on student for each row
begin
	insert into stu_logs(id,operation,operate_time,operate_id,operate_params) values
    (null,'insert',now(),new.id,
     concat('插入的数据内容为:id=',new.id,',name=',new.name,',score=',new.score,',group_id=',new.group_id));
end;

当运行结果为下图时,就代表着插入数据触发器就创建完成了,后续在插入数据记录时,就会自动触发插入数据触发器。

【MySQL进阶】MySQL触发器详解

如果想更直观地知道该插入数据触发器是否创建成功,可以通过SHOW TRIGGERS来进行触发器的查看操作。

【MySQL进阶】MySQL触发器详解

3,修改数据触发器

当对student表中每修改一个数据记录之后,修改数据触发器就会自动触发,记录数据修改信息,修改数据触发器创建的基本语法为:

create trigger stu_update_trigger
	after update on student for each row
begin
	insert into stu_logs(id,operation,operate_time,operate_id,operate_params) values
    (null,'update',now(),new.id,
     concat('更新之前的数据:id=',old.id,',name=',old.name,',score=',old.score,',group_id=',old.group_id,
          ' | 更新之后的数据: id=',new.id,',name=',new.name, ',score=',new.score,',group_id=',new.group_id));
end;

当运行结果为下图时,就代表着修改数据触发器也就创建完成了,后续在修改数据记录时,就会自动触发修改数据触发器。

【MySQL进阶】MySQL触发器详解

如果想更直观地知道该修改数据触发器是否创建成功,可以通过SHOW TRIGGERS来进行触发器的查看操作。

【MySQL进阶】MySQL触发器详解

4,删除数据触发器

当对student表中每删除一个数据记录之后,删除数据触发器就会自动触发,记录数据删除记录,删除数据触发器创建的基本语法为:

create trigger stu_delete_trigger
	after delete on student for each row
begin
	insert into stu_logs(id,operation,operate_time,operate_id,operate_params) values
    (null,'delete',now(),old.id,
     concat('删除的数据内容为:id=',old.id,',name=',old.name,',score=',old.score,',group_id=',old.group_id));
end;

当运行结果为下图时,就代表着删除数据触发器也就创建完成了,后续在删除数据记录时,就会自动触发删除数据触发器。

【MySQL进阶】MySQL触发器详解

如果想更直观地知道该删除数据触发器是否创建成功,也可以通过SHOW TRIGGERS来进行触发器的查看操作。

【MySQL进阶】MySQL触发器详解


2.2,触发器的查看

若想要查看指定数据库中已经存在的触发器语句,状态等信息,可以通过两种方式来实现。

1,SHOW TRIGGERS语句查看

在 MySQL 中,可以通过 SHOW TRIGGERS 语句来查看触发器的基本信息,语法格式如下:

SHOW TRIGGERS [{FROM|IN}数据库名称][LIKE '匹配模式' | WHERE 条件表达式];

在上述语法中,若没有使用FROM或者IN指定数据库时,SHOW TRIGGERS语句获取的是当前所在的数据库下所有的触发器,WHERE用于指定查看触发器的条件,LIKE子句用于匹配触发器作用的数据表,而非是触发器名称。

为了更好地理解SHOW TRIGGERS的语法使用,示例如下:

SHOW TRIGGERS;

在对触发器进行查看操作,如果没有添加其余的参数,就会打印出当前数据库下所有的触发器,而这个触发器的创建效果只能通过查看操作来查看,不会专门地去创建一个库或表来保存创建的触发器。

【MySQL进阶】MySQL触发器详解

2,在triggers表中查看

在 MySQL 中,所有触发器的信息都存在于information_schema数据库的triggers表中,可以通过SELECT语句来查看,基本语法如下:

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

在上述语法中,'触发器名'用来指定要查看的触发器的名称,需要用单引号引起来。这种方式可以查询到指定的触发器,使用起来更加方便、灵活。

为了更好地理解SELECT 语句的使用,示例如下:

1,不带指定条件的查询

SELECT * FROM information_schema.triggers;

如果直接使用SELECT对information_schema数据库的triggers表进行查询操作,而没有其它的参数,查询到的结果是MySQL中所有的触发器,系统原有的和自己创建的。例如,下图中的用方框括起来的是自己创建的触发器,剩余的两个就是系统自带的触发器。

【MySQL进阶】MySQL触发器详解

2,带指定条件的查询

SELECT * FROM information_schema.triggers WHERE trigger_name= 'stu_insert_trigger';

如果在使用SELECT对information_schema数据库的triggers表进行查询操作时,包含指定条件,系统会查询到满足指定条件的触发器。

【MySQL进阶】MySQL触发器详解


2.3,触发器的触发

触发器在创建完成后,若要使触发器触发,则需要让触发器指定的数据表这些设置的对应操作。

为了更好地理解触发器的触发效果,现对上面创建的三种触发器进行逐一演示:

1,插入数据触发器的触发

INSERT INTO student VALUES(2,'xiaoyang',88,1);

【MySQL进阶】MySQL触发器详解

2,修改数据触发器的触发

UPDATE student SET score = 98 WHERE name = 'xiaoyang';

【MySQL进阶】MySQL触发器详解

3,删除数据触发器的触发

DELETE FROM student WHERE name = 'xiaoyang';

【MySQL进阶】MySQL触发器详解


2.4,触发器的删除

触发器的删除操作其实也挺简单,使用DROP TRIGGER语句就可以删除 MySQL 中已经定义的触发器。

DROP TRIGGER [IF EXISTS][数据库.]触发器名称;

在上述语法中,使用IF EXISTS可以避免因触发器不存在而导致删除触发器失败,利用数据库.触发器名称的方式可以删除指定数据库中的触发器;若省略数据库.,则表示删除当前数据库下的触发器;若没有选择数据库则系统会发生报错。

为了更好地理解触发器的删除操作,示例如下:

DROP TRIGGER IF EXISTS stu_insert_trigge;

进行触发器删除操作之前的触发器列表中存在3种触发器,分别为插入数据触发器,修改数据触发器,删除数据触发器这三种。

【MySQL进阶】MySQL触发器详解

进行插入数据触发器的删除操作之后,触发器列表中只剩下2种触发器,分别为修改数据,删除数据触发器,而插入数据触发器不存在。

【MySQL进阶】MySQL触发器详解

进行触发器的删除操作时,要特别注意触发器的删除只能一个一个地进行删除,不能同时删除多个触发器。

注意事项:在删除一个数据表的同时,也会自动删除该数据表上的触发器。另外,触发器不能被更新或者被覆盖,为了修改一个触发器,必须先删除这个触发器,再重新创建。


3,触发器的优缺点

3.1,触发器的优点

1,触发器可以保证数据安全,进行安全检验。

2,触发器可以通过数据库中的相关表实现级联无痕更改操作。

3,触发器可以侦测数据库内的操作,可以帮助我们记录操作日志。

4,触发器可以在操作数据之前,对相应的数据进行合法性检查。

优点说明:

1,利用触发器帮助我们记录操作日志,也就是具体记录什么时间进行了什么操作。这可以帮助我们更好地还原操作时的具体场景,更好地定位到问题原因所在。

2,人为地对数据库中的数据进行操作难免会出现问题,而这些问题可能会造成很严重的问题,而通过触发器的使用可以避免这类问题,触发器可以在实际插入或者更新操作之前,对相应的数据进行合理性检查,若存在错误,及时提示错误,防止错误数据进入系统。


3.2,触发器的缺点

1,触发器的使用会影响数据库的结构,同时增加了维护的复杂程度。

2,触发器的无痕操作会造成数据在程序(如PHP,Java等)层面的不可控。

3,触发器自身的隐蔽性,进而影响到应用程序中错误原因排查的效率。


结语

这就是本期博客的全部内容啦,想必大家已经对MySQL中的触发器的相关内容有了全新地认识和理解吧,如果有什么其他的问题无法自己解决,可以在评论区留言哦!

最后,如果你觉得这篇文章写的还不错的话或者有所收获的话,麻烦小伙伴们动动你们的小手,给个三连呗(点赞????,评论✍,收藏????),多多支持一下!各位的支持是我最大的动力,后期不断更新优质的内容来帮助大家,一起进步。那我们下期见!

【MySQL进阶】MySQL触发器详解