内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Triggers、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com 03 学习交流加群风哥QQ113257174
Part01-基础概念与理论知识
1.1 触发器基础
触发器是与表相关的数据库对象，当表上发生特定事件时自动执行。触发器可以在INSERT、UPDATE或DELETE操作之前或之后执行。 04 风哥提示： 05更多学习教程公众号风哥教程itpux_com 06 from mysql视频:www.itpux.com
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body
# 删除触发器
DROP TRIGGER [IF EXISTS] trigger_name
# 查看触发器
SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name;
Part02-生产环境规划与建议
2.1 INSERT触发器使用
1. 创建AFTER INSERT触发器
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action, action_time, old_data, new_data)
VALUES (NEW.id, ‘INSERT’, NOW(), NULL, JSON_OBJECT(
‘user_name’, NEW.user_name,
’email’, NEW.email,
‘status’, NEW.status
));
END //
DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.02 sec)
2. 插入数据测试触发器
INSERT INTO users (user_name, email, status, created_at)
VALUES (‘test_user’, ‘test@fgedu.net.cn’, 1, NOW());
输出示例：
Query OK, 1 row affected (0.01 sec)
3. 查看审计记录
SELECT * FROM user_audit ORDER BY action_time DESC LIMIT 1;
输出示例：
+—-+———+———+———————+———-+——————————————————–+
| id | user_id | action | action_time | old_data | new_data |
+—-+———+———+———————+———-+——————————————————–+
| 1 | 1501 | INSERT | 2026-04-04 10:00:00 | NULL | {“user_name”: “test_user”, “email”: “test@fgedu.net.cn”, “status”: 1} |
+—-+———+———+———————+———-+——————————————————–+
1 row in set (0.00 sec)
4. 创建BEFORE INSERT触发器
DELIMITER //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF NEW.status IS NULL THEN
SET NEW.status = 0;
END IF;
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
IF NEW.order_no IS NULL THEN
SET NEW.order_no = CONCAT(‘ORD’, DATE_FORMAT(NOW(), ‘%Y%m%d’), LPAD(LAST_INSERT_ID() + 1, 6, ‘0’));
END IF;
END //
DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
5. 测试BEFORE INSERT触发器
INSERT INTO orders (user_id, amount) VALUES (1, 100.00);
输出示例：
Query OK, 1 row affected (0.01 sec)
6. 查看订单数据
SELECT * FROM orders ORDER BY id DESC LIMIT 1;
输出示例：
+—-+———–+—————-+———+——–+———————+
| id | user_id | order_no | amount | status | created_at |
+—-+———–+—————-+———+——–+———————+
| 1 | 1 | ORD202604040001| 100.00 | 0 | 2026-04-04 10:05:00 |
+—-+———–+—————-+———+——–+———————+
1 row in set (0.00 sec)
Part03-生产环境项目实施方案
3.1 UPDATE触发器使用
1. 创建AFTER UPDATE触发器
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.user_name != NEW.user_name OR OLD.email != NEW.email THEN
INSERT INTO user_audit (user_id, action, action_time, old_data, new_data)
VALUES (NEW.id, ‘UPDATE’, NOW(), JSON_OBJECT(
‘user_name’, OLD.user_name,
’email’, OLD.email
), JSON_OBJECT(
‘user_name’, NEW.user_name,
’email’, NEW.email
));
END IF;
END //
DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
2. 更新用户数据测试触发器
UPDATE users SET email = ‘newemail@fgedu.net.cn’ WHERE id = 1;
输出示例：
Query OK, 1 row affected (0.01 sec)
3. 查看审计记录
SELECT * FROM user_audit WHERE action = ‘UPDATE’ ORDER BY action_time DESC LIMIT 1;
输出示例：
+—-+———+———+———————+—————————————————+——————————————————+
| id | user_id | action | action_time | old_data | new_data |
+—-+———+———+———————+—————————————————+——————————————————+
| 2 | 1 | UPDATE | 2026-04-04 10:10:00 | {“user_name”: “user001”, “email”: “user001@test.com”} | {“user_name”: “user001”, “email”: “newemail@fgedu.net.cn”} |
+—-+———+———+———————+—————————————————+——————————————————+
1 row in set (0.00 sec)
4. 创建BEFORE UPDATE触发器
DELIMITER //
CREATE TRIGGER before_order_status_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
IF OLD.status != NEW.status THEN
SET NEW.updated_at = NOW();
IF NEW.status = 3 THEN
SET NEW.completed_at = NOW();
END IF;
END IF;
END //
DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
5. 测试BEFORE UPDATE触发器
UPDATE orders SET status = 3 WHERE id = 1;
输出示例：
Query OK, 1 row affected (0.01 sec)
6. 查看订单数据
SELECT id, status, updated_at, completed_at FROM orders WHERE id = 1;
输出示例：
+—-+——–+———————+———————+
| id | status | updated_at | completed_at |
+—-+——–+———————+———————+
| 1 | 3 | 2026-04-04 10:15:00 | 2026-04-04 10:15:00 |
+—-+——–+———————+———————+
1 row in set (0.00 sec)
Part04-生产案例与实战讲解
4.1 DELETE触发器使用
1. 创建BEFORE DELETE触发器
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO deleted_users (user_id, user_name, email, deleted_at)
VALUES (OLD.id, OLD.user_name, OLD.email, NOW());
END //
DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
2. 删除用户测试触发器
DELETE FROM users WHERE id = 2;
输出示例：
Query OK, 1 row affected (0.01 sec)
3. 查看已删除用户
SELECT * FROM deleted_users ORDER BY deleted_at DESC LIMIT 1;
输出示例：
+—-+———+———–+——————-+———————+
| id | user_id | user_name | email | deleted_at |
+—-+———+———–+——————-+———————+
| 1 | 2 | user002 | user002@test.com | 2026-04-04 10:20:00 |
+—-+———+———–+——————-+———————+
1 row in set (0.00 sec)
4. 创建AFTER DELETE触发器
DELIMITER //
CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_audit (order_id, user_id, amount, action, action_time)
VALUES (OLD.id, OLD.user_id, OLD.amount, ‘DELETE’, NOW());
END //
DELIMITER ;
输出示例：
Query OK, 0 rows affected (0.01 sec)
5. 测试AFTER DELETE触发器
DELETE FROM orders WHERE id = 2;
输出示例：
Query OK, 1 row affected (0.01 sec)
6. 查看订单审计记录
SELECT * FROM order_audit WHERE action = ‘DELETE’ ORDER BY action_time DESC LIMIT 1;
输出示例：
+—-+———+———+——–+———+———————+
| id | order_id | user_id | amount | action | action_time |
+—-+———+———+——–+———+———————+
| 1 | 2 | 1 | 200.00 | DELETE | 2026-04-04 10:25:00 |
+—-+———+———+——–+———+———————+
1 row in set (0.00 sec)
Part05-风哥经验总结与分享
5.1 触发器操作
1. 查看所有触发器
SHOW TRIGGERS;
输出示例：
+—————-+——–+——–+———–+——————+——–+————————+———————-+———————-+——————–+
| Trigger | Event | Table | Statement | Timing | Created| sql_mode | definer | character_set_client| collation_connection|
+—————-+——–+——–+———–+——————+——–+————————+———————-+———————-+——————–+
| after_user_insert| INSERT | users | BEGIN … | AFTER | NULL | STRICT_TRANS_TABLES… | root@localhost | utf8mb4 | utf8mb4_general_ci|
| before_order_insert| INSERT| orders | BEGIN … | BEFORE | NULL | STRICT_TRANS_TABLES… | root@localhost | utf8mb4 | utf8mb4_general_ci|
+—————-+——–+——–+———–+——————+——–+————————+———————-+———————-+——————–+
2. 查看特定表的触发器
SHOW TRIGGERS LIKE ‘users’;
输出示例：
+—————-+——–+——–+———–+——————+——–+————————+———————-+———————-+——————–+
| Trigger | Event | Table | Statement | Timing | Created| sql_mode | definer | character_set_client| collation_connection|
+—————-+——–+——–+———–+——————+——–+————————+———————-+———————-+——————–+
| after_user_insert| INSERT | users | BEGIN … | AFTER | NULL | STRICT_TRANS_TABLES… | root@localhost | utf8mb4 | utf8mb4_general_ci|
| after_user_update| UPDATE | users | BEGIN … | AFTER | NULL | STRICT_TRANS_TABLES… | root@localhost | utf8mb4 | utf8mb4_general_ci|
+—————-+——–+——–+———–+——————+——–+————————+———————-+———————-+——————–+
3. 查看触发器详细信息
SHOW CREATE TRIGGER after_user_insert\G
输出示例：
*************************** 1. row ***************************
Trigger: after_user_insert
sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `after_user_insert` AFTER INSERT ON `users` FOR EACH ROW BEGIN
INSERT INTO user_audit (user_id, action, action_time, old_data, new_data)
VALUES (NEW.id, ‘INSERT’, NOW(), NULL, JSON_OBJECT(
‘user_name’, NEW.user_name,
’email’, NEW.email,
‘status’, NEW.status
));
END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_unicode_ci
4. 删除触发器
DROP TRIGGER IF EXISTS after_user_insert;
输出示例：
Query OK, 0 rows affected (0.01 sec)
5. 从information_schema查询触发器
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();
输出示例：
+——————+——————-+——————–+—————+
| TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_TIMING |
+——————+——————-+——————–+—————+
| after_user_update | UPDATE | users | AFTER |
| before_order_insert| INSERT | orders | BEFORE |
+——————+——————-+——————–+—————+
6. 最佳实践
6.1 触发器最佳实践
1. 使用原则
– 保持触发器逻辑简单
– 避免触发器嵌套
– 谨慎使用触发器
– 充分测试触发器
2. 性能考虑
– 触发器会增加操作开销
– 批量操作时影响更大
– 考虑替代方案
3. 调试技巧
– 使用日志表记录
– 添加调试信息
– 分步测试
4. 文档维护
– 记录触发器用途
– 说明触发条件
– 更新相关文档
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
