Node.js插入MySQL Buffer触发max_allowed_packet报错及插入方案咨询

核心原因 #

你遇到的Got a packet bigger than 'max_allowed_packet' bytes错误，本质是单条SQL请求的总数据量（包含所有BLOB内容）超过了MySQL的数据包大小限制。之前批量插入10条带双LONGBLOB的记录，总数据量直接撞了上限，压缩图片无效、又无法修改服务器参数的情况下，拆分请求是唯一可行的办法。

具体实现方案 #

既然已经成功插入英文数据，现在针对日本相关数据，建议单条插入每条记录（或最多2条一批，单条最稳妥），确保单条SQL请求的数据包大小在限制内。

修改后的代码示例：

function createGardenTable(db) {
  return new Promise((resolve, reject) =>{
    db.query(
      `CREATE TABLE IF NOT EXISTS ${process.env.DATABASE_NAME}.garden_tbl (
        id INT AUTO_INCREMENT PRIMARY KEY, 
        name VARCHAR(255),
        short_description VARCHAR(255),
        long_description VARCHAR(255),
        type VARCHAR(255),
        image_day LONGBLOB DEFAULT NULL,
        image_night LONGBLOB DEFAULT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )`,
      async function(error, result) {
        console.log(error, result)
        if (error) {
          console.error('Error creating Garden table:', error);
          reject(error)
        } else {
          console.log('Garden_tbl table created!');
          
          // 仅处理日本相关数据插入
          const japaneseDayFolderPath = __dirname + '/temp/gardens/japanese_day';
          const japaneseNightFolderPath = __dirname + '/temp/gardens/japanese_night';
          
          const japaneseDayBuffers = await getImageBuffers(japaneseDayFolderPath);
          const japaneseNightBuffers = await getImageBuffers(japaneseNightFolderPath);

          let japaneseGardens = [
            { name: "Okinawa Waters", short_description:"", long_description:"", type: "Japanese", image_day: japaneseDayBuffers[0].buffer, image_night: japaneseNightBuffers[0].buffer},
            { name: "Sapporo Park", short_description:"", long_description:"", type: "Japanese", image_day: japaneseDayBuffers[1].buffer, image_night: japaneseNightBuffers[1].buffer},
            { name: "Kyoto Pagoda", short_description:"", long_description:"", type: "Japanese", image_day: japaneseDayBuffers[2].buffer, image_night: japaneseNightBuffers[2].buffer},
            { name: "Osaka City", short_description:"", long_description:"", type: "Japanese", image_day: japaneseDayBuffers[3].buffer, image_night: japaneseNightBuffers[3].buffer},
            { name: "Hiroshima Blossom", short_description:"", long_description:"", type: "Japanese", image_day: japaneseDayBuffers[4].buffer, image_night: japaneseNightBuffers[4].buffer}
           ];
        
          // 单条插入每条日本记录
          const insertPromises = japaneseGardens.map(garden => {
            return new Promise((insResolve, insReject) => {
              const values = Object.values(garden);
              db.query(
                `INSERT INTO ${process.env.DATABASE_NAME}.garden_tbl (name, short_description, long_description, type, image_day, image_night) VALUES (?, ?, ?, ?, ?, ?)`,
                values,
                (err, result) => {
                  if (err) {
                    console.error(`Error inserting ${garden.name}:`, err);
                    insReject(err);
                  } else {
                    console.log(`${garden.name} inserted:`);
                    insResolve(result);
                  }
                }
              );
            });
          });

          try {
            await Promise.all(insertPromises);
            console.log('All Japanese gardens inserted successfully');
            resolve({success: true, message:"Successfully created gardens"});
          } catch (insertErr) {
            console.error('Failed to insert some gardens:', insertErr);
            reject(insertErr);
          }
        }
      }
    );
  })
}

额外优化建议 #

• 插入前可以先检查记录是否已存在（比如通过name字段），避免重复插入
• 若单条插入仍报错，说明单条记录的双BLOB数据量还是太大，可以尝试进一步压缩图片（比如降低分辨率、转换为WebP格式），或考虑将图片存储到文件系统，仅在数据库存文件路径（这是更优的大文件存储方案）

内容的提问来源于stack exchange，提问作者The Old County