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

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

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

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

一、触发器的基本概念

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

1. 事件类型：表示触发器的执行条件，MySQL 支持 INSERT、UPDATE 和 DELETE 三种操作类型。
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 的员工表，包含 id、name 和 salary 字段。我们想在插入新员工时，将操作记录保存到 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 触发器在更新数据时触发，可以通过 NEW 和 OLD 关键字访问更新前后的记录。

示例：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. 定期监控和审查

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

版权声明：本文为原创文章，版权归 全栈开发技术博客 所有。

本文链接：https://www.lvtao.net/database/mysql-trigger.html

转载时须注明出处及本声明