javascript - How do I insert multiple records from array in mySQL using node.js - Stack Overflow

admin管理员组

文章数量:1024924

I have a look-up table:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

I want to store a new record in this table for every element in an array:

var values = ['Grunt', 'Gulp'];

So that the resulting table will look like:

+----------------+
| technologyName |
+----------------+
| Grunt          |
+----------------+
| Gulp           |
+----------------+

How can I do this using Node?

Update:

I know I can do something like this:

var values = [
  [
    ['Grunt'], 
    ['Something']
  ]
]
connection.query('INSERT IGNORE INTO technologies (technologyName) VALUES ?', values);

I tried this with the original array but it does not work. How can I convert the simple array into the more plicated one?

I have a look-up table:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

I want to store a new record in this table for every element in an array:

var values = ['Grunt', 'Gulp'];

So that the resulting table will look like:

+----------------+
| technologyName |
+----------------+
| Grunt          |
+----------------+
| Gulp           |
+----------------+

How can I do this using Node?

Update:

I know I can do something like this:

var values = [
  [
    ['Grunt'], 
    ['Something']
  ]
]
connection.query('INSERT IGNORE INTO technologies (technologyName) VALUES ?', values);

I tried this with the original array but it does not work. How can I convert the simple array into the more plicated one?

• javascript
• mysql
• sql
• node.js
• node-mysql

Share Improve this question Follow edited Apr 24, 2015 at 14:52 Angular noob asked Apr 24, 2015 at 14:24 Angular noobAngular noob 43755 silver badges1111 bronze badges 2

• @MelissaAvery-Weir No, I do not need do do that. I made the choice to keep the question short and sweet for anyone who might stumble upon it in the future. – Angular noob Commented Apr 24, 2015 at 14:48
• Nope, you certainly don't need to (i.e., the question is unlikely to be closed). I should have said "your question will be more highly voted and more readily answered if you include what you've tried" (per stackoverflow./help/how-to-ask). That all said, thanks for adding the example. – Melissa Avery-Weir Commented Apr 24, 2015 at 14:58

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 2

if you want to use "?" to escape your values and avoid SQL injections, mysqljs accept nested arrays for bulk inserts. So the answer to your update is :

var valuesNestedArray  = [];

for(var v in values ) {
  valuesNestedArray.push([values[v]]);
}

var sql = "INSERT IGNORE INTO technologies (technologyname) values ?";
connection.query(sql, [valuesNestedArray], function(err) {
    if (err) throw err;   
});

You can insert multiple things at once...

From the mysql docs (https://dev.mysql./doc/refman/5.5/en/insert.html)

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

so for you:

insert into technologies (technologyname) values ('grunt'),('gulp')

---

var sql = "insert into technologies (technologyname) values ";

for(var v in values)
  sql += "('" + connection.escape(values[v]) + "'),";

sql = sql.substr(0,sql.length-1); // take off the last ma since we added one after each value

I have a look-up table:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

I want to store a new record in this table for every element in an array:

var values = ['Grunt', 'Gulp'];

So that the resulting table will look like:

+----------------+
| technologyName |
+----------------+
| Grunt          |
+----------------+
| Gulp           |
+----------------+

How can I do this using Node?

Update:

I know I can do something like this:

var values = [
  [
    ['Grunt'], 
    ['Something']
  ]
]
connection.query('INSERT IGNORE INTO technologies (technologyName) VALUES ?', values);

I tried this with the original array but it does not work. How can I convert the simple array into the more plicated one?

I have a look-up table:

CREATE TABLE technologies (
    technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

I want to store a new record in this table for every element in an array:

var values = ['Grunt', 'Gulp'];

So that the resulting table will look like:

+----------------+
| technologyName |
+----------------+
| Grunt          |
+----------------+
| Gulp           |
+----------------+

How can I do this using Node?

Update:

I know I can do something like this:

var values = [
  [
    ['Grunt'], 
    ['Something']
  ]
]
connection.query('INSERT IGNORE INTO technologies (technologyName) VALUES ?', values);

I tried this with the original array but it does not work. How can I convert the simple array into the more plicated one?

• javascript
• mysql
• sql
• node.js
• node-mysql

Share Improve this question Follow edited Apr 24, 2015 at 14:52 Angular noob asked Apr 24, 2015 at 14:24 Angular noobAngular noob 43755 silver badges1111 bronze badges 2

• @MelissaAvery-Weir No, I do not need do do that. I made the choice to keep the question short and sweet for anyone who might stumble upon it in the future. – Angular noob Commented Apr 24, 2015 at 14:48
• Nope, you certainly don't need to (i.e., the question is unlikely to be closed). I should have said "your question will be more highly voted and more readily answered if you include what you've tried" (per stackoverflow./help/how-to-ask). That all said, thanks for adding the example. – Melissa Avery-Weir Commented Apr 24, 2015 at 14:58

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 2

if you want to use "?" to escape your values and avoid SQL injections, mysqljs accept nested arrays for bulk inserts. So the answer to your update is :

var valuesNestedArray  = [];

for(var v in values ) {
  valuesNestedArray.push([values[v]]);
}

var sql = "INSERT IGNORE INTO technologies (technologyname) values ?";
connection.query(sql, [valuesNestedArray], function(err) {
    if (err) throw err;   
});

You can insert multiple things at once...

From the mysql docs (https://dev.mysql./doc/refman/5.5/en/insert.html)

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

so for you:

insert into technologies (technologyname) values ('grunt'),('gulp')

---

var sql = "insert into technologies (technologyname) values ";

for(var v in values)
  sql += "('" + connection.escape(values[v]) + "'),";

sql = sql.substr(0,sql.length-1); // take off the last ma since we added one after each value

本文标签： javascriptHow do I insert multiple records from array in mySQL using nodejsStack Overflow