最近有个朋友在折腾数据库课程设计卡在存储过程怎么写循环跑来问我while、repeat、loop到底啥区别。这东西说难不难但新手确实容易绕晕尤其是第一次写循环条件一不留神就死循环了。我干脆把这几年写存储过程的经验整理一下把三种循环掰开揉碎讲清楚再配一个完整的实战案例帮你一次搞懂。先说清楚存储过程里的循环本质上就是解决“SQL无法表达复杂业务逻辑”这个痛点。单纯的数据操作一条SELECT、UPDATE就能搞定但真实业务往往需要逐行处理、批量生成、条件判断重复执行这时候存储过程加循环就是最直接的方案。下面我会把语法、适用场景、坑位全部过一遍。1. 先搞清楚什么时候才需要循环写存储过程1.1 没有循环存储过程基本是个空壳存储过程被很多人当成“一组SQL的集合”这个理解太浅了。真正让存储过程有灵魂的是它具备编程语言的能力变量、判断、循环、异常处理。循环在其中扮演的角色就是让一段逻辑可以按条件反复执行直到满足某个状态为止。想象一个场景你要往订单表里插入一万条测试数据如果用普通SQL你得复制一万行INSERT语句或者用Excel拼SQL再粘贴回来。遇到这种需求一条WHILE循环就能在几秒内搞定而且数据内容可以带规律变化用户ID递增、金额随机、时间间隔均匀。再比如你需要根据某个状态字段批量更新一批记录每条记录计算逻辑不同这时候逐行读取加循环处理就是最优解。换个生活化的类比普通SQL像超市购物清单一次列出所有要买的东西循环像做饭流程放油、下菜、翻炒、试味不符合口味就再加调料直到满意为止。后者能做前者无法表达的“带条件反复执行”的动作。1.2 三种循环各自的性格特征很多初学者纠结于选择哪一种循环其实没有绝对的好坏只有合不合适。我对这三种循环的形象定义是WHILE先检查条件条件成立才执行条件不成立一次都不执行。像你进健身房先看卡是否有效无效直接不让进。REPEAT先执行一次再检查条件条件不成立就继续成立就退出。像试菜不管饿不饿先尝一口不好吃再继续做。LOOP没有任何内置判断纯粹的死循环必须靠LEAVE语句手动跳出。像一个没有终点线的跑道你得自己找出口。搞清楚这个底层逻辑后面的选择就顺理成章需要“至少执行一次”的逻辑用REPEAT需要“先判断再执行”的逻辑用WHILE需要复杂条件组合跳出时用LOOP加标签label最灵活。2. 逐个拆解语法WHILE、REPEAT、LOOP一个不落2.1 WHILE循环先判断后执行的“门卫”WHILE是使用频率最高的循环语法非常简单WHILE condition DO -- 循环体 END WHILE;condition是一个布尔表达式每次循环开始前都会计算一次。如果为TRUE进入循环体如果为FALSE直接跳过继续执行END WHILE后面的语句。有个关键点要提醒MySQL不像某些语言没有i这种自带语法你必须自己在循环体里修改循环条件中依赖的变量否则就是死循环。-- 示例用WHILE计算1加到100 DELIMITER // CREATE PROCEDURE proc_while_demo() BEGIN DECLARE i INT DEFAULT 1; DECLARE total INT DEFAULT 0; WHILE i 100 DO SET total total i; SET i i 1; END WHILE; SELECT total; END // DELIMITER ;我第一次写的时候就吃过“忘记SET i i 1”的亏Navicat里跑了半天不结束最后只能强制杀掉连接。后来养成一个习惯写完循环体内第一件事就检查循环变量有没有被更新。2.2 REPEAT循环至少执行一次的“验货员”REPEAT的语法如下REPEAT -- 循环体 UNTIL condition END REPEAT;注意两点第一UNTIL后面不需要分号直接跟条件表达式第二条件为TRUE时退出循环为FALSE时继续这和WHILE正好相反。REPEAT适合那些“必须做一次才能判断要不要继续”的场景。比如你要生成一个不重复的订单号先随机生成一个检查表里有没有冲突有冲突就重新生成直到不冲突为止。这种逻辑用REPEAT特别顺手。-- 示例生成一个不重复的订单编号简化版 DELIMITER // CREATE PROCEDURE proc_repeat_demo() BEGIN DECLARE order_no VARCHAR(20); DECLARE exists_count INT DEFAULT 1; REPEAT SET order_no CONCAT(ORD, DATE_FORMAT(NOW(), %Y%m%d), FLOOR(RAND() * 10000)); SELECT COUNT(*) INTO exists_count FROM orders WHERE order_no order_no; UNTIL exists_count 0 END REPEAT; SELECT order_no; END // DELIMITER ;这里有个隐藏细节SELECT COUNT(*) INTO从表里查出来的计数如果表里没有这条记录变量会被赋值为0循环退出。每次循环都会重新生成随机编号直到找到不冲突的为止。2.3 LOOP循环最自由但也最容易跑飞的“赛车”LOOP本身没有条件判断它的结构是这样的label_name: LOOP -- 循环体 IF condition THEN LEAVE label_name; END IF; END LOOP;注意第一行LOOP前面的label_name:是标签必须加否则LEAVE语句找不到跳出的目标。LEAVE就是“跳出整个循环”和Java的break是一个意思。LOOP的典型用法是“你自己掌控一切”循环体内可以写多个退出条件不像WHILE只能写一个判断表达式。比如一个复杂的业务循环当数据量超过上限时退出当某个状态值异常时退出当外部传入参数改变时退出——这种多条件场景用LOOP更清晰。-- 示例LOOP循环根据多个条件退出 DELIMITER // CREATE PROCEDURE proc_loop_demo(IN max_count INT) BEGIN DECLARE i INT DEFAULT 0; my_loop: LOOP SET i i 1; -- 条件1超过指定次数 IF i max_count THEN LEAVE my_loop; END IF; -- 条件2达到100时就停 IF i 100 THEN LEAVE my_loop; END IF; END LOOP; SELECT i; END // DELIMITER ;2.4 三种循环怎么选一张表说透为了让你快速选择我整理了一个对比表特性WHILEREPEATLOOP判断时机先判断后执行先执行后判断无内置判断循环体执行次数可能为0次至少为1次必须用LEAVE跳出结束条件方向条件为TRUE才继续条件为TRUE就结束需要IF LEAVE配合适合场景一般业务循环必须执行一次的场景复杂的多条件退出代码可读性高中低比较考验理解力实际开发中我大概有六成场景用WHILE两成用REPEAT两成用LOOP。LOOP多数用在游标循环或需要多个退出条件的复杂逻辑里。REPEAT的“至少执行一次”特性在处理数据校验、编号生成时确实好用。3. 一个完整的实战批量数据处理存储过程3.1 业务场景与建表准备理论说再多不如一个完整的案例。假设我们现在有个会员积分系统需要给会员按照消费记录重新计算月度积分逻辑是每消费满100元加10积分不满100的部分按1元加0.1积分取整。每个月都要跑一次数据量在几万条级别。先准备好三张表会员表、消费明细表、积分记录表。-- 会员表 CREATE TABLE member ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), total_points INT DEFAULT 0 ); -- 消费明细表 CREATE TABLE consumption ( id INT PRIMARY KEY AUTO_INCREMENT, member_id INT, amount DECIMAL(10,2), consume_date DATETIME, INDEX idx_member_date (member_id, consume_date) ); -- 积分记录表 CREATE TABLE points_log ( id INT PRIMARY KEY AUTO_INCREMENT, member_id INT, points INT, calc_date DATE, remark VARCHAR(100) );为什么强调建索引因为存储过程里循环操作会频繁查这张表索引能大幅提升查询速度批量处理几万条数据时差异非常明显。3.2 用WHILE循环批量插入测试数据先往消费明细表插入10万条测试数据只有数据充足后面的积分计算才有意义。这里用WHILE循环插入。DELIMITER // CREATE PROCEDURE proc_insert_test_data(IN insert_count INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE member_id INT; DECLARE amount DECIMAL(10,2); -- 关闭自动提交每1000条统一提交一次 -- 事务手动控制 START TRANSACTION; WHILE i insert_count DO -- 随机关联一个会员ID(1-1000) SET member_id FLOOR(1 RAND() * 1000); -- 随机消费金额 10元到500元 SET amount ROUND(10 RAND() * 490, 2); INSERT INTO consumption (member_id, amount, consume_date) VALUES (member_id, amount, NOW()); SET i i 1; END WHILE; COMMIT; END // DELIMITER ;这里有几个实操细节用START TRANSACTION包住循环体循环结束再一次性COMMIT避免每次INSERT都自动提交性能差别巨大。10万条数据如果不包事务可能要跑十几分钟包了事务几秒钟就能完成。FLOOR(1 RAND() * 1000)用来生成1到1000的随机整数这个表达式建议背下来很常用。ROUND函数控制小数位生成金额数据时特别方便。调用一下CALL proc_insert_test_data(100000);我实测在普通笔记本上10万条数据大概3-5秒完成效果很理想。3.3 用REPEAT循环处理数据校验数据准备好了现在要检查一下10万条数据里有没有金额为0的异常数据。这类“先查再判断”的逻辑用REPEAT很方便。我们写一个存储过程每次扫描一遍数据找到异常数据则修正直到扫描结果为空。DELIMITER // CREATE PROCEDURE proc_fix_invalid_data() BEGIN DECLARE invalid_count INT DEFAULT 0; REPEAT -- 找到一条异常数据 SELECT COUNT(*) INTO invalid_count FROM consumption WHERE amount 0 OR member_id IS NULL; IF invalid_count 0 THEN -- 修正异常数据金额置为0会员ID置为1 UPDATE consumption SET amount 0, member_id 1 WHERE (amount 0 OR member_id IS NULL) LIMIT 1000; END IF; UNTIL invalid_count 0 END REPEAT; END // DELIMITER ;这段逻辑的意思是每次循环检查还有没有异常数据有就修改一批LIMIT 1000控制单次处理量直到全部修复完。用REPEAT的核心考量是——不管数据有没有问题至少要检查一次。这个“先做一遍再决定是否继续”的思路和REPEAT的特性完美匹配。3.4 用LOOP加游标逐行处理消费明细终于到了重点计算每个会员的积分。这个逻辑需要把消费明细按会员分组统计然后插入积分记录表。方式有两种一条GROUP BY SQL就能完成统计再一条INSERT INTO SELECT写入也可以选择用游标逐行处理展示LOOP配合游标的写法。实际生产我推荐GROUP BY方案但为了演示这里用游标处理举一个典型的数据清理场景。比如按天对账时需要逐行读取消费明细计算积分然后合并到会员总积分。我用一个LOOP循环来处理。DELIMITER // CREATE PROCEDURE proc_calc_points(IN calc_date DATE) BEGIN DECLARE done INT DEFAULT 0; DECLARE v_member_id INT; DECLARE v_amount DECIMAL(10,2); DECLARE v_points INT; -- 游标读取指定日期的所有消费记录 DECLARE cur CURSOR FOR SELECT member_id, amount FROM consumption WHERE DATE(consume_date) calc_date; -- 声明NOT FOUND处理器为done1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; OPEN cur; -- LOOP游标循环 calc_loop: LOOP FETCH cur INTO v_member_id, v_amount; IF done 1 THEN LEAVE calc_loop; END IF; -- 计算积分满100元得10分不满100元部分1元得0.1分 SET v_points FLOOR(v_amount / 100) * 10 FLOOR(MOD(v_amount, 100)); -- 插入积分记录 INSERT INTO points_log (member_id, points, calc_date, remark) VALUES (v_member_id, v_points, calc_date, CONCAT(消费金额, v_amount)); -- 更新会员总积分 UPDATE member SET total_points total_points v_points WHERE id v_member_id; END LOOP; CLOSE cur; END // DELIMITER ;这段代码有四个关键点我给新手划个重点第一游标的声明必须在变量声明之后处理器声明之前顺序搞错会报语法错误。第二DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1是游标循环的定海神针没有它游标读到最后一行会自动抛异常循环不会正常结束。第三FETCH语句要放到循环体开头然后立刻判断done标志位再决定是否LEAVE。第四LOOP循环结束前记得CLOSE cur释放游标资源。4. 循环控制、嵌套循环与常见坑位4.1 LEAVE和ITERATE的灵活控制前面提到了LEAVE它跳出整个循环。还有一个孪生兄弟叫ITERATE作用是“跳过本次循环剩余部分直接进入下一次循环”类似Java的continue。这两个语句使用频率都很高组合起来能做很灵活的控制DELIMITER // CREATE PROCEDURE proc_control_demo() BEGIN DECLARE i INT DEFAULT 0; my_loop: LOOP SET i i 1; IF i 20 THEN LEAVE my_loop; END IF; -- 跳过所有偶数 IF MOD(i, 2) 0 THEN ITERATE my_loop; END IF; -- 只会输出1,3,5,7,...,19 SELECT i; END LOOP; END // DELIMITER ;注意ITERATE只能用在LOOP循环里WHILE和REPEAT不支持。这个限制有时候会让人转换思路既然要精细控制那就用LOOP吧。4.2 嵌套循环的标签管理别让代码变成意大利面嵌套循环在存储过程中很常见外层循环处理会员内层循环处理该会员的订单明细。标签label在嵌套场景下特别重要因为LEAVE和ITERATE需要知道“跳出哪一层”。DELIMITER // CREATE PROCEDURE proc_nested_loop_demo() BEGIN DECLARE outer_i INT DEFAULT 0; DECLARE inner_i INT DEFAULT 0; outer_loop: LOOP SET outer_i outer_i 1; SET inner_i 0; IF outer_i 5 THEN LEAVE outer_loop; END IF; inner_loop: LOOP SET inner_i inner_i 1; IF inner_i 3 THEN -- 跳出内层循环 -- 注意这里如果写 LEAVE outer_loop; 就直接把外层也跳出了 ITERATE inner_loop; END IF; IF inner_i 2 THEN -- 直接跳出外层 LEAVE outer_loop; END IF; END LOOP; END LOOP; END // DELIMITER ;这里最大的坑在于写嵌套循环时,一定要给每个循环起清晰的标签名不要用a_loop、b_loop这种无意义的名字。最好用outer_xxx、inner_xxx这种能表达层级关系的名字。我曾经在一个三层嵌套的存储过程里写错了LEAVE的目标标签结果数据错了一大半排查了很久才发现——那体验真不好受。4.3 常见问题排查速查表在写循环存储过程时我梳理了这些高频问题供你参考问题现象根本原因解决方案循环无限执行Navicat卡死忘记更新循环变量检查循环体内是否有SET i i 1游标循环第一次就退出NOT FOUND处理器设置错误检查DECLARE CONTINUE HANDLER是否在游标声明之后变量值一直是NULL变量未初始化DECLARE时加DEFAULT 0循环内SELECT结果刷屏循环内使用SELECT输出改用变量收集最后统一SELECT嵌套循环跳出层次错误LEAVE目标标签写错检查标签名用有语义的标签数据量大时超慢循环内频繁COMMIT循环外统一事务管理游标循环结束后报错游标未关闭在循环结束后加CLOSE cur5. 性能优化与调试技巧这些经验能帮你少走弯路5.1 循环里的三个性能杀手第一个是循环内执行过多SQL语句。每执行一条SQL都会有网络传输、SQL解析、执行计划生成的开销循环一万次就是一万次开销。能合并的语句尽量合并能用变量算的就不要查表循环体内只保留必要的操作。第二个是频繁提交事务。默认autocommit开启时每次INSERT都会立即落盘性能极差。正确的做法是在循环前START TRANSACTION循环结束后统一COMMIT。但要注意循环过程一旦出错需要手动ROLLBACK不然数据会乱。所以存储过程里最好结合条件判断成功就COMMIT异常就ROLLBACK。第三个是循环没有WHERE条件或索引失效。这在游标循环里尤其明显——游标SELECT的WHERE条件如果没走索引每次FETCH都是一次全表扫描性能直接爆炸。我建议游标SELECT前用EXPLAIN看一下执行计划。比如上面那个积分计算案例如果consumption表的索引没建好几十万数据能跑几分钟。5.2 几种实用的调试方法存储过程不像普通编程语言可以打断点调试起来比较原始但有几个技巧很实用。第一种临时表记录进度。在循环里往临时表插入当前变量值跑完再看临时表内容能清楚看到循环执行轨迹。CREATE TEMPORARY TABLE debug_log (i INT, total INT); -- 在循环体里 INSERT INTO debug_log VALUES (i, total);第二种SELECT输出中间变量。直接在循环体内写SELECT i, totalNavicat和MySQL Workbench会显示查询结果方便观察每次循环的状态。但要注意大量SELECT会刷屏只适合小数据量调试。第三种条件式调试输出。用一个调试开关变量为1时才输出生产环境关掉不用修改代码。DECLARE debug_enable INT DEFAULT 1; -- 调试时改0 IF debug_enable 1 THEN SELECT i, total; END IF;在MySQL Workbench里调试存储过程还有内置的调试器可以加断点单步执行但因为需要额外安装Debug插件很多人没用过。实际工作中我最常用的还是临时表和SELECT输出。5.3 我的几个独家避坑习惯这几条是我写存储过程这几年总结出来的几乎每条背后都有血的教训声明变量时尽量给默认值尤其是存量累积的变量比如total不给默认值就是NULLNULL参与运算结果就是NULL整个结果全乱。循环体中如果有条件判断要修改循环控制变量务必考虑所有分支。比如WHILE里用IF判断是否继续如果某个分支忘了更新控制变量死循环就来了。游标循环的FETCH建议在循环体第一行执行。不要先做其他操作再FETCH这样容易漏读数据或者重复读最后一条记录。大批量循环处理前先备份原表数据。我曾经一个UPDATE循环条件写错把一张线上表的字段全改成了同一个值最后靠备份恢复才没出大事。存储过程的循环操作影响面大执行前一定三思。存储过程写完后用一个小数据集先验证逻辑正确性再跑全量。比如把案例里的INSERT COUNT从100000改成100确认结果没问题再执行完整版本。5.4 什么样的场景不应该用循环循环虽好用但有些场景用循环反而事倍功半。这是我想额外强调的因为很多新人容易陷入“能用循环解决一切”的思维定式。MySQL是关系型数据库擅长的是集合操作也就是一次处理整批数据。能用一条UPDATE或INSERT INTO SELECT解决的就不要用游标逐行处理。比如把积分计算结果写入积分记录表用INSERT INTO SELECT一条语句就能完成效率比游标循环高几十倍不止。-- 集合操作一条SQL完成积分计算和写入 INSERT INTO points_log (member_id, points, calc_date, remark) SELECT member_id, FLOOR(SUM(amount) / 100) * 10, CURDATE(), 月度积分批量计算 FROM consumption WHERE consume_date 2025-01-01 AND consume_date 2025-02-01 GROUP BY member_id;这是“集合操作优先循环兜底”的经典原则。循环只用来处理结果集无法表达的逻辑比如动态条件判断、跨表逐层汇总、数据校验等场景。判断标准很简单如果这条逻辑能用一条SQL表达就不要写循环如果必须逐行判断、逐行处理才考虑游标加循环。我在实际操作中的体会是存储过程循环就像一把瑞士军刀用得好看能解决很多复杂问题用不好反而会扎手。最关键的是理解每种循环的“性格”知道它们在什么场景下发挥作用。把WHILE的“先判断”、REPEAT的“至少执行一次”、LOOP的“自由跳出”吃透再配合游标和事务控制批量数据处理这块基本就能过关了。最后分享一个小习惯每次写完存储过程我都会准备一个包含正常数据、边界数据、异常数据的小测试集先跑一遍确认边界情况不会死循环、不会报错再放到生产环境执行。这个习惯救了我很多次也推荐给你。
最近有个朋友在折腾数据库课程设计卡在存储过程怎么写循环跑来问我while、repeat、loop到底啥区别。这东西说难不难但新手确实容易绕晕尤其是第一次写循环条件一不留神就死循环了。我干脆把这几年写存储过程的经验整理一下把三种循环掰开揉碎讲清楚再配一个完整的实战案例帮你一次搞懂。先说清楚存储过程里的循环本质上就是解决“SQL无法表达复杂业务逻辑”这个痛点。单纯的数据操作一条SELECT、UPDATE就能搞定但真实业务往往需要逐行处理、批量生成、条件判断重复执行这时候存储过程加循环就是最直接的方案。下面我会把语法、适用场景、坑位全部过一遍。1. 先搞清楚什么时候才需要循环写存储过程1.1 没有循环存储过程基本是个空壳存储过程被很多人当成“一组SQL的集合”这个理解太浅了。真正让存储过程有灵魂的是它具备编程语言的能力变量、判断、循环、异常处理。循环在其中扮演的角色就是让一段逻辑可以按条件反复执行直到满足某个状态为止。想象一个场景你要往订单表里插入一万条测试数据如果用普通SQL你得复制一万行INSERT语句或者用Excel拼SQL再粘贴回来。遇到这种需求一条WHILE循环就能在几秒内搞定而且数据内容可以带规律变化用户ID递增、金额随机、时间间隔均匀。再比如你需要根据某个状态字段批量更新一批记录每条记录计算逻辑不同这时候逐行读取加循环处理就是最优解。换个生活化的类比普通SQL像超市购物清单一次列出所有要买的东西循环像做饭流程放油、下菜、翻炒、试味不符合口味就再加调料直到满意为止。后者能做前者无法表达的“带条件反复执行”的动作。1.2 三种循环各自的性格特征很多初学者纠结于选择哪一种循环其实没有绝对的好坏只有合不合适。我对这三种循环的形象定义是WHILE先检查条件条件成立才执行条件不成立一次都不执行。像你进健身房先看卡是否有效无效直接不让进。REPEAT先执行一次再检查条件条件不成立就继续成立就退出。像试菜不管饿不饿先尝一口不好吃再继续做。LOOP没有任何内置判断纯粹的死循环必须靠LEAVE语句手动跳出。像一个没有终点线的跑道你得自己找出口。搞清楚这个底层逻辑后面的选择就顺理成章需要“至少执行一次”的逻辑用REPEAT需要“先判断再执行”的逻辑用WHILE需要复杂条件组合跳出时用LOOP加标签label最灵活。2. 逐个拆解语法WHILE、REPEAT、LOOP一个不落2.1 WHILE循环先判断后执行的“门卫”WHILE是使用频率最高的循环语法非常简单WHILE condition DO -- 循环体 END WHILE;condition是一个布尔表达式每次循环开始前都会计算一次。如果为TRUE进入循环体如果为FALSE直接跳过继续执行END WHILE后面的语句。有个关键点要提醒MySQL不像某些语言没有i这种自带语法你必须自己在循环体里修改循环条件中依赖的变量否则就是死循环。-- 示例用WHILE计算1加到100 DELIMITER // CREATE PROCEDURE proc_while_demo() BEGIN DECLARE i INT DEFAULT 1; DECLARE total INT DEFAULT 0; WHILE i 100 DO SET total total i; SET i i 1; END WHILE; SELECT total; END // DELIMITER ;我第一次写的时候就吃过“忘记SET i i 1”的亏Navicat里跑了半天不结束最后只能强制杀掉连接。后来养成一个习惯写完循环体内第一件事就检查循环变量有没有被更新。2.2 REPEAT循环至少执行一次的“验货员”REPEAT的语法如下REPEAT -- 循环体 UNTIL condition END REPEAT;注意两点第一UNTIL后面不需要分号直接跟条件表达式第二条件为TRUE时退出循环为FALSE时继续这和WHILE正好相反。REPEAT适合那些“必须做一次才能判断要不要继续”的场景。比如你要生成一个不重复的订单号先随机生成一个检查表里有没有冲突有冲突就重新生成直到不冲突为止。这种逻辑用REPEAT特别顺手。-- 示例生成一个不重复的订单编号简化版 DELIMITER // CREATE PROCEDURE proc_repeat_demo() BEGIN DECLARE order_no VARCHAR(20); DECLARE exists_count INT DEFAULT 1; REPEAT SET order_no CONCAT(ORD, DATE_FORMAT(NOW(), %Y%m%d), FLOOR(RAND() * 10000)); SELECT COUNT(*) INTO exists_count FROM orders WHERE order_no order_no; UNTIL exists_count 0 END REPEAT; SELECT order_no; END // DELIMITER ;这里有个隐藏细节SELECT COUNT(*) INTO从表里查出来的计数如果表里没有这条记录变量会被赋值为0循环退出。每次循环都会重新生成随机编号直到找到不冲突的为止。2.3 LOOP循环最自由但也最容易跑飞的“赛车”LOOP本身没有条件判断它的结构是这样的label_name: LOOP -- 循环体 IF condition THEN LEAVE label_name; END IF; END LOOP;注意第一行LOOP前面的label_name:是标签必须加否则LEAVE语句找不到跳出的目标。LEAVE就是“跳出整个循环”和Java的break是一个意思。LOOP的典型用法是“你自己掌控一切”循环体内可以写多个退出条件不像WHILE只能写一个判断表达式。比如一个复杂的业务循环当数据量超过上限时退出当某个状态值异常时退出当外部传入参数改变时退出——这种多条件场景用LOOP更清晰。-- 示例LOOP循环根据多个条件退出 DELIMITER // CREATE PROCEDURE proc_loop_demo(IN max_count INT) BEGIN DECLARE i INT DEFAULT 0; my_loop: LOOP SET i i 1; -- 条件1超过指定次数 IF i max_count THEN LEAVE my_loop; END IF; -- 条件2达到100时就停 IF i 100 THEN LEAVE my_loop; END IF; END LOOP; SELECT i; END // DELIMITER ;2.4 三种循环怎么选一张表说透为了让你快速选择我整理了一个对比表特性WHILEREPEATLOOP判断时机先判断后执行先执行后判断无内置判断循环体执行次数可能为0次至少为1次必须用LEAVE跳出结束条件方向条件为TRUE才继续条件为TRUE就结束需要IF LEAVE配合适合场景一般业务循环必须执行一次的场景复杂的多条件退出代码可读性高中低比较考验理解力实际开发中我大概有六成场景用WHILE两成用REPEAT两成用LOOP。LOOP多数用在游标循环或需要多个退出条件的复杂逻辑里。REPEAT的“至少执行一次”特性在处理数据校验、编号生成时确实好用。3. 一个完整的实战批量数据处理存储过程3.1 业务场景与建表准备理论说再多不如一个完整的案例。假设我们现在有个会员积分系统需要给会员按照消费记录重新计算月度积分逻辑是每消费满100元加10积分不满100的部分按1元加0.1积分取整。每个月都要跑一次数据量在几万条级别。先准备好三张表会员表、消费明细表、积分记录表。-- 会员表 CREATE TABLE member ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), total_points INT DEFAULT 0 ); -- 消费明细表 CREATE TABLE consumption ( id INT PRIMARY KEY AUTO_INCREMENT, member_id INT, amount DECIMAL(10,2), consume_date DATETIME, INDEX idx_member_date (member_id, consume_date) ); -- 积分记录表 CREATE TABLE points_log ( id INT PRIMARY KEY AUTO_INCREMENT, member_id INT, points INT, calc_date DATE, remark VARCHAR(100) );为什么强调建索引因为存储过程里循环操作会频繁查这张表索引能大幅提升查询速度批量处理几万条数据时差异非常明显。3.2 用WHILE循环批量插入测试数据先往消费明细表插入10万条测试数据只有数据充足后面的积分计算才有意义。这里用WHILE循环插入。DELIMITER // CREATE PROCEDURE proc_insert_test_data(IN insert_count INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE member_id INT; DECLARE amount DECIMAL(10,2); -- 关闭自动提交每1000条统一提交一次 -- 事务手动控制 START TRANSACTION; WHILE i insert_count DO -- 随机关联一个会员ID(1-1000) SET member_id FLOOR(1 RAND() * 1000); -- 随机消费金额 10元到500元 SET amount ROUND(10 RAND() * 490, 2); INSERT INTO consumption (member_id, amount, consume_date) VALUES (member_id, amount, NOW()); SET i i 1; END WHILE; COMMIT; END // DELIMITER ;这里有几个实操细节用START TRANSACTION包住循环体循环结束再一次性COMMIT避免每次INSERT都自动提交性能差别巨大。10万条数据如果不包事务可能要跑十几分钟包了事务几秒钟就能完成。FLOOR(1 RAND() * 1000)用来生成1到1000的随机整数这个表达式建议背下来很常用。ROUND函数控制小数位生成金额数据时特别方便。调用一下CALL proc_insert_test_data(100000);我实测在普通笔记本上10万条数据大概3-5秒完成效果很理想。3.3 用REPEAT循环处理数据校验数据准备好了现在要检查一下10万条数据里有没有金额为0的异常数据。这类“先查再判断”的逻辑用REPEAT很方便。我们写一个存储过程每次扫描一遍数据找到异常数据则修正直到扫描结果为空。DELIMITER // CREATE PROCEDURE proc_fix_invalid_data() BEGIN DECLARE invalid_count INT DEFAULT 0; REPEAT -- 找到一条异常数据 SELECT COUNT(*) INTO invalid_count FROM consumption WHERE amount 0 OR member_id IS NULL; IF invalid_count 0 THEN -- 修正异常数据金额置为0会员ID置为1 UPDATE consumption SET amount 0, member_id 1 WHERE (amount 0 OR member_id IS NULL) LIMIT 1000; END IF; UNTIL invalid_count 0 END REPEAT; END // DELIMITER ;这段逻辑的意思是每次循环检查还有没有异常数据有就修改一批LIMIT 1000控制单次处理量直到全部修复完。用REPEAT的核心考量是——不管数据有没有问题至少要检查一次。这个“先做一遍再决定是否继续”的思路和REPEAT的特性完美匹配。3.4 用LOOP加游标逐行处理消费明细终于到了重点计算每个会员的积分。这个逻辑需要把消费明细按会员分组统计然后插入积分记录表。方式有两种一条GROUP BY SQL就能完成统计再一条INSERT INTO SELECT写入也可以选择用游标逐行处理展示LOOP配合游标的写法。实际生产我推荐GROUP BY方案但为了演示这里用游标处理举一个典型的数据清理场景。比如按天对账时需要逐行读取消费明细计算积分然后合并到会员总积分。我用一个LOOP循环来处理。DELIMITER // CREATE PROCEDURE proc_calc_points(IN calc_date DATE) BEGIN DECLARE done INT DEFAULT 0; DECLARE v_member_id INT; DECLARE v_amount DECIMAL(10,2); DECLARE v_points INT; -- 游标读取指定日期的所有消费记录 DECLARE cur CURSOR FOR SELECT member_id, amount FROM consumption WHERE DATE(consume_date) calc_date; -- 声明NOT FOUND处理器为done1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; OPEN cur; -- LOOP游标循环 calc_loop: LOOP FETCH cur INTO v_member_id, v_amount; IF done 1 THEN LEAVE calc_loop; END IF; -- 计算积分满100元得10分不满100元部分1元得0.1分 SET v_points FLOOR(v_amount / 100) * 10 FLOOR(MOD(v_amount, 100)); -- 插入积分记录 INSERT INTO points_log (member_id, points, calc_date, remark) VALUES (v_member_id, v_points, calc_date, CONCAT(消费金额, v_amount)); -- 更新会员总积分 UPDATE member SET total_points total_points v_points WHERE id v_member_id; END LOOP; CLOSE cur; END // DELIMITER ;这段代码有四个关键点我给新手划个重点第一游标的声明必须在变量声明之后处理器声明之前顺序搞错会报语法错误。第二DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1是游标循环的定海神针没有它游标读到最后一行会自动抛异常循环不会正常结束。第三FETCH语句要放到循环体开头然后立刻判断done标志位再决定是否LEAVE。第四LOOP循环结束前记得CLOSE cur释放游标资源。4. 循环控制、嵌套循环与常见坑位4.1 LEAVE和ITERATE的灵活控制前面提到了LEAVE它跳出整个循环。还有一个孪生兄弟叫ITERATE作用是“跳过本次循环剩余部分直接进入下一次循环”类似Java的continue。这两个语句使用频率都很高组合起来能做很灵活的控制DELIMITER // CREATE PROCEDURE proc_control_demo() BEGIN DECLARE i INT DEFAULT 0; my_loop: LOOP SET i i 1; IF i 20 THEN LEAVE my_loop; END IF; -- 跳过所有偶数 IF MOD(i, 2) 0 THEN ITERATE my_loop; END IF; -- 只会输出1,3,5,7,...,19 SELECT i; END LOOP; END // DELIMITER ;注意ITERATE只能用在LOOP循环里WHILE和REPEAT不支持。这个限制有时候会让人转换思路既然要精细控制那就用LOOP吧。4.2 嵌套循环的标签管理别让代码变成意大利面嵌套循环在存储过程中很常见外层循环处理会员内层循环处理该会员的订单明细。标签label在嵌套场景下特别重要因为LEAVE和ITERATE需要知道“跳出哪一层”。DELIMITER // CREATE PROCEDURE proc_nested_loop_demo() BEGIN DECLARE outer_i INT DEFAULT 0; DECLARE inner_i INT DEFAULT 0; outer_loop: LOOP SET outer_i outer_i 1; SET inner_i 0; IF outer_i 5 THEN LEAVE outer_loop; END IF; inner_loop: LOOP SET inner_i inner_i 1; IF inner_i 3 THEN -- 跳出内层循环 -- 注意这里如果写 LEAVE outer_loop; 就直接把外层也跳出了 ITERATE inner_loop; END IF; IF inner_i 2 THEN -- 直接跳出外层 LEAVE outer_loop; END IF; END LOOP; END LOOP; END // DELIMITER ;这里最大的坑在于写嵌套循环时,一定要给每个循环起清晰的标签名不要用a_loop、b_loop这种无意义的名字。最好用outer_xxx、inner_xxx这种能表达层级关系的名字。我曾经在一个三层嵌套的存储过程里写错了LEAVE的目标标签结果数据错了一大半排查了很久才发现——那体验真不好受。4.3 常见问题排查速查表在写循环存储过程时我梳理了这些高频问题供你参考问题现象根本原因解决方案循环无限执行Navicat卡死忘记更新循环变量检查循环体内是否有SET i i 1游标循环第一次就退出NOT FOUND处理器设置错误检查DECLARE CONTINUE HANDLER是否在游标声明之后变量值一直是NULL变量未初始化DECLARE时加DEFAULT 0循环内SELECT结果刷屏循环内使用SELECT输出改用变量收集最后统一SELECT嵌套循环跳出层次错误LEAVE目标标签写错检查标签名用有语义的标签数据量大时超慢循环内频繁COMMIT循环外统一事务管理游标循环结束后报错游标未关闭在循环结束后加CLOSE cur5. 性能优化与调试技巧这些经验能帮你少走弯路5.1 循环里的三个性能杀手第一个是循环内执行过多SQL语句。每执行一条SQL都会有网络传输、SQL解析、执行计划生成的开销循环一万次就是一万次开销。能合并的语句尽量合并能用变量算的就不要查表循环体内只保留必要的操作。第二个是频繁提交事务。默认autocommit开启时每次INSERT都会立即落盘性能极差。正确的做法是在循环前START TRANSACTION循环结束后统一COMMIT。但要注意循环过程一旦出错需要手动ROLLBACK不然数据会乱。所以存储过程里最好结合条件判断成功就COMMIT异常就ROLLBACK。第三个是循环没有WHERE条件或索引失效。这在游标循环里尤其明显——游标SELECT的WHERE条件如果没走索引每次FETCH都是一次全表扫描性能直接爆炸。我建议游标SELECT前用EXPLAIN看一下执行计划。比如上面那个积分计算案例如果consumption表的索引没建好几十万数据能跑几分钟。5.2 几种实用的调试方法存储过程不像普通编程语言可以打断点调试起来比较原始但有几个技巧很实用。第一种临时表记录进度。在循环里往临时表插入当前变量值跑完再看临时表内容能清楚看到循环执行轨迹。CREATE TEMPORARY TABLE debug_log (i INT, total INT); -- 在循环体里 INSERT INTO debug_log VALUES (i, total);第二种SELECT输出中间变量。直接在循环体内写SELECT i, totalNavicat和MySQL Workbench会显示查询结果方便观察每次循环的状态。但要注意大量SELECT会刷屏只适合小数据量调试。第三种条件式调试输出。用一个调试开关变量为1时才输出生产环境关掉不用修改代码。DECLARE debug_enable INT DEFAULT 1; -- 调试时改0 IF debug_enable 1 THEN SELECT i, total; END IF;在MySQL Workbench里调试存储过程还有内置的调试器可以加断点单步执行但因为需要额外安装Debug插件很多人没用过。实际工作中我最常用的还是临时表和SELECT输出。5.3 我的几个独家避坑习惯这几条是我写存储过程这几年总结出来的几乎每条背后都有血的教训声明变量时尽量给默认值尤其是存量累积的变量比如total不给默认值就是NULLNULL参与运算结果就是NULL整个结果全乱。循环体中如果有条件判断要修改循环控制变量务必考虑所有分支。比如WHILE里用IF判断是否继续如果某个分支忘了更新控制变量死循环就来了。游标循环的FETCH建议在循环体第一行执行。不要先做其他操作再FETCH这样容易漏读数据或者重复读最后一条记录。大批量循环处理前先备份原表数据。我曾经一个UPDATE循环条件写错把一张线上表的字段全改成了同一个值最后靠备份恢复才没出大事。存储过程的循环操作影响面大执行前一定三思。存储过程写完后用一个小数据集先验证逻辑正确性再跑全量。比如把案例里的INSERT COUNT从100000改成100确认结果没问题再执行完整版本。5.4 什么样的场景不应该用循环循环虽好用但有些场景用循环反而事倍功半。这是我想额外强调的因为很多新人容易陷入“能用循环解决一切”的思维定式。MySQL是关系型数据库擅长的是集合操作也就是一次处理整批数据。能用一条UPDATE或INSERT INTO SELECT解决的就不要用游标逐行处理。比如把积分计算结果写入积分记录表用INSERT INTO SELECT一条语句就能完成效率比游标循环高几十倍不止。-- 集合操作一条SQL完成积分计算和写入 INSERT INTO points_log (member_id, points, calc_date, remark) SELECT member_id, FLOOR(SUM(amount) / 100) * 10, CURDATE(), 月度积分批量计算 FROM consumption WHERE consume_date 2025-01-01 AND consume_date 2025-02-01 GROUP BY member_id;这是“集合操作优先循环兜底”的经典原则。循环只用来处理结果集无法表达的逻辑比如动态条件判断、跨表逐层汇总、数据校验等场景。判断标准很简单如果这条逻辑能用一条SQL表达就不要写循环如果必须逐行判断、逐行处理才考虑游标加循环。我在实际操作中的体会是存储过程循环就像一把瑞士军刀用得好看能解决很多复杂问题用不好反而会扎手。最关键的是理解每种循环的“性格”知道它们在什么场景下发挥作用。把WHILE的“先判断”、REPEAT的“至少执行一次”、LOOP的“自由跳出”吃透再配合游标和事务控制批量数据处理这块基本就能过关了。最后分享一个小习惯每次写完存储过程我都会准备一个包含正常数据、边界数据、异常数据的小测试集先跑一遍确认边界情况不会死循环、不会报错再放到生产环境执行。这个习惯救了我很多次也推荐给你。
