MySQL Sequelize bulkCreate() 为主键返回 NULL 值

Sequelize bulkCreate() returns NULL value for primary key

提问by Sunil Sharma

I am writing rest using node, sequelize as ORM for mySQL. I am using bulkCreate function to create record in bulk. But in response it is returning nullfor primary key value.

我正在使用 node 编写 rest，sequelize 作为 mySQL 的 ORM。我正在使用 bulkCreate 函数批量创建记录。但是作为响应，它为主键值返回null。

Model

模型

sequelize.define('category', {
    cat_id:{
        type:DataTypes.INTEGER,
        field:'cat_id',
        primaryKey: true,
        autoIncrement: true,
        unique:true
    },
    cat_name:{
        type: DataTypes.STRING,
        field: 'cat_name',
        defaultValue:null
    }
});

Bulk Create operation :

批量创建操作：

var data = [
        {
            'cat_name':'fashion'
        },
        {
            'cat_name':'food'
        }
    ];

    orm.models.category.bulkCreate(data)
    .then(function(response){
        res.json(response);
    })
    .catch(function(error){
        res.json(error);
    })

response :

回复 ：

[
  {
    "cat_id": null,
    "cat_name": "fashion",
    "created_at": "2016-01-29T07:39:50.000Z",
    "updated_at": "2016-01-29T07:39:50.000Z"
  },
  {
    "cat_id": null,
    "cat_name": "food",
    "created_at": "2016-01-29T07:39:50.000Z",
    "updated_at": "2016-01-29T07:39:50.000Z"
  }
]

回答by Adam

You should set the returningoption:

您应该设置returning选项：

Model.bulkCreate(values, {returning: true})

回答by Thiago Silva Ferreira

Tested in MySQL:

在 MySQL 中测试：

Model.bulkCreate(values, { individualHooks: true })

回答by Samantha Bretous

var data = [
    {
        'cat_name':'fashion'
    },
    {
        'cat_name':'food'
    }
];

Model.bulkCreate(data)
.then(function() {

 //(if you try to immediately return the Model after bulkCreate, the ids may all show up as 'null')
  return Model.findAll()
})
.then(function(response){
    res.json(response);
})
.catch(function(error){
    res.json(error);
})

回答by Jan Aagaard Meier

The success handler is passed an array of instances, but please notice that these may not completely represent the state of the rows in the DB. This is because MySQL and SQLite do not make it easy to obtain back automatically generated IDs and other default values in a way that can be mapped to multiple records. To obtain Instances for the newly created values, you will need to query for them again. http://docs.sequelizejs.com/en/latest/api/model/#bulkcreaterecords-options-promisearrayinstance

成功处理程序会传递一个实例数组，但请注意，这些实例可能无法完全代表 DB 中行的状态。这是因为 MySQL 和 SQLite 不容易以可以映射到多个记录的方式获取自动生成的 ID 和其他默认值。要获取新创建的值的实例，您需要再次查询它们。 http://docs.sequelizejs.com/en/latest/api/model/#bulkcreaterecords-options-promisearrayinstance

回答by Finn

From the documentation: please notice that these may not completely represent the state of the rows in the DB. This is because MySQL and SQLite do not make it easy to obtain back automatically generated IDs and other default values in a way that can be mapped to multiple records. To obtain Instances for the newly created values, you will need to query for them again. http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-bulkCreate

从文档中：请注意，这些可能不完全代表数据库中行的状态。这是因为 MySQL 和 SQLite 不容易以可以映射到多个记录的方式获取自动生成的 ID 和其他默认值。要获取新创建的值的实例，您需要再次查询它们。 http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-bulkCreate

But you can pass an option to make it return the id

但是您可以传递一个选项以使其返回 id

orm.models.category.bulkCreate(data, { returning: true })

回答by Ben Polge

Unfortunately that doesn't work in the latest version. They explain why here: http://sequelize.readthedocs.org/en/latest/api/model/#bulkcreaterecords-options-promisearrayinstance

不幸的是，这在最新版本中不起作用。他们在这里解释了原因：http: //sequelize.readthedocs.org/en/latest/api/model/#bulkcreaterecords-options-promisearrayinstance

note that the description specifically mentions mysql. You'll have to query for them. (example includes var _ = require('lodash');

请注意，描述中特别提到了 mysql。您必须查询它们。（示例包括var _ = require('lodash');

var cat = rm.models.category;
cat.bulkCreate(data)
 .then(function (instances) {
    var names = _.map(instances, function (inst) {
      return inst.cat_name;
    });
    return cat.findAll({where: {cat_name: {$in: names}}); 
 })
 .then(function(response){
    res.json(response);
 })
 .catch(function(error){
    res.json(error);
 });

回答by HusseinOsman

var data = [{
   'cat_name':'fashion'
  },
  {
   'cat_name':'food'
  }
 ];

orm.models.category.bulkCreate(data,{individualHooks: true})
 .then(function(response){
   res.json(response);
 })
 .catch(function(error){
   res.json(error);
 });