为什么个人站长的数据库里也需要存储过程
很多个人站长从"SQL 能跑就行"起步，业务逻辑全写在 PHP 或 Node 的代码里，一次请求打十几条 SQL。站点小的时候没问题，一旦遇到批量任务——比如每天凌晨要对几万条文章做归类统计、要清理垃圾评论、要把过期数据归档——代码里就会堆出一大坨循环，几十条 SQL 来回往返，速度慢还容易出错。这时候把逻辑下沉到数据库层，用存储过程（Stored Procedure）、触发器（Trigger）和游标（Cursor）来做，往往能把一次批量操作从"几百次网络往返"压缩成"一次调用"，并且天然带事务保护。
本文不堆文档，而是用一个真实场景串起来讲：假设你有一个内容站，需要（1）每天把 post 表里当天阅读量增长的记录汇总进一张 daily_stats 统计表；（2）在文章被删除时自动清理关联的标签关系记录；（3）对一批文章逐个处理时要用到游标。下面从建表到写法、从踩坑到调优，完整走一遍。
先建三张表：内容、统计与关系
为了让例子贴近真实站点，我们先定义基础结构。注意这里的字段类型和索引选择都是有讲究的，尤其是统计表，它的主键设计直接影响写入性能。
CREATE TABLE post (
cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
views INT UNSIGNED NOT NULL DEFAULT 0,
status VARCHAR(16) NOT NULL DEFAULT 'publish',
created INT UNSIGNED NOT NULL,
PRIMARY KEY (cid),
KEY idx_created (created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE daily_stats (
stat_date DATE NOT NULL,
cid INT UNSIGNED NOT NULL,
delta_views INT NOT NULL DEFAULT 0,
PRIMARY KEY (stat_date, cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE post_tag (
cid INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
PRIMARY KEY (cid, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;这里用了一个小技巧：daily_stats 的主键是 (stat_date, cid) 的复合主键，而不是自增 ID。因为统计场景下我们几乎永远是按日期 + 文章查询，复合主键本身就是聚簇索引，既省了一个二级索引的空间，又让"查某天的数据"变成顺序扫描。这个细节在数据量大起来之后能省下可观的 IO。
存储过程：把"当天汇总"写成一次调用
存储过程的第一价值是"一次网络往返完成多步逻辑"。下面这个 sp_collect_daily 接收一个目标日期，把 post 表当前阅读量写入统计表。为了不依赖外部传入的"昨天阅读量"，我们采用增量记录法：先准备好昨天的基线，用 INSERT ... ON DUPLICATE KEY UPDATE 保证重复执行安全（幂等）。
DELIMITER $$
CREATE PROCEDURE sp_collect_daily(IN p_date DATE)
BEGIN
DECLARE v_rows INT DEFAULT 0;
-- 用开关变量模拟"出错即回滚"的语义
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'procedure failed' AS msg;
END;
START TRANSACTION;
INSERT INTO daily_stats (stat_date, cid, delta_views)
SELECT p_date, cid, views
FROM post
WHERE status = 'publish'
ON DUPLICATE KEY UPDATE
delta_views = VALUES(delta_views),
stat_date = VALUES(stat_date);
SET v_rows = ROW_COUNT();
COMMIT;
SELECT v_rows AS affected_rows, p_date AS stat_date;
END$$
DELIMITER ;调用方式就是把日期当参数传进去：
CALL sp_collect_daily(CURDATE() - INTERVAL 1 DAY);几个关键点：第一，ON DUPLICATE KEY UPDATE 让这个存储过程可以反复执行而不产生重复行，这是幂等设计的核心，定时任务最怕的就是"重跑一次数据翻倍"。第二，DECLARE EXIT HANDLER FOR SQLEXCEPTION 是存储过程里的异常捕获，任何 SQL 报错都会触发 ROLLBACK，避免半截数据落库。第三，ROW_COUNT() 在 INSERT...ON DUPLICATE 之后的语义是"插入 1 行算 1，更新 1 行算 2"，所以实际影响行数会是记录数的两倍左右——这不是 bug，是 MySQL 的历史行为，排查时别被这个数字吓到。
触发器：删除文章时自动清关系
触发器适合做"顺带的清理和维护"，最典型的场景就是级联清理。你可能会问：外键不是也能做级联删除吗？可以，但外键在高并发写入场景下会带来额外的锁开销，很多站长干脆不用外键，转而用触发器兜底。
DELIMITER $$
CREATE TRIGGER trg_post_after_delete
AFTER DELETE ON post
FOR EACH ROW
BEGIN
DELETE FROM post_tag WHERE cid = OLD.cid;
END$$
DELIMITER ;这里必须理解 OLD 和 NEW 的区别：在 DELETE 触发器中只有 OLD 可用，表示被删除的那一行；在 INSERT 触发器中只有 NEW；在 UPDATE 中两者都有，OLD 是改之前，NEW 是改之后。触发器里绝不要写返回结果集的 SELECT，因为它不能把结果返回给调用方，只会静默失败或报错。
触发器的头号大坑是隐式开销与死锁。 触发器是在你原本的写操作里"顺带"执行的，你执行一条 DELETE FROM post WHERE cid = 100，看起来删一行，实际还去 post_tag 删了一遍。如果这行 delete 处于一个更大的事务中，触发器里的操作也在同一事务内，锁的范围就扩大了。更危险的是：如果两个不同的表上都有触发器且互相触碰对方的表，会形成一个环，并发时极易死锁。经验法则是——触发器只做和本表强相关、无外部依赖的简单清理，复杂业务逻辑宁可写在应用层或存储过程里显式调用。
游标：必须逐行处理时怎么办
游标是本文最需要"小心使用"的部分。一句话原则：能在一条 SQL 里做完的，绝不用游标。 游标是逐行处理，性能远逊于集合操作。但确实存在一些场景不得不逐行，比如要对每一行调用一个存储函数、或要根据上一行的结果决定下一行的处理。下面演示一个"遍历某日期所有文章，逐条计算环比涨跌"的例子。
DELIMITER $$
CREATE PROCEDURE sp_loop_stats(IN p_date DATE)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_cid INT UNSIGNED;
DECLARE v_views INT;
DECLARE v_prev INT;
-- 声明游标
DECLARE cur CURSOR FOR
SELECT cid, views FROM post WHERE status = 'publish';
-- 声明"游标取完"的处理器，必须放在游标声明之后
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_cid, v_views;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SELECT IFNULL(delta_views, 0) INTO v_prev
FROM daily_stats
WHERE stat_date = p_date - INTERVAL 1 DAY AND cid = v_cid
LIMIT 1;
INSERT INTO daily_stats (stat_date, cid, delta_views)
VALUES (p_date, v_cid, v_views - IFNULL(v_prev, v_views))
ON DUPLICATE KEY UPDATE delta_views = VALUES(delta_views);
END LOOP;
CLOSE cur;
END$$
DELIMITER ;游标有三条铁律，违反任何一条都会写出静默错误的代码：
第一，DECLARE ... HANDLER FOR NOT FOUND 必须声明在游标声明之后。MySQL 对 DECLARE 语句有严格的顺序要求：变量 → 游标 → 处理器。顺序错了直接报语法错误，很多人卡在这里找不到原因。
第二，done 标志位判断必须紧跟 FETCH 之后。因为 FETCH 到末尾时会把 done 置 1，此时各变量是"上一次的值"或 NULL，如果你先处理数据再判断 done，就会多处理一行脏数据。
第三，一定要 CLOSE cur。游标持有的资源在存储过程结束时会释放，但在长过程里不主动关闭会占用额外的内存和元数据锁。
存储过程、触发器、游标的选择清单
用一张表把三者的适用边界讲清楚，这是排查"该不该用"时最实用的判断依据。
场景 推荐方案 避免方案
批量汇总/统计 集合 SQL 游标
定时批处理多步逻辑 存储过程 应用层循环+多次往返
级联清理(删主表清子表) 触发器/外键 应用层手动删
逐行调用自定义逻辑 游标 集合 SQL
高频写入路径上的额外维护 尽量不做 触发器(锁膨胀)
需要返回多结果集/事务包裹 存储过程 单独 SQL生产环境必须知道的五个坑
1. DEFINER 与权限迁移。 存储过程和触发器都会记录创建者（DEFINER）。当你从测试库用 mysqldump 导出再导入生产库时，如果生产库没有那个用户，会报 The user specified as a definer does not exist。解决办法是用 mysqldump --routines --triggers --single-transaction 导出，导入前先 sed 把 DEFINER 行删掉，或者在生产库先建好同名账号。这一点在跨库迁移时几乎必然踩到。
2. 触发器不参与 explicit 事务之外的自动提交。 如果存储引擎是 MyISAM，触发器里的操作不会被回滚。个人站长现在基本都用 InnoDB，但要检查偶尔遗留的 MyISAM 表——SELECT table_name, engine FROM information_schema.tables WHERE engine='MyISAM' 一查便知。
3. 存储过程里的错误码不会自动冒泡。 过程内部 SELECT 出来的 msg 结果集，在 DSN 驱动层往往需要额外读取才拿得到。PHP 的 PDO 需要 nextRowset() 才能拿到后续结果集，否则你会以为"过程没返回任何东西"。
4. 事件调度器与定时任务。 MySQL 自带 EVENT，可以替代 crontab 跑存储过程，但需要 SET GLOBAL event_scheduler = ON，且重启后失效（要写进 my.cnf 的 event_scheduler=ON）。个人站更稳的做法是用 crontab 调 mysql -e "CALL sp_xxx()"，日志、告警、重试都好控制。
5. 调试手段有限，要善用 SELECT 打点。 存储过程不能像应用代码那样断点调试，最实用的方法是在关键节点用 SELECT 'step 1', v_rows; 输出中间状态，再用命令行执行观察。生产环境记得在发布前删掉这些调试 SELECT，否则每次调用都会多吐几行结果集。
检查清单：上线前过一遍
把上面所有内容浓缩成一份可执行的清单，写存储过程前照着核对：
[ ] 能用集合 SQL 解决的，是否已经避免使用游标
[ ] 存储过程是否用 START TRANSACTION / COMMIT 包裹核心写入
[ ] 是否声明了 SQLEXCEPTION 处理器并 ROLLBACK
[ ] 批量写入是否用 ON DUPLICATE KEY UPDATE 做到幂等
[ ] 触发器是否只做本表相关的简单清理
[ ] 触发器是否可能与其他触发器形成锁环(死锁风险)
[ ] 游标声明顺序是否正确: 变量 -> 游标 -> HANDLER
[ ] 是否在 FETCH 后立即判断 done 标志
[ ] DEFINER 在目标库是否存在(dump 迁移时)
[ ] 发布前是否清掉了调试用的 SELECT存储过程、触发器、游标不是"高级技巧"，而是"合适时机的工具"。个人站长期看，把批量统计、级联清理这类稳定且重复的逻辑固化到数据库层，能显著减少应用代码的复杂度，也让凌晨的定时任务跑得更快、更不容易半途出错。但切记：能在一条 SQL 里用集合思维解决的，永远优先用集合 SQL，游标和触发器都是在集合方案真的做不到时才拿出来的最后手段。