如何解决Firebase Functions中SQL报错：Too many arguments

问题 #

通过Firebase Functions搭建API，使用Postman发送请求体为{"name":"1234"}的请求时，收到401错误：SQL : Error: Too many arguments。

函数代码（index.js） #

const functions = require("firebase-functions");
const mysqlx = require("@mysql/xdevapi");

exports.getUser = functions.https.onRequest(async (req, res) => {
  const name = req.body.name;
  functions.logger.info("name: ", name);
  functions.logger.info("name type: ", typeof name);

  try {
    const session = await mysqlx.getSession({
      user: "user",
      password: "secretpassword",
      host: "hostname",
      schema: "database",
      port: 33060,
    });

    session.sql("USE database").execute().then(() => {
      const sql = "SELECT * FROM database.users WHERE user_name = ?";
      session.sql(sql).bind(1, name).execute()
          .then((result) => {
            res.status(200).send("User gathered: " + result);
          }).catch((err) => {
            functions.logger.info("sql statement: ", sql);
            functions.logger.error("err: ", err);
            res.status(401).send("SQL : " + err);
          });
    }).catch((err) => {
      functions.logger.error("err: ", err);
      res.status(402).send("Session Error:" + err);
    });
  } catch (error) {
    functions.logger.error("error: ", error);
    res.status(500)
        .send("An error occurred while fetching data from the database: " + error);
  }
});

尝试过的操作 #

• 占位符形式：试过user_name = ?和user_name = :name，前者仅当前写法不超时但报错，后者始终报错
• 参数绑定写法：
  1. .bind("name": name)（语法错误）
  2. .bind(name)
  3. .bind([name])

日志信息 #

1. functions.logger.info("name: ", name); 输出：name: 1234
2. functions.logger.info("name type: ", typeof name); 输出：name type: string
3. functions.logger.info("sql statement: ", sql); 输出：sql statement: SELECT * FROM database.users WHERE user_name = ?
4. 错误日志：

err:  Error: Too many arguments
    at SqlResultHandler.BaseHandler.<computed> (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:121:17)
    at Array.entry (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:92:29)
    at WorkQueue.process (/workspace/node_modules/@mysql/xdevapi/lib/WorkQueue.js:77:19)
    at Client.handleServerMessage (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:169:21)
    at Client.handleNetworkFragment (/workspace/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:213:14)
    at TLSSocket.<anonymous> (/workspace/node_modules/@mysql/xdevapi/lib/DevAPI/Connection.js:800:34)
    at TLSSocket.emit (node:events:513:28)
    at TLSSocket.emit (node:domain:552:15)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9) {
  info: {
    severity: 0,
    code: 5015,
    sqlState: 'HY000',
    msg: 'Too many arguments'
  }
}

解决方案 #

错误原因 #

mysqlx的bind方法参数传递错误：当SQL用?作为匿名占位符时，bind只需直接传入对应的值即可，不需要指定位置参数。原代码中.bind(1, name)传入了两个参数（位置1和值name），但SQL仅存在1个占位符，导致数据库判定参数过多。

正确绑定方式 #

针对不同占位符类型，正确写法如下：

1. 匿名占位符?：
   • 直接传值：.bind(name)
   • 数组形式传值：.bind([name])
2. 命名占位符:name：
   • 对象形式传值：.bind({ name: name })

修正后的完整代码 #

同时用async/await替代嵌套.then，避免回调地狱，补充会话关闭逻辑：

const functions = require("firebase-functions");
const mysqlx = require("@mysql/xdevapi");

exports.getUser = functions.https.onRequest(async (req, res) => {
  const name = req.body.name;
  functions.logger.info("name: ", name);
  functions.logger.info("name type: ", typeof name);

  let session;
  try {
    // 建立数据库会话
    session = await mysqlx.getSession({
      user: "user",
      password: "secretpassword",
      host: "hostname",
      schema: "database",
      port: 33060,
    });

    // 执行查询（getSession已指定schema，可省略USE语句）
    const sql = "SELECT * FROM database.users WHERE user_name = ?";
    const result = await session.sql(sql).bind(name).execute();

    // 提取实际查询数据
    const users = result.fetchAll();
    res.status(200).send("User gathered: " + JSON.stringify(users));
  } catch (err) {
    functions.logger.error("err: ", err);
    if (err.info?.msg === "Too many arguments") {
      res.status(401).send("SQL : " + err);
    } else if (session) {
      res.status(402).send("Session Error:" + err);
    } else {
      res.status(500).send("An error occurred while fetching data from the database: " + err);
    }
  } finally {
    // 确保会话关闭
    if (session) {
      await session.close();
    }
  }
});

额外说明 #

• getSession时已指定schema参数，session.sql("USE database").execute()可省略，减少多余操作
• mysqlx的execute()返回Result对象，需调用fetchAll()或fetchOne()获取实际数据，否则直接返回会显示[object Object]

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