mysql - updateOnDuplicate 无效

Question

你好，我想用bulkCreate ex 插入数据:
[
{
"typeId": 5,
"devEui": "0094E796CBFCFEF9",
"application_name": "Pressure No.10",
"createdAt": "2020-02-05T08:07:17.000Z",
"updatedAt": "2020-02-05T08:07:17.000Z"
}
]
和我的 Sequelize 代码:

 return models.sequelize.transaction(t=>{
            return models.iot_nodes.bulkCreate(data,{
                updateOnDuplicate: ["devEui",]
            })
        })

当我在将插入到数据库的第一个数据中点击此代码时
我的问题是当我再次点击未更新的相同数据时，只需插入新行

我使用 mysql db, laragon

日志:

Executing (f202b84c-c5d8-4c67-954c-e22f96fb93d8): START TRANSACTION;
Executing (default): INSERT INTO `iot_nodes` (`id`,`typeId`,`devEui`,`application_name`,`createdAt`,`updatedAt`) VALUES (NULL,5,'0094E796CBFCFEF9','Pressure No.10','2020-02-05 08:07:17','2020-02-05 08:07:17') ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`devEui`=VALUES(`devEui`);
Executing (f202b84c-c5d8-4c67-954c-e22f96fb93d8): COMMIT;

Answer 1

根据信息，它似乎适合 this 场景。您想更新 devEui 字段。 updateOnDuplicate 选项:

Fields to update if row key already exists (on duplicate key update)?

因此， row key already exists 意味着该表必须有一个 唯一的 键，否则在插入数据时主键是重复的。

例如。

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';

class IotNode extends Model {}
IotNode.init(
  {
    typeId: {
      type: DataTypes.INTEGER,
      unique: true,
    },
    devEui: DataTypes.STRING,
    application_name: DataTypes.STRING,
  },
  { sequelize, modelName: 'iot_nodes' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    const datas = [
      {
        typeId: 5,
        devEui: '0094E796CBFCFEF9',
        application_name: 'Pressure No.10',
        createdAt: '2020-02-05T08:07:17.000Z',
        updatedAt: '2020-02-05T08:07:17.000Z',
      },
    ];
    await IotNode.bulkCreate(datas, { updateOnDuplicate: ['devEui'] });
    await IotNode.bulkCreate(datas, { updateOnDuplicate: ['devEui'] });
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

如您所见，我使 typeId 唯一并执行 IotNode.bulkCreate 两次。生成的 SQL 日志:

Executing (default): INSERT INTO "iot_nodes" ("id","typeId","devEui","application_name") VALUES (DEFAULT,5,'0094E796CBFCFEF9','Pressure No.10') ON CONFLICT ("typeId") DO UPDATE SET "devEui"=EXCLUDED."devEui" RETURNING *;
Executing (default): INSERT INTO "iot_nodes" ("id","typeId","devEui","application_name") VALUES (DEFAULT,5,'0094E796CBFCFEF9','Pressure No.10') ON CONFLICT ("typeId") DO UPDATE SET "devEui"=EXCLUDED."devEui" RETURNING *;

sequelize 使用唯一的 typeId 字段作为重复键。检查数据库中的行:

=# select * from iot_nodes;
 id | typeId |      devEui      | application_name
----+--------+------------------+------------------
  1 |      5 | 0094E796CBFCFEF9 | Pressure No.10
(1 row)

数据行按预期插入。

如果我们从 unique: true 字段中删除 typeId。 sequelize 将使用主键作为重复键。看看下面在数据库中生成的 SQL 和数据行:

Executing (default): INSERT INTO "iot_nodes" ("id","typeId","devEui","application_name") VALUES (DEFAULT,5,'0094E796CBFCFEF9','Pressure No.10') ON CONFLICT ("id") DO UPDATE SET "devEui"=EXCLUDED."devEui" RETURNING *;
Executing (default): INSERT INTO "iot_nodes" ("id","typeId","devEui","application_name") VALUES (DEFAULT,5,'0094E796CBFCFEF9','Pressure No.10') ON CONFLICT ("id") DO UPDATE SET "devEui"=EXCLUDED."devEui" RETURNING *;

=# select * from iot_nodes;
 id | typeId |      devEui      | application_name
----+--------+------------------+------------------
  1 |      5 | 0094E796CBFCFEF9 | Pressure No.10
  2 |      5 | 0094E796CBFCFEF9 | Pressure No.10
(2 rows)