MySQL数据库触发器实战：创建、NEW/OLD引用与性能避坑指南

触发器基础与使用场景

触发器（Trigger）是一种特殊的存储过程，它不通过手动调用执行，而是在对数据库表进行 INSERT、DELETE 或 UPDATE 等 DML 操作时自动触发。其核心价值在于：

• 数据完整性校验：在数据写入前进行检查，不符合规则则拦截。
• 审计日志记录：自动记录数据变更的历史痕迹。
• 级联操作：当主表数据变动时，自动同步从表或关联表的数据。

注意：MySQL 触发器仅支持行级触发，不支持语句级触发。在触发器逻辑中，可以使用别名 NEW 引用新写入或修改后的数据，使用 OLD 引用被修改或删除前的旧数据。

环境准备：构建测试表结构

为了直观演示触发器的作用，我们需要准备两张表：用于存储业务数据的 users 表和用于记录操作行为的 user_log 表。

第1步：执行建表 SQL

请在数据库中依次执行以下脚本，创建测试环境。

-- 设置字符集和外键检查
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 创建用户表
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;

-- 2. 创建操作日志表
DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

触发器的创建与基本语法

MySQL 创建触发器的基本逻辑是定义：名称 + 触发时机 + 触发事件 + 作用对象 + 执行动作。

第2步：掌握标准语法格式

当触发逻辑包含多条 SQL 语句时，必须使用 BEGIN ... END 包裹，并临时更改分隔符以避免冲突。

-- 单条语句触发器
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW
statement;

-- 多条语句触发器
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW
BEGIN
    -- 语句列表
    statement1;
    statement2;
END$$
DELIMITER ;

第3步：创建自动化日志触发器

需求：当向 users 表新增或修改数据时，自动在 user_log 表中追加一行记录。

-- 场景一：INSERT 后触发，记录添加日志
DROP TRIGGER IF EXISTS TRIGGER_test;
DELIMITER $$
CREATE TRIGGER TRIGGER_test AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO user_log(content, create_time) VALUES('添加了一条数据', NOW());
END$$
DELIMITER ;

-- 验证插入
INSERT INTO users(user_name, sex) VALUES('xiaohemaio','男');

-- 场景二：UPDATE 前触发，记录修改日志
DROP TRIGGER IF EXISTS TRIGGER_test1;
DELIMITER $$
CREATE TRIGGER TRIGGER_test1 BEFORE UPDATE ON users FOR EACH ROW
BEGIN
    INSERT INTO user_log(content, create_time) VALUES('修改了一条数据', NOW());
END$$
DELIMITER ;

-- 验证更新
UPDATE users SET user_name='迪丽热巴' WHERE id=3;

实战 NEW 与 OLD 引用

NEW 代表当前正在被操作的那一行数据的新值，OLD 代表该行数据被操作前的旧值。在不同事件中可用的变量如下：

• INSERT：只有 NEW（因为还没有旧数据）。
• DELETE：只有 OLD（数据将被移除，保留旧状态）。
• UPDATE：NEW（新值）和 OLD（旧值）均可用。

第4步：精准记录变更详情

结合图片展示具体的引用逻辑 。

-- 案例1：记录新增时的详细信息
DROP TRIGGER IF EXISTS TRIGGER_test2;
DELIMITER $$
CREATE TRIGGER TRIGGER_test2 AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO user_log(content, create_time) 
    VALUES(CONCAT('添加的用户信息为：', NEW.user_name, ' 性别为：', NEW.sex), NOW());
END$$
DELIMITER ;

INSERT INTO users(user_name,sex) VALUES('test_user','女');

-- 案例2：记录更新前后的用户名对比
DROP TRIGGER IF EXISTS TRIGGER_test3;
DELIMITER $$
CREATE TRIGGER TRIGGER_test3 BEFORE UPDATE ON users FOR EACH ROW
BEGIN
    INSERT INTO user_log(content, create_time) 
    VALUES(CONCAT('将：', OLD.user_name, ' 修改为：', NEW.user_name), NOW());
END$$
DELIMITER ;

UPDATE users SET user_name='Kylie' WHERE id=4;

-- 案例3：记录删除前的用户ID
DROP TRIGGER IF EXISTS TRIGGER_test4;
DELIMITER $$
CREATE TRIGGER TRIGGER_test4 BEFORE DELETE ON users FOR EACH ROW
BEGIN
    INSERT INTO user_log(content, create_time) 
    VALUES(CONCAT('将id为：', OLD.id, ' 的用户已删除'), NOW());
END$$
DELIMITER ;

DELETE FROM users WHERE id=4;

执行上述变更后，可通过 SELECT * FROM user_log; 查看记录的详细日志内容 。

常用管理命令与排错

第5步：查询与删除触发器

如需查看数据库中存在的所有触发器及其状态，可使用 SHOW TRIGGERS。若需移除某个触发器，请使用 DROP TRIGGER。

-- 查看所有触发器详情
SHOW TRIGGERS;

-- 安全删除指定触发器
DROP TRIGGER IF EXISTS TRIGGER_test;

性能注意事项与最佳实践

虽然触发器功能强大，但在高并发或大数据量场景下需谨慎使用：

1. 禁止递归调用：触发器内部严禁对本表执行 INSERT/UPDATE/DELETE，否则会导致无限递归循环，最终耗尽内存并报错。
2. 轻量级逻辑：触发器中的逻辑应尽可能简单。如果触发器每次执行耗时 1s，批量导入 500 条数据时将额外消耗 500s，严重影响写入效率。
3. 避免高频触发：对于增删改非常频繁的核心业务表，不建议广泛依赖触发器，以免引发严重的 I/O 和 CPU 资源竞争。优先考虑应用层控制。

总结

本文详细介绍了 MySQL 触发器的语法结构、执行流程以及在维护数据完整性和日志系统中的实际应用。正确理解 NEW 和 OLD 的生命周期，配合合理的业务逻辑设计，能让你轻松实现自动化数据管控。以上就是 MySQL 数据库触发器使用教程的详细内容，更多关于 MySQL 触发器进阶优化的资料请关注本站其它相关文章！