Express.js调用MySQL存储过程报错：SQL语法错误（near 'NULL'）

问题详情 #

在Express API中调用自定义MySQL存储过程时，传入参数['DF1,SD24,DF4', '2024-02-07 12:04:00', '2024-02-28 12:04:00']，始终收到语法错误：

error: "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 'NULL' at line 1"

相关代码 #

存储过程代码

SET @sql := CONCAT('SELECT DTime, ', 
                    (SELECT GROUP_CONCAT(DISTINCT
                        CONCAT('MAX(CASE WHEN TagName = ''', TagName, ''' THEN Value END) AS ', TagName)
                    )
                    FROM values01
                    WHERE FIND_IN_SET(TagName, @tags)
                    ),
                    ' FROM values01
                    WHERE FIND_IN_SET(TagName, @tags)
                    AND DTime BETWEEN ? AND ?
                    GROUP BY DTime
                    ORDER BY DTime');

PREPARE stmt FROM @sql;
EXECUTE stmt USING @start_time, @end_time;
DEALLOCATE PREPARE stmt;

Express代码

try {
    // Change to req.query in axios
    const { tags, startDate, endDate } = req.query;

    const params = [tags.join(","), startDate, endDate];
    const query = "call dustint_testDB.getDataFromTags(?, ?, ?)";

    pool.execute(query, params, (err, result) => {
      if (err) {
        return next(err);
      } else {
        res.status(200).json({ response: result });
      }
    });
  } catch (err) {
    next(err);
  }

报错原因 #

1. 会话变量未绑定参数：存储过程中使用的@tags、@start_time、@end_time是MySQL会话变量，但存储过程的输入参数没有赋值给这些变量，导致动态SQL拼接时出现无效值。
2. GROUP_CONCAT返回NULL：如果values01表中没有匹配@tags的记录，GROUP_CONCAT会返回NULL，拼接后的SQL会变成SELECT DTime, NULL FROM ...，直接触发语法错误。

修复方案 #

1. 修正存储过程

将输入参数绑定到会话变量，同时用IFNULL处理GROUP_CONCAT无结果的情况：

CREATE PROCEDURE dustint_testDB.getDataFromTags(IN p_tags VARCHAR(255), IN p_start_time DATETIME, IN p_end_time DATETIME)
BEGIN
    -- 绑定输入参数到会话变量
    SET @tags = p_tags;
    SET @start_time = p_start_time;
    SET @end_time = p_end_time;

    -- 处理GROUP_CONCAT无结果的情况，避免SQL语法错误
    SET @sql := CONCAT('SELECT DTime, ', 
                        IFNULL((SELECT GROUP_CONCAT(DISTINCT
                            CONCAT('MAX(CASE WHEN TagName = ''', TagName, ''' THEN Value END) AS ', TagName)
                        )
                        FROM values01
                        WHERE FIND_IN_SET(TagName, @tags)
                        ), 'NULL AS NoMatchingTags'), -- 无匹配时返回占位列
                        ' FROM values01
                        WHERE FIND_IN_SET(TagName, @tags)
                        AND DTime BETWEEN ? AND ?
                        GROUP BY DTime
                        ORDER BY DTime');

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @start_time, @end_time;
    DEALLOCATE PREPARE stmt;
END

2. 调整Express代码

增加对tags参数类型的校验，兼容字符串和数组两种输入格式：

try {
    const { tags, startDate, endDate } = req.query;
    // 兼容tags为字符串或数组的情况
    const tagsStr = Array.isArray(tags) ? tags.join(',') : tags;
    const params = [tagsStr, startDate, endDate];
    const query = "CALL dustint_testDB.getDataFromTags(?, ?, ?)";

    pool.execute(query, params, (err, result) => {
        if (err) return next(err);
        res.status(200).json({ response: result });
    });
} catch (err) {
    next(err);
}

额外验证点 #

• 确认values01表中存在TagName为DF1、SD24、DF4的记录，否则会触发占位列返回。
• 检查MySQL用户是否拥有该存储过程的执行权限，以及values01表的读写权限。
• 确保startDate和endDate的格式符合MySQL DATETIME类型要求（如YYYY-MM-DD HH:MM:SS）。

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