MySQL 触发器 存储过程 介绍

一、基础概念1.1 存储过程 (Stored Procedure)定义预编译的SQL语句集合存储在数据库中作用封装复杂业务逻辑提高性能增强安全性特点可带参数可返回结果1.2 触发器 (Trigger)定义特殊的存储过程在特定事件发生时自动执行触发事件INSERT、UPDATE、DELETE执行时机BEFORE之前、AFTER之后、INSTEAD OF替代级别行级触发器、语句级触发器二、存储过程详解MySQL示例2.1 基本语法-- 创建存储过程 DELIMITER // CREATE PROCEDURE GetEmployeeByDept( IN dept_id INT, OUT emp_count INT ) BEGIN -- 业务逻辑 SELECT * FROM employees WHERE department_id dept_id; -- 设置输出参数 SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id dept_id; END // DELIMITER ; -- 调用存储过程 CALL GetEmployeeByDept(10, count); SELECT count;2.2 带控制流的存储过程DELIMITER // CREATE PROCEDURE ProcessSalary( IN emp_id INT, IN raise_percent DECIMAL(5,2) ) BEGIN DECLARE current_salary DECIMAL(10,2); DECLARE new_salary DECIMAL(10,2); DECLARE max_salary DECIMAL(10,2) DEFAULT 20000; -- 异常处理 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 错误发生事务已回滚 AS Message; END; START TRANSACTION; -- 获取当前工资 SELECT salary INTO current_salary FROM employees WHERE id emp_id; -- 验证数据 IF current_salary IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 员工不存在; END IF; -- 计算新工资 SET new_salary current_salary * (1 raise_percent/100); -- 检查上限 IF new_salary max_salary THEN SET new_salary max_salary; END IF; -- 更新工资 UPDATE employees SET salary new_salary, last_raise_date CURDATE() WHERE id emp_id; -- 记录日志 INSERT INTO salary_history (employee_id, old_salary, new_salary, change_date) VALUES (emp_id, current_salary, new_salary, NOW()); COMMIT; SELECT 工资调整完成 AS Message, emp_id AS EmployeeID, current_salary AS OldSalary, new_salary AS NewSalary; END // DELIMITER ;三、触发器详解3.1 基本语法-- 创建BEFORE INSERT触发器 DELIMITER // CREATE TRIGGER trg_before_insert_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN -- 自动设置创建时间 SET NEW.created_at NOW(); -- 数据验证 IF NEW.salary 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 工资不能为负数; END IF; -- 数据转换 SET NEW.email LOWER(TRIM(NEW.email)); END // DELIMITER ; -- 创建AFTER UPDATE触发器 DELIMITER // CREATE TRIGGER trg_after_update_employee AFTER UPDATE ON employees FOR EACH ROW BEGIN -- 记录变更历史 IF OLD.salary ! NEW.salary THEN INSERT INTO salary_audit ( employee_id, old_salary, new_salary, changed_by, change_date ) VALUES ( NEW.id, OLD.salary, NEW.salary, CURRENT_USER(), NOW() ); END IF; -- 更新部门总工资缓存 UPDATE department_stats ds SET total_salary ( SELECT SUM(salary) FROM employees WHERE department_id NEW.department_id ) WHERE ds.department_id NEW.department_id; END // DELIMITER ;3.2 高级触发器示例-- 层级审计触发器 DELIMITER // CREATE TRIGGER trg_audit_employee_changes AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW BEGIN DECLARE operation_type VARCHAR(10); DECLARE old_data JSON; DECLARE new_data JSON; -- 确定操作类型 IF INSERTING THEN SET operation_type INSERT; SET new_data JSON_OBJECT( id, NEW.id, name, NEW.name, salary, NEW.salary, dept_id, NEW.department_id ); SET old_data NULL; ELSEIF UPDATING THEN SET operation_type UPDATE; SET old_data JSON_OBJECT( id, OLD.id, name, OLD.name, salary, OLD.salary, dept_id, OLD.department_id ); SET new_data JSON_OBJECT( id, NEW.id, name, NEW.name, salary, NEW.salary, dept_id, NEW.department_id ); ELSE SET operation_type DELETE; SET old_data JSON_OBJECT( id, OLD.id, name, OLD.name, salary, OLD.salary, dept_id, OLD.department_id ); SET new_data NULL; END IF; -- 插入审计记录 INSERT INTO comprehensive_audit ( table_name, operation_type, old_data, new_data, changed_by, change_timestamp, transaction_id ) VALUES ( employees, operation_type, old_data, new_data, CURRENT_USER(), NOW(), transaction_id ); END // DELIMITER ;四、实战案例4.1 库存管理系统-- 库存表 CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), stock_quantity INT, min_stock_level INT, last_restock_date DATE ); -- 订单表 CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, quantity INT, order_date DATETIME, status VARCHAR(20) ); -- 库存更新存储过程 DELIMITER // CREATE PROCEDURE sp_ProcessOrder( IN p_product_id INT, IN p_quantity INT, OUT p_result VARCHAR(100) ) BEGIN DECLARE v_current_stock INT; DECLARE v_min_stock INT; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_result 处理订单时发生错误; END; START TRANSACTION; -- 检查库存 SELECT stock_quantity, min_stock_level INTO v_current_stock, v_min_stock FROM products WHERE product_id p_product_id FOR UPDATE; -- 行锁防止并发问题 IF v_current_stock IS NULL THEN SET p_result 产品不存在; ROLLBACK; RETURN; END IF; IF v_current_stock p_quantity THEN SET p_result CONCAT(库存不足。当前库存:, v_current_stock, , 需要:, p_quantity); ROLLBACK; RETURN; END IF; -- 更新库存 UPDATE products SET stock_quantity stock_quantity - p_quantity, last_restock_date CASE WHEN (stock_quantity - p_quantity) min_stock_level THEN CURDATE() ELSE last_restock_date END WHERE product_id p_product_id; -- 创建订单记录 INSERT INTO orders (product_id, quantity, order_date, status) VALUES (p_product_id, p_quantity, NOW(), 已完成); -- 检查是否需要补货 IF (v_current_stock - p_quantity) v_min_stock THEN INSERT INTO restock_alerts (product_id, alert_date, message) VALUES (p_product_id, NOW(), CONCAT(产品库存低于最小库存水平。当前库存:, v_current_stock - p_quantity)); END IF; COMMIT; SET p_result 订单处理成功; END // DELIMITER ; -- 库存审计触发器 DELIMITER // CREATE TRIGGER trg_stock_audit AFTER UPDATE ON products FOR EACH ROW BEGIN IF OLD.stock_quantity ! NEW.stock_quantity THEN INSERT INTO stock_transactions ( product_id, old_quantity, new_quantity, change_amount, change_type, changed_by, change_date ) VALUES ( NEW.product_id, OLD.stock_quantity, NEW.stock_quantity, NEW.stock_quantity - OLD.stock_quantity, CASE WHEN NEW.stock_quantity OLD.stock_quantity THEN 入库 ELSE 出库 END, CURRENT_USER(), NOW() ); END IF; END // DELIMITER ;五、最佳实践5.1 存储过程最佳实践命名规范-- 使用有意义的名称 usp_GetCustomerOrders -- SQL Server: usp User Stored Procedure sp_calculate_monthly_report参数验证CREATE PROCEDURE sp_SafeOperation InputParam INT AS BEGIN IF InputParam IS NULL OR InputParam 0 BEGIN RAISERROR(参数必须为正整数, 16, 1); RETURN; END -- 主逻辑... END错误处理DECLARE ErrorNumber INT; DECLARE ErrorSeverity INT; DECLARE ErrorState INT; DECLARE ErrorProcedure NVARCHAR(128); DECLARE ErrorLine INT; DECLARE ErrorMessage NVARCHAR(4000); BEGIN TRY -- 业务逻辑 END TRY BEGIN CATCH SELECT ErrorNumber ERROR_NUMBER(), ErrorMessage ERROR_MESSAGE(); -- 记录错误日志 END CATCH5.2 触发器最佳实践避免递归触发-- 在SQL Server中禁用递归 ALTER DATABASE dbname SET RECURSIVE_TRIGGERS OFF; -- 在触发器中检查递归 CREATE TRIGGER trg_no_recursion ON table1 AFTER UPDATE AS BEGIN IF TRIGGER_NESTLEVEL() 1 RETURN; -- 逻辑... END性能优化-- 只处理实际变化的行 CREATE TRIGGER trg_efficient AFTER UPDATE ON large_table FOR EACH ROW BEGIN IF NEW.column1 ! OLD.column1 OR NEW.column2 ! OLD.column2 THEN -- 仅当相关列变化时执行 END IF; END六、调试与维护6.1 调试技巧-- 临时调试表 CREATE TABLE debug_log ( id INT AUTO_INCREMENT PRIMARY KEY, message TEXT, created_at DATETIME DEFAULT NOW() ); -- 调试存储过程 DELIMITER // CREATE PROCEDURE sp_DebugDemo() BEGIN DECLARE debug_id INT; -- 记录开始 INSERT INTO debug_log (message) VALUES (开始执行存储过程); SET debug_id LAST_INSERT_ID(); -- ... 业务逻辑 -- 记录变量值 INSERT INTO debug_log (message) VALUES (CONCAT(变量值: , variable)); -- 记录结束 UPDATE debug_log SET message CONCAT(message, - 执行完成) WHERE id debug_id; END // DELIMITER ;6.2 系统视图查询-- MySQL: 查看存储过程和触发器 SHOW PROCEDURE STATUS; SHOW CREATE PROCEDURE procedure_name; SHOW TRIGGERS; SHOW CREATE TRIGGER trigger_name; -- 从information_schema查询 SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE PROCEDURE; SELECT * FROM information_schema.TRIGGERS; -- SQL Server: 查看对象定义 SELECT OBJECT_DEFINITION(OBJECT_ID(sp_name)); EXEC sp_helptext sp_name; -- 查看触发器依赖 EXEC sp_depends trigger_name;七、常见问题与解决方案问题1触发器死锁解决方案-- 设置适当的隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 按相同顺序访问表 -- 减少事务持有时间问题2存储过程性能问题优化方案-- 1. 使用临时表减少重复查询 CREATE TEMPORARY TABLE temp_results AS SELECT ...; -- 2. 添加适当的索引 -- 3. 避免在循环中查询数据库 -- 4. 使用SET NOCOUNT ON减少网络流量问题3维护复杂触发器逻辑解决方案-- 1. 拆分为多个简单触发器 -- 2. 使用存储过程封装复杂逻辑 -- 3. 添加详细注释 -- 4. 维护变更文档八、练习与测试练习1创建银行转账系统-- 账户表 CREATE TABLE accounts ( account_id INT PRIMARY KEY, balance DECIMAL(15,2), last_transaction_date DATETIME ); -- 交易表 CREATE TABLE transactions ( transaction_id INT AUTO_INCREMENT PRIMARY KEY, from_account INT, to_account INT, amount DECIMAL(15,2), transaction_date DATETIME, status VARCHAR(20) ); -- 任务 -- 1. 创建转账存储过程包含事务处理 -- 2. 创建触发器记录余额变更 -- 3. 创建触发器检查余额不足 -- 4. 添加每日交易限额检查总结存储过程和触发器是数据库编程的核心组件掌握它们可以提高性能减少网络往返预编译执行计划增强安全性通过封装隐藏数据细节保证数据一致性实现复杂的业务规则简化应用逻辑将业务逻辑移至数据库层关键原则保持触发器简单高效存储过程做好错误处理充分测试并发场景编写清晰的文档和注释定期审查和优化性能