1. 为什么存储过程和函数值得单独拎出来讲

刚入行那会儿，我对存储过程和函数的理解就停留在“能跑就行”的层面。直到有一次接手一个数据报表项目，前任开发把所有业务逻辑都塞进了应用层，几百行代码里嵌套了七八层查询，每次改一个统计口径都要重新发版。后来我把核心计算逻辑下沉到数据库层，用存储过程和函数重构了一遍，不仅响应速度上来了，维护成本也降了一大截。从那以后我就意识到，这两个东西用好了是真能救命，用不好就是给自己挖坑。

这篇文章主要面向已经会写基本SQL、但对存储过程和函数还不太熟悉的开发者，或者虽然用过但总是分不清什么时候该用哪个的人。我会从两者的本质区别讲起，然后逐一拆解变量定义、条件判断、处理程序、游标和流程控制这些核心概念，每个点都配上可以直接跑的代码示例。你看完之后，应该能独立写出结构清晰、异常处理完善的存储过程和函数，并且知道在什么场景下该选哪个。

先说一个最直观的区别：存储过程是一段可以包含任意SQL语句的批处理程序，而函数本质上是一个必须有返回值、且通常不修改数据的计算单元。这个区别决定了它们的使用场景完全不同。存储过程适合做批量数据处理、复杂业务逻辑编排；函数适合做数据转换、计算、格式化这类“输入什么就输出什么”的操作。

但实际工作中，很多人会把两者混用。我见过用存储过程做字符串拼接的，也见过用函数去更新表的。不是说完全不行，而是这样做会让代码的可维护性和性能都打折扣。下面我会把每个知识点拆开讲，你跟着走一遍，自然就能分清什么时候该用哪个。

2. 存储过程与函数的核心差异拆解

2.1 从调用方式看本质区别

存储过程通过CALL语句调用，函数则像内置函数一样直接出现在表达式中。这个差异看起来很小，但它直接影响了两个东西的设计思路。

存储过程的调用是独立的，你可以把它当成一个“动作”来理解。比如“每月1号跑一次数据汇总”，这是一个动作，用存储过程很合适。函数的调用是嵌入式的，它必须出现在SELECT、WHERE、HAVING等子句里，或者赋值给变量。比如“把金额格式化成带千分位的字符串”，这是一个转换，用函数很合适。

-- 存储过程调用方式 CALL sp_monthly_summary('2024-01-01'); -- 函数调用方式 SELECT fn_format_amount(1234567.89); -- 输出: 1,234,567.89

从调用方式就能看出来，函数的设计目标是“可组合”，它应该像积木一样能拼来拼去。存储过程的设计目标是“可执行”，它应该像一个完整的任务单元。

2.2 返回值与参数方向的差异

存储过程可以没有返回值，也可以通过OUT或INOUT参数返回多个值。函数必须有且只有一个返回值，参数默认都是IN方向。

这个差异带来的实际影响是：存储过程适合需要返回多个结果的场景，函数适合需要返回单一计算结果的场景。

-- 存储过程：通过 OUT 参数返回多个值 CREATE PROCEDURE sp_get_user_stats( IN p_user_id INT, OUT p_order_count INT, OUT p_total_amount DECIMAL(10,2) ) BEGIN SELECT COUNT(*), IFNULL(SUM(amount), 0) INTO p_order_count, p_total_amount FROM orders WHERE user_id = p_user_id; END; -- 函数：只返回一个值 CREATE FUNCTION fn_get_user_level(p_total_amount DECIMAL(10,2)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE v_level VARCHAR(20); IF p_total_amount >= 10000 THEN SET v_level = '钻石'; ELSEIF p_total_amount >= 5000 THEN SET v_level = '黄金'; ELSE SET v_level = '普通'; END IF; RETURN v_level; END;

我个人的经验是，如果一个逻辑需要返回两个以上的值，优先考虑存储过程；如果只需要返回一个计算结果，优先考虑函数。不要为了用函数而把多个返回值硬塞进一个字符串里，那样调用方解析起来很痛苦。

2.3 数据修改权限的差异

函数通常不允许修改数据（不同数据库实现有差异，但主流数据库都限制函数中的写操作），存储过程则可以自由执行INSERT、UPDATE、DELETE。

这个限制不是随便定的。函数的设计哲学是“纯计算”，它不应该有副作用。如果一个函数修改了数据，那它在SELECT语句中被调用时，你根本不知道它改了什么，这会让调试变得极其困难。

-- 函数中不允许这样的操作（大多数数据库会报错） CREATE FUNCTION fn_bad_example(p_id INT) RETURNS INT BEGIN UPDATE users SET status = 1 WHERE id = p_id; -- 这行会报错 RETURN 1; END;

注意：MySQL 中函数默认不允许修改数据，但可以通过特定设置绕过。不过我的建议是不要绕过，这个限制是在帮你写出更清晰的代码。

2.4 事务控制的差异

存储过程内部可以开启、提交、回滚事务，函数内部通常不允许事务控制语句。

这意味着如果你需要在一个逻辑单元里保证多条写操作的原子性，存储过程是唯一的选择。函数更适合做那些不涉及事务的纯计算任务。

CREATE PROCEDURE sp_transfer_amount( IN p_from_id INT, IN p_to_id INT, IN p_amount DECIMAL(10,2) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT '转账失败，已回滚' AS result; END; START TRANSACTION; UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id; UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id; COMMIT; SELECT '转账成功' AS result; END;

这个转账的例子很典型：两条更新必须同时成功或同时失败，这种场景只能用存储过程，函数做不到。

2.5 适用场景对照表

这张表建议你存下来，下次纠结用哪个的时候直接对照。

3. 变量定义与使用细节

3.1 局部变量的声明与赋值

局部变量是存储过程和函数内部最基础的存储单元。声明时必须指定类型，赋值可以用SET或SELECT INTO。

CREATE PROCEDURE sp_variable_demo() BEGIN -- 声明局部变量 DECLARE v_count INT DEFAULT 0; DECLARE v_name VARCHAR(50); DECLARE v_total DECIMAL(10,2) DEFAULT 0.00; -- 方式一：SET 赋值 SET v_count = 10; SET v_name = '测试'; -- 方式二：SELECT INTO 赋值（从表中查询） SELECT COUNT(*) INTO v_count FROM users; SELECT username, total_amount INTO v_name, v_total FROM user_stats WHERE user_id = 1; -- 输出结果 SELECT v_count, v_name, v_total; END;

这里有个细节要注意：SELECT INTO如果查询结果为空，变量会保持原值不变，不会变成 NULL。如果查询结果有多行，会报错。所以用SELECT INTO的时候，最好确保查询条件能精确定位到一行。

3.2 用户变量的会话级特性

用户变量以@开头，不需要声明，直接赋值就能用。它的作用域是整个会话，也就是说在同一个连接里，你在这里赋的值，在另一个存储过程里也能读到。

-- 用户变量不需要声明 SET @user_count = 0; SELECT COUNT(*) INTO @user_count FROM users; -- 在另一个存储过程里也能访问 CREATE PROCEDURE sp_read_user_var() BEGIN SELECT @user_count AS user_count_from_session; END;

用户变量很方便，但我建议谨慎使用。因为它不声明类型，容易出隐式转换的问题。而且会话级的作用域意味着你很难追踪它在哪里被修改了。我一般只在调试的时候用用户变量临时存值，正式代码里还是用局部变量。

3.3 系统变量与自定义变量的区分

系统变量以@@开头，分全局和会话两个级别。全局变量影响整个数据库实例，会话变量只影响当前连接。

-- 查看系统变量 SELECT @@autocommit; SELECT @@max_connections; -- 修改会话级系统变量 SET SESSION autocommit = 0; -- 修改全局级系统变量（需要相应权限） SET GLOBAL max_connections = 500;

提示：修改全局系统变量要非常小心，它会影响所有连接。生产环境上改之前一定要确认影响范围。

3.4 变量作用域的实际影响

局部变量的作用域仅限于BEGIN...END块内。如果你在嵌套块里声明了同名变量，内层会遮蔽外层。

CREATE PROCEDURE sp_scope_demo() BEGIN DECLARE v_x INT DEFAULT 1; BEGIN DECLARE v_x INT DEFAULT 2; SELECT v_x; -- 输出 2，内层遮蔽外层 END; SELECT v_x; -- 输出 1，外层变量不受影响 END;

这个特性在写复杂逻辑的时候要特别注意。我踩过的坑是：在内层块里改了变量，以为外层也会变，结果发现改的是内层的同名变量。后来我养成了一个习惯，嵌套块里的变量都加前缀区分，比如v_outer_x和v_inner_x，这样一眼就能看出是哪个。

4. 条件判断与流程控制实战

4.1 IF 语句的完整用法

IF是最基础的条件判断，支持ELSEIF和ELSE分支。

CREATE FUNCTION fn_get_discount_level(p_amount DECIMAL(10,2)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE v_level VARCHAR(20); IF p_amount >= 10000 THEN SET v_level = 'VIP'; ELSEIF p_amount >= 5000 THEN SET v_level = '高级'; ELSEIF p_amount >= 1000 THEN SET v_level = '中级'; ELSE SET v_level = '普通'; END IF; RETURN v_level; END;

IF语句的判断顺序是从上到下，一旦匹配就跳出。所以条件的排列顺序会影响性能。把最可能命中的条件放在前面，可以减少判断次数。

4.2 CASE 语句的两种形态

CASE有两种写法：简单CASE和搜索CASE。简单CASE适合等值判断，搜索CASE适合范围判断。

-- 简单 CASE：等值判断 CREATE FUNCTION fn_get_status_text(p_status INT) RETURNS VARCHAR(20) DETERMINISTIC BEGIN RETURN CASE p_status WHEN 0 THEN '待处理' WHEN 1 THEN '处理中' WHEN 2 THEN '已完成' WHEN 3 THEN '已取消' ELSE '未知状态' END; END; -- 搜索 CASE：范围判断 CREATE FUNCTION fn_get_grade(p_score INT) RETURNS VARCHAR(5) DETERMINISTIC BEGIN RETURN CASE WHEN p_score >= 90 THEN 'A' WHEN p_score >= 80 THEN 'B' WHEN p_score >= 70 THEN 'C' WHEN p_score >= 60 THEN 'D' ELSE 'F' END; END;

CASE比IF更适合做“映射”类的逻辑，代码看起来更整洁。我一般是这样选的：如果是两三个分支的简单判断，用IF；如果是多个等值映射或者需要返回一个表达式，用CASE。

4.3 LOOP、WHILE、REPEAT 三种循环的选择

MySQL 支持三种循环结构，它们的区别在于循环条件的检查时机。

-- LOOP：无限循环，需要 LEAVE 跳出 CREATE PROCEDURE sp_loop_demo() BEGIN DECLARE v_i INT DEFAULT 1; DECLARE v_sum INT DEFAULT 0; my_loop: LOOP SET v_sum = v_sum + v_i; SET v_i = v_i + 1; IF v_i > 10 THEN LEAVE my_loop; END IF; END LOOP my_loop; SELECT v_sum; -- 输出 55 END; -- WHILE：先判断后执行 CREATE PROCEDURE sp_while_demo() BEGIN DECLARE v_i INT DEFAULT 1; DECLARE v_sum INT DEFAULT 0; WHILE v_i <= 10 DO SET v_sum = v_sum + v_i; SET v_i = v_i + 1; END WHILE; SELECT v_sum; -- 输出 55 END; -- REPEAT：先执行后判断 CREATE PROCEDURE sp_repeat_demo() BEGIN DECLARE v_i INT DEFAULT 1; DECLARE v_sum INT DEFAULT 0; REPEAT SET v_sum = v_sum + v_i; SET v_i = v_i + 1; UNTIL v_i > 10 END REPEAT; SELECT v_sum; -- 输出 55 END;

选择建议：如果循环体至少需要执行一次，用REPEAT；如果可能一次都不执行，用WHILE；如果需要在中途根据多个条件跳出，用LOOP配合LEAVE。

4.4 LEAVE 和 ITERATE 的配合使用

LEAVE相当于break，ITERATE相当于continue。它们都需要配合标签使用。

CREATE PROCEDURE sp_leave_iterate_demo() BEGIN DECLARE v_i INT DEFAULT 0; DECLARE v_result VARCHAR(500) DEFAULT ''; outer_loop: LOOP SET v_i = v_i + 1; IF v_i > 10 THEN LEAVE outer_loop; END IF; -- 跳过偶数 IF v_i % 2 = 0 THEN ITERATE outer_loop; END IF; SET v_result = CONCAT(v_result, v_i, ','); END LOOP outer_loop; SELECT v_result; -- 输出 1,3,5,7,9, END;

标签的命名建议用有意义的名字，比如outer_loop、inner_loop，不要用l1、l2这种。嵌套循环的时候，有意义的标签能让你一眼看出LEAVE跳的是哪一层。

5. 处理程序与异常捕获机制

5.1 DECLARE HANDLER 的语法结构

处理程序是存储过程和函数里做异常捕获的核心机制。语法是DECLARE 动作 HANDLER FOR 条件 处理语句。

CREATE PROCEDURE sp_handler_demo() BEGIN -- 声明一个继续执行的处理器，捕获所有 SQL 异常 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SELECT '发生了 SQL 异常' AS error_msg; END; -- 故意触发一个错误 INSERT INTO non_existent_table VALUES (1); SELECT '这行会继续执行' AS after_error; END;

CONTINUE表示处理完之后继续执行后面的语句，EXIT表示处理完之后直接退出当前BEGIN...END块。

5.2 条件类型的三种写法

处理程序可以捕获三种类型的条件：SQLSTATE、错误码、条件名称。

-- 方式一：捕获特定 SQLSTATE DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN SELECT '违反唯一约束' AS error_msg; END; -- 方式二：捕获特定错误码 DECLARE CONTINUE HANDLER FOR 1062 BEGIN SELECT '主键或唯一键重复' AS error_msg; END; -- 方式三：捕获预定义条件名称 DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SELECT '没有找到数据' AS error_msg; END;

NOT FOUND是游标操作里最常用的条件，当FETCH没有更多数据时会触发。SQLEXCEPTION捕获所有异常，SQLWARNING捕获所有警告。

5.3 EXIT 与 CONTINUE 的选择策略

EXIT和CONTINUE的选择取决于你的业务逻辑：如果出错后后续操作没有意义，用EXIT；如果出错后还能继续处理其他数据，用CONTINUE。

CREATE PROCEDURE sp_batch_insert() BEGIN DECLARE v_i INT DEFAULT 1; DECLARE v_error_count INT DEFAULT 0; -- 用 CONTINUE，单条失败不影响后续插入 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET v_error_count = v_error_count + 1; END; WHILE v_i <= 100 DO INSERT INTO target_table (id, value) VALUES (v_i, CONCAT('value_', v_i)); SET v_i = v_i + 1; END WHILE; SELECT v_error_count AS failed_count; END;

批量插入的场景用CONTINUE更合适，因为单条失败不应该中断整个批次。但如果是转账这种场景，任何一步失败都必须回滚，那就必须用EXIT。

5.4 嵌套块中的异常传播

异常在没有被当前块捕获时，会向外层块传播。这个机制让你可以在不同层级做不同的处理。

CREATE PROCEDURE sp_nested_handler() BEGIN -- 外层处理器：最终兜底 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SELECT '外层捕获到异常' AS msg; END; BEGIN -- 内层处理器：处理特定异常 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN SELECT '内层捕获到唯一约束冲突' AS msg; END; INSERT INTO users (id, name) VALUES (1, 'test'); INSERT INTO users (id, name) VALUES (1, 'duplicate'); -- 触发唯一约束 END; SELECT '内层块执行完毕' AS msg; END;

这个例子里，内层捕获了唯一约束冲突并继续执行，所以内层块结束后外层还能继续。如果内层没有捕获，异常会传播到外层，外层用EXIT直接退出。

实操心得：我一般会在最外层放一个EXIT处理器做兜底，确保任何未预期的异常都能被记录并回滚。内层则根据业务需要放CONTINUE处理器，处理那些“预期内”的异常。

6. 游标的使用与性能考量

6.1 游标的声明与生命周期

游标用于逐行处理查询结果集。它的生命周期是：声明 → 打开 → 读取 → 关闭。

CREATE PROCEDURE sp_cursor_demo() BEGIN DECLARE v_id INT; DECLARE v_name VARCHAR(50); DECLARE v_done INT DEFAULT 0; -- 1. 声明游标 DECLARE cur_users CURSOR FOR SELECT id, name FROM users WHERE status = 1; -- 2. 声明结束标志处理器 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; -- 3. 打开游标 OPEN cur_users; -- 4. 循环读取 read_loop: LOOP FETCH cur_users INTO v_id, v_name; IF v_done = 1 THEN LEAVE read_loop; END IF; -- 处理每一行数据 SELECT CONCAT('处理用户: ', v_name) AS msg; END LOOP read_loop; -- 5. 关闭游标 CLOSE cur_users; END;

这里有个顺序问题要注意：游标声明必须在变量声明之后，处理器声明必须在游标声明之后。这个顺序不能乱，否则会报错。

6.2 游标循环的标准写法

游标循环的写法比较固定，我把它总结成一个模板，你直接套用就行。

CREATE PROCEDURE sp_cursor_template() BEGIN -- 变量声明 DECLARE v_field1 INT; DECLARE v_field2 VARCHAR(100); DECLARE v_done INT DEFAULT 0; -- 游标声明 DECLARE cur_data CURSOR FOR SELECT field1, field2 FROM source_table WHERE condition = 1; -- 结束处理器 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; -- 打开游标 OPEN cur_data; -- 循环 cursor_loop: LOOP FETCH cur_data INTO v_field1, v_field2; IF v_done = 1 THEN LEAVE cursor_loop; END IF; -- 业务逻辑写在这里 -- ... END LOOP cursor_loop; -- 关闭游标 CLOSE cur_data; END;

这个模板我用了很多年，基本上游标相关的存储过程都是这个结构。你只需要改游标查询和循环体里的业务逻辑就行。

6.3 游标性能优化的实际经验

游标是逐行处理的，性能天然不如集合操作。我实测过一个场景：用游标逐行更新10万条数据，耗时约45秒；改成一条UPDATE语句，耗时不到1秒。差距就是这么明显。

所以我的原则是：能用集合操作解决的，绝对不用游标。游标只在以下场景才考虑使用：

• 每一行的处理逻辑依赖上一行的结果（比如计算累计值）
• 需要调用其他存储过程，且每次调用的参数来自不同行
• 需要根据每行数据动态生成不同的SQL语句

-- 不推荐：用游标做简单的批量更新 -- 推荐：直接用 UPDATE UPDATE users SET status = 2 WHERE last_login < DATE_SUB(NOW(), INTERVAL 90 DAY); -- 推荐用游标的场景：每行需要调用不同的外部逻辑 CREATE PROCEDURE sp_process_orders() BEGIN DECLARE v_order_id INT; DECLARE v_done INT DEFAULT 0; DECLARE cur_orders CURSOR FOR SELECT id FROM orders WHERE status = 'pending'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; OPEN cur_orders; order_loop: LOOP FETCH cur_orders INTO v_order_id; IF v_done = 1 THEN LEAVE order_loop; END IF; -- 每笔订单需要根据具体情况做不同处理 CALL sp_process_single_order(v_order_id); END LOOP order_loop; CLOSE cur_orders; END;

6.4 游标与临时表的配合使用

有时候游标处理的结果需要暂存，这时候可以配合临时表使用。

CREATE PROCEDURE sp_cursor_with_temp() BEGIN DECLARE v_user_id INT; DECLARE v_amount DECIMAL(10,2); DECLARE v_done INT DEFAULT 0; -- 创建临时表暂存结果 CREATE TEMPORARY TABLE IF NOT EXISTS temp_result ( user_id INT, total_amount DECIMAL(10,2), rank_num INT ); DECLARE cur_data CURSOR FOR SELECT user_id, SUM(amount) FROM orders GROUP BY user_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; OPEN cur_data; cursor_loop: LOOP FETCH cur_data INTO v_user_id, v_amount; IF v_done = 1 THEN LEAVE cursor_loop; END IF; INSERT INTO temp_result (user_id, total_amount) VALUES (v_user_id, v_amount); END LOOP cursor_loop; CLOSE cur_data; -- 对临时表做排名计算 UPDATE temp_result t1 SET rank_num = ( SELECT COUNT(*) + 1 FROM temp_result t2 WHERE t2.total_amount > t1.total_amount ); SELECT * FROM temp_result ORDER BY rank_num; DROP TEMPORARY TABLE temp_result; END;

临时表只在当前会话可见，存储过程结束后自动销毁。这个特性很适合做中间结果的暂存。

7. 常见问题与排查技巧实录

7.1 分隔符导致的创建失败

这是新手最容易踩的坑。存储过程和函数内部有分号，但客户端默认把分号当作语句结束符，导致创建失败。

-- 错误示范：直接这样写会报错 CREATE PROCEDURE sp_test() BEGIN SELECT 1; SELECT 2; END; -- 正确做法：先改分隔符 DELIMITER // CREATE PROCEDURE sp_test() BEGIN SELECT 1; SELECT 2; END // DELIMITER ;

DELIMITER //把语句结束符临时改成//，这样客户端就不会在遇到分号时截断语句。创建完成后再改回分号。这个操作在命令行客户端里必须做，但在一些图形化工具里可能不需要，因为工具会自动处理。

7.2 变量名与列名冲突

当变量名和表中的列名相同时，SQL 解析器可能分不清你指的是哪个。

CREATE PROCEDURE sp_name_conflict() BEGIN DECLARE v_name VARCHAR(50) DEFAULT 'test'; -- 这里的 name 可能被解析成变量，而不是列 SELECT * FROM users WHERE name = v_name; -- 更安全的写法：给变量加前缀 DECLARE v_user_name VARCHAR(50) DEFAULT 'test'; SELECT * FROM users WHERE name = v_user_name; END;

我的习惯是局部变量统一加v_前缀，参数加p_前缀。这样永远不会和列名冲突，代码可读性也更好。

7.3 游标未关闭导致的资源泄漏

游标打开后如果不关闭，会一直占用数据库资源。虽然存储过程结束时会自动清理，但在长时间运行的存储过程里，及时关闭游标是个好习惯。

CREATE PROCEDURE sp_cursor_safe() BEGIN DECLARE v_id INT; DECLARE v_done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id FROM users; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF v_done = 1 THEN LEAVE read_loop; END IF; -- 处理逻辑 END LOOP read_loop; CLOSE cur; -- 一定要关闭 END;

注意：如果游标循环中间发生了异常导致提前退出，CLOSE可能不会被执行。更安全的做法是在异常处理器里也关闭游标。

7.4 处理程序捕获不到预期异常

有时候你声明了处理器，但异常就是捕获不到。常见原因有三个：条件类型写错了、处理器声明位置不对、异常被内层块拦截了。

-- 排查步骤一：确认条件类型 -- 用 SHOW ERRORS 或 GET DIAGNOSTICS 查看实际错误码 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @err_code = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT; SELECT @err_code, @err_msg; END;

GET DIAGNOSTICS能拿到具体的错误码和错误信息，排查的时候非常有用。我一般会在开发阶段加上这个，确认实际抛出的错误码是什么，然后再针对性地写处理器。

7.5 常见问题速查表

7.6 调试存储过程的实用技巧

存储过程不像普通 SQL 那样容易调试，我一般用这几个方法：

方法一：用 SELECT 输出中间变量

CREATE PROCEDURE sp_debug_demo() BEGIN DECLARE v_step INT DEFAULT 0; DECLARE v_result VARCHAR(100); SET v_step = 1; SELECT v_step AS current_step, '开始执行' AS msg; -- 业务逻辑 SET v_result = '处理完成'; SET v_step = 2; SELECT v_step AS current_step, v_result AS msg; END;

方法二：写日志表

CREATE TABLE debug_log ( id INT AUTO_INCREMENT PRIMARY KEY, procedure_name VARCHAR(100), step_desc VARCHAR(200), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE PROCEDURE sp_with_log() BEGIN INSERT INTO debug_log (procedure_name, step_desc) VALUES ('sp_with_log', '步骤1：开始'); -- 业务逻辑 INSERT INTO debug_log (procedure_name, step_desc) VALUES ('sp_with_log', '步骤2：完成'); END;

日志表的方式更适合生产环境排查问题，因为你可以看到完整的执行轨迹。我一般会在关键节点插入日志，出问题的时候直接查日志表就能定位到哪一步出了问题。

8. 从实际项目出发的选型建议

8.1 什么时候该用存储过程

根据我这些年的经验，以下场景用存储过程是最合适的：

• 批量数据处理：比如每月的数据汇总、历史数据清理、批量状态更新
• 多步骤事务操作：比如订单创建涉及库存扣减、订单记录、日志记录等多个步骤
• 复杂业务逻辑编排：需要根据多个条件走不同分支，且每个分支涉及多条SQL
• 定时任务：配合事件调度器做定时执行的任务

-- 典型场景：订单创建 CREATE PROCEDURE sp_create_order( IN p_user_id INT, IN p_product_id INT, IN p_quantity INT, OUT p_order_id INT, OUT p_result VARCHAR(50) ) BEGIN DECLARE v_stock INT; DECLARE v_price DECIMAL(10,2); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_result = '创建失败'; END; START TRANSACTION; -- 检查库存 SELECT stock, price INTO v_stock, v_price FROM products WHERE id = p_product_id FOR UPDATE; IF v_stock < p_quantity THEN SET p_result = '库存不足'; ROLLBACK; ELSE -- 扣减库存 UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id; -- 创建订单 INSERT INTO orders (user_id, product_id, quantity, amount, status) VALUES (p_user_id, p_product_id, p_quantity, v_price * p_quantity, 'created'); SET p_order_id = LAST_INSERT_ID(); COMMIT; SET p_result = '创建成功'; END IF; END;

8.2 什么时候该用函数

函数适合以下场景：

• 数据格式化：金额格式化、日期格式化、手机号脱敏
• 计算逻辑：折扣计算、积分计算、等级判定
• 数据转换：状态码转文字、编码转名称
• 条件判断：在 WHERE 或 HAVING 中做复杂条件判断

-- 典型场景：手机号脱敏 CREATE FUNCTION fn_mask_phone(p_phone VARCHAR(20)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN IF p_phone IS NULL OR LENGTH(p_phone) < 7 THEN RETURN p_phone; END IF; RETURN CONCAT(LEFT(p_phone, 3), '****', RIGHT(p_phone, 4)); END; -- 使用 SELECT fn_mask_phone('13812345678'); -- 输出: 138****5678

8.3 两者混用的注意事项

有时候一个复杂逻辑需要存储过程和函数配合，这时候要注意几点：

• 函数里不要调用存储过程（语法上不允许）
• 存储过程可以调用函数
• 函数里不要做数据修改
• 存储过程调用函数时，注意函数的性能开销

-- 存储过程调用函数 CREATE PROCEDURE sp_use_function() BEGIN DECLARE v_masked VARCHAR(20); SELECT fn_mask_phone(phone) INTO v_masked FROM users WHERE id = 1; SELECT v_masked; END;

8.4 版本管理与部署建议

存储过程和函数的版本管理是个容易被忽视的问题。我的做法是：

• 每个存储过程和函数单独一个.sql文件
• 文件命名格式：sp_功能名_v版本号.sql
• 用DROP ... IF EXISTS再CREATE的方式做更新
• 变更记录写在文件头部注释里

-- sp_create_order_v2.sql -- 变更记录： -- v1: 初始版本 -- v2: 增加库存检查，增加 OUT 参数 p_result DROP PROCEDURE IF EXISTS sp_create_order; DELIMITER // CREATE PROCEDURE sp_create_order( -- 参数定义 ) BEGIN -- 逻辑 END // DELIMITER ;

这样做的好处是，每次部署都是幂等的，不会因为重复执行而报错。而且通过版本号能快速定位到当前运行的是哪个版本。

8.5 权限管理的最小化原则

存储过程和函数涉及权限管理，建议遵循最小化原则：

• 调用者只需要EXECUTE权限，不需要表的直接访问权限
• 创建者需要有CREATE ROUTINE权限
• 涉及DEFINER的设置要谨慎，确保定义者有足够的权限

-- 创建时指定定义者 CREATE DEFINER = 'app_user'@'%' PROCEDURE sp_safe_proc() BEGIN -- 逻辑 END; -- 授权调用者 GRANT EXECUTE ON PROCEDURE sp_safe_proc TO 'app_readonly'@'%';

这样app_readonly用户只能通过存储过程访问数据，不能直接查表，安全性更高。

9. 几个容易忽略的细节补充

9.1 DETERMINISTIC 和 NOT DETERMINISTIC 的影响

创建函数时必须指定DETERMINISTIC或NOT DETERMINISTIC。这个标记影响查询优化器的行为。

• DETERMINISTIC：相同输入总是返回相同输出，优化器可以缓存结果
• NOT DETERMINISTIC：相同输入可能返回不同输出，优化器不会缓存

-- 确定性函数：相同输入总是相同输出 CREATE FUNCTION fn_add(a INT, b INT) RETURNS INT DETERMINISTIC BEGIN RETURN a + b; END; -- 非确定性函数：包含 NOW() 等不确定因素 CREATE FUNCTION fn_get_current_hour() RETURNS INT NOT DETERMINISTIC BEGIN RETURN HOUR(NOW()); END;

如果你的函数确实是确定性的，一定要标记为DETERMINISTIC，这样在WHERE子句中使用时性能会好很多。

9.2 SQL SECURITY 的选择

函数和存储过程可以指定SQL SECURITY DEFINER或SQL SECURITY INVOKER。

• DEFINER：以定义者的权限执行（默认）
• INVOKER：以调用者的权限执行

CREATE PROCEDURE sp_security_demo() SQL SECURITY INVOKER BEGIN -- 以调用者的权限执行 SELECT * FROM sensitive_table; END;

DEFINER模式下，调用者不需要表的访问权限，只需要存储过程的执行权限。INVOKER模式下，调用者需要自己有表的访问权限。选择哪个取决于你的安全策略。

9.3 参数默认值的处理

MySQL 的存储过程和函数不支持参数默认值，但可以通过判断 NULL 来模拟。

CREATE PROCEDURE sp_with_default( IN p_status INT, IN p_limit INT ) BEGIN -- 模拟默认值 IF p_status IS NULL THEN SET p_status = 1; END IF; IF p_limit IS NULL OR p_limit <= 0 THEN SET p_limit = 100; END IF; SELECT * FROM orders WHERE status = p_status LIMIT p_limit; END;

调用的时候传NULL就会使用默认值。这个技巧在实际项目中很实用，能让存储过程的调用更灵活。

9.4 查看和删除存储过程

日常维护中经常需要查看已有的存储过程和函数。

-- 查看存储过程列表 SHOW PROCEDURE STATUS WHERE Db = 'your_database'; -- 查看函数列表 SHOW FUNCTION STATUS WHERE Db = 'your_database'; -- 查看创建语句 SHOW CREATE PROCEDURE sp_create_order; SHOW CREATE FUNCTION fn_mask_phone; -- 删除 DROP PROCEDURE IF EXISTS sp_create_order; DROP FUNCTION IF EXISTS fn_mask_phone;

我建议在删除之前先用SHOW CREATE把定义导出来备份，万一需要恢复也有个依据。

9.5 性能监控与优化

存储过程运行慢的时候，可以用SHOW PROCESSLIST查看当前执行的语句，或者开启慢查询日志。

-- 查看当前运行的存储过程 SHOW PROCESSLIST; -- 开启慢查询日志 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 2;

另外，存储过程内部的 SQL 语句也可以用EXPLAIN分析执行计划。把存储过程里的关键查询单独拿出来EXPLAIN，看看有没有走索引、有没有全表扫描。

-- 分析存储过程中的关键查询 EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

如果发现某个查询没有走索引，可以考虑加索引或者改写查询。存储过程的性能问题，九成以上都出在内部的 SQL 语句上，而不是存储过程本身的逻辑。

9.6 代码规范建议

最后分享几条我总结的代码规范，都是踩坑之后形成的习惯：

• 存储过程名以sp_开头，函数名以fn_开头
• 参数以p_开头，局部变量以v_开头
• 每个BEGIN...END块都加注释说明用途
• 异常处理器统一放在块的末尾
• 游标使用后必须关闭
• 事务操作必须有异常回滚
• 避免在循环中执行查询，尽量用集合操作

CREATE PROCEDURE sp_good_example( IN p_user_id INT, OUT p_result VARCHAR(50) ) BEGIN -- 变量声明 DECLARE v_count INT DEFAULT 0; DECLARE v_total DECIMAL(10,2) DEFAULT 0; -- 异常处理 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_result = '执行失败'; END; -- 业务逻辑 START TRANSACTION; SELECT COUNT(*), IFNULL(SUM(amount), 0) INTO v_count, v_total FROM orders WHERE user_id = p_user_id; IF v_count > 0 THEN UPDATE user_stats SET order_count = v_count, total_amount = v_total WHERE user_id = p_user_id; END IF; COMMIT; SET p_result = '执行成功'; END;

这套规范看起来简单，但坚持下来能避免很多低级错误。尤其是变量命名和异常处理这两块，规范之后代码的可读性和健壮性都有明显提升。

我在实际项目里最大的体会是：存储过程和函数不是用来炫技的，而是用来解决实际问题的。能用简单 SQL 解决的，就不要上存储过程；能用应用层代码处理的，就不要硬塞到数据库里。只有在确实需要减少网络交互、保证事务原子性、或者做批量数据处理的时候，才考虑用存储过程和函数。这个判断标准帮我避免了很多过度设计，也让代码维护变得轻松不少。