Node中使用mssql包避免多次访问SQL Server数据库的方法

我在Node.js应用中使用mssql包查询SQL Server数据库，现在有一个需求：根据从另一数据库获取的ID数组，在SL_Empsheets表中匹配对应的记录。

当前的实现是循环ID数组逐个执行SQL查询（代码如下），但因为数据库规模大且持续增长，多次访问数据库的方式效率很低。想请教如何通过mssql包传入ID数组实现批量查询，减少数据库访问次数？

当前代码示例： #

const sourceArr = [ 
  {id: 1, name: "Joe", notes_detail_id: 123}, 
  {id: 2, name: "Jane", notes_detail_id: 456}, 
  {id: 1, name: "Billy", notes_detail_id: 789} 
];

const getMatchingRecords = async function() { 
  const targetRecords = []; 
  const query = `SELECT NoteDetailsId FROM SL_Empsheets WHERE NoteDetailsId = ${record.notes_detail_id}`; 
  for (let record of sourceArr) { 
    let matchingRecord = await sqlServerQueryHandler(query); 
    if (matchingRecord) { 
      targetRecords.push(matchingRecord); 
    } 
  } 
  return targetRecords; 
};

sqlServerQueryHandler实现： #

const sql = require('mssql'); 
const config = require('./../../../configuration/sql-server-config'); 
const pool = new sql.ConnectionPool(config); 
const poolConnect = pool.connect(); 

pool.on('error', err => { 
  console.log(err); 
}); 

module.exports = async function sqlServerQueryHandler(query) { 
  try { 
    await poolConnect; 
    const request = pool.request(); 
    const result = await request.query(query); 
    return result; 
  } catch (err) { 
    console.error(err); 
  } 
};

这问题我之前做项目时也碰到过，循环挨个查不仅数据库压力大，还存在SQL注入风险（你当前代码直接把变量拼进SQL里，这真的要改！）。下面给你两种安全又高效的批量查询方案，适配不同的场景：

方案1：用参数化IN子句搞定常规规模ID数组 #

mssql本身支持给IN子句传数组参数，不用自己手动拼接字符串，既安全又省事儿。步骤很简单：

1. 先把sourceArr里的notes_detail_id提取出来去个重——毕竟查重复ID纯浪费资源
2. 写参数化的SQL，用@ids接收数组参数
3. 用mssql的request.input()传入数组，它会自动转成SQL Server能识别的格式

优化后的代码： #

首先修改getMatchingRecords，先处理ID数组：

const sourceArr = [ 
  {id: 1, name: "Joe", notes_detail_id: 123}, 
  {id: 2, name: "Jane", notes_detail_id: 456}, 
  {id: 1, name: "Billy", notes_detail_id: 789} 
];

const getMatchingRecords = async function() { 
  // 提取并去重ID，减少查询量
  const uniqueIds = [...new Set(sourceArr.map(item => item.notes_detail_id))];
  
  if (uniqueIds.length === 0) return []; // 没ID直接返回空

  // 调用批量查询方法
  const matchingRecords = await sqlServerBatchQueryHandler(uniqueIds);
  return matchingRecords;
};

然后更新你的SQL处理模块，新增批量查询函数：

const sql = require('mssql'); 
const config = require('./../../../configuration/sql-server-config'); 
const pool = new sql.ConnectionPool(config); 
const poolConnect = pool.connect(); 

pool.on('error', err => { 
  console.log(err); 
}); 

// 新增的批量查询函数
async function sqlServerBatchQueryHandler(ids) {
  try {
    await poolConnect;
    const request = pool.request();
    // 传入数组参数，指定类型为Int（根据你的字段类型调整）
    request.input('ids', sql.Int, ids);
    // 参数化查询，完全避免注入
    const result = await request.query(`
      SELECT NoteDetailsId 
      FROM SL_Empsheets 
      WHERE NoteDetailsId IN (@ids)
    `);
    return result.recordset; // 返回具体的记录数组
  } catch (err) {
    console.error('批量查询出错:', err);
    throw err; // 抛出错误让上层处理，别吞掉
  }
}

// 保留原单条查询（如果还有用到的话）
module.exports = {
  sqlServerQueryHandler: async function(query) {
    try {
      await poolConnect;
      const request = pool.request();
      const result = await request.query(query);
      return result;
    } catch (err) {
      console.error(err);
    }
  },
  sqlServerBatchQueryHandler
};

方案2：表值参数处理超大规模ID数组 #

如果你的ID数组特别大（比如超过1000个），SQL Server默认对IN子句的参数数量有限制，这时候用表值参数（Table-Valued Parameters）是更稳妥的方案：

第一步：先在SQL Server里创建自定义表类型 #

CREATE TYPE IntIdList AS TABLE (Id INT);

第二步：Node.js代码里传入表值参数 #

// 在SQL处理模块里新增这个函数
async function sqlServerTableValuedQueryHandler(ids) {
  try {
    await poolConnect;
    const request = pool.request();
    // 创建表值参数结构
    const tvp = new sql.Table();
    tvp.columns.add('Id', sql.Int); // 和SQL里的表类型字段对应
    // 把所有ID添加进去
    ids.forEach(id => tvp.rows.add(id));
    // 传入表值参数，指定类型为我们创建的IntIdList
    request.input('ids', sql.TVP('IntIdList'), tvp);
    
    // 用JOIN替代IN，效率更高
    const result = await request.query(`
      SELECT e.NoteDetailsId 
      FROM SL_Empsheets e
      JOIN @ids i ON e.NoteDetailsId = i.Id
    `);
    return result.recordset;
  } catch (err) {
    console.error('表值参数查询出错:', err);
    throw err;
  }
}

几个关键提醒： #

• 绝对不要手动拼接SQL字符串：直接拼变量进去等于给攻击者开后门，参数化查询是底线
• 一定要去重ID：重复ID只会让数据库做无用功，浪费性能
• 错误处理别偷懒：批量查询里出错要及时抛出，不然上层根本不知道问题出在哪

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