MySQL教程FG272-MySQL触发器优化

# 触发器概述

1. 触发器定义

触发器特点：
– 与表关联的数据库对象
– 在特定事件发生时自动执行
– 不能直接调用，由事件触发
– 可以执行复杂的业务逻辑

触发器用途：
– 数据验证和约束
– 审计日志记录
– 数据同步
– 业务规则实现

触发器限制：
– 增加数据库负担
– 调试困难
– 可能导致性能问题
– 逻辑隐藏不易发现

2. 创建触发器

基本语法：
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body

创建简单触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be greater than 0'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 查看触发器

查看触发器列表：
mysql> SHOW TRIGGERS FROM production_db;

输出示例：
*************************** 1. row ***************************
Trigger: tr_orders_before_insert
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tr_orders_before_insert`
BEFORE INSERT ON `orders`
FOR EACH ROW
BEGIN
IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be greater than 0'; END IF; END character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8mb4_general_ci 4. 删除触发器删除触发器： mysql> DROP TRIGGER IF EXISTS tr_orders_before_insert;

输出示例：
Query OK, 0 rows affected (0.00 sec)

5. 触发器信息查询

从information_schema查询：
mysql> SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_TIMING,
ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = ‘production_db’;

# 触发器语法

1. 触发时机

BEFORE触发器：
– 在数据变更前执行
– 可以修改NEW值
– 可以阻止操作

AFTER触发器：
– 在数据变更后执行
– 不能修改NEW值
– 用于后续处理

创建BEFORE触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
— 验证状态转换
IF OLD.status = ‘completed’ AND NEW.status = ‘pending’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Cannot change completed order to pending’;
END IF;

— 自动更新修改时间
SET NEW.updated_at = NOW();
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

创建AFTER触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 记录审计日志
INSERT INTO order_audit (order_id, action, action_time)
VALUES (NEW.id, ‘INSERT’, NOW());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 触发事件

INSERT触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
— 创建用户默认设置
INSERT INTO user_settings (user_id, theme, language)
VALUES (NEW.id, ‘default’, ‘zh_CN’);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

UPDATE触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
— 记录价格变更
IF OLD.price != NEW.price THEN
INSERT INTO price_history (product_id, old_price, new_price, changed_at)
VALUES (NEW.id, OLD.price, NEW.price, NOW());
END IF;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

DELETE触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
— 检查订单状态
IF OLD.status = ‘processing’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Cannot delete processing order’;
END IF;

— 归档订单
INSERT INTO orders_archive
SELECT *, NOW() AS archived_at FROM orders WHERE id = OLD.id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. NEW和OLD关键字

INSERT操作：
– NEW：新插入的数据
– OLD：不可用

UPDATE操作：
– NEW：更新后的数据
– OLD：更新前的数据

DELETE操作：
– NEW：不可用
– OLD：被删除的数据

使用示例：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_audit_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (
employee_id,
field_name,
old_value,
new_value,
changed_at
)
VALUES
(NEW.id, ‘name’, OLD.name, NEW.name, NOW()),
(NEW.id, ‘salary’, OLD.salary, NEW.salary, NOW()),
(NEW.id, ‘department’, OLD.department, NEW.department, NOW());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 多语句触发器

复杂业务逻辑：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_order_complete
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
DECLARE v_customer_id INT;
DECLARE v_total_amount DECIMAL(10,2);

— 只在订单完成时执行
IF OLD.status != ‘completed’ AND NEW.status = ‘completed’ THEN
— 获取客户ID
SELECT customer_id INTO v_customer_id
FROM orders WHERE id = NEW.id;

— 更新客户积分
UPDATE customers
SET points = points + FLOOR(NEW.amount)
WHERE id = v_customer_id;

— 发送通知
INSERT INTO notifications (customer_id, message, created_at)
VALUES (v_customer_id,
CONCAT(‘Order ‘, NEW.id, ‘ completed’),
NOW());
END IF;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 条件触发

使用IF条件：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_inventory_check
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
DECLARE v_stock INT;

— 检查库存
SELECT stock INTO v_stock
FROM products WHERE id = NEW.product_id;

IF v_stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

使用CASE条件：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_status_change
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
CASE
WHEN OLD.status = ‘pending’ AND NEW.status = ‘processing’ THEN
SET NEW.processed_at = NOW();
WHEN OLD.status = ‘processing’ AND NEW.status = ‘shipped’ THEN
SET NEW.shipped_at = NOW();
WHEN OLD.status = ‘shipped’ AND NEW.status = ‘completed’ THEN
SET NEW.completed_at = NOW();
END CASE;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

# 触发器类型

1. 行级触发器

MySQL默认是行级触发器：
– FOR EACH ROW：每行触发一次
– 影响多行时多次执行

示例：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_log_each_row
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_log (order_id, action, log_time)
VALUES (NEW.id, ‘created’, NOW());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

批量插入时：
mysql> INSERT INTO orders (status, amount) VALUES
(‘pending’, 100),
(‘pending’, 200),
(‘pending’, 300);

输出示例：
Query OK, 3 rows affected (0.02 sec)

查看日志：
mysql> SELECT * FROM order_log;

输出示例：
+———-+———+———————+
| order_id | action | log_time |
+———-+———+———————+
| 1 | created | 2026-04-01 10:00:00 |
| 2 | created | 2026-04-01 10:00:00 |
| 3 | created | 2026-04-01 10:00:00 |
+———-+———+———————+

2. BEFORE触发器

数据验证触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_validate_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
— 验证邮箱格式
IF NEW.email NOT LIKE ‘%@%.%’ THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Invalid email format’;
END IF;

— 标准化邮箱
SET NEW.email = LOWER(NEW.email);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

数据转换触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_normalize_phone
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
— 移除非数字字符
SET NEW.phone = REGEXP_REPLACE(NEW.phone, ‘[^0-9]’, ”);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. AFTER触发器

审计日志触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_audit_all_changes
AFTER INSERT ON sensitive_data
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
record_id,
action,
new_values,
user_name,
action_time
)
VALUES (
‘sensitive_data’,
NEW.id,
‘INSERT’,
JSON_OBJECT(‘field1’, NEW.field1, ‘field2’, NEW.field2),
CURRENT_USER(),
NOW()
);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

数据同步触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_sync_to_archive
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_archive (
id, status, amount, customer_id, archived_at
)
VALUES (
OLD.id, OLD.status, OLD.amount, OLD.customer_id, NOW()
);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 级联触发器

级联更新示例：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
UPDATE orders o
SET total_amount = (
SELECT SUM(amount) FROM order_items WHERE order_id = o.id
)
WHERE o.id = NEW.order_id;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

注意：级联触发器可能导致性能问题

5. 多触发器

同一表多个触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_insert_1
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END //

mysql> CREATE TRIGGER tr_orders_before_insert_2
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
SET NEW.order_number = CONCAT(‘ORD’, DATE_FORMAT(NOW(), ‘%Y%m%d’), LPAD(NEW.id, 6, ‘0’));
END //

mysql> CREATE TRIGGER tr_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_log (order_id, action) VALUES (NEW.id, ‘created’);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看触发器执行顺序：
mysql> SHOW TRIGGERS LIKE ‘orders’;

# 触发器设计原则

1. 简单原则

问题设计：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_complex_logic
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
— 复杂业务逻辑
— 多表查询
— 远程调用
— 复杂计算
END //
mysql> DELIMITER ;

优化设计：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_simple_validation
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
— 只做简单验证
IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 性能原则

问题设计：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_heavy_operation
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 执行重操作
INSERT INTO large_audit_table SELECT * FROM large_table;
END //
mysql> DELIMITER ;

优化设计：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_lightweight_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 只记录必要信息
INSERT INTO order_log (order_id, action, log_time)
VALUES (NEW.id, ‘created’, NOW());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 避免循环触发

问题设计（可能导致无限循环）：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_update_orders
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
UPDATE orders SET updated_at = NOW() WHERE id = NEW.id;
END //
mysql> DELIMITER ;

优化设计：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_update_orders_safe
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 错误处理原则

完善的错误处理：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_safe_operation
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE v_stock INT;

DECLARE EXIT HANDLER FOR NOT FOUND
BEGIN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Product not found’;
END;

SELECT stock INTO v_stock
FROM products WHERE id = NEW.product_id;

IF v_stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 命名规范

示例：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
— 触发器逻辑
END //

mysql> CREATE TRIGGER tr_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
— 触发器逻辑
END //

mysql> CREATE TRIGGER tr_orders_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
— 触发器逻辑
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

# 触发器优化策略

1. 减少触发器数量

问题：每个表有多个触发器
mysql> SHOW TRIGGERS FROM production_db;

优化：合并相关触发器
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
— 合并所有BEFORE INSERT逻辑
SET NEW.created_at = NOW();
SET NEW.order_number = CONCAT(‘ORD’, DATE_FORMAT(NOW(), ‘%Y%m%d%H%i%s’));

IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 避免复杂查询

问题代码：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_complex_query
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 复杂关联查询
INSERT INTO order_summary
SELECT o.*, c.name, p.product_name, …
FROM orders o
JOIN customers c ON …
JOIN products p ON …
WHERE o.id = NEW.id;
END //
mysql> DELIMITER ;

优化代码：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_simple_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 只记录必要信息
INSERT INTO order_log (order_id, customer_id, amount, created_at)
VALUES (NEW.id, NEW.customer_id, NEW.amount, NOW());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 使用索引优化

为触发器查询创建索引：
mysql> CREATE INDEX idx_products_id ON products(id);

输出示例：
Query OK, 0 rows affected (0.05 sec)

触发器中使用索引列：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_check_stock
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
DECLARE v_stock INT;

— 使用索引列查询
SELECT stock INTO v_stock
FROM products WHERE id = NEW.product_id;

IF v_stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 批量操作优化

问题：批量操作时触发器执行多次
mysql> INSERT INTO orders SELECT * FROM temp_orders;

输出示例：
Query OK, 10000 rows affected (30.00 sec)

解决方案：使用存储过程批量处理
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_batch_insert_orders()
BEGIN
— 禁用触发器（MySQL不支持，使用标志位）
SET @disable_trigger = 1;

INSERT INTO orders SELECT * FROM temp_orders;

— 批量处理日志
INSERT INTO order_log (order_id, action)
SELECT id, ‘created’ FROM temp_orders;

SET @disable_trigger = 0;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 异步处理

使用消息队列替代触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_queue_notification
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
— 只写入消息队列
INSERT INTO notification_queue (order_id, type, status, created_at)
VALUES (NEW.id, ‘order_created’, ‘pending’, NOW());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

后台处理队列：
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_process_notification_queue()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_order_id INT;

DECLARE cur CURSOR FOR
SELECT id, order_id FROM notification_queue
WHERE status = ‘pending’ LIMIT 100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_order_id;
IF done THEN LEAVE read_loop; END IF;

— 发送通知
— …

UPDATE notification_queue SET status = ‘processed’ WHERE id = v_id;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

# 触发器监控

1. 监控触发器执行

创建监控表：
mysql> CREATE TABLE trigger_execution_log (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
table_name VARCHAR(100),
action VARCHAR(20),
execution_time_ms INT,
rows_affected INT,
execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

带监控的触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_after_insert_monitored
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE v_start_time TIMESTAMP;
SET v_start_time = NOW();

— 业务逻辑
INSERT INTO order_log (order_id, action, log_time)
VALUES (NEW.id, ‘created’, NOW());

— 记录执行时间
INSERT INTO trigger_execution_log
(trigger_name, table_name, action, execution_time_ms)
VALUES
(‘tr_orders_after_insert_monitored’, ‘orders’, ‘INSERT’,
TIMESTAMPDIFF(MICROSECOND, v_start_time, NOW()) / 1000);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 分析触发器性能

查询触发器执行统计：
mysql> SELECT
trigger_name,
COUNT(*) AS execution_count,
AVG(execution_time_ms) AS avg_time_ms,
MAX(execution_time_ms) AS max_time_ms,
SUM(execution_time_ms) AS total_time_ms
FROM trigger_execution_log
GROUP BY trigger_name
ORDER BY total_time_ms DESC;

输出示例：
+—————————————+—————–+————-+————-+—————+
| trigger_name | execution_count | avg_time_ms | max_time_ms | total_time_ms |
+—————————————+—————–+————-+————-+—————+
| tr_orders_after_insert_monitored | 10000 | 2 | 50 | 20000 |
| tr_products_after_update | 5000 | 3 | 30 | 15000 |
+—————————————+—————–+————-+————-+—————+

3. 监控触发器错误

创建错误日志表：
mysql> CREATE TABLE trigger_error_log (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
error_code VARCHAR(10),
error_message TEXT,
record_id INT
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

带错误处理的触发器：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_safe_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;

INSERT INTO trigger_error_log
(trigger_name, error_code, error_message, record_id)
VALUES
(‘tr_safe_insert’, @sqlstate, @text, NEW.id);
END;

— 业务逻辑
IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 触发器依赖分析

查看触发器依赖：
mysql> SELECT
TRIGGER_NAME,
EVENT_OBJECT_TABLE AS on_table,
ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = ‘production_db’;

分析触发器引用的表：
mysql> SELECT
TRIGGER_NAME,
EVENT_OBJECT_TABLE,
SUBSTRING_INDEX(
SUBSTRING_INDEX(ACTION_STATEMENT, ‘INSERT INTO ‘, -1),
‘ ‘, 1
) AS referenced_table
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = ‘production_db’;

5. 定期检查

检查触发器状态：
mysql> SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_TIMING,
CREATED
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = ‘production_db’
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;

# 触发器实施规范

1. 触发器模板

标准模板：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_table_before_insert
BEFORE INSERT ON table_name
FOR EACH ROW
COMMENT ‘触发器描述’
BEGIN
— 声明变量
DECLARE v_variable INT;

— 错误处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
— 记录错误
INSERT INTO trigger_error_log …;
END;

— 业务逻辑
— …
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 注释规范

完整注释：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
COMMENT ‘
功能：订单插入前验证
作者：DBA
日期：2026-04-01
说明：
1. 验证订单金额
2. 设置创建时间
3. 生成订单号
‘
BEGIN
— 步骤1：验证金额
IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be greater than 0'; END IF; -- 步骤2：设置创建时间 SET NEW.created_at = NOW(); -- 步骤3：生成订单号 SET NEW.order_number = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')); END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 版本控制

创建版本表：
mysql> CREATE TABLE trigger_versions (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
version VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
definition TEXT,
author VARCHAR(50),
change_log TEXT
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

记录版本：
mysql> INSERT INTO trigger_versions
(trigger_name, version, definition, author, change_log)
VALUES
(‘tr_orders_before_insert’, ‘1.0.0’,
‘CREATE TRIGGER tr_orders_before_insert…’,
‘DBA’, ‘Initial version’);

输出示例：
Query OK, 1 row affected (0.00 sec)

4. 测试规范

创建测试表：
mysql> CREATE TABLE trigger_test_results (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
test_case VARCHAR(100),
expected_result TEXT,
actual_result TEXT,
test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
passed BOOLEAN
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

测试触发器：
mysql> DELIMITER //
mysql> CREATE PROCEDURE test_tr_orders_before_insert()
BEGIN
— 测试用例1：有效金额
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO trigger_test_results
(trigger_name, test_case, expected_result, actual_result, passed)
VALUES (‘tr_orders_before_insert’, ‘valid_amount’, ‘success’, ‘error’, FALSE);
END;

INSERT INTO orders (status, amount) VALUES (‘pending’, 100);

INSERT INTO trigger_test_results
(trigger_name, test_case, expected_result, actual_result, passed)
VALUES (‘tr_orders_before_insert’, ‘valid_amount’, ‘success’, ‘success’, TRUE);
END;

— 测试用例2：无效金额
BEGIN
DECLARE v_error_occurred BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET v_error_occurred = TRUE;
END;

INSERT INTO orders (status, amount) VALUES (‘pending’, -100);

INSERT INTO trigger_test_results
(trigger_name, test_case, expected_result, actual_result, passed)
VALUES (‘tr_orders_before_insert’, ‘invalid_amount’, ‘error’,
IF(v_error_occurred, ‘error’, ‘success’), v_error_occurred);
END;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 部署流程

部署检查清单：
1. 备份现有触发器
2. 在测试环境验证
3. 检查依赖关系
4. 评估性能影响
5. 部署到生产环境
6. 验证触发器功能
7. 监控执行情况

部署脚本：
mysql> DELIMITER //
mysql> CREATE PROCEDURE deploy_trigger(
IN p_trigger_name VARCHAR(100),
IN p_definition TEXT
)
BEGIN
— 备份旧版本
INSERT INTO trigger_versions
(trigger_name, version, definition, author, change_log)
SELECT
TRIGGER_NAME,
‘backup’,
ACTION_STATEMENT,
‘system’,
‘Pre-deployment backup’
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = p_trigger_name;

— 删除旧触发器
SET @drop_sql = CONCAT(‘DROP TRIGGER IF EXISTS ‘, p_trigger_name);
PREPARE stmt FROM @drop_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

— 创建新触发器
SET @create_sql = p_definition;
PREPARE stmt FROM @create_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT ‘Deployment completed’ AS status;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

# 触发器调试

1. 使用日志表调试

创建调试日志表：
mysql> CREATE TABLE trigger_debug_log (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
debug_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
message TEXT,
variable_name VARCHAR(50),
variable_value TEXT
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

在触发器中添加调试日志：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_debug_demo
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE v_count INT;

— 记录开始
INSERT INTO trigger_debug_log (trigger_name, message)
VALUES (‘tr_debug_demo’, ‘Trigger started’);

— 记录输入值
INSERT INTO trigger_debug_log (trigger_name, variable_name, variable_value)
VALUES (‘tr_debug_demo’, ‘NEW.amount’, NEW.amount);

— 业务逻辑
SELECT COUNT(*) INTO v_count FROM orders WHERE customer_id = NEW.customer_id;

— 记录中间结果
INSERT INTO trigger_debug_log (trigger_name, variable_name, variable_value)
VALUES (‘tr_debug_demo’, ‘v_count’, v_count);

— 记录结束
INSERT INTO trigger_debug_log (trigger_name, message)
VALUES (‘tr_debug_demo’, ‘Trigger completed’);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 使用临时表调试

创建临时调试表：
mysql> CREATE TEMPORARY TABLE temp_trigger_debug (
step INT,
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

输出示例：
Query OK, 0 rows affected (0.00 sec)

在触发器中使用：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_temp_debug
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO temp_trigger_debug (step, message) VALUES (1, ‘Start’);

— 业务逻辑
IF NEW.amount <= 0 THEN INSERT INTO temp_trigger_debug (step, message) VALUES (2, 'Amount validation failed'); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount'; END IF; INSERT INTO temp_trigger_debug (step, message) VALUES (3, 'End'); END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 条件调试

使用调试标志：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_conditional_debug
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
— 只在调试模式启用时记录
IF @trigger_debug = 1 THEN
INSERT INTO trigger_debug_log (trigger_name, message)
VALUES (‘tr_conditional_debug’, CONCAT(‘Processing order with amount: ‘, NEW.amount));
END IF;

— 业务逻辑
SET NEW.created_at = NOW();
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

启用调试：
mysql> SET @trigger_debug = 1;
mysql> INSERT INTO orders (status, amount) VALUES (‘pending’, 100);
mysql> SELECT * FROM trigger_debug_log;

输出示例：
+—-+————————+——————————————+
| id | trigger_name | message |
+—-+————————+——————————————+
| 1 | tr_conditional_debug | Processing order with amount: 100 |
+—-+————————+——————————————+

4. 错误追踪

详细错误记录：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_error_tracking
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;

INSERT INTO trigger_error_log
(trigger_name, error_code, error_message, record_id)
VALUES
(‘tr_error_tracking’,
CONCAT(‘SQLSTATE:’, @sqlstate, ‘ ERRNO:’, @errno),
@text,
NEW.id);
END;

— 业务逻辑
IF NEW.amount <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid amount'; END IF; END // mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 性能分析

测量触发器执行时间：
mysql> DELIMITER //
mysql> CREATE TRIGGER tr_performance_measure
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE v_start_time TIMESTAMP(6);
DECLARE v_end_time TIMESTAMP(6);

SET v_start_time = NOW(6);

— 业务逻辑
SET NEW.created_at = NOW();

SET v_end_time = NOW(6);

— 记录执行时间
INSERT INTO trigger_execution_log
(trigger_name, execution_time_ms)
VALUES
(‘tr_performance_measure’,
TIMESTAMPDIFF(MICROSECOND, v_start_time, v_end_time) / 1000);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

# 触发器维护

1. 定期检查触发器

检查触发器状态：
mysql> SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_TIMING,
DTD_IDENTIFIER
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = ‘production_db’;

2. 备份触发器

导出触发器：
mysqldump -u root -p –triggers –no-create-info –no-data \
production_db > triggers_backup.sql

输出示例：
— Dumping triggers for table ‘orders’
— Trigger: tr_orders_before_insert

只导出特定触发器：
mysql> SELECT ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = ‘tr_orders_before_insert’
INTO OUTFILE ‘/tmp/trigger_backup.sql’;

输出示例：
Query OK, 1 row affected (0.00 sec)

3. 清理无用触发器

查找未使用的触发器：
mysql> SELECT
t.TRIGGER_NAME,
COUNT(e.id) AS execution_count
FROM information_schema.TRIGGERS t
LEFT JOIN trigger_execution_log e ON t.TRIGGER_NAME = e.trigger_name
WHERE t.TRIGGER_SCHEMA = ‘production_db’
GROUP BY t.TRIGGER_NAME
HAVING execution_count = 0;

输出示例：
+————————–+—————–+
| TRIGGER_NAME | execution_count |
+————————–+—————–+
| tr_old_unused | 0 |
+————————–+—————–+

删除无用触发器：
mysql> DROP TRIGGER IF EXISTS tr_old_unused;

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 触发器迁移

迁移触发器到新表：
mysql> DELIMITER //
mysql> CREATE PROCEDURE migrate_trigger(
IN p_old_table VARCHAR(100),
IN p_new_table VARCHAR(100)
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_trigger_name VARCHAR(100);
DECLARE v_timing VARCHAR(10);
DECLARE v_event VARCHAR(10);
DECLARE v_statement TEXT;

DECLARE cur CURSOR FOR
SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_TABLE = p_old_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_trigger_name, v_timing, v_event, v_statement;
IF done THEN LEAVE read_loop; END IF;

— 创建新触发器
SET @sql = CONCAT(‘CREATE TRIGGER ‘, v_trigger_name, ‘ ‘,
v_timing, ‘ ‘, v_event, ‘ ON ‘, p_new_table,
‘ FOR EACH ROW ‘, v_statement);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 触发器性能监控

创建监控视图：
mysql> CREATE VIEW v_trigger_performance AS
SELECT
trigger_name,
COUNT(*) AS execution_count,
AVG(execution_time_ms) AS avg_time_ms,

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

MySQL教程FG272-MySQL触发器优化

Part01-基础概念与理论知识

1.1 触发器概述

1.2 触发器语法

1.3 触发器类型

Part02-生产环境规划与建议

2.1 触发器设计原则

2.2 触发器优化策略

2.3 触发器监控

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

3.1 触发器实施规范

3.2 触发器调试

3.3 触发器维护

联系我们

MySQL教程FG272-MySQL触发器优化

Part01-基础概念与理论知识

1.1 触发器概述

1.2 触发器语法

1.3 触发器类型

Part02-生产环境规划与建议

2.1 触发器设计原则

2.2 触发器优化策略

2.3 触发器监控

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

3.1 触发器实施规范

3.2 触发器调试

3.3 触发器维护

相关推荐

联系我们