Node.js下如何批量插入对象数组到MariaDB且主键重复时替换

前置准备 #

首先安装常用的 MariaDB/MySQL 操作库 mysql2，该库原生支持批量操作，性能优于旧版 mysql 库：

npm install mysql2

完整实现代码 #

// 引入 promise 版本的 mysql2，异步处理更便捷
const mysql = require('mysql2/promise');

// 初始化数据库连接池，比单次建连性能远更高
const pool = mysql.createPool({
  host: '数据库host',
  user: '数据库用户名',
  password: '数据库密码',
  database: '数据库名',
  connectionLimit: 10, // 连接池最大连接数，可根据业务调整
  waitForConnections: true
});

/**
 * 批量插入/更新数据函数
 * @param {Array} dataArray 原始对象数组
 */
async function batchUpsertSavings(dataArray) {
  // 格式化原始数据，匹配表字段格式
  const formattedRows = dataArray.map(item => {
    // 将ISO格式时间转为MariaDB支持的datetime格式
    const dt = new Date(item.date).toISOString().slice(0, 19).replace('T', ' ');
    // 顺序对应SQL中的字段顺序：DT, Saving, kpd
    return [dt, item.savings, item.kpdValue];
  });

  // 批量SQL语句，ON DUPLICATE KEY UPDATE 自动处理主键重复场景
  const sql = `
    INSERT INTO savingswest (DT, Saving, kpd)
    VALUES ?
    ON DUPLICATE KEY UPDATE
      Saving = VALUES(Saving),
      kpd = VALUES(kpd)
  `;

  try {
    const [execResult] = await pool.query(sql, [formattedRows]);
    console.log(`操作成功，共影响${execResult.affectedRows}行`);
    return execResult;
  } catch (error) {
    console.error('批量操作失败：', error);
    throw error;
  }
}

// 调用示例
const yourDataArray = [
  { date: '2021-03-01T05:55:00.000Z', kpdValue: 0, savings: 0 },
  { date: '2021-03-01T06:00:00.000Z', kpdValue: 0, savings: 0 }
];
batchUpsertSavings(yourDataArray);

关键说明 #

• 字段映射逻辑：原始数据的date对应表主键DT，savings对应表字段Saving，kpdValue对应表字段kpd，可根据实际字段命名差异自行调整映射关系。
• 重复主键处理：ON DUPLICATE KEY UPDATE 后的字段=VALUES(字段)语法，会自动取用当前待插入行的对应字段值覆盖旧数据，无需手动拼接每个更新值。
• 大数据量优化：如果待插入数组长度超过1000条，建议分片插入避免超出数据库max_allowed_packet限制，分片示例如下：

// 每1000条为一批次插入
async function slicedBatchUpsert(dataArray, sliceSize = 1000) {
  for (let i = 0; i < dataArray.length; i += sliceSize) {
    const currentSlice = dataArray.slice(i, i + sliceSize);
    await batchUpsertSavings(currentSlice);
  }
}

• 时区适配：如果运行环境时区与数据库时区不一致，可自行调整日期转换逻辑，保证存入正确时间。

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