时间戳

首页 > 解决方案 > 带有嵌套 MySQL 查询的 NodeJS 等待/异步

mysql - 带有嵌套 MySQL 查询的 NodeJS 等待/异步

问题描述

我需要有关此代码的帮助：

var sqlCheckIfExist = "SELECT my_refer FROM hub_user WHERE my_refer = '" + friendReferCode + "'";
var sqlCodeCheckSameAsMine = "SELECT my_refer FROM hub_user WHERE uid = '" + uid + "'";

async function checkIfUserCodeExist() {
  connection.promise().query(sqlCheckIfExist)
    .then(([rows, fields]) => {
    if (rows == 0) {
      console.log("Non esiste!")
      return res.send(JSON.stringify({
        "status": 500,
        "response": "codeNotExist"
      }));
    }
    checkIfCodeIsSameAsMine()
    console.log("Esiste!")
    console.log(rows[0].my_refer);
  })
    .catch(console.log)
    .then(() => connection.end());
}

async function checkIfCodeIsSameAsMine() {
  connection.promise().query(sqlCodeCheckSameAsMine)
    .then(([rows, fields]) => {
    if (rows == friendReferCode) {
      console.log("Codice uguale!")
      return res.send(JSON.stringify({
        "status": 500,
        "response": "sameCodeAsMine"
      }));
    }
    console.log("Codice non uguale!")
  })
    .catch(console.log)
    .then(() => connection.end());
}

checkIfUserCodeExist()

我正在以这种方式建立连接：

app.use(function(req, res, next) {
  global.connection = mysql.createConnection({
    host: 'xx',
    user: 'xx',
    password: 'xx',
    database: 'xx'
  });
  connection.connect();
  next();
});

我无法理解一件事：如何调用嵌套查询？当我检查 rows == 0 到 checkIfUserCodeExist() 函数时，如果它为假，我调用 checkIfCodeIsSameAsMine() 但我收到了这个错误：

Error: Can't add new command when connection is in closed state
at Connection._addCommandClosedState (/usr/myserver/node_modules/mysql2/lib/connection.js:135:17)
at Connection.end (/usr/myserver/node_modules/mysql2/lib/connection.js:836:26)
at connection.promise.query.then.catch.then (/usr/myserver/addReferFriend.js:45:31)
at <anonymous>
at process._tickCallback (internal/process/next_tick.js:188:7)

我该如何解决这个问题？

我在这里发布完整的文件：

var express = require('express');
var router = express.Router();

/* GET users listing. */
router.post('/', function(req, res, next) {
    var uid = req.body.uid;
    var friendReferCode = req.body.friendReferCode;

    var sqlCheckIfExist = "SELECT my_refer FROM hub_user WHERE my_refer = '" + friendReferCode + "'";
var sqlCodeCheckSameAsMine = "SELECT my_refer FROM hub_user WHERE uid = '" + uid + "'";
async function checkIfUserCodeExist() {
    connection.promise().query(sqlCheckIfExist)
    .then( ([rows,fields]) => {
            if (rows == 0) {
                console.log("Non esiste!")
                return res.send(JSON.stringify({"status": 500,"response": "codeNotExist"}));
            }
            checkIfCodeIsSameAsMine()
            console.log("Esiste!")
            console.log(rows[0].my_refer);
    })
    .catch(console.log)
    .then( () => connection.end());
    }

    async function checkIfCodeIsSameAsMine() {
        connection.promise().query(sqlCodeCheckSameAsMine)
        .then( ([rows,fields]) => {
                if (rows == friendReferCode) {
                    console.log("Codice uguale!")
                    return res.send(JSON.stringify({"status": 500,"response": "sameCodeAsMine"}));
                }
                console.log("Codice non uguale!")
        })
        .catch(console.log)
        .then( () => connection.end());
        }

checkIfUserCodeExist()
});

module.exports = router;

提前致谢！

标签： mysqlnode.jsasynchronous

解决方案

您的程序中有多个问题需要更新。

首先，您不能使用全局变量来存储每个请求的数据库连接。如果两个请求同时到达，那么一个请求将覆盖connection创建的其他请求，因此您可以对两个请求使用相同的连接，和/或您不关闭导致悬空连接的连接之一，在最坏的情况下，这可能会使您的应用程序无响应。

要解决该问题，您必须传递与请求对象的连接：

app.use(async function(req, res, next) {
  try {
    if( req.dbConnection ) {
      // ensure that req.dbConnection was not set already by another middleware
      throw new Error('req.dbConnection was already set')
    }

    let connection = mysql.createConnection({
      host: 'xx',
      user: 'xx',
      password: 'xx',
      database: 'xx'
    });

    res.on("finish", function() {
      // end the connection after the resonponse was send
      req.dbConnection.end()
    });

    // assign a promise base version of connection to request
    req.dbConnection = connection.promise()

    // wait for the connection to be established
    await connection.connect();
    next();
  } catch(err) {
    next(err);
  }
});

要访问每个请求定义的连接，您可以执行以下操作：

app.get('/', async function(req, res, next) {
   try {
     await checkIfUserCodeExist(req.dbConnection)

     // so something here after `checkIfUserCodeExist` finished
   }  catch(err) {
     next(err); // if an error occured pass it to the next
   }
})

asyncawait如果你await的函数体中没有 a ，那么你不需要在函数之前使用a async。

如果await函数体中没有，则需要从函数返回 Promise 链，以便调用者可以等待函数完成：

function checkIfUserCodeExist(connection) {
  return connection.query(sqlCheckIfExist)
    .then(([rows, fields]) => {
      if (rows == 0) {
        console.log("Non esiste!")

        return res.send(JSON.stringify({
          "status": 500,
          "response": "codeNotExist"
        }));
      }
      console.log("Esiste!")
      console.log(rows[0].my_refer);
      return  checkIfCodeIsSameAsMine(connection)
    })
}

function checkIfCodeIsSameAsMine(connection) {
  return connection.query(sqlCodeCheckSameAsMine)
    .then(([rows, fields]) => {
      if (rows == friendReferCode) {
        console.log("Codice uguale!")
        return res.send(JSON.stringify({
          "status": 500,
          "response": "sameCodeAsMine"
        }));
      }
      console.log("Codice non uguale!")
    })
}

如果您想使用async它，它将如下所示：

async function checkIfUserCodeExist(connection) {
  let [rows, fields] = await connection.query(sqlCheckIfExist)

  if (rows == 0) {
    console.log("Non esiste!")
    return res.send(JSON.stringify({
      "status": 500,
      "response": "codeNotExist"
    }));
  }

  await checkIfCodeIsSameAsMine()

  console.log("Esiste!")
  console.log(rows[0].my_refer);
}

async function checkIfCodeIsSameAsMine(connection) {
  let [rows, fields] = await connection.query(sqlCodeCheckSameAsMine)

  if (rows == friendReferCode) {
    console.log("Codice uguale!")
    return res.send(JSON.stringify({
      "status": 500,
      "response": "sameCodeAsMine"
    }));
  }

  console.log("Codice non uguale!")
}

你会避免这样的事情：

return res.send(JSON.stringify({
  "status": 500,
  "response": "codeNotExist"
}));

取而代之的是，您会抛出一个自定义错误，例如：

throw new CustomError(500, "codeNotExist")

并且有一个错误中间件：

app.use(function(err, req, res, next) {
  return res.send({
    "status": err.status,
    "response": err.message
  });
})

因此，您只有一个地方可以创建错误响应，并且您可以在必要时创建对该错误响应的更改，例如添加一些额外的日志记录。

编辑（以匹配更新的问题）

/* GET users listing. */
router.post('/', function(req, res, next) {
  var uid = req.body.uid;
  var friendReferCode = req.body.friendReferCode;

  var sqlCheckIfExist = "SELECT my_refer FROM hub_user WHERE my_refer = '" + friendReferCode + "'";
  var sqlCodeCheckSameAsMine = "SELECT my_refer FROM hub_user WHERE uid = '" + uid + "'";

  function checkIfUserCodeExist() {
    return req.dbConnection.query(sqlCheckIfExist)
      .then(([rows, fields]) => {
        if (rows == 0) {
          console.log("Non esiste!")

          return res.send(JSON.stringify({
            "status": 500,
            "response": "codeNotExist"
          }));
        }
        console.log("Esiste!")
        console.log(rows[0].my_refer);
        return checkIfCodeIsSameAsMine(connection)
      })
  }

  function checkIfCodeIsSameAsMine() {
    return req.dbConnection.query(sqlCodeCheckSameAsMine)
      .then(([rows, fields]) => {
        if (rows == friendReferCode) {
          console.log("Codice uguale!")
          return res.send(JSON.stringify({
            "status": 500,
            "response": "sameCodeAsMine"
          }));
        }
        console.log("Codice non uguale!")
      })
  }

  checkIfUserCodeExist()
   .catch(next)
});

推荐阅读