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 out0; SET inout10; 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中逐行调用时如果函数内部有查询操作可能导致N1问题。我实测过一个案例一张十万行的表每行调用一个包含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 选择决策表对比维度存储过程函数调用方式CALL语句嵌入SQL表达式参数模式IN/OUT/INOUT仅IN返回值零到多个结果集必须返回单一值事务控制支持不支持在SELECT中使用不可以可以副作用允许修改数据应避免修改数据适用场景批量处理、事务封装计算、转换、嵌入查询经验法则需要返回多个值或做数据修改的用存储过程需要嵌入查询做计算的用函数。如果拿不准优先考虑存储过程因为它的能力更全面后续扩展空间更大。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 游标循环的三种写法上面用的是LOOPLEAVE的写法还有两种常见写法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;三种写法功能等价但LOOPLEAVE的写法最不容易出错因为它把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 常见错误速查表错误现象可能原因排查方法创建过程时报1064语法错误DECLARE顺序不对、缺少分号、DELIMITER未改检查DECLARE是否在块开头用DELIMITER $$包裹调用时报1305过程不存在数据库选错、名称拼写错误、大小写敏感用SHOW PROCEDURE STATUS确认游标循环只执行一次NOT FOUND处理程序位置不对确保HANDLER在CURSOR之后声明变量值为NULLSELECT INTO未匹配到行用IFNULL或COALESCE设默认值处理程序未捕获异常错误码不匹配、HANDLER类型不对用GET DIAGNOSTICS获取实际错误码函数在SELECT中报1418未声明DETERMINISTIC或READS SQL DATA根据函数行为添加正确的特性声明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默认字符集是utf8mb45.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不能改逻辑只能改特性。所以实际中还是用DROPCREATE配合权限恢复脚本。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中调用时如果函数内部有查询注意N1问题。能JOIN就JOIN不能JOIN就考虑用存储过程批量处理。最后分享一个调试技巧如果存储过程逻辑复杂可以先用SELECT语句把中间结果查出来确认每一步的数据都符合预期再改写成存储过程。这样比直接在存储过程里调试效率高得多。