Sequelizeのupsertの挙動について(MySQL)

Sequelize
Node

upsertの動作について調べてみました

データベースにはMySQLを使用してます。

  1. レコード作成
  2. upsertしてみる
  3. upsertでfieldsを指定してみる
  4. uniqueキーを変えてみる
  5. uniqueキーの一部だけfieldsで指定してみる

Modelの定義

Userというテーブルに↓の様なカラムを作成しました。また、num0,num1に対してユニークな複合インデックスが貼ってあります。

カラム名 型
name STRING
num0 INTEGER
num1 INTEGER

CREATE文

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `num0` int(11) DEFAULT NULL,
  `num1` int(11) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_num0_num1` (`num0`,`num1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

挙動を調べる

1. まずレコードを作成します

sequelize.User.create({
    name: 'aaa',
    num0: 0,
    num1: 1
})

SQL文

INSERT INTO `Users` (`id`,`name`,`num0`,`num1`,`createdAt`,`updatedAt`)
  VALUES (DEFAULT,'aaa',0,1,'2018-03-17 01:14:50','2018-03-17 01:14:50');

結果

まずは作成してこの様なデータが入りました。 以下１秒ごとにSQLを実行しています。

{ id: 1,
  name: 'aaa',
  num0: 0,
  num1: 1,
  createdAt: 2018-03-17T01:14:50.000Z,
  updatedAt: 2018-03-17T01:14:50.000Z }

2. upsertしてみる

sequelize.User.upsert({
    name: 'bbb',
    num0: 0,
    num1: 1
})

SQL文

INSERT INTO `Users` (`name`,`num0`,`num1`,`createdAt`,`updatedAt`)
  VALUES ('bbb',0,1,'2018-03-17 01:14:51','2018-03-17 01:14:51')
  ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `num0`=VALUES(`num0`), `num1`=VALUES(`num1`), `updatedAt`=VALUES(`updatedAt`);

結果

nameが書き換わっていることが確認できます。また、updatedAtも更新時刻に書き換わっています。

{ id: 1,
  name: 'bbb',
  num0: 0,
  num1: 1,
  createdAt: 2018-03-17T01:14:50.000Z,
  updatedAt: 2018-03-17T01:14:51.000Z }

3. upsertでfieldsを指定してみる

optionでfieldsを指定できるので指定してみます。

sequelize.User.upsert({
  name: 'ccc',
  num0: 0,
  num1: 1
}, {
  fields: ['num0', 'num1']
})

SQL文

INSERT INTO `Users` (`name`,`num0`,`num1`,`createdAt`,`updatedAt`)
  VALUES ('ccc',0,1,'2018-03-17 01:14:52','2018-03-17 01:14:52')
  ON DUPLICATE KEY UPDATE `num0`=VALUES(`num0`), `num1`=VALUES(`num1`), `updatedAt`=VALUES(`updatedAt`);

結果

nameをfieldsで指定していないので更新されませんでしたがupdatedAdのみ更新されています。

{ id: 1,
  name: 'bbb',
  num0: 0,
  num1: 1,
  createdAt: 2018-03-17T01:14:50.000Z,
  updatedAt: 2018-03-17T01:14:52.000Z }

4. uniqueキーを変えてみる

num1の値を変えてみました。

sequelize.User.upsert({
  name: 'ddd',
  num0: 0,
  num1: 2
})

SQL文

INSERT INTO `Users` (`name`,`num0`,`num1`,`createdAt`,`updatedAt`)
  VALUES ('ddd',0,2,'2018-03-17 01:14:53','2018-03-17 01:14:53')
  ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `num0`=VALUES(`num0`), `num1`=VALUES(`num1`), `updatedAt`=VALUES(`updatedAt`);

結果

新しくレコードが作成されました。upsertすると挿入されるかどうかに関係なく採番されるためIDが飛んでいます。

{ id: 1,
  name: 'bbb',
  num0: 0,
  num1: 1,
  createdAt: 2018-03-17T01:14:50.000Z,
  updatedAt: 2018-03-17T01:14:52.000Z }

{ id: 4,
  name: 'ddd',
  num0: 0,
  num1: 2,
  createdAt: 2018-03-17T01:14:53.000Z,
  updatedAt: 2018-03-17T01:14:53.000Z }

5. uniqueキーの一部だけfieldsで指定してみる

nameとnum0だけfieldsで指定してnum1の値を変えてみました。

sequelize.User.upsert({
  name: 'eee',
  num0: 0,
  num1: 3,
},{
  fields: ['name', 'num0']
})

SQL文

INSERT INTO `Users` (`name`,`num0`,`num1`,`createdAt`,`updatedAt`)
  VALUES ('eee',0,3,'2018-03-17 01:14:54','2018-03-17 01:14:54')
  ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `num0`=VALUES(`num0`), `updatedAt`=VALUES(`updatedAt`);

結果

fieldsで指定した値に関係なく挿入されていました。

{ id: 1,
  name: 'bbb',
  num0: 0,
  num1: 1,
  createdAt: 2018-03-17T01:14:50.000Z,
  updatedAt: 2018-03-17T01:14:52.000Z }

{ id: 4,
  name: 'ddd',
  num0: 0,
  num1: 2,
  createdAt: 2018-03-17T01:14:53.000Z,
  updatedAt: 2018-03-17T01:14:53.000Z }

{ id: 5,
  name: 'eee',
  num0: 0,
  num1: 3,
  createdAt: 2018-03-17T01:14:54.000Z,
  updatedAt: 2018-03-17T01:14:54.000Z }