MySQL 8.1插入多CASE语句触发模糊1064语法错误求助

问题说明 #

在MySQL 8.1中创建存储过程SP_Populate_Tables时，执行包含多个CASE语句的INSERT语句触发语法错误，错误提示：

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 67

错误仅标注位置，未说明具体问题点。单独使用单个CASE语句时存储过程可正常运行；添加DELIMITER语句会直接触发错误。已尝试重新格式化代码、更换变量名、调整变量的@符号前缀等操作，均未解决问题。

报错的完整存储过程代码 #

CREATE PROCEDURE SP_Populate_Tables()
BEGIN
    /* Calendar Load */
    -- Variables Declaration
    SET @StartDate = '2020-01-01';
    SET @EndDate = '2030-12-31';
    SET @PayPeriodStartDate = '2021-12-26';
    SET @PayPeriodEndDate = '2022-01-08';

    -- Populate Calendar Table
    TRUNCATE TABLE Calendar;

    -- Loop to populate Calendar Table
    calendar_loop: WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar (
            `Date`,
            `Month`,
            `Day`,
            `Year`,
            `Month_Name`,
            `Month_Year`,
            `Month_Year_Sort`,
            `Month_Short`,
            `Month_Short_Year`,
            `Quarter_No`,
            `Quarter_Desc`,
            `Week_Num`,
            `Start_Of_Month`,
            `Start_Of_Week`,
            `End_Of_Week`,
            `Pay_Period`,
            `Pay_Period_Start`,
            `Pay_Period_End`,
            `Pay_Period_Pay_Date`
        )
        VALUES (
            @StartDate,
            EXTRACT(MONTH FROM @StartDate),
            EXTRACT(DAY FROM @StartDate),
            EXTRACT(YEAR FROM @StartDate),
            MONTHNAME(@StartDate),
            CONCAT(MONTHNAME(@StartDate), ' ', EXTRACT(YEAR FROM @StartDate)),
            CONCAT(EXTRACT(YEAR FROM @StartDate), LPAD(EXTRACT(MONTH FROM @StartDate), 2, '0')),
            LEFT(MONTHNAME(@StartDate), 3),
            CONCAT(LEFT(MONTHNAME(@StartDate), 3), ' ', EXTRACT(YEAR FROM @StartDate)),
            QUARTER(@StartDate),
            CONCAT('Q', QUARTER(@StartDate)),
            WEEK(@StartDate),
            LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY,
            DATE_SUB(@StartDate, INTERVAL DAYOFWEEK(@StartDate) - 1 DAY),
            DATE_ADD(@StartDate, INTERVAL 7 - DAYOFWEEK(@StartDate) DAY),
            CASE
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE (DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14) + 1
            END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(@PayPeriodStartDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY), INTERVAL 6 DAY)
            END
        );
        
        SET @StartDate = @StartDate + INTERVAL 1 DAY;
    END WHILE calendar_loop;

END

单个CASE语句可正常运行的代码示例（Pay_Period） #

CREATE PROCEDURE SP_Populate_Tables()
BEGIN
    /* Calendar Load */
    -- Variables Declaration
    SET @StartDate = '2020-01-01';
    SET @EndDate = '2030-12-31';
    SET @PayPeriodStartDate = '2021-12-26';
    SET @PayPeriodEndDate = '2022-01-08';

    -- Populate Calendar Table
    TRUNCATE TABLE Calendar;

    -- Loop to populate Calendar Table
    calendar_loop: WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar (
            `Date`,
            `Month`,
            `Day`,
            `Year`,
            `Month_Name`,
            `Month_Year`,
            `Month_Year_Sort`,
            `Month_Short`,
            `Month_Short_Year`,
            `Quarter_No`,
            `Quarter_Desc`,
            `Week_Num`,
            `Start_Of_Month`,
            `Start_Of_Week`,
            `End_Of_Week`,
            `Pay_Period`
            -- `Pay_Period_Start`,
            -- `Pay_Period_End`,
            -- `Pay_Period_Pay_Date`
        )
        VALUES (
            @StartDate,
            EXTRACT(MONTH FROM @StartDate),
            EXTRACT(DAY FROM @StartDate),
            EXTRACT(YEAR FROM @StartDate),
            MONTHNAME(@StartDate),
            CONCAT(MONTHNAME(@StartDate), ' ', EXTRACT(YEAR FROM @StartDate)),
            CONCAT(EXTRACT(YEAR FROM @StartDate), LPAD(EXTRACT(MONTH FROM @StartDate), 2, '0')),
            LEFT(MONTHNAME(@StartDate), 3),
            CONCAT(LEFT(MONTHNAME(@StartDate), 3), ' ', EXTRACT(YEAR FROM @StartDate)),
            QUARTER(@StartDate),
            CONCAT('Q', QUARTER(@StartDate)),
            WEEK(@StartDate),
            LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY,
            DATE_SUB(@StartDate, INTERVAL DAYOFWEEK(@StartDate) - 1 DAY),
            DATE_ADD(@StartDate, INTERVAL 7 - DAYOFWEEK(@StartDate) DAY),
            CASE
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE (DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14) + 1
            END
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(@PayPeriodStartDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            -- END,
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            -- END,
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY), INTERVAL 6 DAY)
            -- END
        );
        
        SET @StartDate = @StartDate + INTERVAL 1 DAY;
    END WHILE calendar_loop;

END

单个CASE语句可正常运行的代码片段（Pay_Period_End） #

calendar_loop: WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar (
            `Date`,
            `Month`,
            `Day`,
            `Year`,
            `Month_Name`,
            `Month_Year`,
            `Month_Year_Sort`,
            `Month_Short`,
            `Month_Short_Year`,
            `Quarter_No`,
            `Quarter_Desc`,
            `Week_Num`,
            `Start_Of_Month`,
            `Start_Of_Week`,
            `End_Of_Week`,
            -- `Pay_Period`,
            -- `Pay_Period_Start`,
            `Pay_Period_End`
            -- `Pay_Period_Pay_Date`
        )
        VALUES (
            @StartDate,
            EXTRACT(MONTH FROM @StartDate),
            EXTRACT(DAY FROM @StartDate),
            EXTRACT(YEAR FROM @StartDate),
            MONTHNAME(@StartDate),
            CONCAT(MONTHNAME(@StartDate), ' ', EXTRACT(YEAR FROM @StartDate)),
            CONCAT(EXTRACT(YEAR FROM @StartDate), LPAD(EXTRACT(MONTH FROM @StartDate), 2, '0')),
            LEFT(MONTHNAME(@StartDate), 3),
            CONCAT(LEFT(MONTHNAME(@StartDate), 3), ' ', EXTRACT(YEAR FROM @StartDate)),
            QUARTER(@StartDate),
            CONCAT('Q', QUARTER(@StartDate)),
            WEEK(@StartDate),
            LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY,
            DATE_SUB(@StartDate, INTERVAL DAYOFWEEK(@StartDate) - 1 DAY),
            DATE_ADD(@StartDate, INTERVAL 7 - DAYOFWEEK(@StartDate) DAY),
            -- CASE
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE (DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14) + 1
            -- END,
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(@PayPeriodStartDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            -- END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            END
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY), INTERVAL 6 DAY)
            -- END
        );
        
        SET @StartDate = @StartDate + INTERVAL 1 DAY;
    END WHILE calendar_loop;

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