MySQL之触发器*1、MySQL触发器* * 1.1、什么是触发器 * 1.2、为什么使用触发器 * 1.3、创建触发器 * * 1.3.1、添加事件 * 1.3.2、删除事件 * 1.3.3、修改事件 * 1.4、删除触发器 * 1.5、触发器的应用场景 * 1.6、???生成列 * * 1.6.1、Trigger做法: * 1.6.2、生成列做法: * 总结1、MySQL触发器----------### 1.1、什么是触发器* MySQL触发器Trigger是一种特殊的存储过程 * 它与表有关当表上的特定事件insertupdatedelete发生时触发器会自动执行。 * 可以使用触发器来实现数据约束数据验证数据复制等功能 ### 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. 强制实施业务规则通过在触发器中编写逻辑可以在特定的表上自动执行业务规则例如检查输入的数据是否符合要求或者限制某些操作的执行。 2. 记录日志变更通过在触发器中编写逻辑可以在特定的表上自动记录数据的变更情况例如记录数据的修改时间、修改人等信息。 3. 复杂的默认值计算通过在触发器中编写逻辑可以在特定的表上自动计算默认值例如根据其他字段的值计算出一个新的字段的值。 4. 数据同步通过在触发器中编写逻辑可以在多个表之间自动同步数据例如在一个表中插入一条数据时自动在另一个表中插入相应的数据。 5. 数据校验通过在触发器中编写逻辑可以在特定的表上自动校验数据的正确性例如检查数据的唯一性、完整性等。 ### 1.6、???生成列在MySQL 5.7之前想要实现当表上的特定事件insertupdatedelete发生时自动操作对应的逻辑只能通过触发器来实现在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,datedate GENERATED ALWAYS AS (cast(date_timeas 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;### 总结查询效率影响1.额外的列更新成本每次插入或更新操作时触发器会额外执行一次赋值操作这可能会略微增加操作的执行时间。 2.索引和查询优化如果 date列是经常用于查询的列并且有索引支持那么触发器可能导致索引的更新频率增加进而影响查询性能。数据库引擎在处理更新时可能需要重新计算索引特别是在高并发情况下会有一定的成本。插入效率影响1.触发器执行开销每次插入操作都会触发触发器执行额外的逻辑包括赋值操作和任何其他逻辑。这些操作可能会轻微延长插入操作的总时间。 2.行级锁和并发性在高并发环境下触发器可能导致对受影响行的行级锁定时间延长因为数据库引擎需要确保触发器的逻辑完整执行。控制和优化虽然触发器可能会带来一些性能开销但可以通过以下方式来控制和优化*适当的索引设计确保 date列及其经常查询的列有合适的索引可以帮助数据库引擎优化查询效率。 *触发器的简化和优化尽量保持触发器的逻辑简单和高效避免复杂的操作和频繁的触发条件。 *硬件和配置优化通过增加硬件资源如更多的内存、更快的存储设备和合理的 MySQL 配置来提升整体性能。 在实际应用中触发器通常不会显著降低 MySQL 的查询和插入效率特别是在合理使用和优化的情况下。它们提供了在旧版本 MySQL 中实现功能的一种有效方法尤其是在没有生成列功能的情况下。然而如同任何数据库设计和优化工作一样评估和测试是确保系统性能满足需求的关键步骤。如果这篇文章对您有帮助希望您能关注、点赞、评论