如何在 save() 上从 Sequelize 获取 MySQL 自动增量主键?

2022-01-19 00:00:00 node.js mysql sequelize.js

我正在尝试将新行插入 Sequelize,但无论我尝试什么,Sequelize 都不会返回刚刚保存的自动增量行的 ID.这是我的代码:

I am trying to insert new rows into Sequelize, but no matter what I try Sequelize does not return the ID of the autoincremented row that was just saved. Here's my code:

        if (isDefined(jsonObject.id) && jsonObject.id == 0) {
            let databaseObject = Database.getInstance().getModel(objectName).build(jsonObject);
            //databaseObject.isNewRecord = true;
            // Does not exist
            //databaseObject.setNew(true);

            transform.setNew(true);
            transform.setJsonObject(jsonObject);
            transform.setDatabaseObject(databaseObject);

            return transform.populateJoins()
            //.then(() => transform.baseMetadata())
            //.then(() => transform.metadata())
            .then(() => transform.baseExtract())
            .then(() => transform.extract())
            //.then(() => transform.clean())
            .then(() => {
                console.log('DATABASE OBJECT PRE SAVE: ');
                console.log(transform.getDatabaseObject());
                return transform.getDatabaseObject().save({
                    returning: true
                });

            }) // function () { return ; }
            .then((data) => {
                if (data instanceof sequelize.ValidationError) {
                    for (var field in data) {
                        this.addError(res.locals, ErrorCode.InvalidValue, field + ': ' + data[field]);
                    }
                    return false;
                } else {
                    return Database.getInstance().getConnection().query('SELECT LAST_INSERT_ID();')
                    .then((row) => {
                        console.log('LAST INSERT ID: ');
                        console.log(row);
                        transform.getDatabaseObject().set('id', row[0]);
                        return transform.getDatabaseObject();
                    });
                }
            })
            //.then(() => transform.getDatabaseObject()) // function () {   return ; }
            .then((data) => {
                //console.log('Data is ' + typeof data);                
                console.log('NEW POST json: ');
                console.log(jsonObject);
                console.log('NEW POST data: ');
                console.log(data);
                console.log(data.get('id'));
                if (jsonObject['id'] == data.get('id')) {
                    this.addError(res.locals, ErrorCode.InvalidValue, 'New object provided did not auto-increment.');
                    return false;
                }

                let dtoObject = {};
                //for (var i in data.dataValues) {
                //  dtoObject[toCamelCase(i)] = data.get(i);
                //}
                for (let i in transform.columns) {
                    dtoObject[i] = data.get(transform.columns[i].name);
                }
                //if (jsonObject.id == data.get('id')) {
                //  throw new FrontendError(ErrorCode.InvalidValue, 'New object provided did not auto-increment.');
                //}
                res.locals.retval.addData(dtoObject);

但是,我总是收到 New object provided did not auto-increment. 异常,因为返回的数据库对象的 ID 始终为 0,尽管保存到我的数据库(ID 273567):

However, I am always receiving the New object provided did not auto-increment. exception because the returned database object always has an ID of 0, despite saving to my database (ID 273567):

NEW POST json: 
{ id: '0',
  name: 'Template 1',
  accountId: 1,
  versionCount: '0',
  enabled: '1',
  processStatusId: '1',
  processStatusModifiedDate: '0001-01-01 00:00:00',
  creationDate: '0001-01-01 00:00:00',
  creationUserId: '1',
  lastModifiedDate: '0001-01-01 00:00:00',
  lastModifiedUserId: '1' }
NEW POST data: 
template {
  dataValues: 
   { version_count: '0',
     process_status_id: '1',
     process_status_modified_date: '0001-01-01 00:00:00',
     creation_date: '0001-01-01 00:00:00',
     creation_user_id: '1',
     last_modified_date: '0001-01-01 00:00:00',
     last_modified_user_id: '1',
     id: '0',
     name: 'Template 1',
     enabled: '1',
     account_id: 1 },
  _previousDataValues: 
   { version_count: '0',
     process_status_id: '1',
     process_status_modified_date: '0001-01-01 00:00:00',
     creation_date: '0001-01-01 00:00:00',
     creation_user_id: '1',
     last_modified_date: '0001-01-01 00:00:00',
     last_modified_user_id: '1',
     id: '0',
     name: 'Template 1',
     enabled: '1',
     account_id: 1,
     current_version: undefined,
     audit_json: undefined,
     deletion_date: undefined,
     deletion_user_id: undefined },
  _changed: 
   { id: false,
     name: false,
     enabled: false,
     account_id: false,
     current_version: false,
     version_count: false,
     audit_json: false,
     process_status_id: false,
     process_status_modified_date: false,
     creation_date: false,
     creation_user_id: false,
     last_modified_date: false,
     last_modified_user_id: false,
     deletion_date: false,
     deletion_user_id: false },
  _modelOptions: 
   { timestamps: false,
     validate: {},
     freezeTableName: false,
     underscored: false,
     underscoredAll: false,
     paranoid: false,
     rejectOnEmpty: false,
     whereCollection: { id: 273523 },
     schema: null,
     schemaDelimiter: '',
     defaultScope: {},
     scopes: [],
     indexes: [],
     name: { plural: 'templates', singular: 'template' },
     omitNull: false,
     tableName: 'template',
     sequelize: 
      Sequelize {
        options: [Template],
        config: [Template],
        dialect: [Template],
        queryInterface: [Template],
        models: [Template],
        modelManager: [Template],
        connectionManager: [Template],
        importCache: [Template],
        test: [Template] },
     hooks: {},
     uniqueKeys: {} },
  _options: { isNewRecord: true, _schema: null, _schemaDelimiter: '' },
  __eagerlyLoadedAssociations: [],
  isNewRecord: false }
0

看起来我正在用一个变量覆盖 isNewRecord 方法,但我删除了该代码并且它似乎没有进行任何更改.

It looks like I'm overriding the isNewRecord method with a variable, but I removed that code and it doesn't seem to have made a change.

推荐答案

这是我能找到的从 Sequelize 获取自增主键的最简单的解决方案.这是一个非常丑陋的解决方案,但如果它有效,它就会有效.

This is the simplest solution I could find to getting the autoincrement primary key from Sequelize. It's a very ugly solution but if it works, it works.

                return Database.getInstance().getConnection().transaction((t) => {
                    return transform.getDatabaseObject().save({
                        transaction: t
                    })
                    .then((data) => {
                        if (data instanceof sequelize.ValidationError) {
                            for (var field in data) {
                                this.addError(res.locals, ErrorCode.InvalidValue, field + ': ' + data[field]);
                            }
                            return false;
                        } else {
                            return Database.getInstance().getConnection().query('SELECT LAST_INSERT_ID() as `id`;', { transaction: t, type: sequelize.QueryTypes.SELECT })
                            .then((row) => {
                                console.log('LAST INSERT ID: ');
                                console.log(row);
                                transform.getDatabaseObject().set('id', row[0]['id']);
                                transform.getDatabaseObject().setDataValue('id', row[0]['id']);

                                console.log('DATABASE OBJECT POST SAVE: ');
                                console.log(transform.getDatabaseObject());
                                return transform.getDatabaseObject();
                            });
                        }
                    })
                })

相关文章