mysql - 在 sequelize bulkCreate 时间戳不更新

Question

我正在使用 bulkCreate 和 uupdate

const item = await models.Gsdatatab.bulkCreate(gsdatamodel,{updateOnDuplicate: ["SCRIP","LTP","OHL","ORB15","ORB30","PRB","CAMARILLA"] });

我看到时间戳(createdAt 和 updatedAt)在更新后没有在数据库中更新。我是否需要在 bulKCreate 中显式传递这两个以在每次有更新时更新它们，或者是否有任何我遗漏的选项。当行更新时，id 也会增加。我不希望 id 列在更新时自动递增。

我正在使用扩展模型创建来定义模型

Answer 1

以下是使用

运行的

• MySQL 服务器版本:8.0.25 MySQL 社区服务器
• Sequelize 版本 6.6.5

总结

时间戳:.bulkCreate 方法返回的值可能会产生误导。您需要在执行 bulkUpdate 后查询项目以查找新值。引用sequelize docs对于版本 6:

The success handler is passed an array of instances, but please noticethat these may not completely represent the state of the rows in theDB. This is because MySQL and SQLite do not make it easy to obtainback automatically generated IDs and other default values in a waythat can be mapped to multiple records. To obtain Instances for thenewly created values, you will need to query for them again.

此外，要更新 updatedAt 列，它需要包含在 updateOnDuplicate 的数组参数中。否则，它将不会收到新的时间戳。

非顺序主键:MySQL 主键的下一个 auto_increment 值似乎在更新完成时递增。我不确定是否有办法防止这种情况发生。但是，仍然可以插入具有已被 auto_increment 机制跳过的主键的行。此外，根据 another answer关于非顺序主键的stackoverflow，应该不会影响效率。作为替代方案，bulkCreate 语句可以分为两组，一组用于插入，一组用于更新，然后可以使用 sequelize 单独完成。缺点是会有额外的查询来确定传入数据是否已存在于数据库中，以便在插入与更新之间做出决定。

这是一个代码示例:

let {
        Sequelize,
        DataTypes,
    } = require('sequelize')

async function run () {
    let sequelize = new Sequelize(process.env.DB_NAME, process.env.DB_USER, process.env.DB_PASSWORD, {
            host:       'localhost',
            dialect:    'mysql',
            logging:    console.log
        })

    let Item = sequelize.define('item', {
            name: DataTypes.STRING,
            age: DataTypes.INTEGER
        }, {
            tableName: 'items',
            schema: 'agw_queries'
        })

    await sequelize.sync({ force: true })

    let wait = sec => new Promise( res => setTimeout(res, sec * 1000));

    let items = await Item.bulkCreate([{ name: 'mickey', age: 32 }, { name: 'minnie', age: 30 }])
    console.log()
    console.log('These values are returned upon creation.')
    console.log()
    console.log(JSON.stringify(items, null, 2))

    console.log()
    console.log('These values are returned after a subsequent query.')
    console.log()
    let r = await Item.findAll({})
    console.log(JSON.stringify(r, null, 2))

    console.log()
    console.log('Waiting two seconds ...')
    console.log()
    await wait(2)

    console.log('These values are returned after an update.')
    console.log()
    items = await Item.bulkCreate(
            [
                { id: 1, name: 'mickey mouse', age: 33 },
                { id: 2, name: 'minnie mouse', age: 31 },
                { name: 'goofy', age: 37 }
            ],
            { updateOnDuplicate: [ 'name', 'updatedAt' ] })
    console.log(JSON.stringify(items, null, 2))

    console.log()
    console.log('These values are returned after another subsequent query.')
    console.log()
    r = await Item.findAll({})
    console.log(JSON.stringify(r, null, 2))

    console.log()
    console.log('Waiting two seconds ...')
    console.log()
    await wait(2)

    console.log('These values are returned after an update.')
    console.log()
    items = await Item.bulkCreate(
            [
                { id: 1, name: 'mickey t. mouse', age: 33 },
                { id: 2, name: 'minerva mouse', age: 31 },
                { name: 'donald duck', age: 32 }
            ],
            { updateOnDuplicate: [ 'name', 'updatedAt' ] })
    console.log(JSON.stringify(items, null, 2))

    console.log()
    console.log('These values are returned after another subsequent query.')
    console.log()
    r = await Item.findAll({})
    console.log(JSON.stringify(r, null, 2))

    await sequelize.close()
}

run()

这是输出

Executing (default): DROP TABLE IF EXISTS `items`;
Executing (default): DROP TABLE IF EXISTS `items`;
Executing (default): CREATE TABLE IF NOT EXISTS `items` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `age` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `items`
Executing (default): INSERT INTO `items` (`id`,`name`,`age`,`createdAt`,`updatedAt`) VALUES (NULL,'mickey',32,'2021-09-06 12:17:44','2021-09-06 12:17:44'),(NULL,'minnie',30,'2021-09-06 12:17:44','2021-09-06 12:17:44');

These values are returned upon creation.

[
  {
    "id": 1,
    "name": "mickey",
    "age": 32,
    "createdAt": "2021-09-06T12:17:44.042Z",
    "updatedAt": "2021-09-06T12:17:44.042Z"
  },
  {
    "id": 2,
    "name": "minnie",
    "age": 30,
    "createdAt": "2021-09-06T12:17:44.042Z",
    "updatedAt": "2021-09-06T12:17:44.042Z"
  }
]

These values are returned after a subsequent query.

Executing (default): SELECT `id`, `name`, `age`, `createdAt`, `updatedAt` FROM `items` AS `item`;
[
  {
    "id": 1,
    "name": "mickey",
    "age": 32,
    "createdAt": "2021-09-06T12:17:44.000Z",
    "updatedAt": "2021-09-06T12:17:44.000Z"
  },
  {
    "id": 2,
    "name": "minnie",
    "age": 30,
    "createdAt": "2021-09-06T12:17:44.000Z",
    "updatedAt": "2021-09-06T12:17:44.000Z"
  }
]

Waiting two seconds ...

These values are returned after an update.

Executing (default): INSERT INTO `items` (`id`,`name`,`age`,`createdAt`,`updatedAt`) VALUES (1,'mickey mouse',33,'2021-09-06 12:17:46','2021-09-06 12:17:46'),(2,'minnie mouse',31,'2021-09-06 12:17:46','2021-09-06 12:17:46'),(NULL,'goofy',37,'2021-09-06 12:17:46','2021-09-06 12:17:46') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`updatedAt`=VALUES(`updatedAt`);
[
  {
    "id": 1,
    "name": "mickey mouse",
    "age": 33,
    "createdAt": "2021-09-06T12:17:46.174Z",
    "updatedAt": "2021-09-06T12:17:46.174Z"
  },
  {
    "id": 2,
    "name": "minnie mouse",
    "age": 31,
    "createdAt": "2021-09-06T12:17:46.174Z",
    "updatedAt": "2021-09-06T12:17:46.174Z"
  },
  {
    "id": 5,
    "name": "goofy",
    "age": 37,
    "createdAt": "2021-09-06T12:17:46.174Z",
    "updatedAt": "2021-09-06T12:17:46.174Z"
  }
]

These values are returned after another subsequent query.

Executing (default): SELECT `id`, `name`, `age`, `createdAt`, `updatedAt` FROM `items` AS `item`;
[
  {
    "id": 1,
    "name": "mickey mouse",
    "age": 32,
    "createdAt": "2021-09-06T12:17:44.000Z",
    "updatedAt": "2021-09-06T12:17:46.000Z"
  },
  {
    "id": 2,
    "name": "minnie mouse",
    "age": 30,
    "createdAt": "2021-09-06T12:17:44.000Z",
    "updatedAt": "2021-09-06T12:17:46.000Z"
  },
  {
    "id": 3,
    "name": "goofy",
    "age": 37,
    "createdAt": "2021-09-06T12:17:46.000Z",
    "updatedAt": "2021-09-06T12:17:46.000Z"
  }
]

Waiting two seconds ...

These values are returned after an update.

Executing (default): INSERT INTO `items` (`id`,`name`,`age`,`createdAt`,`updatedAt`) VALUES (1,'mickey t. mouse',33,'2021-09-06 12:17:48','2021-09-06 12:17:48'),(2,'minerva mouse',31,'2021-09-06 12:17:48','2021-09-06 12:17:48'),(NULL,'donald duck',32,'2021-09-06 12:17:48','2021-09-06 12:17:48') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`updatedAt`=VALUES(`updatedAt`);
[
  {
    "id": 1,
    "name": "mickey t. mouse",
    "age": 33,
    "createdAt": "2021-09-06T12:17:48.258Z",
    "updatedAt": "2021-09-06T12:17:48.258Z"
  },
  {
    "id": 2,
    "name": "minerva mouse",
    "age": 31,
    "createdAt": "2021-09-06T12:17:48.258Z",
    "updatedAt": "2021-09-06T12:17:48.258Z"
  },
  {
    "id": 8,
    "name": "donald duck",
    "age": 32,
    "createdAt": "2021-09-06T12:17:48.258Z",
    "updatedAt": "2021-09-06T12:17:48.258Z"
  }
]

These values are returned after another subsequent query.

Executing (default): SELECT `id`, `name`, `age`, `createdAt`, `updatedAt` FROM `items` AS `item`;
[
  {
    "id": 1,
    "name": "mickey t. mouse",
    "age": 32,
    "createdAt": "2021-09-06T12:17:44.000Z",
    "updatedAt": "2021-09-06T12:17:48.000Z"
  },
  {
    "id": 2,
    "name": "minerva mouse",
    "age": 30,
    "createdAt": "2021-09-06T12:17:44.000Z",
    "updatedAt": "2021-09-06T12:17:48.000Z"
  },
  {
    "id": 3,
    "name": "goofy",
    "age": 37,
    "createdAt": "2021-09-06T12:17:46.000Z",
    "updatedAt": "2021-09-06T12:17:46.000Z"
  },
  {
    "id": 6,
    "name": "donald duck",
    "age": 32,
    "createdAt": "2021-09-06T12:17:48.000Z",
    "updatedAt": "2021-09-06T12:17:48.000Z"
  }
]