程序性对象与视图

存储过程、函数、触发器、事件、视图——这一族"把逻辑放进数据库"的对象，互联网工程圈的主流态度是禁用（原因见 03-数据类型与建表.md 的工厂实践一节）。但"禁用"和"不用懂"是两回事：

• 09 章在线变更前列盘点要读 SHOW TRIGGERS，pt-osc 本身就是靠三个触发器做增量（--preserve-triggers 的纠结就来自这里）；
• 11 章备份的坑：mysqldump 默认不导出存储过程和事件，--routines --events 忘了带，恢复出来的库直接缺一块逻辑；
• 02 章说过 TRUNCATE 不触发触发器、INSERT IGNORE 被忽略的行照样执行 BEFORE INSERT 触发器；
• 存量系统里它们满地都是，接手就得会读；单机的数据修复批处理、审计表这类场景，它们也确实比应用层方案顺手。

所以本章的定位是：会用、看懂、知道边界。所有报错串与执行语义均为本地 8.0.28 实例实测（基线 8.4/9.7 行为一致，个别默认值差异文中注明）。

1. 视图：命名好的派生表

1.1 本质

视图不存数据，只是一段起了名字的 SELECT，查询时对基表展开——和派生表（子查询）同一家族，08 章讲过的派生表合并优化对视图同样生效。建一个：

CREATE VIEW v_user_order AS
SELECT o.id AS order_id, o.user_id, o.amount, u.name
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID';

SELECT * FROM v_user_order WHERE user_id = 42;   -- 用起来和表一样

SHOW CREATE VIEW 实测输出里有个细节：

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW ...

ALGORITHM=UNDEFINED（没指定时就这样存）表示"让优化器定"，实际几乎总是按 MERGE 处理。

1.2 MERGE 与 TEMPTABLE

什么视图注定 TEMPTABLE：聚合/DISTINCT/GROUP BY/UNION/窗口函数这些"合并不了"的结构。这也是下一个话题的引子。

1.3 可更新视图与 WITH CHECK OPTION

对视图的 INSERT/UPDATE/DELETE 要翻译回基表才能执行，翻译得动才"可更新"：单表、无聚合、无 DISTINCT/UNION/窗口函数、被改的列直接映射基表列——大体就这些条件（JOIN 视图部分可更新，规则细且版本间有变，用前查当版手册）。违反时实测：

CREATE VIEW v_agg AS SELECT b, COUNT(*) c FROM base GROUP BY b;
UPDATE v_agg SET c = 99 WHERE b = 'x';
-- ERROR 1288 (HY000): The target table v_agg of the UPDATE is not updatable

WITH CHECK OPTION 给视图加一道"写入后必须仍然满足视图 WHERE"的闸门：

CREATE VIEW v_clean AS SELECT id, amt FROM clean WHERE amt > 50 WITH CHECK OPTION;
INSERT INTO v_clean(amt) VALUES (1);
-- ERROR 1369 (HY000): CHECK OPTION failed 't14.v_clean'

注意：CHECK OPTION 校验的是触发器改写后的最终值。实测一例：基表挂了"amt 一律 +100 再翻倍"的 BEFORE INSERT 触发器后，往 v_clean 里插 amt=1 顺利成功——落库 202。视图的"约束"指望不上触发器环境，真正的约束还得靠列定义和应用层。

视图的三大正当用途：权限收敛（只暴露该见的列，配合 GRANT SELECT ON view）、接口稳定（基表拆列改名时视图挡住上层 SQL）、复杂逻辑命名（多表 JOIN 口径固化，避免各处手写不一致）。

1.4 DEFINER：视图里的定时炸弹

视图默认 SQL SECURITY DEFINER：访问视图用的是创建者的权限，不是查询者的。于是经典事故——DBA 建完视图离职、账号被删：

CREATE USER d14@localhost IDENTIFIED BY 'p';
CREATE DEFINER=d14@localhost VIEW v_d AS SELECT id FROM base;
DROP USER d14@localhost;
SELECT * FROM v_d;
-- ERROR 1449 (HY000): The user specified as a definer ('d14'@'localhost') does not exist

处置：ALTER ... SQL SECURITY INVOKER 重建视图，或给存量对象做"definer 账号盘点"（查 information_schema.views.VIEW_DEFINITION 之外，直接看 DEFINER 列）。存储过程/函数/触发器/事件同样带 DEFINER，同一个雷。

2. 存储过程与存储函数

2.1 语法骨架与 DELIMITER

过程体和函数体内部有分号，mysql 客户端默认按分号切语句，所以要换分隔符——DELIMITER 是客户端指令不是 SQL，脚本和工单里别漏：

DELIMITER //
CREATE PROCEDURE p_sum(IN lo INT, OUT total INT, INOUT rows_n INT)
BEGIN
  DECLARE v INT;
  DECLARE done INT DEFAULT 0;
  DECLARE cur CURSOR FOR SELECT amt FROM base WHERE id >= lo;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET total = 0; SET rows_n = 0;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v;
    IF done THEN LEAVE read_loop; END IF;
    SET total = total + v;
    SET rows_n  = rows_n + 1;
  END LOOP;
  CLOSE cur;
END//
DELIMITER ;

CALL p_sum(1, @s, @r);  -- 实测：两行 amt=10+20 → @s=30, @r=2

2.2 过程与函数的分工

2.3 变量与控制流三件套

• 局部变量 DECLARE（必须放 BEGIN 段最前），系统变量 @@global./@@session.，用户变量 @x（会话级、跨语句，实测触发器里 SET @seen=... 可用）。
• 控制流：IF/ELSEIF/ELSE、CASE、WHILE、REPEAT...UNTIL、LOOP...END LOOP + 标签 + LEAVE（相当于 break）/ITERATE（continue）。上面 read_loop: 就是标签用法。
• 调试靠 SELECT/DO 打印或写日志表；没有断点，这是"过程难调试"说法的具体含义。

2.4 游标与 HANDLER

游标是逐行游标（不可反向、默认只读），配 DECLARE ... HANDLER 处理异常：

• DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;——fetch 取空时置标志，是"循环到末尾"的标准写法（2.1 实例）；
• 也可以捕获错误号/SQLSTATE：DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;（EXIT 表示处理后直接退出当前块）。
• 取诊断信息用 GET DIAGNOSTICS：ROW_COUNT（影响行数）、MYSQL_ERRNO（错误号）。

游标能少用就少用：它把集合操作退化成逐行循环，万行以上基本必慢，绝大多数"游标聚合"都能一条集合 SQL 替代（INSERT ... SELECT、UPDATE ... JOIN）。游标的正当场景是"每行要做的事无法用一条 SQL 表达"（比如逐行调不同接口逻辑），而那通常说明该把事挪回应用层。

2.5 SIGNAL / RESIGNAL：主动抛错

-- 触发器/过程体内：
IF new.amt < 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'amt negative';
END IF;
-- 客户端实测收到：ERROR 1644 (45000): amt negative

1644 是"用户自定义条件"的固定错误号，SQLSTATE 45000 是自定义类的约定值。RESIGNAL 在 HANDLER 里"改一下再抛/原样再抛"，比吞掉错误体面得多。

2.6 binlog 开启时的 1418：创建函数被拒

8.0 默认开 binlog，此时创建不带特性声明的函数会被直接拒绝（实测原文）：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
in its declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

原因与 07 章 STATEMENT 格式不安全同源：binlog 按语句复制时，主从各自执行函数，结果不一致就毁了副本。两条正路：

-- 首选：如实声明特性（写进元数据，SHOW CREATE FUNCTION 可见）
CREATE FUNCTION f1(x INT) RETURNS INT DETERMINISTIC RETURN x * 2;

-- 次选：信任创建者（默认 OFF；打开后普通创建即可通过——实测如此）
SET GLOBAL log_bin_trust_function_creators = ON;

函数特性四个值：CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA，外加独立的 SQL SECURITY DEFINER|INVOKER 和 COMMENT。声明要诚实：把"会查表"的函数标成 DETERMINISTIC，优化器就敢把结果当常量用。

2.7 sql_mode 冻结在创建时

实测 SHOW CREATE FUNCTION 输出里带着 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,...——程序对象创建时的会话 sql_mode 被存进定义，以后调用按它执行。全局 sql_mode 改了、连接客户端不同，老过程行为却"顽固地"按当年模式走，这是"升级实例后程序对象行为漂移"类工单的第一现场。排查入口：information_schema.routines 的 SQL_MODE 列。同理被冻结的还有 character_set_client/collation_connection。

3. 触发器

3.1 组合矩阵

两个时机 × 三个事件，FOR EACH ROW 行级（MySQL 只有行级，没有语句级触发器；也没有 INSTEAD OF）：

CREATE TRIGGER trg_bid BEFORE INSERT ON base FOR EACH ROW
BEGIN
  IF new.amt < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'amt negative'; END IF;
END;

触发器与表绑定、随 RENAME TABLE 走（改表名不用重建触发器），SHOW TRIGGERS LIKE 'base' 或 information_schema.triggers 盘点。

3.2 NEW / OLD 读写规则

• BEFORE：NEW 可改——改写落库前的值（审计脱敏、默认值补齐、格式清洗）；OLD 对 INSERT 无意义。
• AFTER：NEW/OLD 只读。想改也改不了——建的时候就被拒：实测 CREATE TRIGGER ... AFTER UPDATE ... SET new.b=... 直接失败，ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger。想改值请用 BEFORE。
• 被触发器"看到"的行已经过前序触发器加工：实测 AFTER INSERT 触发器里读 NEW.B 拿到的是两个 BEFORE 触发器串起来的最终值 ord2AB。

3.3 同一时机同一事件可以有多个，按创建顺序执行

5.7 起放开（5.6 及以前一种时机+事件只许一个，这是很多旧资料说"只能建一个"的出处）。MySQL 没有 Oracle 的 FOLLOWS/PRECEDES，执行顺序就是创建顺序——实测：

-- 先建 trg_a：b 追加 'A'，amt+100；再建 trg_b：b 追加 'B'，amt*2
INSERT INTO base(b, amt) VALUES ('ord', 1);
SELECT b, amt FROM base;   -- 实测：ordAB / 202   → (1+100)*2，a 先 b 后

调顺序没有 API：想换序就 DROP 再按目标顺序重建。多个触发器互相依赖是坏味道——真需要链条，说明逻辑该收进一个触发器或一个存储过程。

3.4 硬限制：不能改"触发它的那张表"

同一条语句的上下文里，触发器/函数不能再碰驱动它的表（实测原文）：

ERROR 1442 (HY000): Can't update table 'base' in stored function/trigger
because it is already used by statement which invoked this stored function/trigger.

想"插一行后回写本表汇总列"这类需求，只能走别的表（新行→聚合表）或挪到应用层同一事务里做。注意 1442 拦的是同一基表；跨表随便写——审计表配方就是靠它：

CREATE TABLE audit(id INT, act VARCHAR(20), ts DATETIME(3));
CREATE TRIGGER trg_audit AFTER INSERT ON base FOR EACH ROW
  INSERT INTO audit VALUES (new.id, 'ins', NOW(3));   -- 实测正常写入

3.5 触发器 × 复制 / DDL / DML 语义（串起前面章节）

• 复制：ROW 格式下触发器只在主库执行，行变更进 binlog，从库回放的是结果（不重放触发器）；STATEMENT 格式则两端各自执行，触发器里任何不确定操作都是主不一致的温床——这直接呼应 07 章"binlog_format 用 ROW"的建议。
• DML 语义：TRUNCATE 不触发（02 章）；INSERT IGNORE 被忽略的行仍执行 BEFORE INSERT 触发器、REPLACE 是 DELETE+INSERT 两组触发器都触发（02 章）。批量导入前想想触发器的每行开销。
• DDL：ALGORITHM=COPY/表重命名路径下触发器随表搬移有历史坑，变更前 SHOW TRIGGERS 留档 diff 是 09 章检查清单第 9 条的由来；pt-osc 自己要在影子表上挂三个触发器，原表已有触发器时默认拒绝。
• 备份：SHOW TRIGGERS 的 DDL 随表导出（mysqldump --triggers 默认 ON），但换库恢复时触发器 DEFINER 指向的账号要在不在，看 1.4 那个雷。

4. 事件调度器：数据库里的 cron

SET GLOBAL event_scheduler = ON;   -- 默认 OFF；见下方持久化注意
CREATE EVENT ev_purge_tmp
ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
DO BEGIN DELETE FROM tmp_job WHERE created_at < NOW() - INTERVAL 1 DAY; END;

• 观察：information_schema.events（实测新建事件 STATUS=ENABLED；ALTER EVENT ev_x DISABLE 后变 DISABLED）、SHOW EVENTS。
• 一次性任务用 ON SCHEDULE AT timestamp，可配 ON COMPLETION PRESERVE 保留记录。
• event_scheduler 默认 OFF 且 SET GLOBAL 不持久——重启回到默认，这是"事件莫名其妙不再跑"的头号原因。8.0 用 SET PERSIST event_scheduler = ON; 落进 mysqld-auto.cnf；5.7 存量实例请写进 my.cnf。本手册实测实例恰好开在 ON（实例配置所致），不代表默认值。
• 上一轮还没跑完时，错过的下一次调度直接跳过（不排队补跑），事件逻辑要么幂等要么自带补账。
• 定位：单机小任务的省事方案。多实例、需要重跑/告警/依赖编排的任务，用应用侧调度框架或专门的调度中心——12 章和本仓库 asynq 手册的思路。云上托管实例能否开 EVENT 看控制台参数组（很多 RDS 默认锁 OFF）。

5. XA 分布式事务（呼应 07 章内部 2PC）

07 章的"两阶段提交"是 redo 与 binlog 之间的引擎内协议，用户无感；对外那套 XA 命令是应用可操作的跨实例协调原语，语法实测可跑：

XA START 'g1', 's1', 0;      -- gtrid 全局号 / bqual 分支号 / formatID(通常 0)
INSERT INTO base(b, amt) VALUES ('xa', 100);
XA END 'g1', 's1', 0;
XA PREPARE 'g1', 's1', 0;    -- 此刻本分支已可独立存活，等协调者裁决
XA RECOVER;                  -- 实测列出：gtrid_length=2, bqual_length=2, data='g1s1'
XA COMMIT 'g1', 's1', 0;     -- 或 XA ROLLBACK

要点三条：

• prepared 分支会悬挂：会话断了、进程崩了，prepared 状态照旧留在引擎里，占着行锁和 undo；XA RECOVER 盘点、由协调者（或人）决定 commit/rollback。生产里"谁裁决、何时裁决"必须有方案，否则悬挂就是事故。
• binlog 开启时 XA 与 binlog 同样走两阶段登记（恢复协议与 07 章 6.3 节一致：崩溃后 binlog 里找得到 Xid 就提交，找不到就回滚）。
• 工程定位：分库分表的跨片一致性，主流答案仍是"本地事务 + 消息表/MQ 最终一致"（12 章）；XA/TCC 强一致方案成本高（全局锁持有、悬挂治理、性能折损），只在确实必须同步强一致的链路用。

6. 盘点与权限：接手一个"程序对象很多"的库

-- 全库程序对象清单（按库过滤）
SELECT routine_schema, routine_name, routine_type, data_type, sql_mode, definer
FROM information_schema.routines WHERE routine_schema = 'mydb';
SELECT trigger_schema, trigger_table, event_object_type, action_timing, definer
FROM information_schema.triggers WHERE trigger_schema = 'mydb';
SELECT event_schema, event_name, status, definer FROM information_schema.events;
SELECT table_schema, table_name, definer, security_type, is_updatable
FROM information_schema.views WHERE table_schema = 'mydb';

SHOW CREATE PROCEDURE mydb.p_sum\G   -- 过程/函数/触发器/事件/视图都有 SHOW CREATE 家族

权限侧：CREATE ROUTINE（创建）、ALTER ROUTINE（修改/删除），事件另要 EVENT 权，触发器要 TRIGGER；带 DEFINER= 子句创建需要 SET_USER_ID 类权限（8.0 起用它替代旧 SUPER，00 章权限最小化一节同源）。备份带 --routines --events（11 章原文重申过）；迁移/恢复演练时把程序对象当作 schema 的一部分对账，别只 diff 表结构。

实战建议

1. 把"禁用"翻译成边界而不是教条：业务逻辑不进数据库（可测、可版本控制、可水平扩展三样都缺）；但贴着数据走的批处理（一次性洗数、归档清理、审计落表）用过程/触发器写反而比应用层轮询干净。允许清单：审计触发器、报表口径视图、DBA 维护过程；禁止清单：业务流程步骤、跨表事务编排、含游标大循环的"性能优化"。
2. 程序对象进版本控制，用 Flyway/Liquibase 或仓库里的 db/migrations/*.sql 管理——“不在 git 里的代码"这个原罪可以用流程消解，剩下的难调试才是本质缺点。
3. 上线前查三样元数据：SHOW CREATE 看 DEFINER 是否活账号、sql_mode 是否目标模式、函数是否如实标了特性（防 1418 类事故与优化器误判）。
4. 触发器的性能账按行算：每行多几次逻辑，千万行批量导入前先在预发压一遍，或直接导入期间 DISABLE、导完重建对账。
5. EVENT 只当"单机小 cron”：重跑、告警、依赖编排一律交给调度平台；用了 EVENT 就把 SET PERSIST event_scheduler 和"错过不补跑"两条写进运维手册。
6. XA 生产化必须有悬挂治理：XA RECOVER 定期巡检 + 超时兜底裁决；没有裁决方案就退回最终一致。
7. DDL 变更与备份流程把本章对象纳入清单：09 章的触发器盘点、11 章的 --routines --events，是这两个流程对本章的硬性要求。