A complete example of mysql querying batch data from one table and inserting it into another table

Say it in advance

Nodejs reads the database as an asynchronous operation, so the code will continue to execute before the database has read the data.
When I was writing something recently, I needed to query the database for batch data and insert it into another table.
When it comes to batch operations, the for loop is the easiest thing to think of.

Wrong version of for loop

First release the code, explain in advance, here is the encapsulation of sql operation: sql.sever (database connection pool, sql statement splicing function, callback function)

for(let i=0;i<views.xuehao.length;i++){
	sql.sever(pool,sql.select(["name"],"registryinformation",["xuehao="+sql.escape(views.xuehao[i])]),function(data){
  sql.sever(pool,sql.insert("personnelqueue",["xuehao","name","selfgroup","time"],[sql.escape(views.xuehao[i]),data[0].name,selfgroup,'NOW()'],true),function(){
  let allGroup = ['Android', 'ios', 'Web', 'Backend', 'Product']; //Here is the email related code let group = allGroup[selfgroup - 1];
  let mailmsg = "Hello," + group + "The group approval list has been submitted, please review it as soon as possible!";
  mail.mailepass(mailmsg);
  res.write(JSON.stringify({
   style:1,
   msg:"The list has been submitted and is awaiting review by the administrator!"
  }));
  res.end();
  })
 })
}

In the above code, data query is performed first and then data insertion is performed (here we assume that there are 2 data). According to common sense, the execution order we want is: query insert query insert. However, it is not as simple as we think. Although the insert operation is indeed written in the callback of the database query, the actual order is: query query. Once two queries are performed directly, the subsequent code will report an error. Before the callback can be made, the second loop has already been executed.

Improved for loop version

mysql can complete the query and insertion with one statement, the format is: INSERT IGNORE INTO insert table table name (item1, item2) SELECT item1, item2 FROM query table table name WHERE, so I thought of the following solution.

for (let i = 0; i < views.xuehao.length; i++) {
 sql.sever(pool, 'INSERT IGNORE INTO personnelqueue (xuehao,name,selfgroup,time) SELECT xuehao,name,selfgroup,NOW() FROM registryinformation WHERE xuehao=' + sql.escape(views.xuehao[i]) + ' and pass=' + state, function () {
  if (i == views.xuehao.length - 1) {
   let allGroup = ['Android', 'ios', 'Web', 'Backend', 'Product'];
   let group = allGroup[selfgroup - 1];
   let mailmsg = "Hello," + group + "The group approval list has been submitted, please review it as soon as possible!";
   mail.mailepass(mailmsg);
   res.write(JSON.stringify({
    style: 1,
    msg: "The list has been submitted and is awaiting review by the administrator!"
   }));
   res.end();
  }
 })
} 

In this way, the database operates correctly and the purpose is achieved. But if you think about it carefully, this approach still has flaws. This is fine if the amount of data is small, but if the amount of data is large, this will cause the program to establish multiple connections with the database, increasing the server load.

Improved version

Combined with the previous defect, as the name implies, this time we need to reduce the number of times the program connects to the database. Therefore, we no longer write insertion and query together, but separate them and perform batch insertion and query, so as to use the queried data for batch insertion. The code is as follows:

let sqlString = 'SELECT xuehao,name,selfgroup FROM registryinformation WHERE pass=' + state + ' AND (xuehao=' + sql.escape(views.xuehao[0]);
for (let i = 1; i < views.xuehao.length; i++) {
 sqlString += ' OR xuehao=' + sql.escape(views.xuehao[i]);
}
sqlString = sqlString + ')';
sql.sever(pool, sqlString, function (data) {
 //Splicing and inserting SQL statements let istSqlStr = 'INSERT IGNORE INTO personnelqueue (xuehao,name,selfgroup,time) VALUES (' + data[0].xuehao + ',' + sql.escape(data[0].name) + ',' + data[0].selfgroup + ',NOW())';
 for (let j = 1; j < data.length; j++) {
 istSqlStr += ',(' + data[j].xuehao + ',' + sql.escape(data[j].name) + ',' + data[j].selfgroup + ',' + 'NOW())';
 }
 sql.sever(pool, istSqlStr, function () {
 let allGroup = ['Android', 'ios', 'Web', 'Backend', 'Product'];
 let group = allGroup[selfgroup - 1];
 let mailmsg = "Hello," + group + "The group approval list has been submitted, please review it as soon as possible!";
 mail.mailepass(mailmsg);
 res.write(JSON.stringify({
  style: 1,
  msg: "The list has been submitted and is awaiting review by the administrator!"
 }));
 res.end();
 })
})

Replenish

Batch query syntax (and and or are mixed here) SELECT column name, column name FROM table name WHERE condition AND (item1='xxx' OR item1='yyy');
One statement for batch insert syntax INSERT INTO [table name]([column name],[column name]) VALUES([column value],[column value])),([column value],[column value])),([column value],[column value]));

Summarize

This is the end of this article about MySQL querying bulk data from one table and inserting it into another table. For more related MySQL query bulk data insert into another table content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!