sequelize 更新多行

更新多行,使用批量更新是一个好的解决方案还是类似于下面的代码?. 我想用 id 和文件名更新所有记录。


我确实尝试在循环内更新,但迁移挂断并在此块中花费太长时间。是因为迭代吗?还是更新语法有问题?谢谢你。


#代码


 for (let i = 0; i < documents.length; i++) {

        const prefix = Date.now().toString();

        const fileParts = documents[i].filename.split('.');

        const finalname = `${fileParts[0]}-${prefix}.${fileParts[1]}`;

        // eslint-disable-next-line no-await-in-loop

        await queryInterface.sequelize.query(`UPDATE ${EmployeeDocumentsModel.tableName} SET filename='${finalname}' WHERE id=${documents[i].id};`, { transaction });

      }

#代码


module.exports = {

  up: async (queryInterface) => {

    const transaction = await queryInterface.sequelize.transaction();

    try 

      const documents = await sequelizeClient.query(

        `SELECT id, filename, COUNT(filename) FROM ${EmployeeDocumentsModel.tableName} GROUP BY filename

      HAVING COUNT(filename) > 1;`,

        { type: QueryTypes.SELECT },

      );


      // eslint-disable-next-line no-plusplus

      for (let i = 0; i < documents.length; i++) {

        const prefix = Date.now().toString();

        const fileParts = documents[i].filename.split('.');

        const finalname = `${fileParts[0]}-${prefix}.${fileParts[1]}`;

        // eslint-disable-next-line no-await-in-loop

        await queryInterface.sequelize.query(`UPDATE ${EmployeeDocumentsModel.tableName} SET filename='${finalname}' WHERE id=${documents[i].id};`, { transaction });

      }

      // eslint-disable-next-line no-unused-vars

      // const file = await sequelizeClient.query(

      //   `DELETE d1 from ${EmployeeDocumentsModel.tableName} d1 inner join ${EmployeeDocumentsModel.tableName} d2 on d2.id < d1.id and d2.filename = d1.filename and d2.employeeId = d1.employeeId`,

      //   { type: QueryTypes.DELETE },

      // );



HUX布斯
浏览 195回答 1
1回答

白衣非少年

我认为最大的问题是每次更新都在等待前一次完成:for (let i = 0; i < documents.length; i++) {&nbsp; &nbsp; // ...&nbsp; &nbsp; await queryInterface.sequelize.query(`UPDATE ${EmployeeDocumentsModel.tableName} SET filename='${finalname}' WHERE id=${documents[i].id};`, { transaction });}如果将其更改为:const promises = [];for (let i = 0; i < documents.length; i++) {&nbsp; &nbsp; // ...&nbsp; &nbsp; promises.push(queryInterface.sequelize.query(`UPDATE ${EmployeeDocumentsModel.tableName} SET filename='${finalname}' WHERE id=${documents[i].id};`, { transaction }));}await Promise.all(promises);你应该看到一个很大的加速。
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

JavaScript