Node.js mysql流查询被触发两次或更多次

Question

我有一个通过SSH连接到mysql数据库的应用程序，当我想添加一块硬件(终端)到我的mysql数据库。下面是相应的函数:

const SSHConnection = new Promise(async (resolve, reject) => {
sshClient.on('ready', async () => {
sshClient.forwardOut(
forwardConfig.srcHost,
forwardConfig.srcPort,
forwardConfig.dstHost,
forwardConfig.dstPort,
(err, stream) async => {
if (err) reject(err);

// create a new DB server object including stream
const updatedDbServer = {
...dbServer,
stream
};
// connect to mysql
const connection = mysql.createConnection(updatedDbServer);
// check for successful connection
//  resolve or reject the Promise accordingly          
connection.connect(async (error) => {
if (error) {
reject(error);
}
let query = await connection.promise().query("INSERT INTO `mandator_1`.`terminal` (`serialNumber`, `name`, `isActive`, `status`, `profileId`, `businessId`, `divisionId`, `ipAddress`, `groupId`, `setupVersion`, `macAddress`) VALUES ('" + terminal.serial + "', '" + terminal.serial + "', '1', NULL, '1', '1', NULL, '172.45.17.197', '1', NULL, '" + terminal.macAddress + "');");
let updateTerminal = await connection.promise().query("UPDATE `terminal` SET `status` = UNIX_TIMESTAMP(NOW()) * 1000,  `setupVersion` = '005', `hardwareType` = '10', `update` = 0, `lastUpdated` = UNIX_TIMESTAMP(NOW()) * 1000, `dataReceived` = UNIX_TIMESTAMP(NOW()) * 1000 WHERE `setupVersion` IS NULL AND `hardwareType` IS NULL AND `lastUpdated` IS NULL AND `dataReceived` IS NULL AND `serialNumber` = '"+ terminal.serial +"'");
resolve(connection);
});
});
}).connect(tunnelConfig);
});

如果我现在删除终端，并添加另一个终端，第一个终端也会再次添加。我认为这与流/数据库连接有关。也许在增加第一个终端后需要关闭或清理?

Answer 1

一般情况下，不建议将连接保持打开状态，除了类似终端的应用程序。您应该通过执行connection.end()确保在应用程序关闭后关闭连接，以防止资源泄漏。关闭连接将有效地断开第一个终端与数据库的连接，并应解决您的问题。参考链接:什么时候使用node-mysql关闭MySQL连接?

Answer 2

是的，在表格数据库中，每次你想要通信时打开和关闭连接是很重要的:

const SSHConnection = new Promise(async (resolve, reject) => {
sshClient.on('ready', async () => {
sshClient.forwardOut(
forwardConfig.srcHost,
forwardConfig.srcPort,
forwardConfig.dstHost,
forwardConfig.dstPort,
(err, stream) async => {
if (err) reject(err);

// create a new DB server object including stream
const updatedDbServer = {
...dbServer,
stream
};
// connect to mysql
const connection = mysql.createConnection(updatedDbServer);
// check for successful connection
//  resolve or reject the Promise accordingly          
connection.connect(async (error) => {
if (error) {
reject(error);
}
let query = await connection.promise().query("INSERT INTO `mandator_1`.`terminal` (`serialNumber`, `name`, `isActive`, `status`, `profileId`, `businessId`, `divisionId`, `ipAddress`, `groupId`, `setupVersion`, `macAddress`) VALUES ('" + terminal.serial + "', '" + terminal.serial + "', '1', NULL, '1', '1', NULL, '172.45.17.197', '1', NULL, '" + terminal.macAddress + "');");
let updateTerminal = await connection.promise().query("UPDATE `terminal` SET `status` = UNIX_TIMESTAMP(NOW()) * 1000,  `setupVersion` = '005', `hardwareType` = '10', `update` = 0, `lastUpdated` = UNIX_TIMESTAMP(NOW()) * 1000, `dataReceived` = UNIX_TIMESTAMP(NOW()) * 1000 WHERE `setupVersion` IS NULL AND `hardwareType` IS NULL AND `lastUpdated` IS NULL AND `dataReceived` IS NULL AND `serialNumber` = '"+ terminal.serial +"'");
openConnection();
resolve(connection);
});
});
}).connect(tunnelConfig);
closeConnection();
});
openConnection(){
//Define the Connection let's say you're variable for connection is connect you can also try checks with it for error prevention 
connect.open(); 
}
closeConnection(){
//Define the Connection let's say you're variable for connection is connect
connect.close(); 
}