详细分析Mysql触发器的基本使用（图文解析）_数据库触发器

前言

对于维护数据的完整性，如果擅长Java，推荐阅读这篇文章：超全分析MybatisPlus中的MetaObjectHandler全局字段填充的基本知识（附Demo及实战）

1. 基本知识

触发器（Trigger）是数据库的一种特殊存储过程，当某个特定事件在特定表上发生时，触发器会自动执行

触发器在维护数据的完整性、实现复杂的业务规则以及监控数据库活动方面非常有用

基本的概念如下：

• 触发事件：触响应的数据库操作，如 INSERT、UPDATE 或 DELETE
• 触发时间：可以是 BEFORE（操作前）或 AFTER（操作后）
• 触发操作：执行的SQL语句或逻辑块（如 BEGIN…END 块）

触发器的限制和注意事项

• 触发器不能调用另一触发器：在MySQL中，一个触发器的执行不会触发另一个触发器
• 性能影响：触发器会在每次符合条件的事件发生时执行，因此可能对性能产生影响，尤其是在高频操作的表上
• 调试：由于触发器的执行是隐式的，因此调试触发器可能比较困难。建议在触发器中加入日志记录语句来辅助调试
• 事务处理：触发器通常会在事务内执行，因此如果事务回滚，触发器的操作也会回滚

触发器的实际应用场景

• 数据审计：使用触发器记录数据的变化，例如在表中记录每次插入、更新和删除操作
• 数据完整性：确保业务规则的执行，例如防止负库存或无效数据的插入
• 自动计算：在数据插入或更新时自动计算相关字段的值，例如自动更新订单的总金额

对于数据库

2. 创建触发器

基本的创建触发器语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
1
2
3
4
5
6
7

对应创建一个manong表，创建一个在插入数据之前更新 created_at 字段的触发器：

CREATE TABLE manong (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP
);

DELIMITER //

CREATE TRIGGER before_manong_insert
BEFORE INSERT ON manong 
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END//

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

截图如下：

3. 查询触发器

这三者的命令差不多

如果在数据库中了，可直接使用：SHOW TRIGGERS;

如果未在数据库中，需要指明数据库名：SHOW TRIGGERS FROM your_database_name;

还可以通过触发器名字来查询：show create trigger your_trigger_name;

还可直接使用information_schema.TRIGGERS 表这样查询：

SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_STATEMENT,
    ACTION_TIMING,
    CREATED,
    SQL_MODE,
    DEFINER
FROM 
    information_schema.TRIGGERS
WHERE 
    TRIGGER_SCHEMA = 'your_database_name';
1
2
3
4
5
6
7
8
9
10
11
12
13

截图如下：

注意TRIGGER_SCHEMA 应该用单引号引用

对应的参数说明如下：

• TRIGGER_NAME：名称
• EVENT_MANIPULATION： 事件（INSERT, UPDATE, DELETE）
• EVENT_OBJECT_TABLE：作用的表
• ACTION_STATEMENT： 执行的SQL语句
• ACTION_TIMING：触发时间（BEFORE, AFTER）
• CREATED：创建时间
• SQL_MODE：创建时的SQL模式
• DEFINER：定义者

4. 更新触发器

在MySQL中，触发器不能直接更新

需要修改触发器，必须先删除旧的触发器，然后创建一个新的触发器

删除的基本语法如下：DROP TRIGGER [IF EXISTS] trigger_name;
截图如下：

重新创建一个新的触发器：

DELIMITER //

CREATE TRIGGER before_yanjiuseng_insert
BEFORE INSERT ON manong
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    -- 新增逻辑，例如：记录插入操作日志
END//

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

截图如下：

5. Demo

根据以上的示例，测试对应是否可自动补充属性

原表格如下：

先完善表格，基于id自增以及主键

```sql
ALTER TABLE manong DROP PRIMARY KEY; -- 删除原有的主键约束

ALTER TABLE manong MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY; -- 修改 id 字段为自增主键

INSERT INTO manong (name) VALUES ('ma');
INSERT INTO manong (name) VALUES ('nong');
INSERT INTO manong (name) VALUES ('yanjiuseng');
1
2
3
4
5
6
7
8

截图如下：

再次查看对应的属性：