RootStack

06-存储过程与触发器

9分钟阅读

MySQL 06 - 存储过程与触发器

存储过程和触发器是 MySQL 的”自动化”能力——把业务逻辑下沉到数据库层，减少应用与数据库之间的往返。

本章示例表：为了让每段代码都能直接运行，本章统一使用以下表结构。先执行一遍再往后读：

CREATE DATABASE IF NOT EXISTS demo CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE demo;
 
CREATE TABLE IF NOT EXISTS students (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(50) NOT NULL,
    class_id INT,
    score    INT
);
 
CREATE TABLE IF NOT EXISTS student_logs (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    action     VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO students (name, class_id, score) VALUES
    ('张三', 1, 85), ('李四', 1, 92), ('王五', 2, 77);

一、存储过程

1.1 什么是存储过程

存储过程是预编译的 SQL 语句集合，存储在数据库中，通过 CALL 调用。类似 C 语言的函数：有参数、有逻辑、能返回结果。

-- 最简单的存储过程：查询学生数量
DELIMITER //
CREATE PROCEDURE get_student_count()
BEGIN
    SELECT COUNT(*) AS total FROM students;
END //
DELIMITER ;
 
-- 调用
CALL get_student_count();

为什么需要 DELIMITER？ 客户端默认把分号 ; 当作「语句结束」，而存储过程内部有多条以分号结尾的语句。DELIMITER // 临时把结束符改成 //，这样客户端会一直读到 END // 才发送整段给服务器；结束后必须用 DELIMITER ; 改回分号。在图形化客户端（DBeaver/Workbench）中通常可以省略 DELIMITER 直接执行。

1.2 参数类型

类型说明类比 C 函数
IN输入参数（默认），只读值传递
OUT输出参数，存储过程写入后调用者可读指针传递（写）
INOUT既是输入又是输出引用传递
DELIMITER //
CREATE PROCEDURE find_by_class(
    IN p_class_id INT,        -- 输入：班级ID
    OUT p_count INT            -- 输出：该班人数
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM students
    WHERE class_id = p_class_id;
END //
DELIMITER ;
 
-- 调用
CALL find_by_class(3, @cnt);
SELECT @cnt AS class_3_count;

1.3 变量与流程控制

DELIMITER //
CREATE PROCEDURE classify_score(IN p_score INT)
BEGIN
    DECLARE v_grade CHAR(1);          -- 声明变量
    
    IF p_score >= 90 THEN
        SET v_grade = 'A';
    ELSEIF p_score >= 80 THEN
        SET v_grade = 'B';
    ELSEIF p_score >= 60 THEN
        SET v_grade = 'C';
    ELSE
        SET v_grade = 'F';
    END IF;
    
    SELECT v_grade AS grade;
END //
DELIMITER ;
 
CALL classify_score(85);  -- 返回 B

1.4 循环

DELIMITER //
CREATE PROCEDURE generate_numbers(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE result TEXT DEFAULT '';
    
    WHILE i <= n DO
        SET result = CONCAT(result, i, ' ');
        SET i = i + 1;
    END WHILE;
    
    SELECT result;
END //
DELIMITER ;
 
CALL generate_numbers(5);  -- 返回 "1 2 3 4 5"

1.5 游标（Cursor）

游标允许逐行处理查询结果，类似 C 的 for 循环遍历数组：

DELIMITER //
CREATE PROCEDURE update_all_scores()
BEGIN
    DECLARE v_id INT;
    DECLARE v_score INT;
    DECLARE v_done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT id, score FROM students;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_id, v_score;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        -- 对每个学生的分数加 5 分（不超过 100）
        UPDATE students
        SET score = LEAST(v_score + 5, 100)
        WHERE id = v_id;
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

1.6 存储过程 vs 应用代码

维度存储过程应用代码 (Java/Python)
性能预编译，减少网络往返每次发送完整 SQL
维护分散在数据库中，版本控制困难集中在代码仓库，Git 管理
测试难以单元测试容易测试
移植绑定特定数据库方言数据库无关（ORM）
适用场景批量数据处理、ETL、报表业务逻辑、CRUD

建议：简单查询和批量操作可以用存储过程，复杂业务逻辑放在应用层。

二、触发器

触发器是自动执行的 SQL 代码，在 INSERT/UPDATE/DELETE 操作前后触发。

2.1 基本语法

-- 日志表已在章首创建（student_logs），此处直接建触发器
-- 创建触发器：插入学生时自动记录日志
DELIMITER //
CREATE TRIGGER student_after_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs (student_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END //
DELIMITER ;

2.2 触发器类型

类型触发时机用途
BEFORE INSERT插入前数据校验、默认值填充
AFTER INSERT插入后日志记录、级联更新
BEFORE UPDATE更新前数据校验、旧值备份
AFTER UPDATE更新后日志记录、统计更新
BEFORE DELETE删除前数据备份、软删除
AFTER DELETE删除后日志记录、级联清理

2.3 NEW 和 OLD

操作OLDNEW
INSERT不存在新插入的行
UPDATE更新前的行更新后的行
DELETE被删除的行不存在
-- 更新前校验分数范围
DELIMITER //
CREATE TRIGGER student_before_update
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
    IF NEW.score < 0 OR NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Score must be between 0 and 100';
    END IF;
END //
DELIMITER ;

2.4 触发器 vs 应用代码

维度触发器应用代码
执行位置数据库内部应用程序
性能无网络开销有网络开销
可见性隐式执行，调试困难显式调用，易于调试
适用场景审计日志、数据一致性业务逻辑

建议：触发器适合做审计日志和数据完整性约束，复杂业务逻辑放在应用层。

三、事件调度器

MySQL 内置的定时任务系统，类似 Linux 的 cron：

-- 每天凌晨 2 点清理 30 天前的日志
DELIMITER //
CREATE EVENT clean_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 2 HOUR)   -- 明天凌晨 2 点开始
DO
BEGIN
    DELETE FROM student_logs
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;
 
-- 查看事件
SHOW EVENTS;
 
-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

四、实战：学生成绩管理系统

-- 1. 创建日志表
CREATE TABLE score_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    old_score INT,
    new_score INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 2. 创建触发器：分数变更时记录
DELIMITER //
CREATE TRIGGER score_change_audit
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    IF OLD.score != NEW.score THEN
        INSERT INTO score_audit (student_id, old_score, new_score)
        VALUES (OLD.id, OLD.score, NEW.score);
    END IF;
END //
DELIMITER ;
 
-- 3. 创建存储过程：批量调整分数
DELIMITER //
CREATE PROCEDURE adjust_scores(IN p_class_id INT, IN p_adjustment INT)
BEGIN
    UPDATE students
    SET score = LEAST(GREATEST(score + p_adjustment, 0), 100)
    WHERE class_id = p_class_id;
    
    SELECT ROW_COUNT() AS affected_rows;
END //
DELIMITER ;
 
-- 4. 调用
CALL adjust_scores(3, 5);  -- 3班全体加5分

五、注意事项

  1. 存储过程调试困难：没有断点、没有单步执行，出错只能 SELECT 打印
  2. 触发器隐式执行：一个 UPDATE 可能触发多个触发器，连锁反应难以预测
  3. 版本控制：存储过程和触发器的变更需要额外的迁移脚本管理
  4. 性能影响：大量行操作时，触发器的 FOR EACH ROW 可能成为瓶颈

练习

序号任务验收标准
1写一个存储过程，输入班级 ID，返回该班平均分（OUT 参数）CALL 后能读出正确的平均分
2给 students 表写 BEFORE UPDATE 触发器，阻止分数小于 0更新为负数时报出自定义错误信息
3写 AFTER DELETE 触发器，删除学生时把姓名与删除时间记入日志表删除后日志表出现对应记录
4创建事件，每天清理 30 天前的日志SHOW EVENTS 能看到事件，event_scheduler 为 ON
5对比存储过程与 Java/Python 代码实现同一逻辑能说出两种方案各自的优缺点

关系图谱

反向链接