如何从JSON结构向MySQL列插入多行

Question

我需要在mysql列中插入多个json值。这里我有一个名为client的表

query = ' INSERT INTO client (user_id,client_id,tenant_id) values ?'
let queryClient = []
queryClient.push(clientData.user_id)
queryClient.push(clientData.client_id);
queryClient.push(clientData.tenant_id);

在此之前，我将req.body赋值给clientData

下面是到mysql数据库的连接

var queryMapClientIn = mysql.format(query, [queryClient])
const newMapClient = await conPool.query(queryMapClientIn, function (err, result) {
  if (err) throw err;
  console.log("Number of records inserted: " + result.affectedRows);
});

这是代码，json数据是：

{
    "user_id" : 101,
    "client_id": 999,
    "tenant_id" : 1,
    "additionalClient" : [
        {"user_id" : 130},
        {"user_id" : 131}
    ]
}

这是json数据，我需要将多个user_id推入sql表上的user_id列。我可以知道如何在这个表上插入多个user_id吗？

Answer 1

您可以创建一个二维数组来插入多行。

let clientData = {
    "user_id" : 101,
    "client_id": 999,
    "tenant_id" : 1,
    "additionalClient" : [
        {"user_id" : 130},
        {"user_id" : 131}
    ]
};
let queryClient = [[clientData.user_id, clientData.client_id, clientData.tenant_id]];
if (clientData.additionalClient) {
    let additionalClients = clientData.additionalClient.map(({user_id}) => [user_id, clientData.client_id, clientData.tenant_id]);
    queryClient = queryClient.concat(additionalClients);
}

let query = 'INSERT INTO client (user_id,client_id,tenant_id) values ?';
const newMapClient = await conPool.query(query, queryClient, function (err, result) {
  if (err) throw err;
  console.log("Number of records inserted: " + result.affectedRows);
});