Node.js服务器中MySQL多块查询执行失败问题求助

原始数据结构：

目标展示效果：

我写的SQL语句在Toad中能正常运行并返回数据，但在Node.js中执行时卡在了INTO @sql步骤：

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS `',
      itemname, '`'
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

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

数据表结构：

DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);

Node.js代码：

const sql = require('../config/db')

exports.getreportsheet = (req, res) => {
  let sqlstring = `SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = '''',
      itemname,
      '''' then itemvalue end),0) AS `',
      itemname, '`'
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt `
sql.query(sqlstring,
        ['Page', 20],
        function (err, results) {
            console.log('Loading data', results, sqlstring);
            return res.status(200).json({
                data: results
            })
        }
    );
};

解决方案 #

核心原因 #

Node.js的MySQL驱动默认禁用多语句查询（一个query调用执行多个分号分隔的SQL语句），同时原代码存在SQL字符串转义错误、无用参数传递问题，导致执行异常。

方案一：启用多语句支持并修正代码 #

1. 修改数据库连接配置
   在../config/db的连接配置中添加multipleStatements: true，允许一次执行多个SQL语句：

   const mysql = require('mysql');
   const connection = mysql.createConnection({
     host: '你的数据库地址',
     user: '用户名',
     password: '密码',
     database: '数据库名',
     multipleStatements: true // 新增该配置
   });
   module.exports = connection;
   

2. 修正SQL字符串转义
   调整模板字符串中的单引号和反引号转义，避免语法错误：

   let sqlstring = `SELECT
     GROUP_CONCAT(DISTINCT
       CONCAT(
         'ifnull(SUM(case when itemname = ''',
         itemname,
         ''' then itemvalue end),0) AS \`',
         itemname, '\`'
       )
     ) INTO @sql
   FROM
     history;
   SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                     FROM history 
                      GROUP BY hostid');
   
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;`;
   

3. 移除无用参数并处理结果
   原代码中['Page', 20]无对应占位符，需删除；多语句执行的结果是数组，取最后一个元素（EXECUTE stmt的返回数据）：

   sql.query(sqlstring, function (err, results) {
       if (err) {
           console.error('查询错误:', err);
           return res.status(500).json({ error: err.message });
       }
       // 多语句执行结果数组中，第三个元素是EXECUTE的返回数据
       return res.status(200).json({
           data: results[2]
       });
   });
   

方案二：分两次查询（更安全） #

如果不想启用多语句支持，可拆分查询步骤，避免会话变量传递问题：

exports.getreportsheet = (req, res) => {
  // 第一步：生成动态列的SQL片段
  const getDynamicColsSql = `SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'ifnull(SUM(case when itemname = ''', itemname, ''' then itemvalue end),0) AS \`', itemname, '\`'
  )) AS dynamicColumns FROM history`;

  sql.query(getDynamicColsSql, (err, colsResult) => {
    if (err) {
      console.error('获取动态列失败:', err);
      return res.status(500).json({ error: err.message });
    }
    const dynamicColumns = colsResult[0].dynamicColumns;
    // 第二步：执行最终的Pivot查询
    const finalSql = `SELECT hostid, ${dynamicColumns} FROM history GROUP BY hostid`;
    sql.query(finalSql, (err, dataResult) => {
      if (err) {
        console.error('查询数据失败:', err);
        return res.status(500).json({ error: err.message });
      }
      return res.status(200).json({ data: dataResult });
    });
  });
};

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