MySQL含游标存储过程调用及日期转换报错问题求助

错误根源分析 #

你遇到的报错主要来自以下几个问题：

1. 表结构不完整：原始CREATE TABLE语句未闭合，且列数与INSERT语句的字段数量不匹配，导致表结构异常，后续所有操作都会受影响。
2. 非法数值写法：存储过程中monthnr IN (01, 03, ..., 08)里的08是无效八进制数（MySQL中0开头的数值默认是八进制，八进制仅包含0-7），直接引发语法错误。
3. 字符串与数值隐式转换问题：直接用字符串类型的monthnr/daynr和数值比较，可能导致逻辑判断异常，间接触发报错。

修正步骤 #

1. 修复表结构 #

先确保表创建语句完整，与插入数据的列数匹配：

CREATE TABLE patient (
    patno VARCHAR(3), -- 患者编号（3位）
    gender VARCHAR(1), -- 性别（'M'/'F'）
    visit VARCHAR(10), -- 就诊日期（MM/DD/YYYY）
    weight INT,
    systolic INT,
    diastolic INT,
    code1 VARCHAR(1),
    code2 VARCHAR(1)
);

-- 插入数据（现在列数完全匹配）
INSERT INTO patient VALUES ('001','m','11/11/1998',88,140,80,'1','0');
INSERT INTO patient VALUES ('002','f','11/13/1998',84,120,78,'X','0');
INSERT INTO patient VALUES ('003','1','10/21/1998',68,190,100,'3','1');
INSERT INTO patient VALUES ('004','F','01/01/1999',101,200,120,'5','A');

2. 修正存储过程代码 #

调整日期判断逻辑，避免非法数值和隐式转换问题：

DELIMITER //
CREATE PROCEDURE change_date()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE patno_var VARCHAR(3);
    DECLARE visit_var VARCHAR(10);
    DECLARE daynr VARCHAR(2);
    DECLARE monthnr VARCHAR(2);
    DECLARE yearnr VARCHAR(4);
    DECLARE temp VARCHAR(2);
    DECLARE cur1 CURSOR FOR SELECT patno, visit FROM patient;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO patno_var, visit_var;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 拆分日期：优先按MM/DD/YYYY格式拆分
        SET monthnr = SUBSTRING(visit_var, 1, 2);
        SET daynr = SUBSTRING(visit_var, 4, 2);
        SET yearnr = SUBSTRING(visit_var, 7, 4);

        -- 修正日期逻辑：转成数值后再比较，避免字符串问题
        IF CAST(monthnr AS UNSIGNED) > 12 THEN 
            -- 月数大于12，交换日和月
            SET temp = monthnr;
            SET monthnr = daynr;
            SET daynr = temp;
        ELSEIF CAST(monthnr AS UNSIGNED) IN (1,3,5,7,8,10,12) AND CAST(daynr AS UNSIGNED) > 31 THEN
            -- 大月日期超过31，调整到下月
            SET daynr = CAST(daynr AS UNSIGNED) - 31;
            SET daynr = IF(daynr < 10, CONCAT('0', daynr), CAST(daynr AS CHAR));
            SET monthnr = CAST(monthnr AS UNSIGNED) + 1;
            SET monthnr = IF(monthnr < 10, CONCAT('0', monthnr), CAST(monthnr AS CHAR));
        ELSEIF CAST(monthnr AS UNSIGNED) IN (4,6,9,11) AND CAST(daynr AS UNSIGNED) > 30 THEN
            -- 小月日期超过30，调整到下月
            SET daynr = CAST(daynr AS UNSIGNED) - 30;
            SET daynr = IF(daynr < 10, CONCAT('0', daynr), CAST(daynr AS CHAR));
            SET monthnr = CAST(monthnr AS UNSIGNED) + 1;
            SET monthnr = IF(monthnr < 10, CONCAT('0', monthnr), CAST(monthnr AS CHAR));
        END IF;

        -- 重新拼接成MM/DD/YYYY格式
        SET visit_var = CONCAT(monthnr, '/', daynr, '/', yearnr);
        
        -- 更新数据（确保patno是唯一标识）
        UPDATE patient SET visit = visit_var WHERE patno = patno_var;
    END LOOP;
    CLOSE cur1;
END //
DELIMITER ;

3. 正确调用存储过程 #

现在直接执行以下语句即可正常运行：

CALL change_date();

优化建议：避免游标，批量更新 #

游标逐行处理效率极低，推荐用单条UPDATE语句批量完成日期修正：

UPDATE patient
SET visit = CASE
    -- 月数非法，交换日和月
    WHEN CAST(SUBSTRING(visit,1,2) AS UNSIGNED) > 12 THEN
        CONCAT(SUBSTRING(visit,4,2), '/', SUBSTRING(visit,1,2), '/', SUBSTRING(visit,7,4))
    -- 大月日期超31，调整到下月
    WHEN CAST(SUBSTRING(visit,1,2) AS UNSIGNED) IN (1,3,5,7,8,10,12) AND CAST(SUBSTRING(visit,4,2) AS UNSIGNED) > 31 THEN
        CONCAT(
            IF(CAST(SUBSTRING(visit,1,2) AS UNSIGNED)+1 <10, CONCAT('0',CAST(SUBSTRING(visit,1,2) AS UNSIGNED)+1), CAST(SUBSTRING(visit,1,2) AS UNSIGNED)+1),
            '/',
            IF(CAST(SUBSTRING(visit,4,2) AS UNSIGNED)-31 <10, CONCAT('0',CAST(SUBSTRING(visit,4,2) AS UNSIGNED)-31), CAST(SUBSTRING(visit,4,2) AS UNSIGNED)-31),
            '/',
            SUBSTRING(visit,7,4)
        )
    -- 小月日期超30，调整到下月
    WHEN CAST(SUBSTRING(visit,1,2) AS UNSIGNED) IN (4,6,9,11) AND CAST(SUBSTRING(visit,4,2) AS UNSIGNED) > 30 THEN
        CONCAT(
            IF(CAST(SUBSTRING(visit,1,2) AS UNSIGNED)+1 <10, CONCAT('0',CAST(SUBSTRING(visit,1,2) AS UNSIGNED)+1), CAST(SUBSTRING(visit,1,2) AS UNSIGNED)+1),
            '/',
            IF(CAST(SUBSTRING(visit,4,2) AS UNSIGNED)-30 <10, CONCAT('0',CAST(SUBSTRING(visit,4,2) AS UNSIGNED)-30), CAST(SUBSTRING(visit,4,2) AS UNSIGNED)-30),
            '/',
            SUBSTRING(visit,7,4)
        )
    -- 格式正确，无需修改
    ELSE visit
END;

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