使用Sequelize.js批量上传23k行Excel数据到MySQL时报ECONNRESET错误

使用Sequelize.js技术将Excel数据导入MySQL数据库，采用bulkCreate方法执行插入操作。Excel文件共包含23000+行数据，测试导入500行时运行顺畅，但导入全量大数据时会抛出「read ECONNRESET」错误。

相关代码 #

const db = require("../model");

const readXlsxFile = require("read-excel-file/node");

const uploadExcel = async (req, res) => {
  try {
    console.log(req.file);
    if (req.file == undefined) {
      return res.status(400).send({ msg: "No, Excel File Uploaded" });
    }

    let path = "./ExcelFiles/" + req.file.filename;

    readXlsxFile(path).then((rows) => {
      // skip header
      rows.shift();

      let ExcelArray = [];

      rows.forEach((row) => {
        let excelarray = {
          id: row[0],
          SoldBy: row[1],
          SoldOn: row[2],
          Quantity: row[3],
          NetProfit: row[4],
          District: row[5],
          OverallGP: row[6],
          Dayofthemonth: row[7],
          Type: row[8],
          TotalNewActivations: row[9],
          TotalUpgrades: row[10],
          DeviceName: row[11],
          Accessories: row[12],
          DistrictManager: row[13],
          StoreName: row[14],
        };

        ExcelArray.push(excelarray);
      });
      // Adding the data in the Database Schema

      if (db.ExcelData.findAll()) {
        db.ExcelData.destroy({ where: {} }).then(function () {});
        db.ExcelData.bulkCreate(ExcelArray)
          .then(() => {
            res.status(200).send({
              message:
                "Uploaded the file successfully: " + req.file.originalname,
            });
          })
          .catch((error) => {
            res.status(500).send({
              message: "Fail to import data into database!",
              error: error.message,
            });
          });
      } else {
        db.ExcelData.bulkCreate(ExcelArray)
          .then(() => {
            res.status(200).send({
              message:
                "Uploaded the file successfully: " + req.file.originalname,
            });
          })
          .catch((error) => {
            res.status(500).send({
              message: "Fail to import data into database!",
              error: error.message,
            });
          });
      }
    });
  } catch (error) {
    console.log(error);
    res.status(500).send({
      message: "Could not upload the file: " + req.file.originalname,
    });
  }
};

//Get the Data from the Mysql

const GetImport = (req, res) => {
  db.ExcelData.findAll()
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials.",
      });
    });
};

module.exports = {
  uploadExcel,
  GetImport,
};

错误截图 #

1. 单次批量插入数据量过大，23000条数据拼接成的SQL语句长度超过了MySQL默认的max_allowed_packet配置限制，数据库主动断开了连接，触发了ECONNRESET错误。
2. 代码异步逻辑存在时序问题：db.ExcelData.findAll()返回的是Promise对象，直接作为判断条件永远为真；且destroy清空表的操作没有等待执行完成就发起了插入请求，多个大请求并行占用数据库连接，进一步提升了报错概率。

• 分片批量插入：将23000条数据拆分为多个小批次插入，每批控制在500~1000条（可根据实际测试调整阈值），避免单次请求数据包过大。
• 调整MySQL配置：修改MySQL的max_allowed_packet参数，将默认的1M/4M调整为64M或更大，允许更长的SQL请求。
• 修复异步时序：清空表的操作完成后再执行插入逻辑，避免并行请求占用数据库连接。

优化后代码示例 #

const uploadExcel = async (req, res) => {
  try {
    if (req.file == undefined) {
      return res.status(400).send({ msg: "No Excel File Uploaded" });
    }
    let path = "./ExcelFiles/" + req.file.filename;
    const rows = await readXlsxFile(path);
    // 跳过表头
    rows.shift();
    const ExcelArray = rows.map(row => ({
      id: row[0],
      SoldBy: row[1],
      SoldOn: row[2],
      Quantity: row[3],
      NetProfit: row[4],
      District: row[5],
      OverallGP: row[6],
      Dayofthemonth: row[7],
      Type: row[8],
      TotalNewActivations: row[9],
      TotalUpgrades: row[10],
      DeviceName: row[11],
      Accessories: row[12],
      DistrictManager: row[13],
      StoreName: row[14],
    }));

    // 先清空表
    await db.ExcelData.destroy({ where: {} });

    // 分片插入，每批1000条
    const batchSize = 1000;
    for (let i = 0; i < ExcelArray.length; i += batchSize) {
      const batch = ExcelArray.slice(i, i + batchSize);
      await db.ExcelData.bulkCreate(batch);
    }

    res.status(200).send({
      message: "Uploaded the file successfully: " + req.file.originalname,
    });
  } catch (error) {
    console.log(error);
    res.status(500).send({
      message: "Fail to import data into database!",
      error: error.message,
    });
  }
};

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