05 · 存储程序与事务边界

对应第 23～26 章。使用练习数据，先执行 USE showdocs_mysql_book;。创建例程与触发器需要相应权限，且可能受服务端二进制日志策略限制。示例中的 DELIMITER 是 mysql 客户端命令；通过驱动执行时不要把它当成服务器 SQL。

本单元的重点是控制流与边界：过程怎样传入和返回数据、游标何时读完、触发器在哪条写入中执行、失败究竟撤销了什么。能够创建这些对象，只是第一步；还需要能解释它们在出错和并发情况下的行为。

第 23 章：用参数调用存储过程

DELIMITER //
CREATE PROCEDURE book_enrollment_count(IN p_course_id INT, OUT p_total INT)
BEGIN
  SELECT COUNT(*) INTO p_total
  FROM enrollments WHERE course_id = p_course_id;
END//
DELIMITER ;

CALL book_enrollment_count(101, @total);
SELECT @total;
DROP PROCEDURE book_enrollment_count;

预期 @total 为 2。整个调用可以分成三个阶段：创建时保存过程定义；CALL 时执行过程体；调用结束后从会话变量读取 OUT 输出。DROP PROCEDURE 只是清理定义，不会删除报名数据。

DELIMITER 解决客户端如何识别一条完整语句

过程体内有多条 SQL，内部仍使用分号。mysql 客户端通常见到分号就提交一条语句，如果不临时改变结束符，就可能在过程体尚未完整时把前半段发给服务器。

DELIMITER // 让客户端一直收集到 END//，再把完整的 CREATE PROCEDURE 发给服务器。随后 DELIMITER ; 恢复日常用法。// 不是 SQL 运算符，也不是必须使用的固定符号；选一个不会与正文冲突的结束标记即可。驱动通常直接把完整过程定义作为一个字符串发送，不需要包含这两条客户端命令。

参数、局部变量和会话变量

名称或声明	可见范围	作用
IN p_course_id INT	本次过程调用	输入课程编号
OUT p_total INT	本次调用，返回给调用者	返回一个统计值
INOUT p_value INT	本次调用，既读入又写回	适合明确需要原值参与计算的参数
DECLARE v_count INT	声明它的 BEGIN…END 块	存储程序内部临时状态
@total	当前数据库连接	接收调用输出；其他连接没有同一份变量

参数加 p_、局部变量加 v_ 并非语法要求，而是为了减少同名歧义。把条件写成 WHERE course_id = course_id 很可能变成每行都成立，而不是「列等于输入参数」。

SELECT COUNT(*) INTO p_total 把一行标量结果放入变量。如果普通 SELECT 不带 INTO，它会作为结果集返回客户端；一个过程可以返回多个结果集，调用方需要按驱动规则读取。非聚合的 SELECT ... INTO 若没有行，会触发 NOT FOUND 条件；多于一行则是错误。这里的无分组 COUNT(*) 始终返回一行，因此没有这些基数问题。

存储过程不自动等于事务

过程只是数据库端的一组可调用逻辑。自动提交状态下，过程中的多条普通写入不能仅凭它们在同一个 CALL 中，就视作要么全部成功、要么全部撤销。

事务由调用方管理还是由过程管理，应明确选定。下面的实验过程负责自己的事务，演示失败时 ROLLBACK 并重新抛出错误；只在没有未结束事务的会话中调用。MySQL 不支持用 START TRANSACTION 开启嵌套事务，它可能先提交调用方已有事务。

DELIMITER //
CREATE PROCEDURE book_try_enroll(IN p_learner_id INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  INSERT INTO learners (learner_id, learner_name)
  VALUES (p_learner_id, '过程练习学员');
  INSERT INTO enrollments (learner_id, course_id, paid, enrolled_at)
  VALUES (p_learner_id, 104, 50.00, '2026-10-04');
  SELECT learner_id, course_id, paid
  FROM enrollments WHERE learner_id = p_learner_id;
  ROLLBACK;
END//
DELIMITER ;

CALL book_try_enroll(4);
SELECT COUNT(*) AS remaining_learner FROM learners WHERE learner_id = 4;

正常调用先返回 4 / 104 / 50.00，结束后的学员计数为 0，因为这份教学过程故意在成功路径也回滚，便于重复练习。真实写入流程只有在全部业务条件通过后才在成功路径提交。

可以单独执行 CALL book_try_enroll(1); 观察主键冲突：处理器退出过程、回滚并通过 RESIGNAL 保留失败信息。不要把失败吞掉后仍向调用方报告成功。完成观察后执行：

DROP PROCEDURE book_try_enroll;

例程还涉及执行权限与 SQL SECURITY。部署时应确认以定义者还是调用者身份执行，以及定义者账号是否存在；本页重点是控制流，不把拥有建过程权限等同于所有应用账号都应拥有这些权限。

第 24 章：理解游标的生命周期

游标把结果逐行交给存储程序。先声明变量，再声明游标和处理器，然后打开、读取、关闭。下面特意逐行统计报名数，用来观察控制流；实际统计应优先使用 COUNT(*)。

DELIMITER //
CREATE PROCEDURE book_cursor_count(OUT p_total INT)
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE current_learner INT;
  DECLARE registrations CURSOR FOR SELECT learner_id FROM enrollments;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  SET p_total = 0;
  OPEN registrations;
  read_loop: LOOP
    FETCH registrations INTO current_learner;
    IF done THEN LEAVE read_loop; END IF;
    SET p_total = p_total + 1;
  END LOOP;
  CLOSE registrations;
END//
DELIMITER ;

CALL book_cursor_count(@total);
SELECT @total;
DROP PROCEDURE book_cursor_count;

预期为 3。报名表里学员 1 出现两次，因此这里数的是报名行，并不是不同学员。游标没有 ORDER BY，就不应假设逐行访问顺序；本例只计数，顺序不影响结果。

把一次循环拆开看

阶段	发生什么	p_total
初始化	done 为 FALSE，打开游标	0
第一次 FETCH	成功读取一行，done 仍为 FALSE，计数	1
第二次 FETCH	再读一行并计数	2
第三次 FETCH	再读一行并计数	3
第四次 FETCH	没有下一行，NOT FOUND 处理器把 done 设为 TRUE	3
离开循环	IF done THEN LEAVE 执行，关闭游标	3

读完不是通过给变量自动填一个特殊的「结束值」来表示，而是通过条件处理器设置标记。所以一定要在 FETCH 后先检查 done，再使用刚读取的变量。如果先处理后检查，最后一次失效的变量值可能被重复计算；空结果也可能凭空多算一行。

声明顺序与处理器作用域

在同一个块内，局部变量和条件声明在前，游标声明在后，处理器声明最后，然后才是可执行语句。CONTINUE HANDLER 处理条件后继续执行；EXIT HANDLER 则退出它所在的块。它们并不天然等同于「继续当前循环」与「结束整个连接」。

NOT FOUND 处理器不只可能被 FETCH 触发，同一个作用域里的 SELECT ... INTO 取不到行也可能触发它。因此不要把查询缺失与游标读完不加区分地共用一个 done 标记；复杂逻辑可以使用更小的嵌套块，让各处理器只管理自己的操作。

MySQL 存储程序游标是只读、不可滚动的，按 FETCH 向前读取；不要把它想成客户端上可以随意倒退、跳转或直接修改当前行的表格控件。

什么时候不需要游标

上面的所有循环可以用 SELECT COUNT(*) FROM enrollments 完成。求和、筛选、批量更新等能用集合 SQL 清楚表达的问题，通常应先使用集合操作。逐行控制流更难处理空集合、异常、中断和性能，也增加了理解成本。学习游标是为了识别确实需要顺序处理的场景，并能读懂已有程序，不是把所有 SQL 都改写成循环。

第 25 章：触发器跟随写入执行

DELIMITER //
CREATE TRIGGER book_course_price_guard BEFORE UPDATE ON courses
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'price must be nonnegative';
  END IF;
END//
DELIMITER ;

在交互客户端单独执行下列语句，预期失败，价格保持不变；不要将预期失败语句与后续清理放入默认遇错停止的批处理：

UPDATE courses SET price = -1 WHERE course_id = 101;

查看并清理演示触发器：

SELECT price FROM courses WHERE course_id = 101;
DROP TRIGGER book_course_price_guard;

NEW.price 是本次准备写入的新价格。它小于零时，SIGNAL SQLSTATE '45000' 主动产生应用定义的异常，导致该更新失败，101 的价格仍为 39.00。

时间点、事件与行级执行

触发器由「BEFORE 或 AFTER」「INSERT、UPDATE 或 DELETE」「哪张表」共同定位。FOR EACH ROW 表示一条写入影响多行时，逐行执行触发逻辑；不是每条 SQL 只执行一次。

事件	OLD	NEW	常见用途
INSERT	不可用	待插入或已插入的行	检查或补齐新值
UPDATE	修改前的行	修改后的行	比较前后差异、校验新值
DELETE	将删除或已删除的行	不可用	记录删除前信息

BEFORE 触发器适合在写入前检查，也能在允许的情况下通过 SET NEW.column = ... 调整新值；AFTER 触发器看到已经通过相关检查并完成行操作的结果，但这不表示整个事务已经提交。

本例只定义了 BEFORE UPDATE，因此不会拦截直接插入负价格。需要对所有写入都成立的简单行规则，可以优先放进基础表的 CHECK。若确实使用触发器覆盖插入与更新，需要分别设计对应事件。

触发器失败会撤销到哪里

在本实验的 InnoDB 表上，触发器抛错会使触发它的语句失败，相关语句产生的事务性修改会回滚。它不会因此自动帮你提交或回滚调用者在同一事务中更早执行的所有语句。触发器也不能自行执行 START TRANSACTION、COMMIT 或 ROLLBACK 来管理外层事务。

触发器插入的 InnoDB 审计记录通常与原写入一起提交或回滚。因此「事务最终失败」不一定还能在同一事务内写出的审计表中留下记录。需要记录失败请求时，应另行设计应用日志或独立审计路径，不能仅凭数据库触发器假定所有失败都可追溯。

排查「应用只写了一张表，为什么别的表也变了」时，可检查 SHOW TRIGGERS; 和 SHOW CREATE TRIGGER trigger_name;。隐式逻辑必须能被维护人员找到，才能可靠解释写入的副作用。

第 26 章：决定哪些修改一起生效

START TRANSACTION;
UPDATE courses SET price = 45.00 WHERE course_id = 103;
SAVEPOINT after_first_change;
UPDATE courses SET price = 55.00 WHERE course_id = 104;
ROLLBACK TO SAVEPOINT after_first_change;
SELECT course_id, price FROM courses WHERE course_id IN (103, 104) ORDER BY course_id;
ROLLBACK;

中途查询预期为 103 → 45.00、104 → 59.00；最后整体回滚后 103 恢复为 49.00。回到保存点不会结束事务，最后仍需明确 COMMIT 或 ROLLBACK。若希望保留修改，提交必须是业务逻辑确认后的明确动作。

本实验使用支持事务的 InnoDB。默认自动提交会让事务外的单条写入立即提交；不要把建表、改表或权限语句夹进想要整体回滚的实验。

自动提交、显式事务与保存点

用 SELECT @@SESSION.autocommit, @@SESSION.transaction_isolation; 查看当前连接。autocommit = 1 并不禁止显式事务：执行 START TRANSACTION 后，后续事务性操作仍会等待明确的提交或回滚。结束后，连接恢复原有自动提交方式。

保存点是事务内部的撤销位置，不是小型提交。前面实验可分成：

时点	103 的价格	104 的价格	事务状态
开始前	49.00	59.00	尚未开始
两次 UPDATE 后	45.00	55.00	未提交
回滚到保存点后	45.00	59.00	仍未提交
最后 ROLLBACK 后	49.00	59.00	已结束

RELEASE SAVEPOINT 只是删除这个标记，不会提交事务。回滚到保存点也不应被当成已经释放事务全部锁；业务结束仍需及时提交或回滚，避免连接长时间持有资源。

ACID 与这个例子如何对应

性质	本例中的含义	容易误解之处
原子性	选择整体回滚时，事务内的事务性写入全部撤销	一条语句报错不必然自动结束整个事务
一致性	约束和正确业务逻辑共同维持合法状态	数据库无法自动判断错误的商业规则
隔离性	并发事务按所选隔离级别协调可见性与冲突	不同读取方式可能有不同可见性，不能只记一个口号
持久性	成功提交的数据按系统的持久化机制保存	断电保证还受日志刷盘、存储等配置影响；不能代替备份

实验：一条语句失败后，前面的写入还在吗

在 mysql 交互客户端分步执行，保持同一个连接。先运行：

START TRANSACTION;
INSERT INTO learners (learner_id, learner_name) VALUES (4, '失败处理练习');

然后单独执行预期失败的语句：

INSERT INTO learners (learner_id, learner_name) VALUES (1, '重复主键');

出现重复主键错误后，继续在同一会话查询并回滚：

SELECT learner_id, learner_name FROM learners WHERE learner_id = 4;
ROLLBACK;
SELECT COUNT(*) AS remaining_learner FROM learners WHERE learner_id = 4;

第一次仍能看到学员 4，最后计数为零。普通重复键错误撤销的是失败语句，不是已经成功的前一条插入。不同错误类别的回滚范围可能不同，例如 InnoDB 死锁通常回滚被选中的整个事务；应用应在异常路径明确处理事务，并在确认可重试时重试完整业务操作。

两个会话观察可重复读

这一实验使用独立表，不修改课程基线。先用任意管理员练习连接创建：

CREATE TABLE book_isolation_demo (
  id INT PRIMARY KEY,
  amount INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO book_isolation_demo VALUES (1, 10);

打开两个真正独立的连接 A、B，都选中 showdocs_mysql_book。在同一个编辑器中切换两个文件，不一定代表两个连接；可用 SELECT CONNECTION_ID(); 核对。

第一步，会话 A 开始事务并进行第一次普通读取：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT amount FROM book_isolation_demo WHERE id = 1;

此时得到 10。SET TRANSACTION 在这里仅设置接下来的事务，便于不改变整个会话的默认配置。普通一致性读建立当前事务使用的快照。

第二步，会话 B 修改并提交：

START TRANSACTION;
UPDATE book_isolation_demo SET amount = 20 WHERE id = 1;
COMMIT;

第三步，回到会话 A：

SELECT amount FROM book_isolation_demo WHERE id = 1;
COMMIT;
SELECT amount FROM book_isolation_demo WHERE id = 1;

同一事务里的第二次普通读取仍为 10；提交后新的自动提交查询得到 20。这里说明的是 InnoDB 的普通一致性读。SELECT ... FOR UPDATE、更新语句以及事务自身的写入有另外的规则，不能据此说「一个事务永远只看到开始时的数据」。

两个会话都结束事务后，再清理：

DROP TABLE book_isolation_demo;

不要让某个会话挂着未结束事务就直接删表，否则可能等待元数据锁。若想对比 READ COMMITTED，可以重新建表，把 A 的隔离级别改为 READ COMMITTED 并重复；B 提交后，A 的下一次普通读取将使用新的快照而看到 20。

自测

1. 过程执行到一半报错，前面写入会自动全部撤销吗？ 不一定。先看事务归谁管理、有没有异常处理，以及具体错误的回滚范围。单纯使用 CALL 不是原子性保证。
2. 游标空结果时会计数几次？ 正确顺序下为零：第一次 FETCH 就触发 NOT FOUND，然后立即离开循环。把计数写在 done 判断前会出错。
3. 为什么触发器不能只覆盖 UPDATE 就保证价格永不为负？ INSERT 不会触发 UPDATE 事件；应设计覆盖所有相关写入的约束或事件逻辑。
4. 回滚到保存点后能直接把连接交回池里吗？ 不应该。事务尚未结束，连接池下一位使用者可能继承未提交状态或锁。应在成功、失败路径都明确结束事务。
5. 可重复读实验中为什么最后一次 SELECT 能看到 20？ A 已提交旧事务，之后的自动提交读取使用新的事务视图；它不再受旧快照约束。

最后核对报名数仍为 3、收入仍为 157.00、课程价格仍为 39、79、49、59，并确认已清理本页的 book_ 过程、触发器和实验表。若中途停止，先处理未结束事务，再清理对象。

官方游标规则 · 官方条件处理器 · 官方触发器说明 · 官方事务语法 · InnoDB 一致性读

返回导读 · 下一单元：权限与维护