MySQL之触发器
1、MySQL触发器
1.1、什么是触发器
MySQL触发器(Trigger)是一种特殊的存储过程,
它与表有关,当表上的特定事件(insert,update,delete)发生时,触发器会自动执行。
可以使用触发器来实现数据约束,数据验证,数据复制等功能
1.2、为什么使用触发器
可以实现数据约束,数据验证,数据复制等功能
例如:
- 可以创建一个触发器,在向表中插入一条记录时,自动向另一个表中插入一个记录,从而实现数据复制的功能。
触发器还可以用于实现数据验证,
例如
- 在插入或更新数据时,检查记录的某些字段是否符合要求,则拒绝插入或者更新,这样可以保证数据的完整性和一致性。
1.3、创建触发器
语法:
create trigger 触发器名称
{before | after } {insert | update | delete} -- 触发器类型和事件
on 表名称
for each row -- 触发器的作用范围
begin
-- 触发器执行的操作
end;
- 触发器名称可以自定义 ,并唯一性,见名知意
- before / after : 表示触发器的类型,分别表示发生前/发生后执行
- insert / update / delete : 表示触发器的事件类型,分别表示插入 / 更新 / 删除操作
- on 表名称 : 为触发器所在的表名
- for each row : 表示触发器作用的范围,即每一行记录都会触发该触发器
- begin 和 end之间是触发器执行的操作,可以是一条或者多条SQL语句
触发器是自动执行的,无需手动调用,
当表上的特定事件(insert , update, delete ) 发生时,触发器会自动执行
在创建触发器时,可以定义触发器的类型和事件,从而控制触发器的时机和条件
案例:
-- 创建两张表
create table table1(
id int,
name varchar(20) character set utf8
)charset = utf8;
create table table2(
id int,
name varchar(20) character set utf8
)charset = utf8;
1.3.1、添加事件
-- 创建一个触发器,当向表中插入一条记录时,自动向另一个表中插入一条记录
create trigger insert_trigger_1
after insert on table1
for each row
begin
-- 触发器的具体事件
insert into table2(id,name) values (NEW.id,New.name);
end;
添加数据:
向table1表中添加数据时,会触发insert_trigger_1触发器,自动向table2表中添加数据
insert into table1(id,name) values (1,'kobe'),(2,'lebron'),(3,'curry'),(4,'durant'),(5,'paul'),(6,'westbrook');
查询table1表
select * from table1;
查询table2表
select * from table2;
1.3.2、删除事件
-- 创建一个触发器,当从表中删除一条记录时,自动从另一个表中删除一条记录
create trigger delete_trigger_1
after delete on table1
for each row
begin
delete from table2 where id = OLD.id and name = OLD.name;
end;
删除数据:
删除table1表中数据时,会触发delete_trigger_1触发器,自动删除table2表中对应的数据
delete from table1 where id = 5 and name = 'paul';
查看两张表中的数据
select * from table1;
select * from table2;
1.3.3、修改事件
-- 创建一个触发器,当向表中更新一条记录时,自动更新另一个表中的记录
create trigger update_trigger_1
after update on table1
for each row
begin
update table2 set id = NEW.id, name = NEW.name where id = OLD.id and name = OLD.name;
end;
修改数据:
修改table1表中数据时,会触发update_trigger_1触发器,自动更新table2表中对应的数据
update table1 set id = 10,name = 'paul' where id = 6 and name = 'westbrook';
select * from table1;
select * from table2;
1.4、删除触发器
drop trigger 触发器名称;
1.5、触发器的应用场景
触发器的应用场景包括但不限于以下几种:
强制实施业务规则:通过在触发器中编写逻辑,可以在特定的表上自动执行业务规则,例如检查输入的数据是否符合要求,或者限制某些操作的执行。
记录日志变更:通过在触发器中编写逻辑,可以在特定的表上自动记录数据的变更情况,例如记录数据的修改时间、修改人等信息。
复杂的默认值计算:通过在触发器中编写逻辑,可以在特定的表上自动计算默认值,例如根据其他字段的值计算出一个新的字段的值。
数据同步:通过在触发器中编写逻辑,可以在多个表之间自动同步数据,例如在一个表中插入一条数据时,自动在另一个表中插入相应的数据。
数据校验:通过在触发器中编写逻辑,可以在特定的表上自动校验数据的正确性,例如检查数据的唯一性、完整性等。
1.6、🌟🌟🌟生成列
在MySQL 5.7之前,想要实现当表上的特定事件(insert,update,delete)发生时,自动操作对应的逻辑,只能通过触发器来实现,在MySQL 5.7之后可以用生成列来实现,生成列(也称为计算列或虚拟列)。
假设,现在有如下需求,现在有一列date_time数据,在我插入,更新这一列数据的时候,会自动截取,date部分的数据,放入到date列。例如,date_time列插入数据2024-07-18T13:40:30
,date列会自动生成2024-07-18
1.6.1、Trigger做法:
首先创建表
create table date_trigger(
id int,
date_time DATETIME,
date DATE
)charset = utf8;
插入Trigger脚本
-- 创建 BEFORE INSERT 触发器
DELIMITER //
CREATE TRIGGER before_insert_date_trigger
BEFORE INSERT ON date_trigger
FOR EACH ROW
BEGIN
SET NEW.date = CAST(NEW.date_time AS DATE);
END; //
DELIMITER ;
-- 创建 BEFORE UPDATE 触发器
DELIMITER //
CREATE TRIGGER before_update_date_trigger
BEFORE UPDATE ON date_trigger
FOR EACH ROW
BEGIN
SET NEW.date = CAST(NEW.date_time AS DATE);
END; //
DELIMITER ;
插入数据:
insert into date_trigger(id,date_time) values (1,NOW());
查询数据
select * from date_trigger;
1.6.2、生成列做法:
执行下面sql,新建一个date_gen表,同时绑定生成列逻辑
create table date_gen(
id int,
date_time DATETIME,
`date` date GENERATED ALWAYS AS (cast(`date_time` as date)) STORED
)charset = utf8;
我们可以看到他是date字段是根据date_time生成的,将date_time转换成date格式,并持久化(STORED)
我们插入数据看看
insert into date_gen(id,date_time) values (1,NOW());
查询结果
SELECT * from date_gen;
总结:
查询效率影响
额外的列更新成本:每次插入或更新操作时,触发器会额外执行一次赋值操作,这可能会略微增加操作的执行时间。
索引和查询优化:如果 date列是经常用于查询的列,并且有索引支持,那么触发器可能导致索引的更新频率增加,进而影响查询性能。数据库引擎在处理更新时可能需要重新计算索引,特别是在高并发情况下会有一定的成本。
插入效率影响
触发器执行开销:每次插入操作都会触发触发器执行额外的逻辑,包括赋值操作和任何其他逻辑。这些操作可能会轻微延长插入操作的总时间。
行级锁和并发性:在高并发环境下,触发器可能导致对受影响行的行级锁定时间延长,因为数据库引擎需要确保触发器的逻辑完整执行。
控制和优化
虽然触发器可能会带来一些性能开销,但可以通过以下方式来控制和优化:
适当的索引设计:确保 date列及其经常查询的列有合适的索引,可以帮助数据库引擎优化查询效率。
触发器的简化和优化:尽量保持触发器的逻辑简单和高效,避免复杂的操作和频繁的触发条件。
硬件和配置优化:通过增加硬件资源(如更多的内存、更快的存储设备)和合理的 MySQL 配置来提升整体性能。
在实际应用中,触发器通常不会显著降低 MySQL 的查询和插入效率,特别是在合理使用和优化的情况下。它们提供了在旧版本 MySQL 中实现功能的一种有效方法,尤其是在没有生成列功能的情况下。然而,如同任何数据库设计和优化工作一样,评估和测试是确保系统性能满足需求的关键步骤。
如果这篇文章对您有帮助,希望您能关注、点赞、评论!!!