如何将MySQL 8.0存储过程转换为MariaDB兼容版本？

问题重现 #

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH RECURSIVE t as ( select CAST(DATE_FORMAT(selectedDT ,'%Y-%m-01') as...' at line 3

核心原因 #

报错主要是两个问题：

1. phpMyAdmin默认用;作为语句分隔符，存储过程内部的;会提前终止存储过程定义，导致后续语法解析失败。
2. 原代码的日期计算逻辑可以简化，同时需确保递归CTE在MariaDB中的语法兼容性（MariaDB 10.2+支持WITH RECURSIVE）。

兼容MariaDB的修改版本 #

版本1：基于递归CTE（MariaDB 10.2+适用） #

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `stats_getbycurrmonth`(IN selectedDT DATETIME)
BEGIN
    -- 简化当月最后一天计算，无需拼接字符串
    SET @lastday = LAST_DAY(selectedDT);

    WITH RECURSIVE t AS (
        -- 直接获取当月第一天，简化类型转换
        SELECT DATE_FORMAT(selectedDT, '%Y-%m-01') AS dt
        UNION ALL
        SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) 
        FROM t 
        WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= @lastday
    )

    SELECT Y.*, R.COUNT 
    FROM (
        SELECT dt AS Days, DAYNAME(dt) AS DayName 
        FROM t
    ) Y 
    LEFT JOIN (
        SELECT COUNT(*) AS COUNT, DATE(dataora) AS DAY 
        FROM dinaromdb.loguri 
        WHERE nivel LIKE '%ERROR%' 
        GROUP BY DATE(dataora)
    ) R ON Y.Days = R.DAY 
    ORDER BY Y.Days;
END //
DELIMITER ;

版本2：基于数字生成表（兼容所有MariaDB版本） #

如果你的MariaDB版本低于10.2（不支持CTE），可以用固定数字表生成当月所有日期：

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `stats_getbycurrmonth`(IN selectedDT DATETIME)
BEGIN
    SET @first_day = DATE_FORMAT(selectedDT, '%Y-%m-01');
    SET @last_day = LAST_DAY(selectedDT);

    SELECT Y.*, R.COUNT 
    FROM (
        SELECT 
            DATE_ADD(@first_day, INTERVAL (n-1) DAY) AS Days,
            DAYNAME(DATE_ADD(@first_day, INTERVAL (n-1) DAY)) AS DayName
        FROM (
            SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
            UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
            UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
            UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
            UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
            UNION ALL SELECT 31
        ) numbers
        WHERE DATE_ADD(@first_day, INTERVAL (n-1) DAY) <= @last_day
    ) Y 
    LEFT JOIN (
        SELECT COUNT(*) AS COUNT, DATE(dataora) AS DAY 
        FROM dinaromdb.loguri 
        WHERE nivel LIKE '%ERROR%' 
        GROUP BY DATE(dataora)
    ) R ON Y.Days = R.DAY 
    ORDER BY Y.Days;
END //
DELIMITER ;

修改说明 #

• 自定义分隔符：通过DELIMITER //临时修改语句分隔符，避免存储过程内部的;提前终止定义，完成后再改回默认的;。
• 简化日期逻辑：用LAST_DAY(selectedDT)直接获取当月最后一天，比字符串拼接更高效可靠。
• 优化JOIN逻辑：将RIGHT JOIN改为LEFT JOIN，逻辑更直观（以当月所有日期为基础关联错误统计），同时移除冗余的GROUP BY。
• 性能优化：递归CTE中用UNION ALL替代UNION，避免不必要的去重操作。

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