尧图网站建设 
聊到 MySQL 存储过程我身边一直有两类声音一类觉得“都什么年代了还写存储过程业务逻辑放应用层才是正道”另一类是面试前临时抱佛脚把 CREATE PROCEDURE 背得滚瓜烂熟一到实际项目还是不知道怎么下手。这两类情况我都遇到过不少但说实话存储过程这门技术既没有“过气”也不该被无脑捧杀它被误解的原因只有一个——很多人没搞明白它到底适合解决什么问题。这篇内容围绕 MySQL 存储过程展开从“它解决什么场景问题”说起再把完整语法、变量与流程控制、游标与临时表、异常处理与事务控制、线上排查与维护这几个模块逐一拆解。目标是让完全没写过存储过程的新手看完能自己动手建一个也能让已经在用的开发者补上平时容易忽略的坑。1. 为什么需要存储过程先弄清楚它解决的是什么问题1.1 一次慢接口事故暴露出的网络往返开销前几年我接过一个积分清算系统的优化任务业务逻辑不算复杂每天晚上要给一批用户计算积分每个用户的积分要结合订单表、消费流水、当前等级系数才能算出来最后再写入积分明细表。刚开始系统用的是后端代码循环逐条执行 SQL一次处理五千个用户接口直接超时。我当时用最笨的办法统计了一下网络请求量每个用户至少涉及七条 SQL五千个用户就是三万五千次数据库往返。就算每次只算 0.1 秒的额外开销累计起来也是将近一个小时。后来我把整套计算逻辑封装成一个存储过程后端一次 CALL 进去数据库内部自己循环、自己算、自己写整批处理从接近一小时压缩到了三分钟以内。这笔账算完存储过程存在的核心价值就非常清楚了它把原本要跨网络的多次交互压缩成数据库内部的一次执行。打个比方去超市买东西你一件一件结账和推着整购物车到收银台统一结算效率完全不一样。存储过程就是那个“统一结算”的通道尤其适合那些单次数据处理量大、计算链路长、需要反复读写中间结果的场景。1.2 存储过程真正擅长的四类场景结合这些年做过的项目我习惯把适合用存储过程的场景归纳为四类定时批处理每日订单汇总、月末结账、积分清算、会话归档。这类任务往往是凌晨跑不需要在线交互数据库内部执行最方便。复杂报表计算多层子查询、跨多表累计、行转列、汇总后再汇总。报表逻辑通常会同时关联大量数据用一条存储过程可以按步骤逐步计算可读性和维护性都比嵌套二十层的单条 SQL 强。数据迁移与清洗从旧库往新库导数据字段格式要变、数据要滤重、关联字典表做翻译。写一个存储过程先建临时表再分批搬运出错了还能回滚。跨系统共用的数据库逻辑比如统一的分润计算规则、统一的价格策略。多个业务系统如果各写一套迟早会算岔把规则收敛到数据库端的存储过程里约束力更强。为了让你对“适合”和“不适合”有更直观的判断我给一张简单的对照表场景类型是否推荐存储过程原因夜间批处理 / 数据归档非常推荐减少网络往返数据库内部执行效率高复杂报表统计推荐逻辑集中分步计算好排查高并发在线小事务下单、扣库存不推荐数据库计算压力上去了不好水平扩展业务规则频繁变动的系统不推荐存储过程变更要连库操作发布流程重计算密集型但数据量小的逻辑视情况网络开销可接受时应用层可替代1.3 不适合用存储过程的情况别把“手段”当“原则”上面提到了高并发在线小事务这里多解释几句。很多人对存储过程的“嫌恶”大多来自这类场景。用户下单、扣库存、支付回调这些操作特点是请求量大、单次逻辑短、要求毫秒级响应。如果把这类逻辑塞进存储过程数据库的 CPU 会成为瓶颈。数据库的扩展方式主要是垂直扩容和读写分离不像应用服务器那样可以随意横向加节点。你可以为十个应用实例轻松扩容但很难让一套 MySQL 集群扛住所有计算压力。另外存储过程在代码版本管理、单元测试、灰度发布这些方面都不如应用层代码方便。业务流程频繁调整的时候改存储过程意味着要连上生产库执行 DDL这种操作的审批链路和风险都比改应用代码高得多。所以我的结论很明确存储过程不是用来替代应用代码的它是用来承接“批量数据加工”“复杂逻辑收口”“跨系统规则统一”这类特定任务的。把它的定位想清楚你才不会在错误的场景里一边用一边骂。2. 创建第一个存储过程从分隔符到完整语法拆解2.1 环境准备版本、客户端和基础习惯我平时的开发环境以 MySQL 5.7 和 8.0 为主存储过程的核心语法两者基本一致8.0 也继续保持了对旧语法的兼容。如果你用的客户端是 Navicat 或者 DataGrip建存储过程通常有图形化向导但我还是建议先学会在 mysql 命令行客户端里手写因为命令行能把语法报错原原本本地显示出来图形化工具往往会吞掉一部分细节。另外两个基础习惯值得说一句。第一存储过程内部用的字符集建议和表保持一致推荐 utf8mb4免得处理中文时出现乱码或比较错误。第二生产库上创建存储过程之前先查一下当前账号有没有 CREATE ROUTINE 权限否则会直接报权限不足。2.2 DELIMITER 的作用为什么写存储过程前要先改分隔符新手第一次写存储过程最常见的报错就是“怎么语法都对一执行就乱套”十个里有八个是没搞懂 DELIMITER。mysql 客户端默认用分号作为一条 SQL 的结束符你输入一行以分号结尾的语句客户端就立刻发给服务器执行。但存储过程内部大量使用分号分隔语句如果不做处理客户端会在存储过程还没写完时就把片段提交上去服务器当然报错。解决办法是把客户端的结束符临时改成一个不太可能用到的符号比如两个美元符 $$或者双斜杠 //。下面是最标准的创建流程DELIMITER $$ CREATE PROCEDURE sp_hello_world() BEGIN SELECT hello world; END$$ DELIMITER ;注意最后那行DELIMITER ;它负责把结束符恢复成默认分号。如果漏掉后面所有 SQL 都会用新增的这个符号作为结束符控制台行为会变得非常诡异。我自己就因为这个漏掉恢复语句导致后续十条 SQL 全部“无法结束”卡了十分钟才反应过来。2.3 三种参数模式 IN、OUT、INOUT 的用法与区别存储过程的参数一共有三种模式这是我面试新人时喜欢问的一个点参数模式含义使用场景IN传入参数过程内只能读取不能回传大多数业务参数OUT输出参数过程内赋值调用方读取返回统计结果、状态值INOUT既可传入又可回传需要基于传入值修改后返回看一个实际例子。假设要按部门统计人数部门 ID 通过 IN 传入统计结果通过 OUT 传出DELIMITER $$ CREATE PROCEDURE sp_dept_user_count( IN p_dept_id INT, OUT p_total INT ) BEGIN SELECT COUNT(*) INTO p_total FROM user WHERE dept_id p_dept_id; END$$ DELIMITER ;调用方式如下CALL sp_dept_user_count(10, cnt); SELECT cnt;这里我故意把参数名写成了p_dept_id而不是dept_id是有原因的。如果参数名和字段名同名写成SELECT COUNT(*) INTO total FROM user WHERE dept_id dept_id;MySQL 会把dept_id dept_id解析成字段和字段自己比较而不是字段和参数比较结果就是整个查询失去过滤条件。这种 bug 不会报错只会让你查出莫名其妙的总数排查起来非常耗时间。我的习惯是参数一律加p_前缀字段名保持原样从源头避开这类问题。2.4 调用、查看与删除日常管理命令存储过程创建之后日常管理命令也是必须要掌握的-- 调用存储过程 CALL sp_dept_user_count(10, cnt); -- 查看某个存储过程的定义 SHOW CREATE PROCEDURE sp_dept_user_count; -- 查看当前库里所有存储过程 SHOW PROCEDURE STATUS WHERE db test_db; -- 删除存储过程 DROP PROCEDURE IF EXISTS sp_dept_user_count;SHOW PROCEDURE STATUS会显示存储过程的创建时间、修改时间、字符集等信息在排查线上问题时很有用。比如你想知道某个存储过程是哪次变更引入的这个命令能直接给出created和last_altered两个时间比翻代码仓库快多了。3. 变量、流程控制与动态SQL存储过程的“编程感”3.1 三级变量体系系统变量、用户变量、局部变量写存储过程一定会接触变量MySQL 里的变量可以分成三个层级很多初学者经常把它们混在一起用。系统变量以开头反映的是数据库服务器的运行状态和配置比如sql_mode、character_set_client。这类变量普通业务逻辑里很少直接操作主要是排查问题时查看。用户变量以开头作用域是当前会话。比如前面调用存储过程时传入的cnt就是用户变量。用户变量不用提前声明直接赋值就能用。局部变量是存储过程内部用DECLARE声明的变量作用域限定在所在的 BEGIN...END 块里。局部变量是编写存储过程时最常用的变量类型。看个对比示例SET max_id 1000; -- 用户变量当前会话内有效 DELIMITER $$ CREATE PROCEDURE sp_var_demo() BEGIN DECLARE local_num INT DEFAULT 0; -- 局部变量仅在存储过程内有效 SET local_num max_id 1; SELECT local_num; END$$ DELIMITER ;需要特别提醒的是DECLARE语句必须放在 BEGIN...END 块的最前面不能在中间任意位置声明。这一点和 Java、C 这些现代语言差别很大我刚开始写的时候习惯性把声明放在使用处结果连续报语法错误。3.2 IF/CASE 与三种循环的适用场景存储过程的流程控制和大多数编程语言类似IF、CASE、循环都是必备品。IF 条件判断的完整写法是IF score 90 THEN SET level A; ELSEIF score 60 THEN SET level B; ELSE SET level C; END IF;CASE 更适合多个固定值匹配的场景CASE level WHEN A THEN SET bonus 1000; WHEN B THEN SET bonus 500; ELSE SET bonus 0; END CASE;循环有三种形式很多人不清楚它们的区别其实只需要记口诀LOOP无限循环必须在内部通过LEAVE手动退出适合退出条件在循环体中间的场景。WHILE先判断后执行条件是假的就不进入循环体。REPEAT先执行后判断至少执行一次。举个 WHILE 循环批量插入日志的例子DELIMITER $$ CREATE PROCEDURE sp_batch_insert_log(IN p_count INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i p_count DO INSERT INTO t_log(create_time, remark) VALUES (NOW(), CONCAT(batch-, i)); SET i i 1; END WHILE; END$$ DELIMITER ;如果用 LOOP 实现同样逻辑需要这样写DELIMITER $$ CREATE PROCEDURE sp_batch_insert_log(IN p_count INT) BEGIN DECLARE i INT DEFAULT 0; insert_loop: LOOP IF i p_count THEN LEAVE insert_loop; END IF; INSERT INTO t_log(create_time, remark) VALUES (NOW(), CONCAT(batch-, i)); SET i i 1; END LOOP insert_loop; END$$ DELIMITER ;实际使用中WHILE是最常用的代码可读性也最好。REPEAT通常用在“先做一次再判断要不要继续”的场景比如首次生成报表后检查数据量是否达标。3.3 动态SQL当表名和条件来自参数时怎么处理静态 SQL 写多了你会发现一个尴尬情况有些场景下表名或者排序字段是由参数决定的比如管理后台让你“按任意字段排序”或者归档时表名带日期后缀。这时候就需要动态 SQL。MySQL 里动态 SQL 的标准流程是三步PREPARE 准备、EXECUTE 执行、DEALLOCATE PREPARE 释放。看一个例子按传入表名查询前十条数据DELIMITER $$ CREATE PROCEDURE sp_query_dynamic(IN p_table_name VARCHAR(64)) BEGIN SET sql CONCAT(SELECT * FROM , p_table_name, LIMIT 10); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ;这里有几个关键点必须说清楚第一PREPARE 的占位符?只能用于值不能用于表名和字段名。也就是说PREPARE stmt FROM SELECT * FROM ? LIMIT 10是行不通的表名只能靠字符串拼接。第二字符串拼接意味着必须做安全校验。这种存储过程如果开放给业务方调用等于把数据库查询能力暴露出去一旦表名参数里被塞进恶意内容后果非常严重。我一般会做一层白名单校验DELIMITER $$ CREATE PROCEDURE sp_query_dynamic_safe(IN p_table_name VARCHAR(64)) BEGIN IF p_table_name NOT IN (user, order, product) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT table not allowed; END IF; SET sql CONCAT(SELECT * FROM , p_table_name, LIMIT 10); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ;第三动态 SQL 的执行计划无法提前缓存。如果某个存储过程是高频调用而且动态拼 SQL性能可能不如静态 SQL因为查询优化器每次都要重新解析。这一点尤其要注意别把高频核心接口做成动态 SQL。4. 游标与临时表组合正确处理逐行数据4.1 游标标准写法与最常见的翻车点游标是存储过程里最容易被写崩的部分。它的作用是一行一行地从查询结果集里取数据类似于编程语言里的迭代器。标准使用流程只有四步声明、打开、取数、关闭。DELIMITER $$ CREATE PROCEDURE sp_calc_score() BEGIN DECLARE v_user_id INT; DECLARE v_done INT DEFAULT 0; DECLARE cur_user CURSOR FOR SELECT id FROM user WHERE status 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur_user; user_loop: LOOP FETCH cur_user INTO v_user_id; IF v_done THEN LEAVE user_loop; END IF; UPDATE user_score SET score score 10 WHERE user_id v_user_id; END LOOP; CLOSE cur_user; END$$ DELIMITER ;这段代码看起来不长但翻车点密集第一个坑NOT FOUND 处理器的位置。DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1;必须声明在游标声明之后、OPEN 之前位置不对处理器不生效一取完数据就会死循环。第二个坑FETCH 之后必须立刻判断退出标志。游标取完最后一条记录后再次 FETCH 才会触发 NOT FOUND。如果你把判断放在循环体末尾就会多处理一次“幽灵数据”或者退出时机错误。第三个坑关闭游标。游标一旦 OPEN就和连接资源绑定必须成对出现 CLOSE。长期不关连接资源会累积消耗最终影响数据库连接池。第四个坑游标中做 DML 操作会长时间持有行锁。如果在游标内逐行 UPDATE 大量数据每行之间的事务持续时间很长容易造成锁等待和死锁。能用一条 UPDATE 完成的逻辑永远不要用游标。4.2 用临时表承接结果分组TopN的完整案例游标配合临时表可以解决一类经典问题MySQL 5.7 及更早版本里没有窗口函数想按部门取工资前三名员工得费不少功夫。存储过程加临时表是那个时代比较通用的解法。需求描述有一张员工表 employee字段为 id、dept_id、salary需要输出每个部门工资前三高的员工。DELIMITER $$ CREATE PROCEDURE sp_dept_top3() BEGIN DECLARE v_dept_id INT; DECLARE v_done INT DEFAULT 0; DECLARE cur_dept CURSOR FOR SELECT DISTINCT dept_id FROM employee; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; DROP TEMPORARY TABLE IF EXISTS tmp_top3; CREATE TEMPORARY TABLE tmp_top3 ( dept_id INT, emp_id INT, salary DECIMAL(10,2) ); OPEN cur_dept; dept_loop: LOOP FETCH cur_dept INTO v_dept_id; IF v_done THEN LEAVE dept_loop; END IF; INSERT INTO tmp_top3 SELECT dept_id, id, salary FROM employee WHERE dept_id v_dept_id ORDER BY salary DESC LIMIT 3; END LOOP; CLOSE cur_dept; SELECT * FROM tmp_top3 ORDER BY dept_id, salary DESC; END$$ DELIMITER ;核心思路是先用游标遍历所有部门每个部门内部单独执行一次排序取前三再把结果累积写入临时表最后统一查询临时表。这里 DROP TEMPORARY TABLE 用的临时表只对当前会话可见存储过程执行完连接关闭后临时表自动消失不需要手动清理。如果你用的是 MySQL 8.0同样需求可以一行窗口函数解决SELECT dept_id, emp_id, salary FROM ( SELECT dept_id, emp_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 3;但生产环境里依然有大量实例停留在 5.7而且有些分库分表中间件不支持窗口函数下推所以存储过程加临时表的写法依然有存在价值。至少说明白这两种方案的取舍面试聊到也不虚。5. 异常处理与事务控制别让中途报错留下半截数据5.1 为什么批处理存储过程必须做事务处理写批处理类存储过程最怕的就是“跑到一半报错”。假设一个存储过程要批量更新一万个会员的等级第八千条数据因为某个脏数据触发了一个约束错误。如果整个过程没有事务包裹前七千九百九十九条已经提交生效数据就处于一种“一半新一半旧”的状态而且很不好判断哪些是更新过的恢复成本极高。解决办法很简单在存储过程内部用START TRANSACTION开启事务正常情况下COMMIT提交异常情况下ROLLBACK回滚。这样要么全部成功要么全部失败不会留下半截数据。看一个完整的转账存储过程示例DELIMITER $$ CREATE PROCEDURE sp_transfer_money( IN p_from_account INT, IN p_to_account INT, IN p_amount DECIMAL(10,2) ) BEGIN -- 声明异常处理器遇到任何 SQL 异常回滚事务并重新抛出 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; UPDATE account SET balance balance - p_amount WHERE id p_from_account; IF ROW_COUNT() 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 转出账户不存在; END IF; UPDATE account SET balance balance p_amount WHERE id p_to_account; IF ROW_COUNT() 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 转入账户不存在; END IF; COMMIT; END$$ DELIMITER ;注意两点。第一这里检查ROW_COUNT()只能判断更新了几行没法判断余额是否够扣。真正的余额校验应该在 UPDATE 条件里加上AND balance p_amount然后通过 ROW_COUNT 来判断是否满足UPDATE account SET balance balance - p_amount WHERE id p_from_account AND balance p_amount; IF ROW_COUNT() 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 账户不存在或余额不足; END IF;第二RESIGNAL的作用是把原始异常重新抛给调用方让应用层能感知到失败避免存储过程内部悄悄吞掉错误。5.2 条件处理器CONTINUE 与 EXIT 的取舍上一节的示例里用了EXIT HANDLER意思是“遇到异常就退出当前存储过程”。MySQL 还支持CONTINUE HANDLER意思是“遇到异常后继续执行”。两者怎么选我一般是这么判断的如果是破坏性操作比如批量修改、转账、删除必须用EXIT快速回滚退出别留后患。如果是非关键环节比如“日志表写入失败不应该影响主流程”可以用CONTINUE在处理器里记录一个状态标记主流程继续往下走。CONTINUE 处理器的一个典型例子DELIMITER $$ CREATE PROCEDURE sp_main_process() BEGIN DECLARE v_log_failed INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_log_failed 1; -- 主处理逻辑 INSERT INTO biz_data SELECT ...; -- 写日志失败也不影响主流程 INSERT INTO process_log(ts, status) VALUES (NOW(), done); END$$ DELIMITER ;这里有个细节CONTINUE 处理器捕获异常后如果依赖的上下文比如某个临时表已经被破坏后续 SQL 可能继续报错。所以 CONTINUE 一定要用在“捕获后不会再依赖被破坏资源”的场景否则副作用比异常本身还大。5.3 兜底方案SIGNAL 抛出业务错误日志表记录现场存储过程里除了捕获数据库异常还需要主动检查业务条件不满足就抛出业务错误。SIGNAL就是干这个的IF v_cnt 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 没有找到任何符合条件的数据; END IF;SQLSTATE 可以简单理解为错误码45000是用户自定义异常的通用状态码不会和 MySQL 内置错误码冲突。另外一个建议是给批处理类存储过程配套一张日志表把每次执行的开始时间、结束时间、影响行数、错误信息记下来。这一步在线上维护时收益巨大出了问题能直接翻日志表定位是哪一批次执行失败、卡在哪个阶段。CREATE TABLE proc_run_log ( id INT AUTO_INCREMENT PRIMARY KEY, proc_name VARCHAR(64) NOT NULL, start_time DATETIME, end_time DATETIME, affected_rows INT, error_msg TEXT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;我自己的习惯是每个批处理存储过程结尾都写一条执行日志。刚开始觉得麻烦直到有一次夜间批处理在凌晨三点静默失败靠这张日志表十分钟定位到了原因之后我就把这条规矩写进了团队的开发规范里。6. 线上排查与日常维护教程里不讲的几个关键细节6.1 权限、DEFINER 与安全上下文存储过程创建好以后调用方账号需要EXECUTE权限GRANT EXECUTE ON PROCEDURE test_db.sp_dept_user_count TO app_user%;如果没有 EXECUTE 权限调用会直接报EXECUTE command denied。这个错误定位起来很快但有一个更隐蔽的问题——DEFINER 安全上下文。存储过程默认以 DEFINER定义者的身份去访问表也就是说如果存储过程定义者的账号没有访问某些表的权限即使调用方有权限存储过程照样会报“table access denied”。反过来也有可能调用方账号本身没有表权限但因为存储过程是定义者身份执行的反而能查到数据。MySQL 支持修改这个行为创建时可以指定安全模式CREATE DEFINER rootlocalhost PROCEDURE sp_demo() SQL SECURITY DEFINER BEGIN ... END; CREATE PROCEDURE sp_demo2() SQL SECURITY INVOKER BEGIN ... END;SQL SECURITY DEFINER是默认行为适合那种“不想把底层表权限开放给每个业务账号”的场景SQL SECURITY INVOKER则要求调用方自己具有表权限权限模型更严格也更安全。生产环境我建议优先用 INVOKER避免因为 DEFINER 权限过大导致数据越权访问。6.2 存储过程的性能分析思路存储过程跑得慢排查思路和普通慢 SQL 不太一样。普通 SQL 可以直接 EXPLAIN但存储过程是多条 SQL 的集合没法整体 EXPLAIN必须拆开看。我的排查顺序一般是先跑整体记录总耗时。用SELECT NOW();配合日志表看执行时间。把存储过程内部每条 SQL 单独拿出来 EXPLAIN。重点看有没有全表扫描、有没有索引失效、有没有 filesort。优先怀疑游标逐行循环。前面提过游标每 FETCH 一行如果内部还有 UPDATE 或 SELECT就是行级逐条操作数据量一大必然慢。优化方向是把逐行操作改成集合操作比如临时表 JOIN 一次更新。查锁等待和事务状态。如果存储过程里开了事务但长时间不提交其他会话会被阻塞。用下面这条 SQL 可以看当前事务SELECT * FROM information_schema.innodb_trx\G用元数据表确认变更时间。前面提到过SHOW PROCEDURE STATUS如果怀疑线上跑的存储过程和代码仓库里不一致可以对比last_altered时间。实际上大部分“存储过程慢”的问题都不是语法问题而是写法问题——把应该在集合层面完成的操作硬生生写成了逐行处理。优化哲学就一句话能一条 UPDATE 解决绝不用循环。6.3 版本差异、字符集与数据截断的坑最后汇总几个线上实际踩过的坑。binlog 与函数创建限制。MySQL 开启 binlog 后创建存储过程可能报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration...这是因为 MySQL 担心存储过程产生不确定结果导致基于 binlog 的主从复制数据不一致。解决办法是在存储过程声明里加上DETERMINISTIC等特征声明或者开启log_bin_trust_function_creators ON。CREATE PROCEDURE sp_demo() DETERMINISTIC BEGIN ... END;字符集不一致导致乱码和比较错误。存储过程的字符集和表的字符集不一致时可能出现两类问题中文乱码以及看似相同的字符串在 WHERE 比较时匹配不上。排查方法是先看如下三项SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE character_set_database; SHOW CREATE PROCEDURE sp_demo;变量长度截断。存储过程里 DECLARE 一个 VARCHAR(20)但查询结果实际返回了三十个字符直接赋值就会报Data too long。这个问题好定位但容易犯的隐蔽版本是 DECLARE 变量太短导致数据被静默截断不会报错但结果不对。我的建议是所有承接查询结果的变量长度按表字段定义来宁可长一点不要短一点。游标内 UPDATE 引发的死锁。两个并发存储过程如果以不同顺序更新同一条数据可能形成死锁。MySQL 检测到死锁后会回滚其中一方事务并返回类似Deadlock found的报错。这种问题通常要靠统一更新顺序来规避比如所有更新都按 ID 升序执行。诚实的说我现在做日常开发也很少把核心业务逻辑塞进存储过程但凡是定时批处理、报表汇总、数据迁移这类任务我反而会优先考虑存储过程。理由就一个它跑在数据库内部不受应用发布节奏限制出问题直接连库排查日志表一翻就知道卡在哪一步重跑和回滚都方便。最后分享一个我个人的操作习惯每个存储过程开头写清楚用途和参数关键步骤加上注释末尾把执行结果写进日志表。这个习惯不复杂但每次线上遇到和批处理相关的问题这些注释和日志都会成为第一手排查线索。如果你正准备把第一个存储过程写到生产环境建议从“给现有表做个统计汇总”这种小功能开始把它跑熟、看透再逐步处理更复杂的逻辑。 MySQL存储过程实战指南：批处理、游标、事务与动态SQL全解析
聊到 MySQL 存储过程我身边一直有两类声音一类觉得“都什么年代了还写存储过程业务逻辑放应用层才是正道”另一类是面试前临时抱佛脚把 CREATE PROCEDURE 背得滚瓜烂熟一到实际项目还是不知道怎么下手。这两类情况我都遇到过不少但说实话存储过程这门技术既没有“过气”也不该被无脑捧杀它被误解的原因只有一个——很多人没搞明白它到底适合解决什么问题。这篇内容围绕 MySQL 存储过程展开从“它解决什么场景问题”说起再把完整语法、变量与流程控制、游标与临时表、异常处理与事务控制、线上排查与维护这几个模块逐一拆解。目标是让完全没写过存储过程的新手看完能自己动手建一个也能让已经在用的开发者补上平时容易忽略的坑。1. 为什么需要存储过程先弄清楚它解决的是什么问题1.1 一次慢接口事故暴露出的网络往返开销前几年我接过一个积分清算系统的优化任务业务逻辑不算复杂每天晚上要给一批用户计算积分每个用户的积分要结合订单表、消费流水、当前等级系数才能算出来最后再写入积分明细表。刚开始系统用的是后端代码循环逐条执行 SQL一次处理五千个用户接口直接超时。我当时用最笨的办法统计了一下网络请求量每个用户至少涉及七条 SQL五千个用户就是三万五千次数据库往返。就算每次只算 0.1 秒的额外开销累计起来也是将近一个小时。后来我把整套计算逻辑封装成一个存储过程后端一次 CALL 进去数据库内部自己循环、自己算、自己写整批处理从接近一小时压缩到了三分钟以内。这笔账算完存储过程存在的核心价值就非常清楚了它把原本要跨网络的多次交互压缩成数据库内部的一次执行。打个比方去超市买东西你一件一件结账和推着整购物车到收银台统一结算效率完全不一样。存储过程就是那个“统一结算”的通道尤其适合那些单次数据处理量大、计算链路长、需要反复读写中间结果的场景。1.2 存储过程真正擅长的四类场景结合这些年做过的项目我习惯把适合用存储过程的场景归纳为四类定时批处理每日订单汇总、月末结账、积分清算、会话归档。这类任务往往是凌晨跑不需要在线交互数据库内部执行最方便。复杂报表计算多层子查询、跨多表累计、行转列、汇总后再汇总。报表逻辑通常会同时关联大量数据用一条存储过程可以按步骤逐步计算可读性和维护性都比嵌套二十层的单条 SQL 强。数据迁移与清洗从旧库往新库导数据字段格式要变、数据要滤重、关联字典表做翻译。写一个存储过程先建临时表再分批搬运出错了还能回滚。跨系统共用的数据库逻辑比如统一的分润计算规则、统一的价格策略。多个业务系统如果各写一套迟早会算岔把规则收敛到数据库端的存储过程里约束力更强。为了让你对“适合”和“不适合”有更直观的判断我给一张简单的对照表场景类型是否推荐存储过程原因夜间批处理 / 数据归档非常推荐减少网络往返数据库内部执行效率高复杂报表统计推荐逻辑集中分步计算好排查高并发在线小事务下单、扣库存不推荐数据库计算压力上去了不好水平扩展业务规则频繁变动的系统不推荐存储过程变更要连库操作发布流程重计算密集型但数据量小的逻辑视情况网络开销可接受时应用层可替代1.3 不适合用存储过程的情况别把“手段”当“原则”上面提到了高并发在线小事务这里多解释几句。很多人对存储过程的“嫌恶”大多来自这类场景。用户下单、扣库存、支付回调这些操作特点是请求量大、单次逻辑短、要求毫秒级响应。如果把这类逻辑塞进存储过程数据库的 CPU 会成为瓶颈。数据库的扩展方式主要是垂直扩容和读写分离不像应用服务器那样可以随意横向加节点。你可以为十个应用实例轻松扩容但很难让一套 MySQL 集群扛住所有计算压力。另外存储过程在代码版本管理、单元测试、灰度发布这些方面都不如应用层代码方便。业务流程频繁调整的时候改存储过程意味着要连上生产库执行 DDL这种操作的审批链路和风险都比改应用代码高得多。所以我的结论很明确存储过程不是用来替代应用代码的它是用来承接“批量数据加工”“复杂逻辑收口”“跨系统规则统一”这类特定任务的。把它的定位想清楚你才不会在错误的场景里一边用一边骂。2. 创建第一个存储过程从分隔符到完整语法拆解2.1 环境准备版本、客户端和基础习惯我平时的开发环境以 MySQL 5.7 和 8.0 为主存储过程的核心语法两者基本一致8.0 也继续保持了对旧语法的兼容。如果你用的客户端是 Navicat 或者 DataGrip建存储过程通常有图形化向导但我还是建议先学会在 mysql 命令行客户端里手写因为命令行能把语法报错原原本本地显示出来图形化工具往往会吞掉一部分细节。另外两个基础习惯值得说一句。第一存储过程内部用的字符集建议和表保持一致推荐 utf8mb4免得处理中文时出现乱码或比较错误。第二生产库上创建存储过程之前先查一下当前账号有没有 CREATE ROUTINE 权限否则会直接报权限不足。2.2 DELIMITER 的作用为什么写存储过程前要先改分隔符新手第一次写存储过程最常见的报错就是“怎么语法都对一执行就乱套”十个里有八个是没搞懂 DELIMITER。mysql 客户端默认用分号作为一条 SQL 的结束符你输入一行以分号结尾的语句客户端就立刻发给服务器执行。但存储过程内部大量使用分号分隔语句如果不做处理客户端会在存储过程还没写完时就把片段提交上去服务器当然报错。解决办法是把客户端的结束符临时改成一个不太可能用到的符号比如两个美元符 $$或者双斜杠 //。下面是最标准的创建流程DELIMITER $$ CREATE PROCEDURE sp_hello_world() BEGIN SELECT hello world; END$$ DELIMITER ;注意最后那行DELIMITER ;它负责把结束符恢复成默认分号。如果漏掉后面所有 SQL 都会用新增的这个符号作为结束符控制台行为会变得非常诡异。我自己就因为这个漏掉恢复语句导致后续十条 SQL 全部“无法结束”卡了十分钟才反应过来。2.3 三种参数模式 IN、OUT、INOUT 的用法与区别存储过程的参数一共有三种模式这是我面试新人时喜欢问的一个点参数模式含义使用场景IN传入参数过程内只能读取不能回传大多数业务参数OUT输出参数过程内赋值调用方读取返回统计结果、状态值INOUT既可传入又可回传需要基于传入值修改后返回看一个实际例子。假设要按部门统计人数部门 ID 通过 IN 传入统计结果通过 OUT 传出DELIMITER $$ CREATE PROCEDURE sp_dept_user_count( IN p_dept_id INT, OUT p_total INT ) BEGIN SELECT COUNT(*) INTO p_total FROM user WHERE dept_id p_dept_id; END$$ DELIMITER ;调用方式如下CALL sp_dept_user_count(10, cnt); SELECT cnt;这里我故意把参数名写成了p_dept_id而不是dept_id是有原因的。如果参数名和字段名同名写成SELECT COUNT(*) INTO total FROM user WHERE dept_id dept_id;MySQL 会把dept_id dept_id解析成字段和字段自己比较而不是字段和参数比较结果就是整个查询失去过滤条件。这种 bug 不会报错只会让你查出莫名其妙的总数排查起来非常耗时间。我的习惯是参数一律加p_前缀字段名保持原样从源头避开这类问题。2.4 调用、查看与删除日常管理命令存储过程创建之后日常管理命令也是必须要掌握的-- 调用存储过程 CALL sp_dept_user_count(10, cnt); -- 查看某个存储过程的定义 SHOW CREATE PROCEDURE sp_dept_user_count; -- 查看当前库里所有存储过程 SHOW PROCEDURE STATUS WHERE db test_db; -- 删除存储过程 DROP PROCEDURE IF EXISTS sp_dept_user_count;SHOW PROCEDURE STATUS会显示存储过程的创建时间、修改时间、字符集等信息在排查线上问题时很有用。比如你想知道某个存储过程是哪次变更引入的这个命令能直接给出created和last_altered两个时间比翻代码仓库快多了。3. 变量、流程控制与动态SQL存储过程的“编程感”3.1 三级变量体系系统变量、用户变量、局部变量写存储过程一定会接触变量MySQL 里的变量可以分成三个层级很多初学者经常把它们混在一起用。系统变量以开头反映的是数据库服务器的运行状态和配置比如sql_mode、character_set_client。这类变量普通业务逻辑里很少直接操作主要是排查问题时查看。用户变量以开头作用域是当前会话。比如前面调用存储过程时传入的cnt就是用户变量。用户变量不用提前声明直接赋值就能用。局部变量是存储过程内部用DECLARE声明的变量作用域限定在所在的 BEGIN...END 块里。局部变量是编写存储过程时最常用的变量类型。看个对比示例SET max_id 1000; -- 用户变量当前会话内有效 DELIMITER $$ CREATE PROCEDURE sp_var_demo() BEGIN DECLARE local_num INT DEFAULT 0; -- 局部变量仅在存储过程内有效 SET local_num max_id 1; SELECT local_num; END$$ DELIMITER ;需要特别提醒的是DECLARE语句必须放在 BEGIN...END 块的最前面不能在中间任意位置声明。这一点和 Java、C 这些现代语言差别很大我刚开始写的时候习惯性把声明放在使用处结果连续报语法错误。3.2 IF/CASE 与三种循环的适用场景存储过程的流程控制和大多数编程语言类似IF、CASE、循环都是必备品。IF 条件判断的完整写法是IF score 90 THEN SET level A; ELSEIF score 60 THEN SET level B; ELSE SET level C; END IF;CASE 更适合多个固定值匹配的场景CASE level WHEN A THEN SET bonus 1000; WHEN B THEN SET bonus 500; ELSE SET bonus 0; END CASE;循环有三种形式很多人不清楚它们的区别其实只需要记口诀LOOP无限循环必须在内部通过LEAVE手动退出适合退出条件在循环体中间的场景。WHILE先判断后执行条件是假的就不进入循环体。REPEAT先执行后判断至少执行一次。举个 WHILE 循环批量插入日志的例子DELIMITER $$ CREATE PROCEDURE sp_batch_insert_log(IN p_count INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i p_count DO INSERT INTO t_log(create_time, remark) VALUES (NOW(), CONCAT(batch-, i)); SET i i 1; END WHILE; END$$ DELIMITER ;如果用 LOOP 实现同样逻辑需要这样写DELIMITER $$ CREATE PROCEDURE sp_batch_insert_log(IN p_count INT) BEGIN DECLARE i INT DEFAULT 0; insert_loop: LOOP IF i p_count THEN LEAVE insert_loop; END IF; INSERT INTO t_log(create_time, remark) VALUES (NOW(), CONCAT(batch-, i)); SET i i 1; END LOOP insert_loop; END$$ DELIMITER ;实际使用中WHILE是最常用的代码可读性也最好。REPEAT通常用在“先做一次再判断要不要继续”的场景比如首次生成报表后检查数据量是否达标。3.3 动态SQL当表名和条件来自参数时怎么处理静态 SQL 写多了你会发现一个尴尬情况有些场景下表名或者排序字段是由参数决定的比如管理后台让你“按任意字段排序”或者归档时表名带日期后缀。这时候就需要动态 SQL。MySQL 里动态 SQL 的标准流程是三步PREPARE 准备、EXECUTE 执行、DEALLOCATE PREPARE 释放。看一个例子按传入表名查询前十条数据DELIMITER $$ CREATE PROCEDURE sp_query_dynamic(IN p_table_name VARCHAR(64)) BEGIN SET sql CONCAT(SELECT * FROM , p_table_name, LIMIT 10); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ;这里有几个关键点必须说清楚第一PREPARE 的占位符?只能用于值不能用于表名和字段名。也就是说PREPARE stmt FROM SELECT * FROM ? LIMIT 10是行不通的表名只能靠字符串拼接。第二字符串拼接意味着必须做安全校验。这种存储过程如果开放给业务方调用等于把数据库查询能力暴露出去一旦表名参数里被塞进恶意内容后果非常严重。我一般会做一层白名单校验DELIMITER $$ CREATE PROCEDURE sp_query_dynamic_safe(IN p_table_name VARCHAR(64)) BEGIN IF p_table_name NOT IN (user, order, product) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT table not allowed; END IF; SET sql CONCAT(SELECT * FROM , p_table_name, LIMIT 10); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ;第三动态 SQL 的执行计划无法提前缓存。如果某个存储过程是高频调用而且动态拼 SQL性能可能不如静态 SQL因为查询优化器每次都要重新解析。这一点尤其要注意别把高频核心接口做成动态 SQL。4. 游标与临时表组合正确处理逐行数据4.1 游标标准写法与最常见的翻车点游标是存储过程里最容易被写崩的部分。它的作用是一行一行地从查询结果集里取数据类似于编程语言里的迭代器。标准使用流程只有四步声明、打开、取数、关闭。DELIMITER $$ CREATE PROCEDURE sp_calc_score() BEGIN DECLARE v_user_id INT; DECLARE v_done INT DEFAULT 0; DECLARE cur_user CURSOR FOR SELECT id FROM user WHERE status 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur_user; user_loop: LOOP FETCH cur_user INTO v_user_id; IF v_done THEN LEAVE user_loop; END IF; UPDATE user_score SET score score 10 WHERE user_id v_user_id; END LOOP; CLOSE cur_user; END$$ DELIMITER ;这段代码看起来不长但翻车点密集第一个坑NOT FOUND 处理器的位置。DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1;必须声明在游标声明之后、OPEN 之前位置不对处理器不生效一取完数据就会死循环。第二个坑FETCH 之后必须立刻判断退出标志。游标取完最后一条记录后再次 FETCH 才会触发 NOT FOUND。如果你把判断放在循环体末尾就会多处理一次“幽灵数据”或者退出时机错误。第三个坑关闭游标。游标一旦 OPEN就和连接资源绑定必须成对出现 CLOSE。长期不关连接资源会累积消耗最终影响数据库连接池。第四个坑游标中做 DML 操作会长时间持有行锁。如果在游标内逐行 UPDATE 大量数据每行之间的事务持续时间很长容易造成锁等待和死锁。能用一条 UPDATE 完成的逻辑永远不要用游标。4.2 用临时表承接结果分组TopN的完整案例游标配合临时表可以解决一类经典问题MySQL 5.7 及更早版本里没有窗口函数想按部门取工资前三名员工得费不少功夫。存储过程加临时表是那个时代比较通用的解法。需求描述有一张员工表 employee字段为 id、dept_id、salary需要输出每个部门工资前三高的员工。DELIMITER $$ CREATE PROCEDURE sp_dept_top3() BEGIN DECLARE v_dept_id INT; DECLARE v_done INT DEFAULT 0; DECLARE cur_dept CURSOR FOR SELECT DISTINCT dept_id FROM employee; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; DROP TEMPORARY TABLE IF EXISTS tmp_top3; CREATE TEMPORARY TABLE tmp_top3 ( dept_id INT, emp_id INT, salary DECIMAL(10,2) ); OPEN cur_dept; dept_loop: LOOP FETCH cur_dept INTO v_dept_id; IF v_done THEN LEAVE dept_loop; END IF; INSERT INTO tmp_top3 SELECT dept_id, id, salary FROM employee WHERE dept_id v_dept_id ORDER BY salary DESC LIMIT 3; END LOOP; CLOSE cur_dept; SELECT * FROM tmp_top3 ORDER BY dept_id, salary DESC; END$$ DELIMITER ;核心思路是先用游标遍历所有部门每个部门内部单独执行一次排序取前三再把结果累积写入临时表最后统一查询临时表。这里 DROP TEMPORARY TABLE 用的临时表只对当前会话可见存储过程执行完连接关闭后临时表自动消失不需要手动清理。如果你用的是 MySQL 8.0同样需求可以一行窗口函数解决SELECT dept_id, emp_id, salary FROM ( SELECT dept_id, emp_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 3;但生产环境里依然有大量实例停留在 5.7而且有些分库分表中间件不支持窗口函数下推所以存储过程加临时表的写法依然有存在价值。至少说明白这两种方案的取舍面试聊到也不虚。5. 异常处理与事务控制别让中途报错留下半截数据5.1 为什么批处理存储过程必须做事务处理写批处理类存储过程最怕的就是“跑到一半报错”。假设一个存储过程要批量更新一万个会员的等级第八千条数据因为某个脏数据触发了一个约束错误。如果整个过程没有事务包裹前七千九百九十九条已经提交生效数据就处于一种“一半新一半旧”的状态而且很不好判断哪些是更新过的恢复成本极高。解决办法很简单在存储过程内部用START TRANSACTION开启事务正常情况下COMMIT提交异常情况下ROLLBACK回滚。这样要么全部成功要么全部失败不会留下半截数据。看一个完整的转账存储过程示例DELIMITER $$ CREATE PROCEDURE sp_transfer_money( IN p_from_account INT, IN p_to_account INT, IN p_amount DECIMAL(10,2) ) BEGIN -- 声明异常处理器遇到任何 SQL 异常回滚事务并重新抛出 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; UPDATE account SET balance balance - p_amount WHERE id p_from_account; IF ROW_COUNT() 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 转出账户不存在; END IF; UPDATE account SET balance balance p_amount WHERE id p_to_account; IF ROW_COUNT() 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 转入账户不存在; END IF; COMMIT; END$$ DELIMITER ;注意两点。第一这里检查ROW_COUNT()只能判断更新了几行没法判断余额是否够扣。真正的余额校验应该在 UPDATE 条件里加上AND balance p_amount然后通过 ROW_COUNT 来判断是否满足UPDATE account SET balance balance - p_amount WHERE id p_from_account AND balance p_amount; IF ROW_COUNT() 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 账户不存在或余额不足; END IF;第二RESIGNAL的作用是把原始异常重新抛给调用方让应用层能感知到失败避免存储过程内部悄悄吞掉错误。5.2 条件处理器CONTINUE 与 EXIT 的取舍上一节的示例里用了EXIT HANDLER意思是“遇到异常就退出当前存储过程”。MySQL 还支持CONTINUE HANDLER意思是“遇到异常后继续执行”。两者怎么选我一般是这么判断的如果是破坏性操作比如批量修改、转账、删除必须用EXIT快速回滚退出别留后患。如果是非关键环节比如“日志表写入失败不应该影响主流程”可以用CONTINUE在处理器里记录一个状态标记主流程继续往下走。CONTINUE 处理器的一个典型例子DELIMITER $$ CREATE PROCEDURE sp_main_process() BEGIN DECLARE v_log_failed INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_log_failed 1; -- 主处理逻辑 INSERT INTO biz_data SELECT ...; -- 写日志失败也不影响主流程 INSERT INTO process_log(ts, status) VALUES (NOW(), done); END$$ DELIMITER ;这里有个细节CONTINUE 处理器捕获异常后如果依赖的上下文比如某个临时表已经被破坏后续 SQL 可能继续报错。所以 CONTINUE 一定要用在“捕获后不会再依赖被破坏资源”的场景否则副作用比异常本身还大。5.3 兜底方案SIGNAL 抛出业务错误日志表记录现场存储过程里除了捕获数据库异常还需要主动检查业务条件不满足就抛出业务错误。SIGNAL就是干这个的IF v_cnt 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 没有找到任何符合条件的数据; END IF;SQLSTATE 可以简单理解为错误码45000是用户自定义异常的通用状态码不会和 MySQL 内置错误码冲突。另外一个建议是给批处理类存储过程配套一张日志表把每次执行的开始时间、结束时间、影响行数、错误信息记下来。这一步在线上维护时收益巨大出了问题能直接翻日志表定位是哪一批次执行失败、卡在哪个阶段。CREATE TABLE proc_run_log ( id INT AUTO_INCREMENT PRIMARY KEY, proc_name VARCHAR(64) NOT NULL, start_time DATETIME, end_time DATETIME, affected_rows INT, error_msg TEXT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;我自己的习惯是每个批处理存储过程结尾都写一条执行日志。刚开始觉得麻烦直到有一次夜间批处理在凌晨三点静默失败靠这张日志表十分钟定位到了原因之后我就把这条规矩写进了团队的开发规范里。6. 线上排查与日常维护教程里不讲的几个关键细节6.1 权限、DEFINER 与安全上下文存储过程创建好以后调用方账号需要EXECUTE权限GRANT EXECUTE ON PROCEDURE test_db.sp_dept_user_count TO app_user%;如果没有 EXECUTE 权限调用会直接报EXECUTE command denied。这个错误定位起来很快但有一个更隐蔽的问题——DEFINER 安全上下文。存储过程默认以 DEFINER定义者的身份去访问表也就是说如果存储过程定义者的账号没有访问某些表的权限即使调用方有权限存储过程照样会报“table access denied”。反过来也有可能调用方账号本身没有表权限但因为存储过程是定义者身份执行的反而能查到数据。MySQL 支持修改这个行为创建时可以指定安全模式CREATE DEFINER rootlocalhost PROCEDURE sp_demo() SQL SECURITY DEFINER BEGIN ... END; CREATE PROCEDURE sp_demo2() SQL SECURITY INVOKER BEGIN ... END;SQL SECURITY DEFINER是默认行为适合那种“不想把底层表权限开放给每个业务账号”的场景SQL SECURITY INVOKER则要求调用方自己具有表权限权限模型更严格也更安全。生产环境我建议优先用 INVOKER避免因为 DEFINER 权限过大导致数据越权访问。6.2 存储过程的性能分析思路存储过程跑得慢排查思路和普通慢 SQL 不太一样。普通 SQL 可以直接 EXPLAIN但存储过程是多条 SQL 的集合没法整体 EXPLAIN必须拆开看。我的排查顺序一般是先跑整体记录总耗时。用SELECT NOW();配合日志表看执行时间。把存储过程内部每条 SQL 单独拿出来 EXPLAIN。重点看有没有全表扫描、有没有索引失效、有没有 filesort。优先怀疑游标逐行循环。前面提过游标每 FETCH 一行如果内部还有 UPDATE 或 SELECT就是行级逐条操作数据量一大必然慢。优化方向是把逐行操作改成集合操作比如临时表 JOIN 一次更新。查锁等待和事务状态。如果存储过程里开了事务但长时间不提交其他会话会被阻塞。用下面这条 SQL 可以看当前事务SELECT * FROM information_schema.innodb_trx\G用元数据表确认变更时间。前面提到过SHOW PROCEDURE STATUS如果怀疑线上跑的存储过程和代码仓库里不一致可以对比last_altered时间。实际上大部分“存储过程慢”的问题都不是语法问题而是写法问题——把应该在集合层面完成的操作硬生生写成了逐行处理。优化哲学就一句话能一条 UPDATE 解决绝不用循环。6.3 版本差异、字符集与数据截断的坑最后汇总几个线上实际踩过的坑。binlog 与函数创建限制。MySQL 开启 binlog 后创建存储过程可能报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration...这是因为 MySQL 担心存储过程产生不确定结果导致基于 binlog 的主从复制数据不一致。解决办法是在存储过程声明里加上DETERMINISTIC等特征声明或者开启log_bin_trust_function_creators ON。CREATE PROCEDURE sp_demo() DETERMINISTIC BEGIN ... END;字符集不一致导致乱码和比较错误。存储过程的字符集和表的字符集不一致时可能出现两类问题中文乱码以及看似相同的字符串在 WHERE 比较时匹配不上。排查方法是先看如下三项SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE character_set_database; SHOW CREATE PROCEDURE sp_demo;变量长度截断。存储过程里 DECLARE 一个 VARCHAR(20)但查询结果实际返回了三十个字符直接赋值就会报Data too long。这个问题好定位但容易犯的隐蔽版本是 DECLARE 变量太短导致数据被静默截断不会报错但结果不对。我的建议是所有承接查询结果的变量长度按表字段定义来宁可长一点不要短一点。游标内 UPDATE 引发的死锁。两个并发存储过程如果以不同顺序更新同一条数据可能形成死锁。MySQL 检测到死锁后会回滚其中一方事务并返回类似Deadlock found的报错。这种问题通常要靠统一更新顺序来规避比如所有更新都按 ID 升序执行。诚实的说我现在做日常开发也很少把核心业务逻辑塞进存储过程但凡是定时批处理、报表汇总、数据迁移这类任务我反而会优先考虑存储过程。理由就一个它跑在数据库内部不受应用发布节奏限制出问题直接连库排查日志表一翻就知道卡在哪一步重跑和回滚都方便。最后分享一个我个人的操作习惯每个存储过程开头写清楚用途和参数关键步骤加上注释末尾把执行结果写进日志表。这个习惯不复杂但每次线上遇到和批处理相关的问题这些注释和日志都会成为第一手排查线索。如果你正准备把第一个存储过程写到生产环境建议从“给现有表做个统计汇总”这种小功能开始把它跑熟、看透再逐步处理更复杂的逻辑。