相关推荐recommended
【MySQL进阶】MySQL触发器详解
作者:mmseoamin日期:2023-12-18
序号系列文章
7【MySQL基础】运算符及相关函数详解
8【MySQL基础】MySQL多表操作详解
9【MySQL进阶】MySQL事务详解
10【MySQL进阶】MySQL视图详解

文章目录

  • 前言
    • 1,触发器
      • 1.1,触发器概述
      • 1.2,触发器使用环境
      • 2,触发器基本操作
        • 2.1,触发器的创建
        • 2.2,触发器的查看
        • 2.3,触发器的触发
        • 2.4,触发器的删除
        • 3,触发器的优缺点
          • 3.1,触发器的优点
          • 3.2,触发器的缺点
          • 结语

            前言

            大家好,我是小杨!前面我已经为大家介绍了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触发器详解,image-20230126211858034,第1张

            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触发器详解,image-20230126215956364,第2张

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

            【MySQL进阶】MySQL触发器详解,image-20230126222923746,第3张

            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触发器详解,image-20230126221138523,第4张

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

            【MySQL进阶】MySQL触发器详解,image-20230126223004083,第5张

            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触发器详解,image-20230126221052843,第6张

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

            【MySQL进阶】MySQL触发器详解,image-20230126223030987,第7张


            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触发器详解,image-20230126221511429,第8张

            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触发器详解,image-20230126221806452,第9张

            2,带指定条件的查询

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

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

            【MySQL进阶】MySQL触发器详解,image-20230126223154123,第10张


            2.3,触发器的触发

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

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

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

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

            【MySQL进阶】MySQL触发器详解,image-20230126224813110,第11张

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

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

            【MySQL进阶】MySQL触发器详解,image-20230126225304342,第12张

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

            DELETE FROM student WHERE name = 'xiaoyang';
            

            【MySQL进阶】MySQL触发器详解,image-20230126225505337,第13张


            2.4,触发器的删除

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

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

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

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

            DROP TRIGGER IF EXISTS stu_insert_trigge;
            

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

            【MySQL进阶】MySQL触发器详解,image-20230126230135837,第14张

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

            【MySQL进阶】MySQL触发器详解,image-20230126230343478,第15张

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

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


            3,触发器的优缺点

            3.1,触发器的优点

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

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

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

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

            优点说明:

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

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


            3.2,触发器的缺点

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

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

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


            结语

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

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

            【MySQL进阶】MySQL触发器详解,在这里插入图片描述,第16张