Mysql inserts multiple lines with comma-separated values ​​with the sql statement alone

advertisements

The intention to execute multiple sql insert statements without running them into some kind of loop in node.js etc.

A typical sql statement looks like this.

INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?);
INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?);

OR

INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?), (?, ?);

The ordinary node.js loop looks like this.

for (var i = 0; i < request.body.length; i++) {
    queryRequest.sql += "INSERT INTO session_speaker(session_id, speaker_id) VALUES(?, ?);";
    queryRequest.values.push(request.body[i].id, request.params.id);
}

Intention/Question:

Exploring a way if possible to pass request.body to an intelligent SQL that takes array itself or maybe comma separated list of values and insert multiple rows from there without node.js loop.

request.body looks like this (changeable to meet the requirements)

[
  {
    "id": 12
  },
  {
    "id": 34
  }
]


Yes, This is possible. Take a look at this reference. https://dev.mysql.com/doc/refman/5.7/en/json.html

Here is what you can do.

  1. Select your object data
  2. Convert to array
  3. Unnest the array ( make array in column view ) reference: UNNEST function in MYSQL like POSTGRESQL, This function needs a string so convert your data (array ) into data (string) here is reference: Is there a MySQL equivalent to PostgreSQL array_to_string
  4. Use insert into select reference: https://dev.mysql.com/doc/refman/5.1/en/insert-select.html

At the end, your code something like this:

insert  into session_speaker(session_id, speaker_id)
select unnest_mysql ( arrayToString (dataTOArray( stringtojson (json_string) ) ) )

json_string = this is your data from php

dataTOArray = this function convert your data to mysql json to array

dataTOArray = this is to convert array to string ex. [1,2,3] to '1,2,3' unnest_mysql = your string to row data

 string '1,2,3'
 --view like 

 |-------String-----|
 |---------1--------|
 |---------2--------|
 |---------3--------|
 |----------- ------|
 So when you insert this , it will become row in your table

Note: Change my function to MySQL specific function.