MySQL存储过程与触发器详解
一、存储过程概述 存储过程是预编译并存储在数据库中的SQL语句集合，通过调用可提升应用开发效率。其核心优势包括：
- 模块化设计：将业务逻辑封装为可复用组件
- 参数化处理：支持输入参数和输出结果的双向交互
- 网络优化：减少多条SQL语句的网络传输次数
二、存储过程操作 2.1 创建与调用
DELIMITER //
CREATE PROCEDURE proc_student_count()
BEGIN
DECLARE studentCount INT DEFAULT 0;
SELECT COUNT(*) INTO studentCount FROM student_table;
SELECT studentCount;
END //
DELIMITER ;
2.2 管理操作
-- 查询存储过程信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'database_name';
-- 查看定义详情
SHOW CREATE PROCEDURE proc_student_count;
2.3 变量管理 系统变量
SET GLOBAL max_connections = 1000;
SELECT @@GLOBAL.max_connections;
用户变量
SET @result = (SELECT name FROM user WHERE id = 1);
SELECT @result;
局部变量
DELIMITER //
CREATE PROCEDURE proc_grade_analysis()
BEGIN
DECLARE score INT DEFAULT 58;
DECLARE result VARCHAR(10);
IF score >= 85 THEN
SET result := '优秀';
ELSEIF score >= 60 THEN
SET result := '及格';
ELSE
SET result := '不及格';
END IF;
SELECT result;
END //
DELIMITER ;
三、流程控制结构 3.1 条件判断
CREATE PROCEDURE proc_season_check(IN month INT)
BEGIN
DECLARE season VARCHAR(10);
CASE
WHEN month BETWEEN 1 AND 3 THEN SET season := '第一季度';
WHEN month BETWEEN 4 AND 6 THEN SET season := '第二季度';
WHEN month BETWEEN 7 AND 9 THEN SET season := '第三季度';
WHEN month BETWEEN 10 AND 12 THEN SET season := '第四季度';
ELSE SET season := '非法参数';
END CASE;
SELECT CONCAT('月份:', month, ' 季度:', season);
END;
3.2 循环结构
CREATE PROCEDURE proc_sum_series(IN n INT)
BEGIN
DECLARE total INT DEFAULT 0;
WHILE n > 0 DO
SET total := total + n;
SET n := n - 1;
END WHILE;
SELECT total;
END;
3.3 游标应用
CREATE PROCEDURE proc_data_migration(IN age_limit INT)
BEGIN
DECLARE name_var VARCHAR(100);
DECLARE profession_var VARCHAR(100);
DECLARE cur CURSOR FOR SELECT name, profession FROM user_table WHERE age <= age_limit;
DROP TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table (...);
OPEN cur;
REPEAT
FETCH cur INTO name_var, profession_var;
INSERT INTO temp_table VALUES (name_var, profession_var);
UNTIL NOT FOUND END REPEAT;
CLOSE cur;
END;
四、触发器机制 4.1 基本概念 触发器是在表级操作（INSERT/UPDATE/DELETE）前后自动执行的SQL集合，可用于数据校验、日志记录等场景。支持BEFORE/AFTER事件类型，使用NEW/OLD引用操作记录。
4.2 实践示例
-- 创建日志表
CREATE TABLE operation_log (
id INT AUTO_INCREMENT PRIMARY KEY,
action_type ENUM('INSERT','UPDATE','DELETE'),
operation_time DATETIME,
affected_id INT,
detail TEXT
);
-- 创建更新触发器
CREATE TRIGGER trg_user_update
AFTER UPDATE ON user_table
FOR EACH ROW
BEGIN
INSERT INTO operation_log (action_type, operation_time, affected_id, detail)
VALUES ('UPDATE', NOW(), NEW.id, CONCAT('更新ID:', NEW.id, ' 姓名:', NEW.name));
END;
