mysql - 我在哪里关闭 Node mysql 中的连接？

Question

这是我的代码:

var set = {
                FIRST_NAME: professor.value_20,
                SURNAME: professor.value_21
            }
            //logs
            //console.log(set);
            //executes query
            connection.query({
                sql: 'SELECT TEACHERID FROM TEACHER WHERE FIRST_NAME = ? AND SURNAME = ?',
                //'INSERT INTO TEACHER set ? ON DUPLICATE KEY UPDATE FIRST_NAME = ?, SURNAME = ?',
                values: [set.FIRST_NAME,set.SURNAME]
            }, (err, results, fields) => {
                if (err) throw err;

                if(results.length < 0) {
                    connection.query({
                        sql: 'INSERT INTO TEACHER set ?',
                        values: [set]
                    }, (err, results, fields) => {
                        if (err) throw err;

                    });
                }
                else {
                    connection.query({
                        sql: 'UPDATE TEACHER (FIRST_NAME, SURNAME) VALUES(?,?)',
                        values: [set.FIRST_NAME, set.SURNAME]

                    }, (err, results, fields) => {
                        if (err) throw err;
                        connection.end();
                    });
                }

            });

当我运行此代码时，出现以下错误:

Error: Cannot enqueue Query after invoking quit.

问题似乎发生在第三个查询的级别，尽管我没有提前关闭连接。我该如何解决这个问题？

另一个问题:在这种情况下使用池而不是直接连接更好吗？

谢谢!

Answer 1

How do I fix this?

您必须先删除 connection.end() 或重新建立连接。

var connection = mysql.createConnection(config); //establish new connection
connection.query({
  sql: 'SELECT TEACHERID FROM TEACHER WHERE FIRST_NAME = ? AND SURNAME = ?',
  //'INSERT INTO TEACHER set ? ON DUPLICATE KEY UPDATE FIRST_NAME = ?, SURNAME = ?',
  values: [set.FIRST_NAME, set.SURNAME]
}, (err, results, fields) => {
  if (err) throw err;

  if (results.length < 0) {
    connection.query({
      sql: 'INSERT INTO TEACHER set ?',
      values: [set]
    }, (err, results, fields) => {
      if (err) throw err;

    });
  } else {
    connection.query({
      sql: 'UPDATE TEACHER (FIRST_NAME, SURNAME) VALUES(?,?)',
      values: [set.FIRST_NAME, set.SURNAME]

    }, (err, results, fields) => {
      if (err) throw err;
      connection.end(); //or just remove this line
    });
  }

});

我不建议进行上述修复。最好的解决方案是使用池连接。

pool.getConnection(function(er,connection){
    if(er){
      throw er;
    }
    connection.query({
      sql: 'SELECT TEACHERID FROM TEACHER WHERE FIRST_NAME = ? AND SURNAME = ?',
      //'INSERT INTO TEACHER set ? ON DUPLICATE KEY UPDATE FIRST_NAME = ?, SURNAME = ?',
      values: [set.FIRST_NAME, set.SURNAME]
    }, (err, results, fields) => {
      if (err) throw err;

      if (results.length < 0) {
        connection.query({
          sql: 'INSERT INTO TEACHER set ?',
          values: [set]
        }, (err, results, fields) => {
          if (err) throw err;

        });
      } else {
        connection.query({
          sql: 'UPDATE TEACHER (FIRST_NAME, SURNAME) VALUES(?,?)',
          values: [set.FIRST_NAME, set.SURNAME]

        }, (err, results, fields) => {
          if (err) throw err;
        });
      }

    });
});

此方法将消除在每个查询中建立新连接的开销。