Error: Cannot enqueue Query after invoking quit.
at Protocol._validateEnqueue (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/protocol/Protocol.js:215:16)
at Protocol._enqueue (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/protocol/Protocol.js:138:13)
at Connection.query (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/Connection.js:201:25)
at Query.<anonymous> (/Users/jabingp/Github/CodeSharing/dist/backEnd/db/DB.js:86:34)
at Query.<anonymous> (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/Connection.js:525:10)
at Query._callback (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/Connection.js:491:16)
at Query.Sequence.end (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at Query._handleFinalResultPacket (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/protocol/sequences/Query.js:139:8)
at Query.OkPacket (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/protocol/sequences/Query.js:72:10)
at Protocol._parsePacket (/Users/jabingp/Github/CodeSharing/node_modules/mysql/lib/protocol/Protocol.js:291:23)
出錯(cuò)代碼如下
updateDatas(datas:Array<any>){
this.connect();
console.log("要修改的數(shù)據(jù)：")
console.log(datas);
for(let data of datas){
this.connection.query(`update test set name = "${data.name}" where id = ${data.id}`,(error, results) =>{
if (error) throw error;
console.log(results);
this.connection.query('SELECT * from test', function (error, results, fields) {
if (error) throw error;
console.log("查詢結(jié)果是");
console.log(results);
});
});
}
this.end();
}
這段代碼我想完成的事情是每次更新后都查詢所有的值沦偎，輸出一下每個(gè)值的當(dāng)前狀態(tài)搅吁，然后執(zhí)行完所有查詢就把數(shù)據(jù)庫(kù)關(guān)閉。
解決方案
從錯(cuò)誤中可以看出來(lái)大概是數(shù)據(jù)庫(kù)以及關(guān)閉了，但代碼卻執(zhí)行了數(shù)據(jù)庫(kù)操作御铃，我們把最下面的this.end()去掉，代碼就成功執(zhí)行了沈矿，或者在代碼運(yùn)行的其他階段執(zhí)行this.end()上真，避免數(shù)據(jù)庫(kù)連接關(guān)閉的情況下執(zhí)行數(shù)據(jù)庫(kù)查詢操作。
執(zhí)行結(jié)果
更改數(shù)據(jù)
要修改的數(shù)據(jù)：
[ { name: 'change Hello World again', id: 4 },
{ name: 'change Hellow JabinGP again', id: 5 } ]
connected as id 285992
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '(Rows matched: 1 Changed: 0 Warnings: 0',
protocol41: true,
changedRows: 0 }
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '(Rows matched: 1 Changed: 0 Warnings: 0',
protocol41: true,
changedRows: 0 }
查詢結(jié)果是
[ RowDataPacket { id: 4, name: 'change Hello World again' },
RowDataPacket { id: 5, name: 'change Hellow JabinGP again' } ]
查詢結(jié)果是
[ RowDataPacket { id: 4, name: 'change Hello World again' },
RowDataPacket { id: 5, name: 'change Hellow JabinGP again' } ]
結(jié)果并不是預(yù)想的輸出一個(gè)插入結(jié)果后輸出一個(gè)查詢結(jié)果羹膳，而是兩個(gè)插入結(jié)果先輸出后輸出兩個(gè)查詢結(jié)果睡互，為什么？下面進(jìn)行個(gè)人的推測(cè)分析陵像。
原因分析
錯(cuò)誤分析
我在前面的文章http://www.reibang.com/p/59fda67a868e
提到過(guò)就珠，connection關(guān)閉時(shí)并不會(huì)直接關(guān)閉，而是會(huì)將任務(wù)隊(duì)列中的任務(wù)都執(zhí)行完才關(guān)閉醒颖，而回調(diào)是任務(wù)執(zhí)行成功或者失敗后調(diào)用的妻怎，關(guān)閉連接時(shí)的確會(huì)檢查任務(wù)隊(duì)列，但是回調(diào)函數(shù)不在任務(wù)隊(duì)列中泞歉，也就是說(shuō)回調(diào)函數(shù)里面的:
this.connection.query('SELECT * from test', function (error, results, fields) {
if (error) throw error;
console.log("查詢結(jié)果是");
console.log(results);
});
這個(gè)部分不會(huì)被識(shí)別為任務(wù)隊(duì)列逼侦，所以系統(tǒng)并不會(huì)知道這個(gè)任務(wù)是要再關(guān)閉數(shù)據(jù)庫(kù)之前執(zhí)行的，系統(tǒng)僅僅只是確保了循環(huán)中的"insert xxx" 會(huì)在被關(guān)閉連接前執(zhí)行腰耙，但不保證回調(diào)中"select * xxx"會(huì)在關(guān)閉連接前執(zhí)行榛丢，于是在執(zhí)行這個(gè)語(yǔ)句之前數(shù)據(jù)庫(kù)連接就關(guān)閉了，導(dǎo)致報(bào)錯(cuò)挺庞。
結(jié)果為何不理想
由于for循環(huán)執(zhí)行極快（對(duì)比使用網(wǎng)絡(luò)請(qǐng)求數(shù)據(jù)庫(kù)）晰赞，兩個(gè)插入操作幾乎是同時(shí)進(jìn)入任務(wù)隊(duì)列然后先后執(zhí)行，執(zhí)行兩個(gè)插入操作后此時(shí)任務(wù)隊(duì)列為空挠阁，不執(zhí)行任何數(shù)據(jù)庫(kù)操作宾肺，等待插入操作成功后，輸出完兩個(gè)的結(jié)果侵俗，代碼才執(zhí)行到查詢操作锨用，查詢操作才進(jìn)入任務(wù)隊(duì)列，隨后才回調(diào)輸出查詢結(jié)果隘谣，所以是兩個(gè)插入先依次輸出增拥，兩個(gè)查詢后依次輸出啄巧。
驗(yàn)證分析
因?yàn)閚ode中的mysql是異步的，但是有任務(wù)隊(duì)列機(jī)制掌栅，我們將查詢部分的代碼拉出回調(diào)秩仆，改為和插入并列，這樣由于隊(duì)列機(jī)制猾封，執(zhí)行上也是有先后順序的澄耍，所以能達(dá)到我想要的效果：
updateDatas(datas:Array<any>){
this.connect();
console.log("要修改的數(shù)據(jù)：")
console.log(datas);
for(let data of datas){
this.connection.query(`update test set name = "${data.name}" where id = ${data.id}`,(error, results) =>{
if (error) throw error;
console.log(results);
});
this.connection.query('SELECT * from test', (error, results, fields)=> {
if (error) throw error;
console.log("查詢結(jié)果是");
console.log(results);
});
}
this.end();
}
執(zhí)行結(jié)果：
更改數(shù)據(jù)
要修改的數(shù)據(jù)：
[ { name: 'change Hello World again', id: 4 },
{ name: 'change Hellow JabinGP again', id: 5 } ]
connected as id 285986
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '(Rows matched: 1 Changed: 0 Warnings: 0',
protocol41: true,
changedRows: 0 }
查詢結(jié)果是
[ RowDataPacket { id: 4, name: 'change Hello World again' },
RowDataPacket { id: 5, name: 'change Hellow JabinGP again' } ]
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '(Rows matched: 1 Changed: 0 Warnings: 0',
protocol41: true,
changedRows: 0 }
查詢結(jié)果是
[ RowDataPacket { id: 4, name: 'change Hello World again' },
RowDataPacket { id: 5, name: 'change Hellow JabinGP again' } ]
