S11-J08 · 约束、变量、游标、触发器与存储函数：服务端编程的边界

适用读者：要维护遗留数据库（大量触发器/存储过程）的人；被问"为什么不用触发器"的人。
前置：S11-J06、J07、B02（建表）。
覆盖旧文：4520（第 16 章变量/流程控制/游标）、4536/4537（第 10 章建表管理 + 练习）、4540/4541（第 13 章约束 + 练习）、4542（第 14 章视图）、4544/4545（存储函数 + 调用）、4546/4547（第 16 章 + 调用）、4548/4549（第 17 章触发器 + 练习）、4551（第 18 章练习）→ 12 篇 / 约 8 万字符。
扩展点：E-S11-025（六类约束与 ON DELETE 三动作的真实行为）、E-S11-026（三类变量的作用域与优先级）、E-S11-027（游标 + HANDLER 的固定套路与死循环）、E-S11-028（触发器为何在生产被限制）。
下一篇：S11-A04 · 分库分表决策与数据库路由。

1. 一句话结论

把业务逻辑写进数据库（触发器/存储过程）等于把"隐式执行 + 难调试 + 复制不安全"三件事一起搬进系统。 面试正确口径：能用约束表达的用约束，能放应用层的放应用层，服务端编程只用于批处理与数据修复。

出处：A-4540（约束）、A-4546（游标）、A-4548（触发器）、A-4544（存储函数）。

2. 六类约束与 `ON DELETE` 三动作（E-S11-025）

CREATE TABLE orders (
  id      BIGINT PRIMARY KEY AUTO_INCREMENT,          -- 主键：非空 + 唯一 + 聚簇索引
  user_id BIGINT NOT NULL,
  email   VARCHAR(120) UNIQUE,                         -- 唯一：允许多个 NULL（8.0）
  amount  DECIMAL(12,2) DEFAULT 0.00,                   -- 默认值
  status  ENUM('NEW','PAID','CANCEL') DEFAULT 'NEW',     -- 枚举即约束
  qty     INT CHECK (qty > 0),                          -- 检查（8.0.16+ 强制执行）
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

约束	强制的行为	坑
主键	非空 + 唯一 + 聚簇	自增主键不连续（回滚/批量插入预分配会跳号）
唯一	唯一，NULL 不参与比较	多个 NULL 合法；业务"唯一"需 `NOT NULL` 配合
非空	写入校验	8.0 之前 `NOT NULL` 加在 `TIMESTAMP` 上有特殊语义
默认值	未指定列时填充	`DEFAULT CURRENT_TIMESTAMP` 在 8.0 可用表达式
检查	`CHECK` 表达式	8.0.16 前解析但忽略——旧库的 CHECK 可能是"装饰"
外键	引用完整性	高并发下外键检查加锁，很多团队禁用外键改应用层保证

ON DELETE 三动作的真实差别：

动作	删除父行时	风险
`RESTRICT`/`NO ACTION`	拒绝删除	需先清子表
`CASCADE`	级联删除子行	一次误删可能连锁清空多表
`SET NULL`	子行外键置 NULL	子表外键列必须允许 NULL

判据：外键的代价是"父行删除/更新要检查子表并加锁"——单库小表用外键很省心；分库分表后物理外键不可用（跨实例无法约束），只能靠应用层 + 定期校验。

3. 三类变量与作用域（E-S11-026）

类型	写法	作用域	生命周期
用户变量	`SET @x = 1;`（会话级）	当前连接	连接断开即消失
局部变量	`DECLARE x INT DEFAULT 0;`（只在 `BEGIN...END` 内）	存储程序块	块结束消失
系统变量	`@@session.sql_mode`、`SET GLOBAL max_connections`	会话/全局	全局需 `SUPER`/`SYSTEM_VARIABLES_ADMIN`，重启丢失（除非写配置文件）

优先级与坑：

SET @x = 1;
SELECT @x;                    -- 1（用户变量）
-- 存储过程内：
DECLARE x INT DEFAULT 0;      -- 局部变量遮蔽同名用户变量
SET x = 2;
SELECT x, @x;                 -- 2, 1（同名但不同对象！）

坑 1：DECLARE 必须在块首、且在所有可执行语句之前——写在中间直接语法错误。

流程控制：IF...THEN...ELSEIF...END IF、CASE...WHEN...END CASE、LOOP/WHILE/REPEAT、LEAVE/ITERATE（类似 break/continue）。

4. 游标：固定套路与死循环（E-S11-027）

CREATE PROCEDURE fix_orders()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id BIGINT;
  DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status='NEW';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   -- 必须！否则 FETCH 到底后死循环

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id;
    IF done = 1 THEN LEAVE read_loop; END IF;             -- 顺序不能反：先 FETCH 再判 done
    UPDATE orders SET status='CHECKED' WHERE id = v_id;
  END LOOP;
  CLOSE cur;
END;

要点	说明
顺序	`DECLARE`（变量→游标→HANDLER）→ `OPEN` → `LOOP{FETCH; 判 done}` → `CLOSE`
`NOT FOUND` 的触发范围	任何 `SELECT ... INTO` 无行也会置 `done=1`——这是最常见的"只处理了一行"的 bug
性能	逐行处理 = N 次单行 DML，远慢于一条批量 `UPDATE ... WHERE`
替代	能用 `UPDATE ... JOIN` / `INSERT ... SELECT` 就不要游标

判据：游标的唯一合理场景是"必须逐行调用外部逻辑或复杂分支"；纯数据变换一律用集合语句。

5. 触发器：为什么生产不推荐（E-S11-028）

CREATE TRIGGER trg_orders_bi BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.created_at = IFNULL(NEW.created_at, NOW());   -- BEFORE 才能改 NEW
END;

维度	说明
六种组合	`BEFORE/AFTER` × `INSERT/UPDATE/DELETE`
`NEW`/`OLD`	`INSERT` 只有 `NEW`；`DELETE` 只有 `OLD`；`UPDATE` 两者都有
限制	触发器内不能显式提交/回滚；不能对本表做增删改（防递归，8.0 报错）

五个生产级问题（必答）：

① 隐式执行：应用看不到，排障时不知道谁改了数据
② 级联复杂：触发器调触发器，链式放大，难估算影响面
③ 复制风险：binlog 为 STATEMENT 时触发器在从库重放可能不一致（ROW 格式相对安全）
④ 性能不透明：批量导入 10 万行 = 10 万次触发器调用
⑤ 版本与迁移：ORM/中间件/分库分表工具对触发器支持不一致

替代：应用层事件（事务内写业务表 + 消息表）、CDC（binlog 订阅）、定时对账。

6. 视图与存储函数（`4542`/`4544`）

CREATE VIEW v_active AS SELECT id, name FROM users WHERE status='ACTIVE';
SELECT * FROM v_active;             -- 视图是"命名查询"，不是物化表

CREATE FUNCTION email_by_name(n VARCHAR(50)) RETURNS VARCHAR(120)
DETERMINISTIC READS SQL DATA
RETURN (SELECT email FROM users WHERE name = n LIMIT 1);
SELECT email_by_name('alice');      -- 函数可在 SQL 中调用；存储过程不行

对象	能否在 SQL 中调用	返回	注意
视图	✅ 当表用	结果集	不可索引（除非物化/物化视图插件）；嵌套视图易失控
存储函数	✅	单值	必须声明 `DETERMINISTIC`/`READS SQL DATA` 等特征（否则 binlog 不安全时拒绝创建）
存储过程	❌（`CALL`）	可选结果集	事务由调用方控制

坑 2：CREATE FUNCTION 报 ERROR 1418 (HY000): This function has none of DETERMINISTIC... —— 不是权限问题，是没声明函数特征且 log_bin_trust_function_creators=OFF（默认）。

7. 反例：五种"数据库里写业务"的代价

做法	代价
用触发器算余额	看不到执行，审计与对账困难
用游标做批量更新	慢 1~2 个数量级，锁持有时间长
用外键做分库后的完整性	跨实例无法约束，只能应用层保证
依赖 `CHECK` 而库是 5.7	约束不生效
用存储函数做权限判断	每次查询都执行，且难缓存

8. 自测

六类约束？8.0.16 之前 CHECK 有什么问题？
ON DELETE 三动作的差别？分库分表后外键为何不可用？
三类变量的作用域？同名时哪个生效？
游标为什么必须有 NOT FOUND handler？顺序要求？
触发器生产的五个问题？替代方案？

答案

主键/唯一/非空/默认/检查/外键；5.7 及更早解析但忽略 CHECK。
RESTRICT 拒绝、CASCADE 级联、SET NULL 置空；跨实例无法跨库约束。
用户（连接）、局部（块）、系统（会话/全局）；块内同名局部优先。
FETCH 到底不报错会无限循环；先 FETCH 再判 done。
隐式执行、链式放大、复制风险、性能不透明、工具支持不一致；应用层事件/CDC/对账。

9. 下一篇

S11-A04 · 分库分表决策与数据库路由 —— 什么时候该分、分片键怎么选、路由组件（基于 Hash 散列）怎么设计、扩容与双写迁移、跨片查询与事务的代价。

本篇属于系列《S11 · 数据库与 SQL》。旧文来源：A-4520、A-4536–4549、A-4551；扩展点 E-S11-025/026/027/028。版本口径 MySQL 8.0；8.4 相关项标待核。