MySQL 触发器详解:创建、查看、示例及性能优化

我平时开发的时候,基本上不会用到这个触发器,主要是碰不到必须用它的场景理由,不过最近的项目中,已经上线了的一套系统中,客户突然要修改一个功能,大致意思就是,就是列表的一个状态,在处理流程的时候,它是flow的状态,但是客户要求列表查询的时候,依旧要知道它原来的状态,这时候就要有一个字段去记录它原来的状态,但是流程会改变这个状态,不管是回退还是到下个状态,要是改代码所涉及的逻辑处理过多,于是乎触发器就产生了, 我只需要关注列表状态的这个字段,只要不是审批状态值,其它的都同步更新,而列表查询则只查询我新增加的字段就成,至此也不用大改代码...


触发器(Trigger)是 MySQL 中一种特殊的数据库对象,用于在指定的表上自动执行特定的操作。触发器通常在数据库操作如插入、更新或删除数据时触发,用来维护数据的完整性或自动执行某些逻辑操作。

本文将详解 MySQL 触发器的创建、查看已有触发器、基本语法、典型示例,以及触发器性能优化等内容。

一、触发器的基本概念

触发器的核心要素有三个:

  1. 事件类型:表示触发器的执行条件,MySQL 支持 INSERTUPDATEDELETE 三种操作类型。
  2. 触发时间:表示触发器何时执行,可以是在指定操作之前BEFORE)或者之后AFTER)。
  3. 触发对象:触发器是针对表中的每一行记录(FOR EACH ROW)执行的。每张表最多可以定义 6 种触发器,分别为:

    • BEFORE INSERT
    • AFTER INSERT
    • BEFORE UPDATE
    • AFTER UPDATE
    • BEFORE DELETE
    • AFTER DELETE

触发器的典型用途:

  • 数据验证:在插入或更新数据之前检查数据合法性。
  • 日志记录:自动记录表的插入、更新或删除操作。
  • 数据同步:通过触发器自动将变动的数据同步到其他表。
  • 业务逻辑处理:实现特定的业务规则,如自动更新某些字段或处理其他复杂逻辑。

二、创建触发器

MySQL 中,创建触发器需要使用 CREATE TRIGGER 语句,并结合 DELIMITER 来更改语句结束符,以确保触发器主体中的 SQL 语句不会因为 ; 而导致语法冲突。

1. 创建触发器的语法

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器主体
    trigger_body;
END $$

DELIMITER ;
  • trigger_name:触发器名称。
  • {BEFORE | AFTER}:指定触发时机。
  • {INSERT | UPDATE | DELETE}:指定触发事件。
  • table_name:操作的表。
  • FOR EACH ROW:触发器针对表中的每一行数据执行。
  • trigger_body:定义触发器的业务逻辑,可以包含多个 SQL 语句。

2. 示例:创建 AFTER INSERT 触发器

假设我们有一张名为 employees 的员工表,包含 idnamesalary 字段。我们想在插入新员工时,将操作记录保存到 audit_log 审计表中。

创建 audit_log

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(50),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建 AFTER INSERT 触发器

DELIMITER $$

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (employee_id, action)
    VALUES (NEW.id, 'INSERT');
END $$

DELIMITER ;

触发器示例解释:

  • 触发器名称:after_employee_insert
  • 触发时间:AFTER INSERT,即当插入操作完成后触发。
  • 触发对象:表 employees
  • 操作:在插入员工数据后,触发器会自动将 NEW.id(插入的新员工 ID)和操作类型 'INSERT' 记录到 audit_log 审计表中。

三、查看已有的触发器

MySQL 中,可以通过以下方式查看已经创建的触发器。

1. 使用 SHOW TRIGGERS 命令查看触发器

SHOW TRIGGERS;

该命令会显示当前数据库中所有触发器的信息,包含触发器名称、关联的表、触发事件、触发时间等详细内容。

2. 查看特定表的触发器

如果只想查看某张表的触发器,可以加上 LIKE 来过滤特定的表名:

SHOW TRIGGERS LIKE 'employees';

3. 查询 INFORMATION_SCHEMA

MySQL 提供了 INFORMATION_SCHEMA.TRIGGERS 系统表,存储了所有触发器的信息。可以通过查询此表来获取更详细的触发器信息。

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database_name';

这将列出特定数据库下的所有触发器,包括其定义、触发时间和事件类型等。

四、触发器的事件类型

1. INSERT 触发器

INSERT 触发器用于在插入数据时触发,通常用于对新插入的数据进行处理。可以使用 NEW 关键字访问新插入的记录。

示例:BEFORE INSERT 触发器

DELIMITER $$

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be greater than zero';
    END IF;
END $$

DELIMITER ;

该触发器在插入操作之前执行,检查新员工的工资是否为正。如果工资不符合要求,则抛出错误,阻止插入。

2. UPDATE 触发器

UPDATE 触发器在更新数据时触发,可以通过 NEWOLD 关键字访问更新前后的记录。

示例:AFTER UPDATE 触发器

DELIMITER $$

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (employee_id, action)
    VALUES (OLD.id, 'UPDATE');
END $$

DELIMITER ;

该触发器在员工记录更新后执行,将更新操作记录到审计日志中。

3. DELETE 触发器

DELETE 触发器在删除记录时触发,可以通过 OLD 关键字访问被删除的记录。

示例:BEFORE DELETE 触发器

DELIMITER $$

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (employee_id, action)
    VALUES (OLD.id, 'DELETE');
END $$

DELIMITER ;

该触发器在删除记录之前执行,记录删除操作。

五、触发器的性能优化

触发器虽然能帮助实现自动化业务逻辑,但如果设计不当,可能会影响性能。以下是一些优化建议:

1. 避免复杂逻辑

触发器会在表操作时立即执行,因此复杂的业务逻辑会增加操作延迟。建议将复杂的逻辑移到应用层,触发器中仅进行简单的操作。

2. 减少不必要的表操作

触发器中的每个 SQL 操作都会增加数据库的负担,尤其是在高频的插入、更新或删除操作中,避免在触发器中执行过多查询或更新操作。

3. 避免递归调用

如果一个触发器在其操作过程中触发了另一个触发器,可能会导致递归调用,甚至出现死循环。确保触发器操作的原子性,避免触发器间的相互依赖。

4. 使用索引优化查询

触发器中如果涉及查询操作,确保相关字段上创建了合适的索引,以提高查询效率。

5. 定期监控和审查

随着业务增长,触发器的执行时间可能变长。定期审查触发器的运行效率,及时优化其逻辑或将部分功能移至应用层执行。

标签: MySQL

相关文章

php+mysql中如何处理嵌套(子)事务并保持原子性一致

在PHP和MySQL中处理子事务并保持原子性一致性是一个复杂但非常重要的问题,尤其是在处理涉及多个数据库操作的业务逻辑时。以下是一些关键的解决方案、思路、技术要点和涉及的难点讲解。解决方案与思路...

MySQL函数使用总结

MySQL提供了许多内置函数,可以方便地进行数据操作和计算。本文将对MySQL中的常用函数进行总结,包括数学函数、字符串函数、日期和时间函数等。数学函数ABS(x):返回x的绝对值。CEIL(x...

Typecho开发数据库常用API操作

表创建和删除在 Typecho 插件开发过程中,往往需要创建表。可以使用query()来进行表的创建、修改或者删除。$db= Typecho_Db::get(); $prefix = $db-&...

MySQL查询附近的人

MySQL中的POINT字段简介在MySQL数据库中,POINT字段是一种空间数据类型,用于存储地理数据的位置信息。这种数据类型属于MySQL的空间扩展,这些扩展允许用户在数据库中存储和操作GI...

MySQL DATE_SUB 函数各种用法及举例

MySQL DATE_SUB 函数简介在MySQL数据库中,DATE_SUB 函数是一个非常有用的日期时间函数,用于从指定日期中减去一个时间或日期间隔。这使得它在处理时间序列数据、生成报告或进行...

图片Base64编码

CSR生成

图片无损放大

图片占位符

Excel拆分文件