Mysql用逗号分隔值插入多行，单独使用sql语句 [英] Mysql Insert Multiple Rows with comma separated values with sql statement alone

问题描述

意图执行多个 sql insert 语句而不将它们运行到 node.js 等中的某种循环中

典型的 sql 语句如下所示.

INSERT INTO session_speaker(session_id, Speaker_id) VALUES(?, ?);插入 session_speaker(session_id,speaker_id) VALUES(?, ?);

或

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

普通的 node.js 循环如下所示.

for (var i = 0; i 

意图/问题:

如果可能，探索一种将 request.body 传递给智能 SQL 的方法，该 SQL 接受数组本身或逗号分隔的值列表，并从那里插入多行而无需 node.js 循环.

request.body 看起来像这样(可更改以满足要求)

<预><代码>[{身份证":12},{身份证":34}]

解决方案

我认为这让我更接近我正在寻找的东西.

https://github.com/felixge/node-mysql/issues/814

根据那里的 dougwilson 评论和他对嵌套数组的解决方案，我可以简单地将多对象数组传递给查询，它会一次插入多条记录.

var currentLogs = [['服务器'，'Socketio 在线'，'端口 3333'，'2014-05-14 14:41:11']，['服务器'、'等待 Pi 连接...'、'端口:8082'、'2014-05-14 14:41:11']];pool.query('INSERT INTO logs_debug (socket_id,message,data,logged) VALUES ?', [currentLogs])

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
  }
]

解决方案

I think this brings me closer to what I'm looking for.

https://github.com/felixge/node-mysql/issues/814

As per dougwilson comment there and his solution for nested arrays, I can simply pass the multi object array to the query and it will insert multiple records at once.

var currentLogs = [
  [ 'Server', 'Socketio online', 'Port 3333', '2014-05-14 14:41:11' ],
  [ 'Server', 'Waiting for Pi to connect...', 'Port: 8082', '2014-05-14 14:41:11' ]
];

pool.query('INSERT INTO logs_debug (socket_id,message,data,logged) VALUES ?', [currentLogs])

这篇关于Mysql用逗号分隔值插入多行，单独使用sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！