【Trigger】MySQL之触发器

MySQL之触发器

1、MySQL触发器

1.1、什么是触发器

• MySQL触发器（Trigger）是一种特殊的存储过程，
• 它与表有关，当表上的特定事件（insert，update，delete）发生时，触发器会自动执行。
• 可以使用触发器来实现数据约束，数据验证，数据复制等功能

1.2、为什么使用触发器

• 可以实现数据约束，数据验证，数据复制等功能- 例如：- 可以创建一个触发器，在向表中插入一条记录时，自动向另一个表中插入一个记录，从而实现数据复制的功能。
• 触发器还可以用于实现数据验证，- 例如- 在插入或更新数据时，检查记录的某些字段是否符合要求，则拒绝插入或者更新，这样可以保证数据的完整性和一致性。

1.3、创建触发器

语法：

createtrigger 触发器名称
{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 ) 发生时，触发器会自动执行
• 在创建触发器时，可以定义触发器的类型和事件，从而控制触发器的时机和条件

案例：

-- 创建两张表createtable table1(
    id int,
    name varchar(20)characterset utf8
)charset= utf8;createtable table2(
    id int,
    name varchar(20)characterset utf8
)charset= utf8;

1.3.1、添加事件

-- 创建一个触发器，当向表中插入一条记录时，自动向另一个表中插入一条记录createtrigger insert_trigger_1
afterinserton table1
    for each rowbegin-- 触发器的具体事件insertinto table2(id,name)values(NEW.id,New.name);end;

添加数据：

向table1表中添加数据时，会触发insert_trigger_1触发器，自动向table2表中添加数据

insertinto 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、删除事件

-- 创建一个触发器，当从表中删除一条记录时，自动从另一个表中删除一条记录createtrigger delete_trigger_1
 afterdeleteon table1
    for each rowbegindeletefrom table2 where id = OLD.id and name = OLD.name;end;

删除数据：

删除table1表中数据时，会触发delete_trigger_1触发器，自动删除table2表中对应的数据

deletefrom table1 where id =5and name ='paul';

查看两张表中的数据

select*from table1;

select*from table2;

1.3.3、修改事件

-- 创建一个触发器，当向表中更新一条记录时，自动更新另一个表中的记录createtrigger update_trigger_1
afterupdateon table1
    for each rowbeginupdate 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 =6and name ='westbrook';

select*from table1;

select*from table2;

1.4、删除触发器

droptrigger 触发器名称；

1.5、触发器的应用场景

触发器的应用场景包括但不限于以下几种：

1. 强制实施业务规则：通过在触发器中编写逻辑，可以在特定的表上自动执行业务规则，例如检查输入的数据是否符合要求，或者限制某些操作的执行。
2. 记录日志变更：通过在触发器中编写逻辑，可以在特定的表上自动记录数据的变更情况，例如记录数据的修改时间、修改人等信息。
3. 复杂的默认值计算：通过在触发器中编写逻辑，可以在特定的表上自动计算默认值，例如根据其他字段的值计算出一个新的字段的值。
4. 数据同步：通过在触发器中编写逻辑，可以在多个表之间自动同步数据，例如在一个表中插入一条数据时，自动在另一个表中插入相应的数据。
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做法:

首先创建表

createtable date_trigger(
    id int,
    date_time DATETIME,dateDATE)charset= utf8;

插入Trigger脚本

-- 创建 BEFORE INSERT 触发器DELIMITER//CREATETRIGGER before_insert_date_trigger
BEFORE INSERTON date_trigger
FOR EACH ROWBEGINSET NEW.date= CAST(NEW.date_time ASDATE);END;//DELIMITER;-- 创建 BEFORE UPDATE 触发器DELIMITER//CREATETRIGGER before_update_date_trigger
BEFORE UPDATEON date_trigger
FOR EACH ROWBEGINSET NEW.date= CAST(NEW.date_time ASDATE);END;//DELIMITER;

插入数据：

insertinto date_trigger(id,date_time)values(1,NOW());

查询数据

select*from date_trigger;

1.6.2、生成列做法:

执行下面sql，新建一个date_gen表，同时绑定生成列逻辑

createtable date_gen(
    id int,
    date_time DATETIME,`date`date GENERATED ALWAYS AS(cast(`date_time`asdate)) STORED
)charset= utf8;

我们可以看到他是date字段是根据date_time生成的，将date_time转换成date格式，并持久化(STORED)

我们插入数据看看

insertinto date_gen(id,date_time)values(1,NOW());

查询结果

SELECT*from date_gen;

总结：

查询效率影响

1. 额外的列更新成本：每次插入或更新操作时，触发器会额外执行一次赋值操作，这可能会略微增加操作的执行时间。
2. 索引和查询优化：如果 date列是经常用于查询的列，并且有索引支持，那么触发器可能导致索引的更新频率增加，进而影响查询性能。数据库引擎在处理更新时可能需要重新计算索引，特别是在高并发情况下会有一定的成本。

插入效率影响

1. 触发器执行开销：每次插入操作都会触发触发器执行额外的逻辑，包括赋值操作和任何其他逻辑。这些操作可能会轻微延长插入操作的总时间。
2. 行级锁和并发性：在高并发环境下，触发器可能导致对受影响行的行级锁定时间延长，因为数据库引擎需要确保触发器的逻辑完整执行。

控制和优化

虽然触发器可能会带来一些性能开销，但可以通过以下方式来控制和优化：

• 适当的索引设计：确保 date列及其经常查询的列有合适的索引，可以帮助数据库引擎优化查询效率。
• 触发器的简化和优化：尽量保持触发器的逻辑简单和高效，避免复杂的操作和频繁的触发条件。
• 硬件和配置优化：通过增加硬件资源（如更多的内存、更快的存储设备）和合理的 MySQL 配置来提升整体性能。

在实际应用中，触发器通常不会显著降低 MySQL 的查询和插入效率，特别是在合理使用和优化的情况下。它们提供了在旧版本 MySQL 中实现功能的一种有效方法，尤其是在没有生成列功能的情况下。然而，如同任何数据库设计和优化工作一样，评估和测试是确保系统性能满足需求的关键步骤。

如果这篇文章对您有帮助，希望您能关注、点赞、评论！！！