mysql - 在 for 循环 MySQL Node.js 内切换数据库，它的切换速度不够快/它们重叠？

Question

只是在 Node.js 中的数据库（都托管在同一连接上）之间切换时出现问题。

这是我的代码：

Db.js 设置（可能不重要，但认为应该包括在内）：

const mysql = require('mysql');
let assure_api;
const dbSettings = require('./settings.json').db_settings

function connectMainDB() {
    if (!assure_api) {
        assure_api = mysql.createConnection({
            host: dbSettings.assure_api_settings.host,
            user: dbSettings.user,
            password: dbSettings.password,
            database:  dbSettings.assure_api_settings.name,
            port: dbSettings.assure_api_settings.port
        });

        assure_api.connect(function(err){
            if(!err) {
                console.log('Main DB is connected!');
            } else {
                console.log('Error connecting database!');

            }
        });
    }
    return assure_api;
}

function connectDbs() {

    let dbHosts;
    let dbConnections = {}
    assure_api.query("SELECT DISTINCT wh_db_host FROM license", (err, result) => {
    if(err){
        console.log(err)
    }
    else {
        dbHosts = result;
        for(i=0; i < dbHosts.length; i++){
            let db = mysql.createConnection({
                host: dbHosts[i].wh_db_host,
                user: dbSettings.user,
                password: dbSettings.password
            })

            let dbName = dbHosts[i].wh_db_host
            console.log('Setup connection for ', dbName)


            dbConnections[dbHosts[i].wh_db_host] = db
        }
    }
})
   return dbConnections; 
}

function createTenantTable() {

    let tenantTable = {}
    assure_api.query("SELECT client_id, wh_db_host FROM license", (err, result) => {
        if(err){
            console.log(err)
        }
        else { 
            for(i=0; i < result.length; i++){
                tenantTable[`${result[i].client_id}`] = result[i].wh_db_host
            }
        }
    })
    return tenantTable;
}





module.exports = {
    assure_api: connectMainDB(),
    dbConnections: connectDbs(),
    tenantTable: createTenantTable()
}

路线：

app.get('/getMatModes', function(req, res){

    assure_api.query("SELECT client_id FROM license", (err, result) => {
        if(err){
            throw err;
        }
        else {
            let schools = result;
            let temp = schools
            schools = [];


            for(let i=0; i < temp.length; i++){
                schools.push(temp[i].client_id)
            }


            let matModes = [];

            async.forEachOf(schools, function(dataElement, i, inner_callback){
                let query = `SELECT parameter_value FROM parameter_data where parameter_name='maintenance_mode'`
                let schoolCode = dataElement;
                dbConnections[tenantTable[`${schoolCode}`]].query(`USE ${schoolCode}`, () => {

/*this is the line trying to debug which database is being looked at, because the values being 
returned in the query below were just simply wrong.*/
                    dbConnections[tenantTable[`${schoolCode}`]].query(`SELECT Database()`, (err, result) => {
                        console.log(result[0])
                    })
                        dbConnections[tenantTable[`${schoolCode}`]].query(query, (err, result) => {
                            if(err){
                                inner_callback(err)
                            }
                            else {
                                matModes.push(result, schoolCode)
                                inner_callback(null)
                            }



                        })
                    })

            },
            function(err){
                if(err){
                    throw err;
                }
                else{
                    console.log(matModes)
                }
            }
            )







        }
    })




})

你会期望控制台的输出是：

RowDataPacket { 'Database()': 'new' }
RowDataPacket { 'Database()': 'joeys' }
RowDataPacket { 'Database()': 'rvw' }
RowDataPacket { 'Database()': 'sfx' }
RowDataPacket { 'Database()': 'tss' }
[
  [ RowDataPacket { parameter_value: '1' } ],
  'new',
  [ RowDataPacket { parameter_value: '1' } ],
  'joeys',
  [ RowDataPacket { parameter_value: '0' } ],
  'rvw',
  [ RowDataPacket { parameter_value: '0' } ],
  'sfx',
  [ RowDataPacket { parameter_value: '1' } ],
  'tss'
]

但是输出是这样的：

RowDataPacket { 'Database()': 'new' }
RowDataPacket { 'Database()': 'tss' }
RowDataPacket { 'Database()': 'tss' }
RowDataPacket { 'Database()': 'tss' }
RowDataPacket { 'Database()': 'tss' }
[
  [ RowDataPacket { parameter_value: '1' } ],
  'new',
  [ RowDataPacket { parameter_value: '1' } ],
  'joeys',
  [ RowDataPacket { parameter_value: '1' } ],
  'rvw',
  [ RowDataPacket { parameter_value: '1' } ],
  'sfx',
  [ RowDataPacket { parameter_value: '1' } ],
  'tss'
]

以上只是它随机选择的众多变体之一，始终能够进行第一次切换，但后续切换无论成功与否都是随机的。我到底该如何修复这个错误。我现在已经花了好几天的时间，我快疯了。有人请帮忙。

编辑：截至 2020 年 5 月 27 日，我有一个临时工作，但仍在寻找解决此问题的原因和解决方法。

标签： mysqlnode.jsexpress