2. 首页 > MariaDB教程 > 正文

MariaDB教程FG024-MariaDB触发器数据校验与日志记录实战

教程整理：风哥教程  |  更新时间：2026-01-01  |  教程分类：MariaDB教程  |  文档学习：21
内容简介：本文风哥教程参考MariaDB官方文档MariaDB Server、Triggers等章节，详细讲解MariaDB触发器的使用与业务应用，包括触发器的创建、数据校验、日志记录等实战内容。

Part01-基础概念与理论知识

1.1 触发器基本概念

触发器是一种特殊的存储过程，它在特定的数据库操作（如INSERT、UPDATE、DELETE）发生时自动执行。触发器可以用于数据校验、日志记录、数据转换等场景。

1.2 触发器类型与触发时机

  • 按触发时机分：
    • BEFORE：操作前触发
    • AFTER：操作后触发
  • 按操作类型分：
    • INSERT：插入操作触发
    • UPDATE：更新操作触发
    • DELETE：删除操作触发

1.3 触发器优势与适用场景

  • 优势：
    • 自动执行，无需手动调用
    • 可以在数据操作前进行校验
    • 可以记录操作日志
    • 可以实现复杂的业务逻辑
  • 适用场景：
    • 数据完整性校验
    • 操作日志记录
    • 数据转换与同步
    • 业务规则 enforcement
更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 触发器设计原则

风哥提示：良好的触发器设计可以提高数据质量和系统可靠性。
  • 单一职责：每个触发器只负责一个功能
  • 性能考虑：触发器逻辑应简洁高效
  • 错误处理：包含适当的错误处理机制
  • 可维护性：代码清晰，易于理解和维护
  • 命名规范：使用统一的命名标准

2.2 性能优化建议

  • 保持触发器逻辑简单，避免复杂操作
  • 避免在触发器中执行大量数据操作
  • 合理使用索引
  • 避免递归触发
  • 定期清理触发器产生的日志数据

2.3 生产环境最佳实践

  • 在开发环境充分测试触发器
  • 监控触发器的执行性能
  • 限制触发器的数量和复杂度
  • 使用事务确保数据一致性
  • 定期备份包含触发器的数据库
学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 触发器创建与管理

更多学习教程公众号风哥教程itpux_com

# 创建触发器
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_before_insert_user
-> BEFORE INSERT ON fgedu_users
-> FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 查看触发器
MariaDB [fgedudb]> SHOW TRIGGERS LIKE ‘fgedu%’\G
*************************** 1. row ***************************
Trigger: fgedu_before_insert_user
Event: INSERT
Table: fgedu_users
Statement: BEGIN
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
END
Timing: BEFORE
Created: 2023-01-01 12:00:00
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@fgedu.localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
database_collation: utf8mb4_unicode_ci
# 删除触发器
MariaDB [fgedudb]> DROP TRIGGER IF EXISTS fgedu_before_insert_user;
Query OK, 0 rows affected (0.00 sec)

3.2 数据校验触发器

# 创建数据校验触发器
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_before_insert_product
-> BEFORE INSERT ON fgedu_products
-> FOR EACH ROW
BEGIN
IF NEW.price <= 0 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Price must be greater than 0’;
END IF;
IF NEW.stock < 0 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Stock cannot be negative’;
END IF;
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 测试数据校验触发器
MariaDB [fgedudb]> INSERT INTO fgedu_products (name, price, stock) VALUES (‘Test Product’, -10, 100);
ERROR 1644 (45000): Price must be greater than 0
MariaDB [fgedudb]> INSERT INTO fgedu_products (name, price, stock) VALUES (‘Test Product’, 100, -10);
ERROR 1644 (45000): Stock cannot be negative
MariaDB [fgedudb]> INSERT INTO fgedu_products (name, price, stock) VALUES (‘Test Product’, 100, 100);
Query OK, 1 row affected (0.00 sec)

3.3 日志记录触发器

# 创建操作日志表
MariaDB [fgedudb]> CREATE TABLE fgedu_audit_logs (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> table_name VARCHAR(50),
-> operation VARCHAR(10),
-> record_id INT,
-> old_data TEXT,
-> new_data TEXT,
-> user VARCHAR(50),
-> ip VARCHAR(20),
-> created_at TIMESTAMP DEFAULT NOW()
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 创建更新操作日志触发器
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_after_update_user
-> AFTER UPDATE ON fgedu_users
-> FOR EACH ROW
BEGIN
INSERT INTO fgedu_audit_logs (
table_name, operation, record_id, old_data, new_data, user, ip
) VALUES (
‘fgedu_users’,
‘UPDATE’,
OLD.id,
CONCAT(‘name=’, OLD.name, ‘, email=’, OLD.email),
CONCAT(‘name=’, NEW.name, ‘, email=’, NEW.email),
USER(),
SUBSTRING_INDEX(USER(), ‘@’, -1)
);
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 测试日志记录触发器
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘Updated Name’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查看操作日志
MariaDB [fgedudb]> SELECT * FROM fgedu_audit_logs;
+—-+————+———–+———–+—————-+————————+—————-+———–+———————+
| id | table_name | operation | record_id | old_data | new_data | user | ip | created_at |
+—-+————+———–+———–+—————-+————————+—————-+———–+———————+
| 1 | fgedu_users | UPDATE | 1 | name=user, email= | name=Updated Name, email= | root@fgedu.localhost | fgedu.localhost | 2023-01-01 12:00:00 |
+—-+————+———–+———–+—————-+————————+—————-+———–+———————+
学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 数据校验触发器示例

# 示例1：用户注册数据校验
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_before_insert_user_validation
-> BEFORE INSERT ON fgedu_users
-> FOR EACH ROW
BEGIN
— 验证邮箱格式
IF NOT NEW.email REGEXP ‘^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$’ THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Invalid email format’;
END IF;
— 验证密码长度
IF LENGTH(NEW.password) < 6 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Password must be at least 6 characters’;
END IF;
— 验证用户名长度
IF LENGTH(NEW.name) < 2 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Name must be at least 2 characters’;
END IF;
— 设置时间戳
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 测试数据校验
MariaDB [fgedudb]> INSERT INTO fgedu_users (name, email, password) VALUES (‘a’, ‘invalid-email’, ‘123’);
ERROR 1644 (45000): Invalid email format
MariaDB [fgedudb]> INSERT INTO fgedu_users (name, email, password) VALUES (‘Test’, ‘test@fgedu.net.cn’, ‘123’);
ERROR 1644 (45000): Password must be at least 6 characters
MariaDB [fgedudb]> INSERT INTO fgedu_users (name, email, password) VALUES (‘T’, ‘test@fgedu.net.cn’, ‘123456’);
ERROR 1644 (45000): Name must be at least 2 characters
MariaDB [fgedudb]> INSERT INTO fgedu_users (name, email, password) VALUES (‘Test User’, ‘test@fgedu.net.cn’, ‘123456’);
Query OK, 1 row affected (0.00 sec)
# 示例2：订单数据校验
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_before_insert_order
-> BEFORE INSERT ON fgedu_orders
-> FOR EACH ROW
BEGIN
DECLARE v_balance DECIMAL(10,2);
— 验证用户余额
SELECT balance INTO v_balance FROM fgedu_accounts WHERE user_id = NEW.user_id;
IF v_balance < NEW.total_amount THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Insufficient balance’;
END IF;
— 验证订单金额
IF NEW.total_amount <= 0 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Order amount must be greater than 0’;
END IF;
— 设置时间戳
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)

4.2 日志记录触发器示例

# 示例1：全表操作日志记录
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_after_insert_user_log
-> AFTER INSERT ON fgedu_users
-> FOR EACH ROW
BEGIN
INSERT INTO fgedu_audit_logs (
table_name, operation, record_id, old_data, new_data, user, ip
) VALUES (
‘fgedu_users’,
‘INSERT’,
NEW.id,
NULL,
CONCAT(‘id=’, NEW.id, ‘, name=’, NEW.name, ‘, email=’, NEW.email),
USER(),
SUBSTRING_INDEX(USER(), ‘@’, -1)
);
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_after_delete_user_log
-> AFTER DELETE ON fgedu_users
-> FOR EACH ROW
BEGIN
INSERT INTO fgedu_audit_logs (
table_name, operation, record_id, old_data, new_data, user, ip
) VALUES (
‘fgedu_users’,
‘DELETE’,
OLD.id,
CONCAT(‘id=’, OLD.id, ‘, name=’, OLD.name, ‘, email=’, OLD.email),
NULL,
USER(),
SUBSTRING_INDEX(USER(), ‘@’, -1)
);
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 测试日志记录
MariaDB [fgedudb]> INSERT INTO fgedu_users (name, email, password) VALUES (‘Log Test’, ‘log@fgedu.net.cn’, ‘123456’);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> DELETE FROM fgedu_users WHERE email = ‘log@fgedu.net.cn’;
Query OK, 1 row affected (0.00 sec)
# 查看操作日志
MariaDB [fgedudb]> SELECT * FROM fgedu_audit_logs WHERE table_name = ‘fgedu_users’ ORDER BY created_at DESC;
+—-+————+———–+———–+—————————–+—————————–+—————-+———–+———————+
| id | table_name | operation | record_id | old_data | new_data | user | ip | created_at |
+—-+————+———–+———–+—————————–+—————————–+—————-+———–+———————+
| 3 | fgedu_users | DELETE | 13 | id=13, name=Log Test, email=log@fgedu.net.cn | NULL | root@fgedu.localhost | fgedu.localhost | 2023-01-01 12:00:00 |
| 2 | fgedu_users | INSERT | 13 | NULL | id=13, name=Log Test, email=log@fgedu.net.cn | root@fgedu.localhost | fgedu.localhost | 2023-01-01 12:00:00 |
| 1 | fgedu_users | UPDATE | 1 | name=user, email= | name=Updated Name, email= | root@fgedu.localhost | fgedu.localhost | 2023-01-01 12:00:00 |
+—-+————+———–+———–+—————————–+—————————–+—————-+———–+———————+
# 示例2：敏感数据操作日志
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_after_update_account_log
-> AFTER UPDATE ON fgedu_accounts
-> FOR EACH ROW
BEGIN
IF OLD.balance != NEW.balance THEN
INSERT INTO fgedu_audit_logs (
table_name, operation, record_id, old_data, new_data, user, ip
) VALUES (
‘fgedu_accounts’,
‘UPDATE’,
NEW.user_id,
CONCAT(‘balance=’, OLD.balance),
CONCAT(‘balance=’, NEW.balance),
USER(),
SUBSTRING_INDEX(USER(), ‘@’, -1)
);
END IF;
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)

4.3 触发器调试与优化

# 启用触发器调试
MariaDB [fgedudb]> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
# 创建调试触发器
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_before_insert_debug
-> BEFORE INSERT ON fgedu_debug
-> FOR EACH ROW
BEGIN
DECLARE v_start_time TIMESTAMP;
DECLARE v_end_time TIMESTAMP;
SET v_start_time = NOW();
— 模拟复杂操作
DO SLEEP(0.01);
SET v_end_time = NOW();
— 记录执行时间
INSERT INTO fgedu_trigger_logs (trigger_name, execution_time, created_at)
VALUES (‘fgedu_before_insert_debug’, TIMESTAMPDIFF(MICROSECOND, v_start_time, v_end_time), NOW());
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 测试触发器性能
MariaDB [fgedudb]> INSERT INTO fgedu_debug (value) VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
# 查看触发器执行时间
MariaDB [fgedudb]> SELECT * FROM fgedu_trigger_logs;
+—-+————————+—————+———————+
| id | trigger_name | execution_time | created_at |
+—-+————————+—————+———————+
| 1 | fgedu_before_insert_debug | 10123 | 2023-01-01 12:00:00 |
| 2 | fgedu_before_insert_debug | 10087 | 2023-01-01 12:00:00 |
| 3 | fgedu_before_insert_debug | 10056 | 2023-01-01 12:00:00 |
| 4 | fgedu_before_insert_debug | 10034 | 2023-01-01 12:00:00 |
| 5 | fgedu_before_insert_debug | 10012 | 2023-01-01 12:00:00 |
+—-+————————+—————+———————+
# 优化触发器 – 减少日志记录开销
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE TRIGGER fgedu_before_insert_optimized
-> BEFORE INSERT ON fgedu_debug
-> FOR EACH ROW
BEGIN
— 只在调试模式下记录
IF @debug_mode THEN
INSERT INTO fgedu_trigger_logs (trigger_name, execution_time, created_at)
VALUES (‘fgedu_before_insert_optimized’, 0, NOW());
END IF;
END //
DELIMITER;
Query OK, 0 rows affected (0.01 sec)
# 测试优化后的触发器
MariaDB [fgedudb]> SET @debug_mode = OFF;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_debug (value) VALUES (6), (7), (8), (9), (10);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
# 验证日志未记录
MariaDB [fgedudb]> SELECT * FROM fgedu_trigger_logs WHERE trigger_name = ‘fgedu_before_insert_optimized’;
Empty set (0.00 sec)
风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 触发器使用经验

在实际生产环境中，触发器的使用需要注意以下几点：

  • 合理使用触发器，避免过度依赖
  • 保持触发器逻辑简单高效
  • 使用触发器进行数据校验和日志记录
  • 避免在触发器中执行复杂的业务逻辑
  • 定期清理触发器产生的日志数据

5.2 常见问题与解决方案

  • 性能问题：
    • 问题：触发器执行缓慢
    • 解决方案：优化触发器逻辑、减少日志记录、使用批处理
  • 递归触发：
    • 问题：触发器导致递归调用
    • 解决方案：使用条件判断避免递归、合理设计触发器逻辑
  • 调试困难：
    • 问题：触发器调试困难
    • 解决方案：添加日志输出、使用变量跟踪执行过程

5.3 生产故障案例分析

某金融系统在批量处理交易时出现触发器执行超时的问题，经过分析发现：

  • 问题原因：
    • 触发器中包含大量的日志记录操作
    • 没有合理的日志清理机制
    • 触发器逻辑过于复杂
  • 解决方案：
    • 优化触发器逻辑，减少日志记录
    • 实现日志自动清理机制
    • 将复杂逻辑移至存储过程
    • 使用异步日志记录
  • 效果：触发器执行时间从毫秒级降至微秒级，系统处理能力提升10倍
from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息