MySQL存储过程与函数实战：核心区别、游标、异常处理与性能优化

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

刚入行那会儿，我对存储过程和函数的理解就停留在“数据库里能写逻辑”这个层面。直到有一次做报表统计，一条复杂查询在应用层拼了七八个SQL，网络往返十几次，响应时间直接飙到三秒多。后来把逻辑下沉到数据库，用存储过程封装，同样的业务响应降到两百毫秒以内。那次之后我才真正意识到，存储过程和函数不是“老古董”，而是特定场景下的性能利器。

这篇文章面向的是已经会写基本SQL、但对数据库编程还不太熟的开发者。我会把存储过程和函数的区别、变量定义、条件判断、异常处理、游标、流程控制这些核心知识点全部拆开讲，每个点都配可直接运行的代码示例。你不需要有数据库编程经验，只要会写SELECT、INSERT、UPDATE、DELETE，就能跟着走下来。读完之后，你应该能独立写出带异常处理、带游标遍历、带复杂流程控制的存储过程和函数，并且知道什么场景该用哪个。

先明确一个前提：不同数据库的语法有差异，下面所有示例以MySQL 8.0为基准，大部分语法在MariaDB上也能跑。其他数据库如PostgreSQL、SQL Server的思路上相通，具体写法需要查对应文档。我选择MySQL作为示例，是因为它足够普及，而且8.0之后对存储程序的支持已经相当完善。

2. 存储过程和函数到底哪里不一样

2.1 从调用方式看本质区别

存储过程和函数最直观的区别在调用方式上。存储过程用CALL语句调用，函数则像内置函数一样直接嵌在SQL表达式里使用。这个差异看起来只是语法问题，但它决定了二者在设计哲学上的分道扬镳。

存储过程更像一个“批处理脚本”，它可以包含事务控制、可以返回多个结果集、可以修改数据、可以调用其他存储过程。函数则更像一个“计算器”，它接收参数、返回一个值，通常不应该有副作用（虽然MySQL不强制禁止函数修改数据，但这是行业共识）。

-- 存储过程调用方式 CALL sp_get_employee_stats('研发部', @total, @avg_salary); SELECT @total, @avg_salary; -- 函数调用方式 SELECT fn_calculate_bonus(employee_id, performance_score) FROM employees;

从上面代码能看出来，存储过程通过输出参数带回多个值，函数则直接返回单一值。这个差异直接影响了它们的使用场景：需要返回多个结果、需要做一系列数据操作的，用存储过程；需要嵌入查询、需要参与计算的，用函数。

2.2 参数传递的细节差异

存储过程的参数有三种模式：IN、OUT、INOUT。IN是默认模式，表示传入值；OUT表示传出值；INOUT表示既可传入也可传出。函数参数只有IN一种模式，因为函数的设计目标就是“输入参数、返回结果”。

-- 存储过程：三种参数模式 CREATE PROCEDURE sp_demo_params( IN p_input INT, OUT p_output INT, INOUT p_inout INT ) BEGIN SET p_output = p_input * 2; SET p_inout = p_inout + p_input; END; -- 函数：只有输入参数 CREATE FUNCTION fn_demo_params(p_input INT) RETURNS INT DETERMINISTIC BEGIN RETURN p_input * 2; END;

这里有个容易踩的坑：存储过程的OUT和INOUT参数在调用时必须传变量，不能传字面量。比如CALL sp_demo_params(5, 10, 15)会报错，因为10和15是字面量，无法接收输出值。必须写成SET @out=0; SET @inout=10; CALL sp_demo_params(5, @out, @inout);。

2.3 返回值能力的对比

存储过程可以返回零个、一个或多个结果集，也可以完全没有任何返回值。函数必须返回一个值，而且返回类型在定义时就固定了。这个差异导致了一个重要的使用限制：函数可以出现在SELECT语句的列表达式中，存储过程不行。

-- 函数可以这样用 SELECT employee_name, salary, fn_calculate_tax(salary) AS tax FROM employees; -- 存储过程不能这样用，只能单独调用 CALL sp_calculate_all_taxes();

从性能角度看，函数在SELECT中逐行调用时，如果函数内部有查询操作，可能导致N+1问题。我实测过一个案例：一张十万行的表，每行调用一个包含SELECT查询的函数，执行时间超过两分钟。改成JOIN或者存储过程批量处理后，降到三秒以内。所以函数虽好，但不要在函数里做复杂查询。

2.4 事务控制的权限差异

存储过程内部可以开启、提交、回滚事务，函数则不允许。这是因为函数设计上应该是“无副作用”的，如果函数能控制事务，在SELECT语句中调用时会产生不可预期的行为。

-- 存储过程中可以控制事务 CREATE PROCEDURE sp_transfer_funds( IN p_from_account INT, IN p_to_account INT, IN p_amount DECIMAL(10,2) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account; UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account; COMMIT; END;

这个事务控制能力是存储过程在金融、电商等场景中不可替代的原因之一。把转账逻辑封装在存储过程里，应用层只需要调用一次，事务边界清晰，不会因为网络中断导致部分提交。

2.5 选择决策表

经验法则：需要返回多个值或做数据修改的，用存储过程；需要嵌入查询做计算的，用函数。如果拿不准，优先考虑存储过程，因为它的能力更全面，后续扩展空间更大。

3. 变量、条件与处理程序：存储程序的骨架

3.1 变量定义的三种方式

MySQL存储程序中的变量分三类：局部变量、用户变量、系统变量。局部变量用DECLARE定义在BEGIN...END块内，作用域限于该块；用户变量以@开头，会话级别有效；系统变量分全局和会话两种，通常不需要我们手动定义。

CREATE PROCEDURE sp_variable_demo() BEGIN -- 局部变量，必须定义在块的开头 DECLARE v_counter INT DEFAULT 0; DECLARE v_name VARCHAR(50); DECLARE v_total DECIMAL(10,2) DEFAULT 0.00; -- 用户变量，不需要DECLARE SET @user_var = 'hello'; -- 赋值方式一：SET SET v_counter = 10; SET v_name = '张三'; -- 赋值方式二：SELECT INTO SELECT COUNT(*) INTO v_counter FROM employees; SELECT employee_name INTO v_name FROM employees WHERE employee_id = 1; -- 多变量同时赋值 SELECT COUNT(*), AVG(salary) INTO v_counter, v_total FROM employees; SELECT v_counter, v_name, v_total, @user_var; END;

这里有个关键细节：DECLARE语句必须放在BEGIN...END块的最前面，不能和普通SQL语句混在一起。我见过不少新手把DECLARE写在SET语句后面，结果直接报语法错误。另外，SELECT INTO如果查询返回多行，会抛出异常；如果返回零行，变量保持原值但会发出警告。稳妥的做法是配合异常处理使用。

3.2 条件判断的两种写法

MySQL存储程序支持IF和CASE两种条件判断。IF适合少量分支，CASE适合多分支且条件基于同一个表达式。

CREATE PROCEDURE sp_grade_employee(IN p_score INT, OUT p_grade VARCHAR(10)) BEGIN -- IF写法 IF p_score >= 90 THEN SET p_grade = '优秀'; ELSEIF p_score >= 80 THEN SET p_grade = '良好'; ELSEIF p_score >= 60 THEN SET p_grade = '及格'; ELSE SET p_grade = '不及格'; END IF; END; CREATE PROCEDURE sp_grade_employee_case(IN p_score INT, OUT p_grade VARCHAR(10)) BEGIN -- CASE写法（简单CASE） CASE WHEN p_score >= 90 THEN SET p_grade = '优秀'; WHEN p_score >= 80 THEN SET p_grade = '良好'; WHEN p_score >= 60 THEN SET p_grade = '及格'; ELSE SET p_grade = '不及格'; END CASE; END;

CASE还有另一种“搜索CASE”写法，和IF更接近。两种写法性能上没有明显差异，选择哪种主要看可读性。我的习惯是：分支超过三个且条件都是对同一个变量做范围判断时用CASE，否则用IF。

3.3 处理程序：异常捕获的核心机制

处理程序（HANDLER）是存储程序中最容易被忽视但最重要的部分。没有异常处理的存储过程，一旦出错就会直接中断，调用方只能看到一个模糊的错误信息。加上处理程序后，你可以决定是继续执行、退出还是回滚。

CREATE PROCEDURE sp_safe_insert_employee( IN p_name VARCHAR(50), IN p_salary DECIMAL(10,2), OUT p_result VARCHAR(100) ) BEGIN -- 声明一个CONTINUE处理程序，捕获重复键错误 DECLARE CONTINUE HANDLER FOR 1062 BEGIN SET p_result = CONCAT('员工 ', p_name, ' 已存在，跳过插入'); END; -- 声明一个EXIT处理程序，捕获其他SQL异常 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; SET p_result = CONCAT('插入失败，错误码：', @errno, '，错误信息：', @msg); END; INSERT INTO employees (employee_name, salary) VALUES (p_name, p_salary); SET p_result = CONCAT('员工 ', p_name, ' 插入成功'); END;

处理程序有三种类型：CONTINUE表示出错后继续执行当前块的后续语句；EXIT表示出错后退出当前块；UNDO表示回滚当前块的操作（MySQL不支持UNDO）。捕获条件可以是错误码、SQLSTATE或者SQLEXCEPTION/SQLWARNING/NOT FOUND这些统称。

实操心得：处理程序的声明顺序有讲究。MySQL要求DECLARE HANDLER必须放在变量和游标声明之后。如果你把HANDLER写在DECLARE CURSOR前面，会报语法错误。我踩过这个坑，排查了半小时才发现是顺序问题。

3.4 条件定义与自定义异常

除了捕获系统异常，你还可以定义自己的条件名称，让代码更可读。

CREATE PROCEDURE sp_check_salary(IN p_salary DECIMAL(10,2)) BEGIN -- 定义自定义条件 DECLARE salary_too_low CONDITION FOR SQLSTATE '45000'; -- 声明处理程序 DECLARE EXIT HANDLER FOR salary_too_low BEGIN SELECT '薪资低于最低标准，操作终止' AS error_message; END; IF p_salary < 3000 THEN SIGNAL salary_too_low SET MESSAGE_TEXT = '薪资不能低于3000'; END IF; SELECT CONCAT('薪资 ', p_salary, ' 验证通过') AS result; END;

SIGNAL语句用来主动抛出一个异常，RESIGNAL用来在处理程序中重新抛出异常。这两个语句配合自定义条件使用，可以让错误处理逻辑非常清晰。

4. 游标：逐行处理的正确姿势

4.1 游标的使用场景与代价

游标用于逐行遍历结果集。很多教程把游标讲得很美好，但我要先泼一盆冷水：游标在绝大多数情况下都是性能杀手。它本质上是用过程式的方式处理集合式的问题，违背了SQL的设计哲学。

那什么时候不得不用游标？当你需要对每一行执行不同的操作，而这些操作无法用一条UPDATE或INSERT...SELECT表达时。比如：根据每行数据调用不同的外部逻辑、需要逐行计算并更新多个关联表、需要生成复杂的序号或层级路径。

-- 游标的标准使用模板 CREATE PROCEDURE sp_cursor_demo() BEGIN -- 1. 声明变量，用于接收游标数据 DECLARE v_done INT DEFAULT FALSE; DECLARE v_emp_id INT; DECLARE v_emp_name VARCHAR(50); DECLARE v_salary DECIMAL(10,2); -- 2. 声明游标 DECLARE cur_employee CURSOR FOR SELECT employee_id, employee_name, salary FROM employees WHERE salary > 5000; -- 3. 声明NOT FOUND处理程序，用于结束循环 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; -- 4. 打开游标 OPEN cur_employee; -- 5. 循环读取 read_loop: LOOP FETCH cur_employee INTO v_emp_id, v_emp_name, v_salary; IF v_done THEN LEAVE read_loop; END IF; -- 6. 处理每一行数据 UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_emp_id; SELECT CONCAT('已调整 ', v_emp_name, ' 的薪资，原薪资：', v_salary) AS log_msg; END LOOP; -- 7. 关闭游标 CLOSE cur_employee; END;

这个模板里的每一步都有讲究。变量声明必须在游标之前，NOT FOUND处理程序必须在游标之后，OPEN和CLOSE必须成对出现。漏掉CLOSE会导致游标占用的资源无法释放，在长连接场景下可能引发内存泄漏。

4.2 游标循环的三种写法

上面用的是LOOP+LEAVE的写法，还有两种常见写法：REPEAT和WHILE。

-- REPEAT写法 OPEN cur_employee; REPEAT FETCH cur_employee INTO v_emp_id, v_emp_name, v_salary; IF NOT v_done THEN -- 处理逻辑 END IF; UNTIL v_done END REPEAT; CLOSE cur_employee; -- WHILE写法 OPEN cur_employee; FETCH cur_employee INTO v_emp_id, v_emp_name, v_salary; WHILE NOT v_done DO -- 处理逻辑 FETCH cur_employee INTO v_emp_id, v_emp_name, v_salary; END WHILE; CLOSE cur_employee;

三种写法功能等价，但LOOP+LEAVE的写法最不容易出错，因为它把FETCH和判断放在循环体开头，逻辑最直观。REPEAT写法在空结果集时也能正确处理，WHILE写法需要先FETCH一次，容易漏掉。

4.3 游标性能优化的实战经验

游标慢，但有些场景绕不开。我总结了几个优化技巧：

第一，尽量缩小游标结果集。在游标的SELECT语句里加WHERE条件，只取需要处理的行。我见过有人把整张表放进游标，然后在循环里用IF过滤，这是典型的反模式。

第二，游标内避免复杂查询。如果循环体里每行都要做JOIN查询，考虑先把数据JOIN好再放进游标。比如需要员工和部门信息，就在游标的SELECT里直接JOIN，而不是在循环里再查部门表。

第三，批量提交。如果游标内做UPDATE或INSERT，不要每行提交一次。可以在循环外开启事务，循环结束后统一提交。但要注意事务大小，太大可能导致锁等待和回滚段膨胀。

-- 优化后的游标示例：批量提交 CREATE PROCEDURE sp_batch_update_salary() BEGIN DECLARE v_done INT DEFAULT FALSE; DECLARE v_emp_id INT; DECLARE v_counter INT DEFAULT 0; DECLARE cur_employee CURSOR FOR SELECT employee_id FROM employees WHERE department_id = 10; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; START TRANSACTION; OPEN cur_employee; read_loop: LOOP FETCH cur_employee INTO v_emp_id; IF v_done THEN LEAVE read_loop; END IF; UPDATE employees SET salary = salary * 1.05 WHERE employee_id = v_emp_id; SET v_counter = v_counter + 1; -- 每1000行提交一次 IF v_counter % 1000 = 0 THEN COMMIT; START TRANSACTION; END IF; END LOOP; CLOSE cur_employee; COMMIT; END;

注意：游标内的COMMIT会释放游标持有的锁，但不会关闭游标。不过在某些数据库版本中，COMMIT后游标可能失效，需要重新OPEN。MySQL中游标是只读的，COMMIT不影响游标继续读取，但为了保险起见，建议在测试环境验证后再上生产。

5. 流程控制：让存储程序真正“会思考”

5.1 IF与CASE的实战选择

前面讲了IF和CASE的基本语法，这里补充几个实战中的选择依据。IF适合条件之间没有直接关系的场景，比如“如果用户是VIP就打八折，如果订单金额超过500就免运费”。CASE适合对同一个表达式做多值匹配，比如“根据订单状态码返回不同的描述文本”。

-- IF适合独立条件 CREATE FUNCTION fn_calculate_discount( p_is_vip BOOLEAN, p_order_amount DECIMAL(10,2) ) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE v_discount DECIMAL(10,2) DEFAULT 0; IF p_is_vip THEN SET v_discount = p_order_amount * 0.2; END IF; IF p_order_amount > 500 THEN SET v_discount = v_discount + 50; END IF; RETURN v_discount; END; -- CASE适合多值匹配 CREATE FUNCTION fn_order_status_text(p_status INT) RETURNS VARCHAR(20) DETERMINISTIC BEGIN RETURN CASE p_status WHEN 1 THEN '待支付' WHEN 2 THEN '已支付' WHEN 3 THEN '已发货' WHEN 4 THEN '已完成' WHEN 5 THEN '已取消' ELSE '未知状态' END; END;

5.2 循环结构的三种形态

MySQL支持LOOP、WHILE、REPEAT三种循环。LOOP是无条件循环，必须配合LEAVE退出；WHILE先判断后执行；REPEAT先执行后判断。

-- LOOP：至少执行一次，需要手动退出 CREATE PROCEDURE sp_loop_demo() BEGIN DECLARE v_i INT DEFAULT 1; my_loop: LOOP IF v_i > 5 THEN LEAVE my_loop; END IF; SELECT CONCAT('LOOP迭代：', v_i) AS msg; SET v_i = v_i + 1; END LOOP; END; -- WHILE：可能一次都不执行 CREATE PROCEDURE sp_while_demo() BEGIN DECLARE v_i INT DEFAULT 1; WHILE v_i <= 5 DO SELECT CONCAT('WHILE迭代：', v_i) AS msg; SET v_i = v_i + 1; END WHILE; END; -- REPEAT：至少执行一次 CREATE PROCEDURE sp_repeat_demo() BEGIN DECLARE v_i INT DEFAULT 1; REPEAT SELECT CONCAT('REPEAT迭代：', v_i) AS msg; SET v_i = v_i + 1; UNTIL v_i > 5 END REPEAT; END;

选择哪种循环？如果循环次数确定且需要先判断，用WHILE；如果至少需要执行一次，用REPEAT；如果需要复杂的退出条件（比如多个LEAVE点），用LOOP。我个人的习惯是优先用WHILE，因为它的语义最清晰。

5.3 LEAVE与ITERATE的配合使用

LEAVE用于跳出循环或BEGIN...END块，ITERATE用于跳过本次循环进入下一次。这两个语句配合标签（label）使用，可以实现复杂的控制流。

CREATE PROCEDURE sp_leave_iterate_demo() BEGIN DECLARE v_i INT DEFAULT 0; DECLARE v_j INT DEFAULT 0; outer_loop: LOOP SET v_i = v_i + 1; IF v_i > 3 THEN LEAVE outer_loop; END IF; SET v_j = 0; inner_loop: LOOP SET v_j = v_j + 1; IF v_j > 3 THEN ITERATE outer_loop; -- 跳过内层剩余，直接进入外层下一次 END IF; IF v_i = 2 AND v_j = 2 THEN ITERATE inner_loop; -- 跳过本次内层循环 END IF; SELECT CONCAT('i=', v_i, ', j=', v_j) AS msg; END LOOP inner_loop; END LOOP outer_loop; END;

标签的命名要有意义，不要用l1、l2这种。另外，LEAVE可以用于跳出BEGIN...END块，这在需要提前返回的场景中很有用。

5.4 流程控制在实际业务中的组合应用

单独看每个流程控制语句都很简单，但组合起来能解决复杂问题。举个例子：生成员工工号，规则是“部门代码+入职年份+三位序号”，序号在部门内按年份递增。

CREATE PROCEDURE sp_generate_employee_code( IN p_department_code VARCHAR(10), IN p_hire_year INT, OUT p_employee_code VARCHAR(30) ) BEGIN DECLARE v_max_seq INT DEFAULT 0; DECLARE v_new_seq INT DEFAULT 1; DECLARE v_lock_result INT DEFAULT 0; -- 异常处理 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_employee_code = NULL; RESIGNAL; END; -- 获取当前最大序号，加锁防止并发冲突 SELECT COALESCE(MAX(CAST(SUBSTRING(employee_code, -3) AS UNSIGNED)), 0) INTO v_max_seq FROM employees WHERE employee_code LIKE CONCAT(p_department_code, p_hire_year, '%') FOR UPDATE; SET v_new_seq = v_max_seq + 1; -- 序号超过999时循环进位（简化处理，实际业务可能不同） WHILE v_new_seq > 999 DO SET v_new_seq = v_new_seq - 999; END WHILE; -- 拼接工号 SET p_employee_code = CONCAT( p_department_code, p_hire_year, LPAD(v_new_seq, 3, '0') ); SELECT CONCAT('生成工号：', p_employee_code) AS result; END;

这个例子用到了变量、异常处理、SELECT INTO、WHILE循环、字符串函数，是一个比较完整的业务场景。实际使用时还需要考虑并发情况下的锁策略，这里用FOR UPDATE做了行锁，但高并发下可能需要更细粒度的控制。

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

6.1 存储过程调试的土办法

MySQL没有像PL/SQL Developer那样好用的存储过程调试器，我常用的办法是“日志表法”：建一张临时日志表，在存储过程的关键节点插入日志记录。

CREATE TABLE IF NOT EXISTS debug_log ( id INT AUTO_INCREMENT PRIMARY KEY, proc_name VARCHAR(100), step_name VARCHAR(100), var_values TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 在存储过程中插入日志 INSERT INTO debug_log (proc_name, step_name, var_values) VALUES ('sp_generate_employee_code', 'after_max_seq', CONCAT('v_max_seq=', v_max_seq));

这个方法虽然原始，但非常有效。调试完成后删掉日志插入语句即可。注意不要在正式环境长期保留日志表，会积累大量数据。

6.2 常见错误速查表

6.3 性能问题的排查思路

存储过程变慢，通常从三个方向排查：游标使用、循环内查询、事务大小。

先看有没有游标。如果有，评估能否改成集合操作。我优化过一个存储过程，把游标遍历改成UPDATE...JOIN，执行时间从45秒降到0.8秒。

再看循环体内有没有查询。每行一次SELECT，一万行就是一万次查询。改成JOIN或者临时表预加载，性能提升立竿见影。

最后看事务。大事务会导致锁等待和回滚段膨胀。如果存储过程处理大量数据，考虑分批提交。但要注意，分批提交会破坏原子性，需要业务上能接受部分成功。

-- 用性能模式查看存储过程执行统计 SELECT OBJECT_NAME, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms, AVG_TIMER_WAIT / 1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_program WHERE OBJECT_TYPE = 'PROCEDURE' ORDER BY SUM_TIMER_WAIT DESC;

6.4 版本兼容性避坑

MySQL 5.7和8.0在存储程序上有一些差异。8.0支持窗口函数、CTE，可以在存储过程中使用；5.7不支持。8.0默认字符集是utf8mb4，5.7是latin1，涉及中文时要注意。另外，8.0对存储程序的权限管理更严格，CREATE ROUTINE权限和EXECUTE权限是分开的。

如果你写的存储过程需要在多个版本上运行，避免使用版本特有的语法。比如不要用CTE，用临时表替代；不要用窗口函数，用变量模拟。

7. 一个完整的实战案例：订单超时自动处理

把前面所有知识点串起来，做一个完整的案例：电商场景中，订单超过30分钟未支付自动取消，并释放库存。

DELIMITER $$ CREATE PROCEDURE sp_auto_cancel_expired_orders( IN p_timeout_minutes INT, OUT p_cancelled_count INT, OUT p_result_msg VARCHAR(200) ) BEGIN -- 变量声明 DECLARE v_order_id BIGINT; DECLARE v_product_id BIGINT; DECLARE v_quantity INT; DECLARE v_done INT DEFAULT FALSE; DECLARE v_error_count INT DEFAULT 0; DECLARE v_success_count INT DEFAULT 0; -- 游标：查询所有超时未支付订单 DECLARE cur_expired_orders CURSOR FOR SELECT o.order_id, oi.product_id, oi.quantity FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.status = 'PENDING' AND o.created_at < DATE_SUB(NOW(), INTERVAL p_timeout_minutes MINUTE) FOR UPDATE; -- 处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; ROLLBACK; SET p_result_msg = CONCAT('处理失败，错误码：', @errno, '，信息：', @msg); END; -- 初始化输出参数 SET p_cancelled_count = 0; -- 开启事务 START TRANSACTION; OPEN cur_expired_orders; cancel_loop: LOOP FETCH cur_expired_orders INTO v_order_id, v_product_id, v_quantity; IF v_done THEN LEAVE cancel_loop; END IF; -- 更新订单状态 UPDATE orders SET status = 'CANCELLED', cancelled_at = NOW(), cancel_reason = '超时未支付自动取消' WHERE order_id = v_order_id AND status = 'PENDING'; -- 只有确实更新了订单才释放库存 IF ROW_COUNT() > 0 THEN UPDATE product_stock SET stock = stock + v_quantity, updated_at = NOW() WHERE product_id = v_product_id; SET v_success_count = v_success_count + 1; END IF; SET p_cancelled_count = p_cancelled_count + 1; -- 每500条提交一次，避免大事务 IF p_cancelled_count % 500 = 0 THEN COMMIT; START TRANSACTION; END IF; END LOOP; CLOSE cur_expired_orders; COMMIT; SET p_result_msg = CONCAT('处理完成，共取消订单 ', p_cancelled_count, ' 个'); END$$ DELIMITER ; -- 调用示例 SET @cancelled = 0; SET @msg = ''; CALL sp_auto_cancel_expired_orders(30, @cancelled, @msg); SELECT @cancelled AS cancelled_count, @msg AS result_message;

这个案例涵盖了变量声明、游标、异常处理、事务控制、流程控制、批量提交等核心知识点。实际部署时，建议用定时任务每5分钟调用一次，而不是实时触发。另外，FOR UPDATE在订单量大时可能造成锁竞争，可以考虑用乐观锁替代。

实操心得：这个存储过程在测试环境跑十万订单大约需要8秒，主要耗时在游标逐行处理。如果订单量更大，建议改成批量UPDATE：先用一条UPDATE取消所有超时订单，再用一条UPDATE...JOIN释放库存。虽然逻辑上不如游标直观，但性能会好很多。游标适合逻辑复杂、无法批量化的场景，能批量就批量。

8. 函数的高级用法与限制

8.1 函数的特性声明

创建函数时必须声明特性，这决定了函数的行为和优化器的处理方式。DETERMINISTIC表示相同输入总是返回相同输出；NOT DETERMINISTIC表示可能返回不同结果；READS SQL DATA表示会读取数据但不修改；MODIFIES SQL DATA表示会修改数据；NO SQL表示不涉及SQL。

-- 确定性函数：相同输入相同输出 CREATE FUNCTION fn_add_tax(p_amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC NO SQL BEGIN RETURN p_amount * 1.13; END; -- 非确定性函数：依赖外部数据 CREATE FUNCTION fn_get_current_stock(p_product_id BIGINT) RETURNS INT NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_stock INT; SELECT stock INTO v_stock FROM product_stock WHERE product_id = p_product_id; RETURN IFNULL(v_stock, 0); END;

如果函数在SELECT中被调用，且声明了DETERMINISTIC，优化器可能会缓存结果。如果实际是非确定性的但声明了DETERMINISTIC，会导致错误结果。所以特性声明必须准确。

8.2 函数中不能做的事

函数中不能使用事务控制语句（COMMIT、ROLLBACK），不能使用LOCK TABLES，不能使用LOAD DATA，不能使用ALTER、CREATE、DROP等DDL。这些限制是为了保证函数在SELECT中调用时不会产生不可预期的副作用。

另外，函数中不能返回结果集，只能返回单一值。如果需要返回多行多列，只能用存储过程。

8.3 函数与存储过程的互相调用

函数可以调用其他函数，存储过程可以调用函数和其他存储过程。但函数不能调用存储过程，因为存储过程可能返回结果集或控制事务，这与函数的设计原则冲突。

-- 存储过程调用函数 CREATE PROCEDURE sp_use_function() BEGIN DECLARE v_tax DECIMAL(10,2); SET v_tax = fn_add_tax(1000); SELECT CONCAT('税额：', v_tax) AS result; END; -- 函数调用函数 CREATE FUNCTION fn_calculate_total(p_amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC NO SQL BEGIN RETURN p_amount + fn_add_tax(p_amount); END;

这种调用关系在拆分复杂逻辑时很有用。把通用的计算逻辑封装成函数，存储过程负责流程编排和数据操作。

9. 权限管理与部署实践

9.1 存储程序的权限模型

MySQL中存储程序的权限分创建权限和执行权限。CREATE ROUTINE权限允许创建存储过程和函数，EXECUTE权限允许调用。ALTER ROUTINE权限允许修改和删除。

-- 创建专用账号，只给执行权限 CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password'; GRANT EXECUTE ON PROCEDURE mydb.sp_auto_cancel_expired_orders TO 'app_user'@'%'; GRANT EXECUTE ON FUNCTION mydb.fn_add_tax TO 'app_user'@'%'; -- 查看权限 SHOW GRANTS FOR 'app_user'@'%';

这里有个安全细节：存储过程默认以定义者（DEFINER）的权限执行，而不是调用者的权限。这意味着如果定义者有高权限，调用者可以通过存储过程间接执行高权限操作。所以创建存储过程时要谨慎选择DEFINER，必要时用SQL SECURITY INVOKER让存储过程以调用者权限执行。

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

9.2 版本管理与部署脚本

存储过程的版本管理是个痛点。我的做法是把每个存储过程的创建脚本单独存文件，文件名包含版本号，部署时用脚本批量执行。

# 部署脚本示例 mysql -u deploy_user -p mydb < procedures/sp_auto_cancel_expired_orders_v1.2.sql mysql -u deploy_user -p mydb < functions/fn_add_tax_v1.0.sql

脚本里用DROP PROCEDURE IF EXISTS + CREATE PROCEDURE的方式，保证可重复执行。但要注意，DROP会丢失权限设置，所以部署后需要重新授权。更好的方式是用ALTER PROCEDURE修改，但ALTER不能改逻辑，只能改特性。所以实际中还是用DROP+CREATE，配合权限恢复脚本。

9.3 监控与告警

存储过程在生产环境运行，需要监控执行时间和错误率。MySQL的performance_schema提供了存储程序的执行统计，可以定期采集。

-- 查看执行最慢的存储过程 SELECT OBJECT_NAME, COUNT_STAR AS exec_count, SUM_TIMER_WAIT / 1000000000 AS total_ms, AVG_TIMER_WAIT / 1000000000 AS avg_ms, MAX_TIMER_WAIT / 1000000000 AS max_ms FROM performance_schema.events_statements_summary_by_program WHERE OBJECT_TYPE = 'PROCEDURE' ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

如果某个存储过程的平均执行时间突然上升，可能是数据量增长或索引失效。如果错误率上升，检查最近的代码变更或数据异常。

10. 一些零散但重要的经验

存储过程的参数不要超过10个，太多参数说明逻辑太复杂，应该拆分。参数命名用p_前缀，变量用v_前缀，这是约定俗成的规范，能大幅提升代码可读性。

游标内的UPDATE要加WHERE条件限制范围，我见过有人游标遍历A表，UPDATE B表时忘了WHERE，结果每次循环都更新全表，数据量小时没发现，数据量大了直接锁死。

异常处理不要吞掉异常。CONTINUE HANDLER里至少要记录日志，否则出了问题完全不知道。EXIT HANDLER里要RESIGNAL，让调用方知道出错了。

存储过程的注释要写清楚业务逻辑、参数含义、返回值说明。半年后回来看自己的代码，没有注释等于重新读一遍。

测试存储过程时，先用小数据集验证逻辑，再用大数据集验证性能。我习惯用LIMIT 100先跑一遍，确认结果正确后再去掉LIMIT跑全量。

函数在SELECT中调用时，如果函数内部有查询，注意N+1问题。能JOIN就JOIN，不能JOIN就考虑用存储过程批量处理。

最后分享一个调试技巧：如果存储过程逻辑复杂，可以先用SELECT语句把中间结果查出来，确认每一步的数据都符合预期，再改写成存储过程。这样比直接在存储过程里调试效率高得多。