# Multivalue mysql insert to mariadb

**URL:** <https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661>\
**Category:** General\
**Created:** [14 August 2025 12:43 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661 "2025-08-14T12:43:49Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![gmc\_5303](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@gmc\_5303](https://discourse.nodered.org/u/gmc_5303)\
**Post date:** [14 August 2025 12:43 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/1 "2025-08-14T12:43:49Z")

</div>

Hello, first time poster here! I’ve been working to replace some older python scripts that export data from MSSQL and import it into MariaDB. I can successfully do this with nodered function by creating separate insert statements for the data using the node-red-node-mysql process, using a parameterized insert statement. What’s tripping me up is that I'm doing ~150,000 inserts, and I want to validate that the process is completed successfully, and do it as fast as possible. It would seem that the most straightforward way is to use the VALUES ? parameter in the query in the .topic, and feed an array of arrays via the .payload, but I cannot, for the life of me, get it to work.

For diagnostic purposes, I have broken it down to just a function node that states:

```auto
msg.payload = [["aa","bb","cc","dd","ee","ff","gg","hh","ii"],
               ["ab","bc","cd","de","ef","fg","gh","hi","ij"]];
msg.topic = "insert into Company_ShipTo (Name,Address1,Address2,Address3,City,State,Zip,CoNum,CustNum) VALUES ? ;";  
return msg;

```

and pass that to the node-red-node-mysql node, which will only produce:

```auto
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''aa', 'bb', 'cc', 'dd', 'ee', 'ff', 'gg', 'hh', 'ii'' at line 1

```

This same node will take hundreds of thousands of individual inserts. Can anyone point me in the direction of what I’m doing wrong here?

Nodered 4.1, node-red-node-mysql 2.0.0

---

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [14 August 2025 13:34 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/2 "2025-08-14T13:34:00Z")

</div>

You can try to compose your values into the SQL statement after the `VALUES` keyword.  
For example:

```auto
const values = [
    ["a1","b1","c1"],
    ["a2","b2","c2"]
];
let valueStr = "";
for (let i = 0 ; i < values.length ; i++)
    valueStr += ( "(" + values[i] + "),");
valueStr = valueStr.replace(/,$/,';');

msg.topic = "insert into myTable (fieldA,fieldB,fieldC) VALUES " + valueStr;
return msg;

```

---

<div class="post-metadata">

**Author:** ![gmc\_5303](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@gmc\_5303](https://discourse.nodered.org/u/gmc_5303)\
**Post date:** [14 August 2025 13:57 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/3 "2025-08-14T13:57:02Z")

</div>

That is definitely workable. I was hoping to use a parameterized approach to protect me from SQL injection problems by keeping the data in the payload object. All the mysql nodes seem to have large bold warnings about using values from variables on the SQL line directly.

Is it a known issue the that node-red-node-mysql node doesn’t handle the “VALUES ?” parameter?

---

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [14 August 2025 14:00 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/4 "2025-08-14T14:00:38Z")

</div>

Maybe someone else here knows, or you can check this in the node's documentation or code. Of course sending the values as msg parameters is preferable, but the question is if the node supports it.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [14 August 2025 14:39 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/5 "2025-08-14T14:39:30Z")

</div>

> [@gmc\_5303](#):
>
> `msg.topic = "insert into Company_ShipTo (Name,Address1,Address2,Address3,City,State,Zip,CoNum,CustNum) VALUES ? ;"; `

The [docs](https://flows.nodered.org/node/node-red-node-mysql) suggest that you should use something like  
`msg.topic = "insert into Company_ShipTo ('Name','Address1', ...) VALUES (?,?,?,?,?,?,?,?,?) ;"`

---

<div class="post-metadata">

**Author:** ![gmc\_5303](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@gmc\_5303](https://discourse.nodered.org/u/gmc_5303)\
**Post date:** [14 August 2025 17:31 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/6 "2025-08-14T17:31:07Z")

</div>

That doesn’t seem to work when I pass an array of arrays.

> ```auto
> msg.topic = "insert into Company_ShipTo (Name,Address1,Address2,Address3,City,State,Zip,CoNum,CustNum) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?);";
> msg.payload = [["aa","bb","cc","dd","ee","ff","gg","hh","ii"],
> ["ab","bc","cd","de","ef","fg","gh","hi","ij"]];
> return msg;
> 
> ```

node-red-node-mysql returns:

> ```auto
> You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?, ?, ?, ?, ?, ?, ?)' at line 1
> 
> ```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [14 August 2025 18:25 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/7 "2025-08-14T18:25:02Z")

</div>

This flow accepts multiple records as an array of objects and inserts them into a mariadb table

```auto
[{"id":"c91a72c08f2ba23d","type":"mysql","z":"5398fd8367dbf130","mydb":"2381cb15.b563bc","name":"","x":240,"y":160,"wires":[["b9789d624273ac6c"]]},{"id":"b91b24827d05e067","type":"inject","z":"5398fd8367dbf130","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":110,"y":100,"wires":[["0456ccacd8e9f01e"]]},{"id":"0456ccacd8e9f01e","type":"template","z":"5398fd8367dbf130","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"[\n{ \"flavour\": \"Pea\", \"colour\": \"green\" },\n{ \"flavour\": \"Oxtail\", \"colour\": \"Brown\" },\n{ \"flavour\": \"Tomato\", \"colour\": \"Red\" },\n{ \"flavour\": \"Chicken\", \"colour\": \"beige\" },\n{ \"flavour\": \"Mushroom\", \"colour\": \"beige\" },\n{ \"flavour\": \"Pea\", \"colour\": \"green\" },\n{ \"flavour\": \"Oxtail\", \"colour\": \"Brown\" },\n{ \"flavour\": \"Tomato\", \"colour\": \"Red\" },\n{ \"flavour\": \"Chicken\", \"colour\": \"beige\" },\n{ \"flavour\": \"Mushroom\", \"colour\": \"beige\" }\n]","output":"json","x":240,"y":100,"wires":[["67450b9b5fa2cea6"]]},{"id":"67450b9b5fa2cea6","type":"function","z":"5398fd8367dbf130","name":"function 5","func":"let soups = msg.payload;\nlet sql = \"INSERT INTO soup (flavour, colour) VALUES ?\";\nlet values = soups.map(soup => [soup.flavour, soup.colour]);\n\nmsg.topic = sql;\nmsg.payload = [values]; // MySQL node expects an array of arrays for multiple inserts\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":100,"wires":[["c91a72c08f2ba23d"]]},{"id":"b9789d624273ac6c","type":"debug","z":"5398fd8367dbf130","name":"debug 20","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":380,"y":160,"wires":[]},{"id":"2381cb15.b563bc","type":"MySQLdatabase","name":"espresso","host":"127.0.0.1","port":"3306","db":"espresso","tz":"","charset":"UTF8"},{"id":"1bd9f1dc25e8bfad","type":"global-config","env":[],"modules":{"node-red-node-mysql":"2.0.0"}}]

```

---

<div class="post-metadata">

**Author:** ![gmc\_5303](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@gmc\_5303](https://discourse.nodered.org/u/gmc_5303)\
**Post date:** [14 August 2025 18:44 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/8 "2025-08-14T18:44:56Z")

</div>

So, it’s working will all your help. Turns out, you have to define the payload as an array, of arrays, of arrays.

jbudd, the example you provided took the array of arrays, and put that in an array when defining the payload. **That’s what I was missing.**

The value for msg.payload ended up being:

```auto
[[["Pea","green"],["Oxtail","Brown"],["Tomato","Red"],["Chicken","beige"],["Mushroom","beige"],["Pea","green"],["Oxtail","Brown"],["Tomato","Red"],["Chicken","beige"],["Mushroom","beige"]]]

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [28 August 2025 18:44 UTC](https://discourse.nodered.org/t/multivalue-mysql-insert-to-mariadb/98661/9 "2025-08-28T18:44:59Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.