Node.js+Hapi.js实现MariaDB批量更新插入的格式转换求助

一、修正Handlebars表单的字段名称错误 #

原表单中opt1至opt5对应的input标签name属性均错误设为opt0，会导致提交数据混乱，修正后的代码如下：

{{#each parlist}}
    <input type="hidden" name="id" value="{{this.id}}" form="form-edit">
    <td style="text-align:center;">{{this.id}}</td>
    <td><input type="text" name="name" value="{{this.name}}" form="form-edit"></td>
    <td><input type="text" name="description" value="{{this.description}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt0}}" form="form-edit"></td>
    <td><input type="text" name="opt1" value="{{this.opt1}}" form="form-edit"></td>
    <td><input type="text" name="opt2" value="{{this.opt2}}" form="form-edit"></td>
    <td><input type="text" name="opt3" value="{{this.opt3}}" form="form-edit"></td>
    <td><input type="text" name="opt4" value="{{this.opt4}}" form="form-edit"></td>
    <td><input type="text" name="opt5" value="{{this.opt5}}" form="form-edit"></td>
    <td><input type="text" name="multiplier" value="{{this.multiplier}}" form="form-edit"></td>
{{/each}}

二、将req.payload转换为对象数组 #

提交的payload以字段为键、对应值数组为值，需转换为每行数据对应一个对象的数组，同时处理数据类型转换：

function transformPayload(payload) {
  const rowCount = payload.id.length;
  const result = [];

  for (let i = 0; i < rowCount; i++) {
    result.push({
      id: payload.id[i] ? parseInt(payload.id[i], 10) : null,
      name: payload.name[i],
      description: payload.description[i],
      opt0: payload.opt0[i],
      opt1: payload.opt1[i],
      opt2: payload.opt2[i],
      opt3: payload.opt3[i],
      opt4: payload.opt4[i],
      opt5: payload.opt5[i],
      multiplier: parseFloat(payload.multiplier[i]) || 1
    });
  }

  return result;
}

// 使用示例
const transformedData = transformPayload(req.payload);

转换后的数据格式示例：

[
  { id: 1, name: 'Social', description: 'Desc 1', ..., multiplier: 1 },
  { id: 3, name: 'Work ethic', description: 'Desc 2', ..., multiplier: 1 },
  { id: null, name: 'Dominance', description: 'Desc 3', ..., multiplier: 1 }
]

三、拆分更新与插入数据集 #

根据id是否为空，将转换后的数据拆分为更新组（已有数据）和插入组（新数据）：

const updateItems = transformedData.filter(item => item.id !== null);
const insertItems = transformedData.filter(item => item.id === null);

四、批量操作MariaDB #

使用Node.js MariaDB插件实现批量插入和更新，建议用事务保证操作原子性。

批量插入新数据 #

// 准备插入数据，剔除id字段
const insertValues = insertItems.map(item => {
  const { id, ...rest } = item;
  return Object.values(rest);
});

const insertSql = `
  INSERT INTO parameters (name, description, opt0, opt1, opt2, opt3, opt4, opt5, multiplier)
  VALUES ?
`;

批量更新已有数据 #

利用MariaDB的CASE语句实现批量更新：

let updateSql = '';
if (updateItems.length > 0) {
  const setClauses = [
    'name = CASE id',
    'description = CASE id',
    'opt0 = CASE id',
    'opt1 = CASE id',
    'opt2 = CASE id',
    'opt3 = CASE id',
    'opt4 = CASE id',
    'opt5 = CASE id',
    'multiplier = CASE id'
  ];
  const ids = [];

  updateItems.forEach(item => {
    ids.push(item.id);
    setClauses[0] += ` WHEN ${item.id} THEN ${connection.escape(item.name)}`;
    setClauses[1] += ` WHEN ${item.id} THEN ${connection.escape(item.description)}`;
    setClauses[2] += ` WHEN ${item.id} THEN ${connection.escape(item.opt0)}`;
    setClauses[3] += ` WHEN ${item.id} THEN ${connection.escape(item.opt1)}`;
    setClauses[4] += ` WHEN ${item.id} THEN ${connection.escape(item.opt2)}`;
    setClauses[5] += ` WHEN ${item.id} THEN ${connection.escape(item.opt3)}`;
    setClauses[6] += ` WHEN ${item.id} THEN ${connection.escape(item.opt4)}`;
    setClauses[7] += ` WHEN ${item.id} THEN ${connection.escape(item.opt5)}`;
    setClauses[8] += ` WHEN ${item.id} THEN ${item.multiplier}`;
  });

  const finalSet = setClauses.map(clause => `${clause} END`).join(', ');
  updateSql = `UPDATE parameters SET ${finalSet} WHERE id IN (${ids.join(', ')})`;
}

事务执行 #

try {
  await connection.beginTransaction();
  
  if (insertItems.length > 0) {
    await connection.query(insertSql, [insertValues]);
  }
  
  if (updateItems.length > 0) {
    await connection.query(updateSql);
  }
  
  await connection.commit();
} catch (err) {
  await connection.rollback();
  throw err;
}

内容的提问来源于stack exchange，提问作者LKE