MySQL动态SQL中GROUP_CONCAT返回NULL引发1064语法错误求助

1. 生成目标月份的完整日期序列 #

先通过CTE生成指定月份的所有日期，确保报表不会遗漏任何日期（哪怕当天无数据）：

WITH date_range AS (
    SELECT DATE_ADD('2024-10-01', INTERVAL (n-1) DAY) AS stat_date
    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
    ) nums
    WHERE DATE_ADD('2024-10-01', INTERVAL (n-1) DAY) <= LAST_DAY('2024-10-01')
)

这段代码会自动过滤掉月份不存在的日期（比如2月的30号）。

2. 修复GROUP_CONCAT的NULL语法问题 #

不要从业务表聚合后拼接统计字段，直接基于上面的日期序列生成每个日期对应的统计片段，确保GROUP_CONCAT永远不会返回NULL：

SELECT GROUP_CONCAT(
    CONCAT("SUM(CASE WHEN DATE(b.book_date) = '", stat_date, "' THEN 1 ELSE 0 END) AS `", DATE_FORMAT(stat_date, '%m-%d'), "`")
    SEPARATOR ', '
) INTO @stat_columns
FROM date_range;

如果不得不从业务表提取日期，必须用COALESCE兜底，保证返回合法的SQL片段：

SELECT COALESCE(GROUP_CONCAT(
    CONCAT("SUM(CASE WHEN DATE(b.book_date) = '", stat_date, "' THEN 1 ELSE 0 END) AS `", DATE_FORMAT(stat_date, '%m-%d'), "`")
    SEPARATOR ', '
), "0 AS `no_data`") INTO @stat_columns
FROM (SELECT DISTINCT DATE(book_date) AS stat_date FROM bookings WHERE book_date BETWEEN '2024-10-01' AND LAST_DAY('2024-10-01')) d;

3. 关联全量用户并填充空值为0 #

用左联确保所有用户都出现在报表中，空统计值自动替换为0：

SET @sql = CONCAT(
    "SELECT 
        u.Id,
        ui.UserName,
        ", @stat_columns, ",
        SUM(CASE WHEN b.status = 'approved' THEN 1 ELSE 0 END) AS total_approved
    FROM aspnetusers u
    LEFT JOIN userinfo ui ON u.Id = ui.UserId
    LEFT JOIN bookings b ON u.Id = b.UserId AND DATE(b.book_date) BETWEEN '2024-10-01' AND LAST_DAY('2024-10-01')
    GROUP BY u.Id, ui.UserName;"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

4. MariaDB兼容性调整 #

MySQL 8.0和MariaDB在该场景下语法几乎一致，只需注意两点：

• 两者都支持LAST_DAY、CTE和PREPARE/EXECUTE动态SQL语法，无需大改
• 若后续用窗口函数，两者语法兼容，直接迁移即可

额外优化 #

给bookings表建立联合索引提升查询效率：

CREATE INDEX idx_bookings_user_date ON bookings(UserId, book_date);

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