存储过程与触发器：自动化你的数据库

云笙521

213人浏览 · 2026-07-09 16:08:37

云笙521 · 2026-07-09 16:08:37 发布

存储过程与触发器：自动化你的数据库

一句话总结：存储过程是把常用 SQL 逻辑封装成可调用的程序单元，实现代码复用和性能优化；触发器是数据库的"自动哨兵"，在特定事件发生时自动执行预定义操作，两者结合让数据库从被动存储升级为主动智能处理引擎。

一、为什么需要存储过程和触发器？

1.1 没有它们时的问题

假设你开发了一个电商系统，每次用户下单时，都需要执行以下操作：

插入订单记录
扣减商品库存
更新用户消费金额
记录操作日志

如果在应用程序里写这些 SQL，会出现：

代码重复：多个地方（Web、App、小程序）都要写同样的 SQL 组合
网络开销：每个 SQL 都要发一次请求到数据库，来回通信耗时长
安全性差：SQL 分散在应用中，难以统一管理和审计
性能瓶颈：复杂查询在应用层处理，数据库优势没有发挥

存储过程和触发器就是解决这些问题的利器。

二、存储过程：数据库里的"函数"

2.1 什么是存储过程？

存储过程（Stored Procedure）是预先编译好并存储在数据库中的一组 SQL 语句。调用时只需传入参数，数据库直接执行，无需重新编译。

2.2 创建存储过程（MySQL 示例）

-- 修改分隔符，避免和过程中的分号冲突
DELIMITER //

CREATE PROCEDURE 查询学生信息(IN 学生学号 CHAR(10))
BEGIN
    SELECT 学号, 姓名, 性别, 专业
    FROM 学生
    WHERE 学号 = 学生学号;
END //

DELIMITER ;

-- 调用存储过程
CALL 查询学生信息('2024001');

2.3 带输入输出参数的存储过程

DELIMITER //

CREATE PROCEDURE 统计系人数(
    IN  系编号 CHAR(2),       -- 输入参数
    OUT 人数 INT              -- 输出参数
)
BEGIN
    SELECT COUNT(*) INTO 人数
    FROM 学生
    WHERE 系号 = 系编号;
END //

DELIMITER ;

-- 调用
CALL 统计系人数('01', @人数);
SELECT @人数 AS 计算机系人数;

2.4 带逻辑判断的存储过程

DELIMITER //

CREATE PROCEDURE 给学生加分(
    IN 学生学号 CHAR(10),
    IN 加分 INT
)
BEGIN
    DECLARE 当前成绩 INT;
    
    -- 查询当前成绩
    SELECT 成绩 INTO 当前成绩
    FROM 选课
    WHERE 学号 = 学生学号;
    
    -- 判断加分后是否超过 100
    IF 当前成绩 + 加分 > 100 THEN
        UPDATE 选课 SET 成绩 = 100 WHERE 学号 = 学生学号;
    ELSE
        UPDATE 选课 SET 成绩 = 成绩 + 加分 WHERE 学号 = 学生学号;
    END IF;
    
    -- 返回更新后的成绩
    SELECT 成绩 AS 更新后成绩 FROM 选课 WHERE 学号 = 学生学号;
END //

DELIMITER ;

-- 调用
CALL 给学生加分('2024001', 10);

2.5 存储过程的优点

优点	说明
性能提升	预编译执行，省去解析和优化时间；减少网络往返
代码复用	一处编写，多处调用，避免重复代码
安全性	用户只需执行权限，不需要底层表的直接访问权限
维护方便	业务逻辑修改只需改存储过程，不用改应用代码
事务封装	把多个操作封装在一个事务中，保证原子性

2.6 存储过程的缺点

缺点	说明
可移植性差	MySQL 和 PostgreSQL 的存储过程语法差异大，迁移成本高
调试困难	没有 IDE 友好调试工具，排查问题麻烦
版本控制	存储过程在数据库里，不在 Git 仓库里，版本管理困难
扩展性差	不适合分布式数据库，可能成为性能瓶颈

现代微服务架构中，存储过程的使用有所减少，但在报表、ETL、金融核心系统等场景仍有重要价值。

三、触发器：数据库的"自动哨兵"

3.1 什么是触发器？

触发器（Trigger）是特殊的存储过程，它不需要手动调用，而是在特定事件（INSERT/UPDATE/DELETE）发生前或发生后自动执行。

3.2 触发器的三要素

要素	说明	示例
事件	什么操作触发	INSERT、UPDATE、DELETE
时机	操作前还是操作后	BEFORE、AFTER
动作	触发后执行什么	执行一段 SQL 逻辑

3.3 创建触发器（MySQL 示例）

-- 创建日志表
CREATE TABLE 操作日志 (
    日志ID INT AUTO_INCREMENT PRIMARY KEY,
    表名 VARCHAR(50),
    操作类型 VARCHAR(10),
    操作时间 DATETIME DEFAULT CURRENT_TIMESTAMP,
    操作内容 TEXT
);

-- 创建触发器：记录学生表的插入操作
DELIMITER //

CREATE TRIGGER 记录学生插入
AFTER INSERT ON 学生
FOR EACH ROW
BEGIN
    INSERT INTO 操作日志 (表名, 操作类型, 操作内容)
    VALUES ('学生', 'INSERT', CONCAT('新增学生: ', NEW.学号, ' - ', NEW.姓名));
END //

DELIMITER ;

-- 测试：插入学生时，日志自动记录
INSERT INTO 学生 (学号, 姓名, 性别) VALUES ('2024006', '周八', '男');
SELECT * FROM 操作日志;

3.4 NEW 和 OLD 关键字

关键字	含义	适用场景
NEW	新值（插入或更新后的值）	INSERT、UPDATE 触发器
OLD	旧值（更新或删除前的值）	UPDATE、DELETE 触发器

-- 记录学生修改前后的变化
DELIMITER //

CREATE TRIGGER 记录学生修改
AFTER UPDATE ON 学生
FOR EACH ROW
BEGIN
    INSERT INTO 操作日志 (表名, 操作类型, 操作内容)
    VALUES (
        '学生', 
        'UPDATE', 
        CONCAT(
            '学生 ', NEW.学号, ' 从 [', OLD.姓名, '] 改为 [', NEW.姓名, ']',
            ', 年龄从 ', OLD.年龄, ' 改为 ', NEW.年龄
        )
    );
END //

DELIMITER ;

3.5 级联操作触发器

-- 当系被删除时，自动将该系学生转移到"待分配"状态
DELIMITER //

CREATE TRIGGER 系删除处理
BEFORE DELETE ON 系
FOR EACH ROW
BEGIN
    UPDATE 学生 SET 系号 = '00' WHERE 系号 = OLD.系号;
    
    INSERT INTO 操作日志 (表名, 操作类型, 操作内容)
    VALUES ('系', 'DELETE', CONCAT('系 ', OLD.系名, ' 被删除，相关学生已转移'));
END //

DELIMITER ;

3.6 触发器的使用场景

场景	触发器方案
审计日志	记录所有对敏感表的修改
数据同步	主表修改后，自动更新相关表
级联操作	删除父表时自动处理子表
数据校验	插入前检查数据合法性（BEFORE 触发器）
自动填充	自动记录创建时间、修改时间

3.7 触发器的注意事项

注意点	说明
递归触发	触发器 A 触发 B，B 又触发 A，可能导致死循环。需要设置递归深度限制
性能影响	触发器在事务中同步执行，过多触发器会严重影响写入性能
隐式操作	触发器是"隐式"执行的，开发者可能意识不到，导致调试困难
事务一致性	触发器中的操作失败，会导致整个事务回滚

四、游标：逐行处理数据

4.1 什么是游标？

游标（Cursor）是指向查询结果集的指针，可以逐行读取数据，适合需要逐行处理（而非批量处理）的场景。

4.2 游标使用示例

DELIMITER //

CREATE PROCEDURE 给所有学生加分(IN 加分值 INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE 当前学号 CHAR(10);
    
    -- 声明游标
    DECLARE 学生游标 CURSOR FOR
        SELECT 学号 FROM 学生;
    
    -- 游标结束时的处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN 学生游标;
    
    读取循环: LOOP
        FETCH 学生游标 INTO 当前学号;
        IF done THEN
            LEAVE 读取循环;
        END IF;
        
        -- 逐行处理：给每个学生加分
        UPDATE 选课 SET 成绩 = LEAST(成绩 + 加分值, 100) WHERE 学号 = 当前学号;
    END LOOP;
    
    CLOSE 学生游标;
END //

DELIMITER ;

⚠️ 游标是逐行处理，性能远低于集合操作。能用批量 SQL 解决的，不要用游标。

五、存储过程 vs 触发器 vs 应用层代码

维度	存储过程	触发器	应用层代码
调用方式	显式 CALL	隐式自动执行	程序员主动调用
适用场景	复用性高的业务逻辑	自动响应、审计、校验	复杂业务逻辑、外部交互
可控性	高	低（自动执行）	最高
调试难度	中	高	低
可移植性	低	低	高
现代架构中	报表/ETL/金融核心	审计/简单校验	主流推荐

六、动手练习

练习 1：创建存储过程

创建一个存储过程 统计课程平均分，输入课程号，输出该课程的平均分、最高分和最低分。

练习 2：创建触发器

创建 BEFORE INSERT 触发器，在插入学生时自动检查：

年龄必须在 0-120 之间
如果不符合，抛出错误，阻止插入

练习 3：综合实战

实现一个"下订单"存储过程：

输入：用户ID、商品ID、购买数量
检查库存是否足够
如果足够，插入订单记录，扣减库存，更新用户消费金额
如果不足，返回错误信息

要求：使用事务保证原子性。

七、常见误区与避坑指南

误区	正确理解
“触发器越多越好”	触发器是双刃剑，过多会导致性能下降和调试困难
“存储过程能完全替代应用层”	存储过程适合数据密集操作，应用层适合业务逻辑和外部交互
“游标是处理数据的标准方式”	游标逐行处理效率低，能用集合操作（UPDATE/INSERT SELECT）就别用游标
“触发器中的错误不会影响主操作”	触发器在事务中执行，触发器失败会导致整个事务回滚
“存储过程不需要事务控制”	存储过程内部应显式使用 BEGIN/ COMMIT/ROLLBACK 保证原子性