9.3 存储过程、触发器与动态 SQL

9.3 存储过程、触发器与动态 SQL：把规则搬进数据库？

本节摘要：存储过程把一段带流程控制的 SQL 存进数据库一次调用，触发器在增删改时自动执行，动态 SQL 在运行期拼出语句文本。三者共同的问题是"什么逻辑值得搬进数据库"：强一致的核心规则（如扣库存）适合上收，业务流程与频繁变更的规则留在应用层。动态 SQL 的拼接是注入的温床，参数化是唯一正解。

一、把业务规则搬进数据库

先看一个真实冲突。订单服务要扣库存，应用层代码是"查出库存、判断够不够、更新减一"三步，两个并发请求同时通过判断，库存 1 被扣成 -1——应用层的"检查再更新"存在天然竞态窗口。数据库侧的解法有两种浓度：轻量级是把整个判断塞进一条 UPDATE（第 2 章的表达式更新），重量级是存储过程把流程封装进库内执行：

-- 存储过程：扣库存 强一致版（MySQL 语法 节选核心）
DELIMITER //
CREATE PROCEDURE deduct_stock(IN p_book_id INT, IN p_qty INT, OUT p_result VARCHAR(20))
BEGIN
  DECLARE v_stock INT;

  SELECT stock INTO v_stock FROM books WHERE id = p_book_id FOR UPDATE;  -- 加锁读
  IF v_stock IS NULL THEN
    SET p_result = '书不存在';
  ELSEIF v_stock < p_qty THEN
    SET p_result = '库存不足';
  ELSE
    UPDATE books SET stock = stock - p_qty WHERE id = p_book_id;
    SET p_result = '扣减成功';
  END IF;
END //
DELIMITER ;

-- 调用：一条语句完成 判断加更新
CALL deduct_stock(1, 2, @r);
SELECT @r AS 结果;
​

Query OK, 0 rows affected (0.01 sec)

+--------------+
| 结果         |
+--------------+
| 扣减成功     |
+--------------+
​

存储过程的卖点：流程在数据库内一次往返跑完（省网络来回）、FOR UPDATE 的锁在过程内全程持有（竞态窗口关闭）、多处应用共用同一份规则（口径统一）。代价同样具体：逻辑进了数据库，版本管理、代码评审、单元测试的工具链全要另搭；数据库成为业务逻辑的单点，扩容与迁移成本上升。业界的钟摆近二十年明显摆向应用层，存储过程收缩到"强一致 + 高频 + 稳定"的核心动作——扣库存、账户划转这类，是它剩下的自留地。

触发器：自动执行的守门员

触发器挂在表上，INSERT/UPDATE/DELETE 发生时自动执行，不需要任何调用方知情。典型用途是兜底校验与审计日志：

-- 触发器：订单明细变动时 自动同步订单合计金额（BEFORE 触发 校验并阻断）
DELIMITER //
CREATE TRIGGER trg_items_guard BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  IF NEW.quantity <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数量必须大于零';
  END IF;
END //
DELIMITER ;

-- 试插入非法数据
INSERT INTO order_items (order_id, book_id, quantity) VALUES (201, 1, 0);
​

ERROR 1644 (45000): 数量必须大于零
-- 插入被触发器拦下 数据库层面杜绝脏明细
​

NEW 关键字代表"即将插入的新行"（UPDATE 触发器里还有 OLD 代表旧值）。这个例子体现了触发器的独特价值：无论数据从哪个入口进来（应用、脚本、DBA 手工），规则都生效——应用层校验可以被绕过，触发器不能。审计场景同理：AFTER UPDATE 触发器把 OLD 与 NEW 的差值写进日志表，谁改的、改了什么，留痕可查。

但触发器是"隐性行为"的极致：SELECT 表面看不出任何附带动作，出问题时没人第一时间想到"有个触发器在捣乱"。调试困难、性能随行数放大（FOR EACH ROW 逐行执行）、层级深了互相触发，这三宗罪让多数团队立规：触发器只做兜底校验与审计，不做业务流程，且一张表上的触发器数量越少越好。

三种可编程对象的选型表

动态 SQL：运行期才定形的语句

以上所有语句在写下的那一刻就定形了；动态 SQL 是"语句的文本在运行期拼出来再执行"。报表场景的合法需求：让用户选排序字段与方向——ORDER BY 的列名没法用普通参数占位符表达，只能拼接：

-- 动态 SQL（应用侧伪代码形态）结构参数只能拼接
SET @col = 'price';            -- 来自白名单校验后的字段名
SET @dir = 'DESC';
SET @sql = CONCAT('SELECT title, price FROM books ORDER BY ', @col, ' ', @dir, ' LIMIT 5');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
​

+-----------------------+----------+
| title                 | price    |
+-----------------------+----------+
| 算法导论              |   128.00 |
| 数据库系统概念        |    89.00 |
+-----------------------+----------+
​

PREPARE/EXECUTE 是数据库侧的动态执行三件套，应用侧的等价物是各语言驱动里的参数化查询接口。动态 SQL 的头号风险是 SQL 注入：若拼接的内容来自用户输入且未校验，用户输入的不再是"价格"，而是一段恶意的语句文本：

-- 注入现场：用户输入被当成语句的一部分执行
-- 用户在"书名"输入框键入：  x' ; DROP TABLE books; --
拼接结果： SELECT * FROM books WHERE title = 'x' ; DROP TABLE books; --'
​

三条语句被执行 第二条 DROP 直接删表 引号被闭合 注释符吞掉残余语法
​

防御的铁律只有一条：值永远走参数占位符，绝不全靠拼接——WHERE title = ? 把值交给驱动转义；只有表名、列名、排序方向这类"结构本身"才允许拼接，且来源必须是白名单（服务端枚举合法值），不是用户原话。第 9.2 节的最小权限在这里形成纵深防御：即便注入发生，只读账号也 DROP 不动任何表。

⚠️ 常见坑：把"能不能用"当"该不该用"。存储过程、触发器、动态 SQL 都能解决眼前问题，但每一样都把复杂度从应用层搬进更难测试、更难版本化的地方。动手前先问：这段逻辑一年要改几次？改一次要过几道流程？答案越高，越应该留在应用层。

💡 关键直觉：可编程对象是数据库的"自动守卫与内部通道"——守卫（触发器）拦在每张门口，通道（存储过程）为高频核心动作开快速路，而动态 SQL 是给"结构不定"的语句留的活口。守卫宜少、通道宜精、活口必须上锁。

本节要点回顾

• 存储过程收发自如：库内一次往返、锁全程持有、口径统一；代价是测试与版本化工具链缺失，收缩用于强一致核心动作；
• 触发器是不可绕过的守门员：兜底校验与审计留痕的独特价值；隐性调试困难，只做守卫不做流程；
• 动态 SQL 处理结构参数：表名列名方向只能拼接，但必须白名单校验；
• 值一律参数化：注入的成因是"用户输入变成语句结构"，占位符让输入永远只是值；
• 最小权限构成纵深防御：注入发生时，只读账号把损失锁死在数据泄露之外；
• 选型问改动频率：一年改几次、一次过几道流程，频率越高越留应用层。