MySQL存储过程while循环调用内层存储过程仅首次执行无报错排查

问题核心原因 #

你遇到的循环仅执行一次的问题，90%以上的概率是会话级变量被内层存储过程意外修改导致：
你外层循环使用的@TempStart是MySQL会话级全局变量，整个连接内所有存储过程/语句都可以读写修改这个变量，如果内层innersp中存在修改这个变量的逻辑，或者innersp的参数将该变量定义为INOUT/OUT类型，就会直接修改外层循环的判断条件，导致循环提前终止。

修复方案 #

1. 优先替换为存储过程局部变量（最稳妥的修复方式） #

将外层循环变量改为存储过程内部声明的局部变量，和内层存储过程完全隔离，避免被意外修改，修改后的外层存储过程代码如下：

DELIMITER $$

USE `xxx_db`$$

DROP PROCEDURE IF EXISTS `wrapper`$$

CREATE DEFINER=`root`@`%` PROCEDURE `wrapper`(p_StartPeriod DATE, p_EndPeriod DATE)
BEGIN
    DECLARE v_TempStart DATE;
    DECLARE v_nextdate DATE;
    -- 直接用入参初始化局部循环变量，不需要额外中转变量
    SET v_TempStart = p_StartPeriod;
    
    WHILE (v_TempStart < p_EndPeriod) DO
        SET v_nextdate = DATE_ADD(v_TempStart, INTERVAL 1 DAY);
        CALL innersp(v_TempStart, v_nextdate);
        SET v_TempStart = DATE_ADD(v_TempStart, INTERVAL 1 DAY);
    END WHILE;

END$$

DELIMITER ;

局部变量仅在当前存储过程内生效，内层存储过程无法直接读写，完全避免变量被意外篡改的问题。

2. 确认内层存储过程的参数类型 #

检查innersp的定义，确认你传入的两个日期参数都是IN类型，不要定义为INOUT或者OUT，避免参数回写修改外层变量：

-- 正确的参数定义示例
CREATE PROCEDURE innersp(IN p_start DATE, IN p_end DATE)
-- 不要写成 CREATE PROCEDURE innersp(INOUT p_start DATE, IN p_end DATE)

3. 排查内层存储过程的会话变量操作 #

如果确实要保留使用@TempStart会话变量的写法，检查innersp内部所有SET @TempStart = xxx的语句，删除或修改为局部变量，避免修改外层的循环变量。

4. 增加日志排查（可选） #

如果按上述方式修改后仍有问题，可以增加临时日志确认循环执行情况：

1. 先创建日志表：

CREATE TABLE IF NOT EXISTS sp_exec_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    current_date DATE,
    remark VARCHAR(100)
);

2. 在循环中插入日志：

WHILE (v_TempStart < p_EndPeriod) DO
    INSERT INTO sp_exec_log(current_date, remark) VALUES (v_TempStart, '开始执行');
    SET v_nextdate = DATE_ADD(v_TempStart, INTERVAL 1 DAY);
    CALL innersp(v_TempStart, v_nextdate);
    INSERT INTO sp_exec_log(current_date, remark) VALUES (v_TempStart, '执行完成');
    SET v_TempStart = DATE_ADD(v_TempStart, INTERVAL 1 DAY);
END WHILE;

执行完存储过程后查询sp_exec_log表，即可确认是循环没有执行多次，还是循环执行了但内层存储过程没有插入数据，进一步定位问题。

内容的提问来源于stack exchange，提问作者SSSSS