1. 概述MySQL 存储过程和函数是数据库编程的核心能力允许将一组 SQL 语句封装为可复用的数据库对象直接在数据库服务端执行。配合变量声明、流程控制和游标机制可以实现复杂的业务逻辑处理减少应用层与数据库之间的交互次数提升性能和可维护性。2. 变量体系MySQL 中的变量分为三大类各有不同的作用域和使用场景。2.1 系统变量由 MySQL 服务器维护分为全局变量和会话变量。类型前缀作用域示例全局变量global影响整个 MySQL 实例重启后丢失持久化需写入配置文件global.max_connections会话变量session仅影响当前客户端连接断开后丢失session.character_set_client-- 查看系统变量SHOWGLOBALVARIABLESLIKEmax_connections;SHOWSESSIONVARIABLESLIKE%timeout%;-- 修改全局变量临时生效重启后失效SETGLOBALmax_connections500;-- 修改会话变量SETSESSIONwait_timeout300;2.2 用户变量用户自定义的会话级变量以开头在当前连接内有效。-- 赋值方式一SET 赋值SETuser_name张三;SETtotal_count100;-- 赋值方式二SELECT INTO 赋值SELECTCOUNT(*)INTOtotal_countFROMuser_info;-- 使用SELECTuser_name,total_count;用户变量无需声明类型MySQL 会根据赋的值自动推断类型。2.3 局部变量在存储过程、函数或代码块内部声明的变量必须使用DECLARE声明并指定类型作用域仅限于声明它的BEGIN...END块。-- 声明局部变量必须在 BEGIN 块的最前面声明DECLAREvar_nameVARCHAR(50)DEFAULT默认值;DECLAREvar_countINTDEFAULT0;DECLAREvar_priceDECIMAL(10,2);局部变量必须在BEGIN...END块的第一条语句位置声明且不能加前缀。2.4 三类变量对比特性系统变量用户变量局部变量声明方式系统预置变量名直接使用DECLARE 变量名 类型作用域全局/会话当前连接存储过程/函数内部生命周期实例运行期间连接断开后丢失代码块执行完即销毁前缀无前缀3. 存储过程存储过程是一组预编译的 SQL 语句集合封装在数据库中通过CALL语句调用执行。3.1 创建与调用-- 语法结构DELIMITER$$CREATEPROCEDUREprocedure_name([IN|OUT|INOUT]param1 datatype,[IN|OUT|INOUT]param2 datatype)BEGIN-- SQL 语句END$$DELIMITER;参数类型模式说明IN输入参数调用时传入值过程内部可读取但修改不会影响调用方默认OUT输出参数过程内部赋值后调用方可以获取修改后的值INOUT输入输出参数调用时传入初始值过程内部可修改并返回示例根据用户 ID 查询订单总金额OUT 参数DELIMITER$$CREATEPROCEDUREget_user_order_total(INp_user_idINT,OUTp_total_amountDECIMAL(12,2))BEGINSELECTIFNULL(SUM(total_amount),0)INTOp_total_amountFROMorder_infoWHEREuser_idp_user_id;END$$DELIMITER;-- 调用CALLget_user_order_total(1001,total);SELECTtotalAS用户订单总额;3.2 存储过程管理-- 查看已有的存储过程SHOWPROCEDURESTATUSWHEREdbgmall;-- 查看存储过程定义SHOWCREATEPROCEDUREget_user_order_total;-- 删除存储过程DROPPROCEDUREIFEXISTSget_user_order_total;4. 函数MySQL 自定义函数与存储过程类似但函数必须有返回值且可以在 SQL 语句中直接调用。4.1 创建与调用DELIMITER$$CREATEFUNCTIONfunction_name(param1 datatype,param2 datatype)RETURNSreturn_datatypeBEGIN-- SQL 语句RETURNresult;END$$DELIMITER;示例根据订单状态码返回状态描述DELIMITER$$CREATEFUNCTIONget_order_status_desc(status_codeVARCHAR(10))RETURNSVARCHAR(50)BEGINDECLAREstatus_descVARCHAR(50);CASEstatus_codeWHEN1001THENSETstatus_desc未支付;WHEN1002THENSETstatus_desc已支付;WHEN1003THENSETstatus_desc已发货;WHEN1004THENSETstatus_desc已完成;WHEN1005THENSETstatus_desc已取消;ELSESETstatus_desc未知状态;ENDCASE;RETURNstatus_desc;END$$DELIMITER;-- 在 SQL 中直接调用SELECTid,user_id,total_amount,get_order_status_desc(order_status)ASstatus_descFROMorder_infoWHEREcreate_time2024-01-01;4.2 函数管理-- 查看函数SHOWFUNCTIONSTATUSWHEREdbgmall;-- 查看函数定义SHOWCREATEFUNCTIONget_order_status_desc;-- 删除函数DROPFUNCTIONIFEXISTSget_order_status_desc;4.3 存储过程 vs 函数维度存储过程函数调用方式CALL语句调用在 SQL 语句中直接调用返回值无返回值通过 OUT 参数输出必须有RETURN返回值参数类型支持 IN / OUT / INOUT仅支持输入参数相当于 IN嵌入 SQL不能嵌入 SQL 语句中可以嵌入 SELECT / UPDATE 等语句事务支持可以开启和管理事务不支持事务控制语句适用场景复杂的批量操作、多步骤逻辑单一计算、数据转换、值映射5. 流程控制存储过程和函数内部支持丰富的流程控制语句实现条件判断和循环处理。5.1 IF 条件判断-- 单分支IFconditionTHEN-- 语句ENDIF;-- 双分支IFconditionTHEN-- 语句ELSE-- 语句ENDIF;-- 多分支IFcondition1THEN-- 语句ELSEIFcondition2THEN-- 语句ELSE-- 语句ENDIF;示例根据订单金额划分等级DECLAREorder_levelVARCHAR(20);IForder_amount10000THENSETorder_level钻石订单;ELSEIForder_amount5000THENSETorder_level金牌订单;ELSEIForder_amount1000THENSETorder_level银牌订单;ELSESETorder_level普通订单;ENDIF;5.2 CASE 分支选择-- 语法一CASE 值匹配CASEexpressionWHENvalue1THEN-- 语句1WHENvalue2THEN-- 语句2ELSE-- 默认语句ENDCASE;-- 语法二CASE 条件判断CASEWHENcondition1THEN-- 语句1WHENcondition2THEN-- 语句2ELSE-- 默认语句ENDCASE;CASE 既可作为流程控制语句带END CASE也可作为表达式直接在 SELECT 中使用不写END CASE。5.3 循环语句MySQL 提供三种循环结构WHILE 循环先判断后执行-- 累加 1 到 nDECLAREiINTDEFAULT1;DECLAREtotal_sumINTDEFAULT0;WHILEi100DOSETtotal_sumtotal_sumi;SETii1;ENDWHILE;REPEAT 循环先执行后判断DECLAREiINTDEFAULT1;DECLAREtotal_sumINTDEFAULT0;REPEATSETtotal_sumtotal_sumi;SETii1;UNTIL i100ENDREPEAT;LOOP 循环无条件循环需手动跳出DECLAREiINTDEFAULT1;DECLAREtotal_sumINTDEFAULT0;loop_label:LOOPSETtotal_sumtotal_sumi;SETii1;IFi100THENLEAVEloop_label;-- 跳出循环ENDIF;ENDLOOPloop_label;LOOP 需要配合标签label和LEAVE跳出/ITERATE跳过本次进入下一次使用。5.4 三种循环对比循环判断时机最少执行次数适用场景WHILE先判断后执行0 次条件可能一开始就不满足REPEAT先执行后判断1 次至少要执行一次LOOP无内置判断1 次需要灵活控制退出条件6. 游标游标用于在存储过程或函数中逐行处理 SELECT 查询返回的结果集类似于编程语言中的迭代器。6.1 游标四步操作-- 1. 声明游标DECLAREcursor_nameCURSORFORselect_statement;-- 2. 打开游标OPENcursor_name;-- 3. 读取数据FETCH 一行到变量FETCHcursor_nameINTOvar1,var2,...;-- 4. 关闭游标CLOSEcursor_name;6.2 游标结合循环遍历单独使用游标只能逐行读取实际开发中需要配合循环和异常处理来遍历所有行DELIMITER$$CREATEPROCEDUREprocess_all_users()BEGIN-- 声明变量DECLAREv_user_idINT;DECLAREv_user_nameVARCHAR(100);DECLAREv_doneINTDEFAULT0;-- 结束标志-- 声明游标DECLAREcur_usersCURSORFORSELECTid,login_nameFROMuser_infoWHEREstatus1;-- 声明 NOT FOUND 处理程序当游标读取完毕时将 v_done 置为 1DECLARECONTINUEHANDLERFORNOTFOUNDSETv_done1;-- 打开游标OPENcur_users;-- 循环读取read_loop:LOOPFETCHcur_usersINTOv_user_id,v_user_name;-- 判断是否读取完毕IFv_done1THENLEAVEread_loop;ENDIF;-- 处理逻辑示例打印用户信息-- 实际场景中可以执行更新、插入等操作SELECTCONCAT(处理用户: ,v_user_id, - ,v_user_name)ASmsg;ENDLOOPread_loop;-- 关闭游标CLOSEcur_users;END$$DELIMITER;6.3 游标关键要点要点说明必须配合 HANDLER用DECLARE CONTINUE HANDLER FOR NOT FOUND捕获游标读取完毕的信号声明顺序变量声明 → 游标声明 → HANDLER 声明顺序不能乱资源释放使用完毕后必须CLOSE游标释放资源性能注意游标逐行处理数据量大时性能不如批量 SQL。优先用 SQL 批量操作替代7. 综合应用示例以下示例展示存储过程中变量、流程控制、游标的综合使用批量为用户生成消费统计报告。DELIMITER$$CREATEPROCEDUREgenerate_user_report(INp_start_dateDATE,INp_end_dateDATE,OUTp_success_countINT,OUTp_fail_countINT)BEGIN-- 局部变量DECLAREv_user_idINT;DECLAREv_user_nameVARCHAR(100);DECLAREv_total_amountDECIMAL(12,2);DECLAREv_order_countINT;DECLAREv_doneINTDEFAULT0;-- 计数器SETp_success_count0;SETp_fail_count0;-- 声明游标DECLAREcur_usersCURSORFORSELECTid,login_nameFROMuser_infoWHEREstatus1;DECLARECONTINUEHANDLERFORNOTFOUNDSETv_done1;OPENcur_users;user_loop:LOOPFETCHcur_usersINTOv_user_id,v_user_name;IFv_done1THENLEAVEuser_loop;ENDIF;-- 查询用户的订单统计SELECTIFNULL(COUNT(*),0),IFNULL(SUM(total_amount),0)INTOv_order_count,v_total_amountFROMorder_infoWHEREuser_idv_user_idANDcreate_timeBETWEENp_start_dateANDp_end_dateANDorder_statusIN(1002,1003,1004);-- 根据消费金额分级IFv_total_amount10000THEN-- 钻石用户写入 VIP 报告表INSERTINTOuser_report(user_id,user_name,order_count,total_amount,user_level,report_date)VALUES(v_user_id,v_user_name,v_order_count,v_total_amount,钻石,CURDATE());SETp_success_countp_success_count1;ELSEIFv_total_amount1000THEN-- 银牌用户INSERTINTOuser_report(user_id,user_name,order_count,total_amount,user_level,report_date)VALUES(v_user_id,v_user_name,v_order_count,v_total_amount,银牌,CURDATE());SETp_success_countp_success_count1;ELSE-- 消费不足 1000 的用户跳过SETp_fail_countp_fail_count1;ENDIF;ENDLOOPuser_loop;CLOSEcur_users;END$$DELIMITER;-- 调用生成 2024 年全年报告CALLgenerate_user_report(2024-01-01,2024-12-31,success,fail);SELECTsuccessAS成功处理用户数,failAS跳过用户数;8. 注意事项要点说明DELIMITER创建存储过程/函数前必须修改分隔符如$$避免内部;被误解析权限控制创建需要CREATE ROUTINE权限执行需要EXECUTE权限调试方法可通过临时表或日志表记录中间变量值来辅助调试性能游标逐行处理数据量大时性能差优先用 SQL 批量操作INSERT INTO … SELECT替代嵌套层数MySQL 存储过程支持嵌套调用但建议控制层级避免逻辑过于复杂事务存储过程内部可以使用START TRANSACTION/COMMIT/ROLLBACK管理事务字符集创建时注意数据库字符集设置避免中文乱码
