第 56 章存储过程与触发器踩坑

站长2026-11-09 14:00:00

内容摘要

存储过程和触发器是 MySQL 里"把业务逻辑写进数据库"的双刃剑——它们性能直接、封装方便，但也带来难调试、难维护、权限隐患、隐式触发等坑。本章梳理存储过程与触发器使用中的 11 个高频踩坑：DELIMITER、DECLARE 顺序、HANDLER、SQL SECURITY、触发器对同表限制、动态 SQL 注入等。示例基于 MySQL 8.0（本机无服务，语法按官方文档书写）。读完你能判断什么时候该用、怎么用才安全。

📖 本篇简介：存储过程和触发器是 MySQL 里"把业务逻辑写进数据库"的双刃剑——它们性能直接、封装方便，但也带来难调试、难维护、权限隐患、隐式触发等坑。本章梳理存储过程与触发器使用中的 11 个高频踩坑：DELIMITER、DECLARE 顺序、HANDLER、SQL SECURITY、触发器对同表限制、动态 SQL 注入等。示例基于 MySQL 8.0（本机无服务，语法按官方文档书写）。读完你能判断什么时候该用、怎么用才安全。

坑 1：DELIMITER 没改导致分号提前结束
坑 2：DECLARE 顺序写错建不上
坑 3：异常处理 HANDLER 不写直接中断
坑 4：触发器里不能改触发它的同一张表
坑 5：SQL SECURITY DEFINER 权限隐患
坑 6：把业务逻辑堆进存储过程难维护
坑 7：触发器递归级联把性能拖垮
坑 8：动态 SQL 拼接导致注入
坑 9：存储过程里 autocommit 行为误解
坑 10：游标 NOT FOUND 变量被污染
坑 11：以为存储过程能跨库主从一致自动同步
速查表：存储过程触发器速记
注意事项：用存储过程前的 Checklist
自测：你会写存储过程吗

坑 1：DELIMITER 没改导致分号提前结束

场景 / 错误示范：

-- 写存储过程，里面每条语句都用分号结尾
CREATE PROCEDURE p()
BEGIN
    SELECT 1;     -- 客户端把这里的 ; 当成整个语句结束了！
    SELECT 2;
END;
-- 结果：CREATE PROCEDURE 在第一个 ; 就被截断，报语法错误

为什么是坑：客户端默认用 ; 作为语句结束符。存储过程体里有多条以 ; 结尾的语句，客户端会在第一个 ; 就把"半个过程"发给服务器，报语法错。这是新手写存储过程必踩的第一个坑。

正确做法：

-- 先把语句结束符临时改成别的（如 //），过程结束后改回来
DELIMITER //
CREATE PROCEDURE p()
BEGIN
    SELECT 1;
    SELECT 2;
END //
DELIMITER ;     -- 改回分号
-- 之后 CALL p();

加分点：DELIMITER 是客户端命令，不是 SQL 服务器命令。用程序（如 JDBC）创建存储过程时不需要它，因为驱动是整段发送的。

坑 2：DECLARE 顺序写错建不上

场景 / 错误示范：

CREATE PROCEDURE p()
BEGIN
    DECLARE a INT DEFAULT 0;
    SELECT v INTO a FROM t WHERE id=1;   -- 先写了可执行语句
    DECLARE b INT DEFAULT 0;             -- 再声明变量，报错！
END
-- ERROR: DECLARE 必须放在 BEGIN...END 的最前面

为什么是坑：MySQL 规定 DECLARE 必须集中在 BEGIN...END 块的最开头，而且顺序固定：先变量/条件，再游标，再 handler。把 DECLARE 插在语句中间就建不上过程。这是语法硬性要求。

正确做法：

CREATE PROCEDURE p()
BEGIN
    -- 第 1 段：变量声明
    DECLARE a INT DEFAULT 0;
    DECLARE b INT DEFAULT 0;
    -- 第 2 段：游标（如有）
    -- 第 3 段：异常处理 handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
    -- 之后才是可执行语句
    SELECT v INTO a FROM t WHERE id=1;
END

加分点：记住 DECLARE 顺序口诀——"变量、游标、handler"，这个顺序错一个都建不上。

坑 3：异常处理 HANDLER 不写直接中断

场景 / 错误示范：

CREATE PROCEDURE transfer(...)
BEGIN
    UPDATE account SET bal=bal-10 WHERE from_id=1;
    UPDATE account SET bal=bal+10 WHERE to_id=2;   -- 万一这句出错
    -- 没写 handler，过程直接中断，前面的 UPDATE 怎么办？
END

为什么是坑：存储过程里某条 SQL 报错，如果没声明 handler，过程会立即中断，且不自动回滚（取决于 autocommit）。开发者以为"过程是一个事务"，实际出错时状态不可控。

正确做法：

CREATE PROCEDURE transfer(...)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION   -- 任何 SQL 异常都走这里
    BEGIN
        ROLLBACK;                           -- 整体回滚
        RESIGNAL;                           -- 把错误再抛给调用方
    END;
    START TRANSACTION;
    UPDATE account SET bal=bal-10 WHERE from_id=1;
    UPDATE account SET bal=bal+10 WHERE to_id=2;
    COMMIT;
END

加分点：写涉及多表写的存储过程，一定配 DECLARE EXIT HANDLER FOR SQLEXCEPTION ... ROLLBACK，否则出错就是半成品数据。

坑 4：触发器里不能改触发它的同一张表

场景 / 错误示范：

-- 想在 t 表 UPDATE 后，再 UPDATE t 自己做个联动
CREATE TRIGGER trg AFTER UPDATE ON t
FOR EACH ROW
BEGIN
    UPDATE t SET col = col + 1 WHERE id = NEW.id;   -- 报错：不能修改触发表
END
-- ERROR: Can't update table 't' in stored function/trigger...

为什么是坑：MySQL 规定触发器里不能对"触发它的那张表"再做 UPDATE/DELETE，怕引起递归触发、状态不可控。开发者想"联动更新本表"，被这条规则挡住。

正确做法：

-- 要改本行的值，BEFORE 触发器里直接改 NEW 列（这是允许的）
CREATE TRIGGER trg BEFORE UPDATE ON t
FOR EACH ROW
BEGIN
    SET NEW.col = NEW.col + 1;     -- BEFORE 触发器改 NEW 是合法的
END;

-- 要改别的表，随便改；要改本表，用 BEFORE 改 NEW，别 AFTER 再 UPDATE 本表

加分点：BEFORE 触发器改 NEW 字段是合法且常用的（如自动补默认值、格式化）；AFTER 触发器只能影响别的表。

坑 5：SQL SECURITY DEFINER 权限隐患

场景 / 错误示范：

-- 存储过程用 DEFINER（默认），以创建者权限执行
CREATE PROCEDURE delete_log()
SQL SECURITY DEFINER
BEGIN
    DELETE FROM log_t;     -- 普通用户调用，却拥有创建者的删表权限
END
-- 应用账号本不该有删表权，通过这个过程间接拿到了

为什么是坑：SQL SECURITY DEFINER（默认）让过程以创建者的权限运行，普通用户调用它时，获得了创建者的权限。这是权限提升的隐患——审计、合规上都是问题。

正确做法：

-- 改成 INVOKER，以调用者自己的权限执行
CREATE PROCEDURE delete_log()
SQL SECURITY INVOKER
BEGIN
    DELETE FROM log_t;
END
-- 调用者必须自己真的有 log_t 的 DELETE 权限才行

-- 审计：找出所有 DEFINER 过程，评估是否需要收紧
SELECT routine_name, security_type FROM information_schema.routines
WHERE routine_schema = 'your_db';

加分点：安全敏感的库一律用 SQL SECURITY INVOKER。DEFINER 只在"必须让低权限用户临时做高权限操作"的受控场景用。

坑 6：把业务逻辑堆进存储过程难维护

场景 / 错误示范：

-- 几百行的业务逻辑全写在存储过程里
CREATE PROCEDURE big_business()
BEGIN
    -- 几百行 IF/ELSE、循环、SQL 拼接，业务规则全在这
END;
-- 应用层只是 CALL 一下，出问题没法调试、没法版本管理

为什么是坑：存储过程里的业务逻辑没有版本控制、没有单元测试、调试困难，换个 DBA 根本不敢动。业务变更时改存储过程风险极高。这是"把逻辑塞数据库"的经典反模式。

正确做法：

-- 业务逻辑放应用层，存储过程只做"数据库内密集、批量"的事
-- 比如批量数据清洗、跨表统计，这类放在过程里才合理
-- 普通 CRUD、业务规则，一律在应用代码里写，可测试可部署

加分点：存储过程适合"数据库内部的批量计算/数据迁移"，不适合承载核心业务规则。面试说"我们业务逻辑在应用层，存储过程只做数据清洗"，是成熟架构观。

坑 7：触发器递归级联把性能拖垮

场景 / 错误示范：

-- t1 的触发器改 t2，t2 的触发器又改 t1
CREATE TRIGGER t1_trg AFTER UPDATE ON t1 FOR EACH ROW
BEGIN UPDATE t2 SET ...; END;

CREATE TRIGGER t2_trg AFTER UPDATE ON t2 FOR EACH ROW
BEGIN UPDATE t1 SET ...; END;   -- 又触发 t1_trg，来回递归！
-- 一条 UPDATE 引发连锁更新，性能雪崩

为什么是坑：多个表的触发器互相触发，形成级联甚至循环，一条简单 UPDATE 背后是 N 倍的隐式写。开发者完全不知道"我 update 一行，数据库偷偷干了这么多事"，性能问题极难排查。

正确做法：

-- 关掉触发器递归（8.0 默认 already 受限），但更根本的是：
-- 避免在设计上让触发器互相联动
-- 关联逻辑放应用层显式写，别靠触发器隐式串联
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
-- 评估每张表有哪些触发器：
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers WHERE trigger_schema='your_db';

加分点：能用应用层显式写的联动，就别用触发器。触发器是隐式行为，调试和性能预估都困难。

坑 8：动态 SQL 拼接导致注入

场景 / 错误示范：

-- 存储过程里拼接表名/列名，直接拼用户输入
CREATE PROCEDURE query_dyn(tbl VARCHAR(50), cond VARCHAR(200))
BEGIN
    SET @s = CONCAT('SELECT * FROM ', tbl, ' WHERE ', cond);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END;
-- cond 传入 '1=1; DROP TABLE t'，注入！

为什么是坑：预处理语句的 ? 占位符只能绑定值，不能绑定表名/列名。一旦为了动态表名去 CONCAT 用户输入，就打开了 SQL 注入的大门。存储过程里拼动态 SQL 是注入高发区。

正确做法：

-- 动态标识符用白名单校验，绝不直接拼用户输入
CREATE PROCEDURE query_dyn(tbl VARCHAR(50))
BEGIN
    -- 白名单：只允许这几张表
    IF tbl NOT IN ('orders','users','products') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '非法表名';
    END IF;
    SET @s = CONCAT('SELECT * FROM ', tbl);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

加分点：动态 SQL 的值用 ? 绑定，标识符用白名单——两条铁律，能防住存储过程里几乎所有注入。

坑 9：存储过程里 autocommit 行为误解

场景 / 错误示范：

-- 开发者以为过程里多条语句是一个事务
CREATE PROCEDURE p()
BEGIN
    INSERT INTO a VALUES (1);   -- autocommit=1：这句立刻提交
    INSERT INTO b VALUES (1);   -- 这句也立刻提交
END;
-- 第二句失败，第一句已提交，数据不一致

为什么是坑：和第 53 章坑 9 一致——过程默认继承会话 autocommit。autocommit=1 时每条语句独立提交，过程并没有自动包事务。开发者误以为"调用过程=一个事务"。

正确做法：

CREATE PROCEDURE p()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
    START TRANSACTION;      -- 显式开事务
    INSERT INTO a VALUES (1);
    INSERT INTO b VALUES (1);
    COMMIT;
END;

加分点：过程里要原子性，显式 START TRANSACTION + HANDLER，别赌 autocommit。

坑 10：游标 NOT FOUND 变量被污染

场景 / 错误示范：

-- 用游标循环，DECLARE CONTINUE HANDLER FOR NOT FOUND 置标志位
CREATE PROCEDURE p()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT id FROM t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id;
        IF done = 1 THEN LEAVE read_loop; END IF;
        -- 循环里又执行了一条"查不到行"的 SELECT INTO，done 被提前置 1
        SELECT xx INTO v_xx FROM other WHERE id = v_id;   -- 查不到也触发 NOT FOUND！
    END LOOP;
END;
-- done 在循环中途被置 1，游标提前结束

为什么是坑：NOT FOUND handler 是全局的——循环里任何一条"没查到行"的 SELECT INTO 都会触发它，把 done 置 1，导致游标循环提前退出。这是游标存储过程最隐蔽的 bug。

正确做法：

-- 把"可能查不到"的查询挪出游标循环，或用单独的 handler 作用域
-- 更稳：循环内先判断 COUNT 或用 DECLARE CONTINUE HANDLER 精确处理
-- 经验：游标循环体内别再写可能 NOT FOUND 的 SELECT INTO
-- 需要的数据在 OPEN 前一次性准备好

加分点：写游标存储过程时，循环体越简单越好，把可能"查无此行"的查询移出循环，避免 done 被污染。

坑 11：以为存储过程能跨库主从一致自动同步

场景 / 错误示范：

-- 主库建了存储过程，开发者以为从库自动就有
-- 结果从库上 CALL 这个过程报 "procedure does not exist"

为什么是坑：存储过程、函数、触发器、事件都要在主从之间同步，依赖 binlog 记录了这些 DDL。如果备份/导出时忘了带 --routines --events，或 binlog 格式问题，从库就缺这些对象。

正确做法：

-- 用 mysqldump 导出时必须带这两个参数，否则丢存储过程/事件
-- mysqldump -uroot -p --routines --events --triggers your_db > db.sql
-- 从库上确认对象存在：
SELECT routine_name FROM information_schema.routines WHERE routine_schema='your_db';

加分点：迁移/备份数据库时，--routines --events --triggers 三个参数缺一不可，否则业务对象丢了都不知道。

速查表：存储过程触发器速记

| 坑点 | 错误做法 | 正确做法 |

|---|---|---|

| 结束符 | 直接写过程 | DELIMITER 改结束符 |

| DECLARE 位置 | 插在语句中间 | BEGIN 开头，变量/游标/handler 顺序 |

| 异常处理 | 不写 handler | EXIT HANDLER FOR SQLEXCEPTION ROLLBACK |

| 改本表 | AFTER 触发器 UPDATE 本表 | BEFORE 改 NEW |

| 权限 | 默认 DEFINER | 敏感库用 INVOKER |

| 业务逻辑 | 全堆过程里 | 业务放应用层 |

| 触发器联动 | 表间互相触发 | 避免级联 |

| 动态 SQL | 拼用户输入 | 白名单校验标识符 |

| 事务 | 赌 autocommit | 显式 START TRANSACTION |

| 游标 NOT FOUND | 循环内再 SELECT INTO | 移出循环 |

| 迁移 | 漏 --routines | 带 --routines --events |

注意事项：用存储过程前的 Checklist

[ ] 真的需要存储过程吗？（业务逻辑优先应用层）
[ ] DELIMITER 改了吗？
[ ] DECLARE 顺序对吗？
[ ] 写操作配了 EXIT HANDLER ROLLBACK 吗？
[ ] 敏感过程用 INVOKER 了吗？
[ ] 动态 SQL 标识符白名单校验了吗？
[ ] 迁移备份带了 --routines --events --triggers 吗？

一句话：存储过程能不用就不用；真要用，DELIMITER、DECLARE 顺序、HANDLER、INVOKER、白名单这五件事必须做对。

自测：你会写存储过程吗

题 1：写存储过程为什么要先 DELIMITER？

客户端默认以 ; 结束语句，过程体内多条 ; 会被提前截断。先把结束符改成 //，过程写完再改回 ;。

</details>

题 2：DECLARE 语句在 BEGIN...END 里的位置要求？

必须放在最开头，且顺序固定：变量/条件 → 游标 → handler。插在可执行语句中间会建不上。

</details>

题 3：存储过程里多条写操作出错，为什么会留下半成品数据？

没配 EXIT HANDLER ROLLBACK，过程中断时事务行为不可控。要 DECLARE EXIT HANDLER FOR SQLEXCEPTION 里 ROLLBACK。

</details>

题 4：AFTER UPDATE 触发器里想再 UPDATE 本表，为什么报错？

MySQL 不允许触发器修改触发它的同一张表。要改本行值，用 BEFORE 触发器改 NEW 字段；要改别的表才行。

</details>

题 5：SQL SECURITY DEFINER 有什么权限风险？

过程以创建者权限执行，普通用户通过调用间接获得创建者权限，造成权限提升。敏感库改用 SQL SECURITY INVOKER。

</details>

题 6：动态 SQL 拼接表名怎么防注入？

占位符 ? 不能绑定标识符，所以标识符必须白名单校验；值才用 ? 绑定。绝不直接 CONCAT 用户输入。

</details>

题 7：游标循环里 done 标志位被提前置 1，常见原因？

循环体内写了一条可能"查不到行"的 SELECT INTO，触发全局的 NOT FOUND handler，把 done 置 1。这类查询要移出循环。

</details>

题 8：mysqldump 导出数据库，漏了哪个参数会丢存储过程？

漏 --routines（还有 --events --triggers）。只导表结构和数据，不导存储过程/事件/触发器。

</details>

题 9：为什么不建议把核心业务逻辑写进存储过程？

没有版本控制、难调试、难单元测试，变更风险高。业务逻辑应放应用层，存储过程只做数据库内批量计算。

</details>

题 10：两张表的触发器互相触发，可能引发什么问题？

级联甚至递归触发，一条 UPDATE 背后是 N 倍隐式写，性能雪崩且难排查。关联逻辑应放应用层显式写。

</details>

下一篇预告：第 57 章事件调度踩坑

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 56 章存储过程与触发器踩坑

目录

坑 1：DELIMITER 没改导致分号提前结束

坑 2：DECLARE 顺序写错建不上

坑 3：异常处理 HANDLER 不写直接中断

坑 4：触发器里不能改触发它的同一张表

坑 5：SQL SECURITY DEFINER 权限隐患

坑 6：把业务逻辑堆进存储过程难维护

坑 7：触发器递归级联把性能拖垮

坑 8：动态 SQL 拼接导致注入

坑 9：存储过程里 autocommit 行为误解

坑 10：游标 NOT FOUND 变量被污染

坑 11：以为存储过程能跨库主从一致自动同步

速查表：存储过程触发器速记

注意事项：用存储过程前的 Checklist

自测：你会写存储过程吗

文章推广海报

文章回复

第 56 章 存储过程与触发器踩坑

目录

坑 1：DELIMITER 没改导致分号提前结束

坑 2：DECLARE 顺序写错建不上

坑 3：异常处理 HANDLER 不写直接中断

坑 4：触发器里不能改触发它的同一张表

坑 5：SQL SECURITY DEFINER 权限隐患

坑 6：把业务逻辑堆进存储过程难维护

坑 7：触发器递归级联把性能拖垮

坑 8：动态 SQL 拼接导致注入

坑 9：存储过程里 autocommit 行为误解

坑 10：游标 NOT FOUND 变量被污染

坑 11：以为存储过程能跨库主从一致自动同步

速查表：存储过程触发器速记

注意事项：用存储过程前的 Checklist

自测：你会写存储过程吗

第 56 章存储过程与触发器踩坑