时间戳

首页 > 解决方案 > updateOnDuplicate 无效

mysql - updateOnDuplicate 无效

问题描述

你好我想用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" } ]

和我的续集代码：

 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;

标签： mysqlnode.jsexpresssequelize.js

解决方案

根据信息，它似乎适合这种情况。您要更新devEui字段。updateOnDuplicate选项：

如果行键已经存在（重复键更新时）要更新的字段？

因此，这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: truefromtypeId字段。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)

推荐阅读