Node.JS只回傳MySQL“WHEREIN”子句的一部分記錄

我正在嘗試執行以下GET功能。

http://127.0.0.1:3000/sportfolioimages/getbyportfoliolist?portfolioid_list=69,70,71

我的代碼在 Node.js 中，請查看下面的內容。

const mysql = require('mysql2');
const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');

const con = mysql.createConnection({
  host: prop.get('server.host'),
  user: prop.get("server.username"),
  password: prop.get("server.password"),
  port: prop.get("server.port"),
  database: prop.get("server.dbname")
});


exports.getSellerPortfolioItemImagesByPortfolioList = (event, context, callback) => {

  const params = event.queryStringParameters;


  if (!params || portfolioid_list == null) {
    context.callbackWaitsForEmptyEventLoop = false;
    var response = errorCodes.missing_parameters;
    callback(null, response)
  }
  else {

    const portfolioid_list = event.queryStringParameters.portfolioid_list;
    context.callbackWaitsForEmptyEventLoop = false;

    const sql = "SELECT * FROM peresia.seller_portfolio_item_images WHERE idseller_portfolio_item IN (?)";
      con.execute(sql, [portfolioid_list], function (err, result) {
      console.log(sql);
        if (err) {
          console.log(err);
          var response = errorCodes.internal_server_error;
          callback(null, response);
        }
        else {
          var response = {
            "statusCode": 200,
            "headers": {
              "Content-Type": "application/json"
            },
            "body": JSON.stringify(result),
            "isBase64Encoded": false
          };
          callback(null, response)
        }
      });


  }
};

我的代碼總是回傳位于我呼叫中的值串列的第一個值的任何值。由于我的值串列是69,70,71它始終只回傳匹配的記錄，并且即使資料庫中有69記錄也不回傳任何記錄。7071

所以舉個例子，下面是我在執行上述GET函式時得到的結果。

[
    {
        "idseller_portfolio_item_images": 22,
        "idseller_portfolio_item": 69,
        "image_url": "https://database.com/portfolio/IMG_20211020_114254-1634730049335.jpg"
    },
    {
        "idseller_portfolio_item_images": 23,
        "idseller_portfolio_item": 69,
        "image_url": "https://database.com/portfolio/IMG_20211020_114254-1634730049335.jpg"
    },
    {
        "idseller_portfolio_item_images": 31,
        "idseller_portfolio_item": 69,
        "image_url": "https://peresia3.s3.us-east-2.amazonaws.com/portfolio/IMG_20211020_114254-1634730049335.jpg"
    },
    {
        "idseller_portfolio_item_images": 32,
        "idseller_portfolio_item": 69,
        "image_url": "https://database/portfolio/IMG_20211020_114254-1634730049335.jpg"
    }
]

如果我直接在資料庫中運行 MySQL 代碼，我將能夠毫無問題地獲得完整的記錄集。

為什么會這樣，我該如何解決？

uj5u.com熱心網友回復：

根據this你將需要這樣的東西

const portfolioid_list = [69,70,71];

const sql = "SELECT * FROM peresia.seller_portfolio_item_images WHERE idseller_portfolio_item IN (?,?,?)";
      con.execute(sql, portfolioid_list, function (err, result) { ...

通過這種方式，您可以動態構建查詢。參考

const portfolioid_list = event.queryStringParameters.portfolioid_list.split(",");  //converts to a list ["69", "70", "71"]
    
portfolioidValsPlaceHolders=Array(portfolioid_list.length).fill("?").join();
const sql = "SELECT * FROM peresia.seller_portfolio_item_images WHERE idseller_portfolio_item IN (" portfolioidValsPlaceHolders ")"; 
      con.execute(sql, portfolioid_list, function (err, result) { ...    

標籤：javascript mysql 节点.js http 得到

上一篇：從行中獲取最大值并插入一個sql查詢

下一篇：記錄一次服務器被拿去挖礦的經歷