ON DUPLICATE KEY UPDATE構文を activerecord-import で使う

INSERT .. ON DUPLICATE KEY UPDATE 構文とはなにか
使い方の例
テーブルに一意のインデックスが複数存在する場合は要注意
参考

MySQL が提供する INSERT .. ON DUPLICATE KEY UPDATE 構文の使い方について、activerecord-import という Ruby のライブラリを例に整理します。

また、この構文を MySQL で使うときの注意点についても後半に書きます。

INSERT .. ON DUPLICATE KEY UPDATE 構文とはなにか

登録しようとしたレコードが存在しなければ INSERT、既に存在する場合は UPDATE する構文です。

MySQL の場合、存在するかレコードかどうかは PRIMARY KEY もしくは UNIQUE インデックスに重複する値が発生するか否か で判定されます。

使い方の例

次のような、会社情報を管理する companies テーブルがあるとします。

Field	Type	Key	NULL
id	BIGINT	PRIMARY	NOT NULL
name	VARCHAR		NOT NULL
employee_count	INT		NOT NULL

PRIMARY KEY である id に加えて会社名と従業員数を持ちます。
次の SQL でテーブルを作成できます。

CREATE TABLE `companies` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `employee_count` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CSV 形式で定義された会社のマスタデータを渡され、それらをデータベースに取り込むかんたんなお仕事をしている、という状況を想定しましょう。

1社目の登録依頼

最初に、foo 株式会社なるところの登録依頼がきました。

# CSV
foo株式会社,10人

新規登録かつ1件しかないので、シンプルに #create で取り込んでしまいます。

Company.create(name: 'foo株式会社', employee_count: 10)
# => INSERT INTO `companies` (`name`, `employee_count`) VALUES ('foo株式会社', 10)

テーブルはこのようになりました。

2社の追加と1社の修正

次に2件の会社情報の追加依頼がきました。また、登録済みの foo 株式会社の従業員数が1人増えたようです。

foo株式会社,11人
bar株式会社,10人
baz株式会社,10人

foo 株式会社は従業員数の更新のみ行い、bar 株式会社と baz 株式会社は新規登録としたいですね。このようなケースで ON DUPLICATE KEY UPDATE 構文が役に立ちます。
以下は activerecord-import を利用してこの構文を利用するサンプルコードです。

# CSVはパース済みとします
csv = [
  { name: 'foo株式会社', employee_count: 11 },
  { name: 'bar株式会社', employee_count: 10 },
  { name: 'baz株式会社', employee_count: 10 },
]

companies = csv.map do |row|
  company = Company.find_or_initialize_by(name: row[:name])
  company.employee_count = row[:employee_count]
  company
end

Company.import(companies, on_duplicate_key_update: %i[employee_count])
# => INSERT INTO `companies` (`id`, `name`, `employee_count`)
#    VALUES
#      (1, 'foo株式会社', 11),
#      (NULL, 'bar株式会社', 10),
#      (NULL, 'baz株式会社', 10)
#    ON DUPLICATE KEY UPDATE `companies`.`employee_count` = VALUES (`employee_count`)

コードの実行後、テーブルは次のようになりました。

ここで抑えておきたいポイントは以下です。

ON DUPLICATE KEY UPDATE 構文は、PRIMARY KEY もしくは UNIQUE インデックスに重複する値が発生した場合に更新となる。companies テーブルの場合は id が PRIMARY KEY となっているため、id が重複する場合は更新処理になる。
#import メソッドに on_duplicate_key_update オプションを指定することで ON DUPLICATE KEY UPDATE 構文が有効になる
on_duplicate_key_update オプションの値として、更新をしたい列を指定する
- 今回の例だと employee_count だけ修正したいので1つ指定

3社全ての修正

その後、3つの会社全てで従業員数が倍増したようです。

foo株式会社,22人
bar株式会社,20人
baz株式会社,20人

今回は新規で増えた会社は存在しないので更新のみでよいわけですが、このようなケースでも ON DUPLICATE KEY UPDATE 構文は利用できます。

csv = [
  { name: 'foo株式会社', employee_count: 22 },
  { name: 'bar株式会社', employee_count: 20 },
  { name: 'baz株式会社', employee_count: 20 },
]

companies = csv.map do |row|
  # 全て存在する会社であることはわかっているため find_by を利用
  company = Company.find_by(name: row[:name])
  company.employee_count = row[:employee_count]
  company
end

Company.import(companies, on_duplicate_key_update: %i[employee_count])
# => INSERT INTO `companies` (`id`, `name`, `employee_count`)
#    VALUES
#      (1, 'foo株式会社', 22),
#      (2, 'bar株式会社', 20),
#      (3, 'baz株式会社', 20)
#    ON DUPLICATE KEY UPDATE `companies`.`employee_count` = VALUES (`employee_count`)

先ほどは #find_or_initialize_by で DB に存在しない会社の場合は初期化していましたが、今回は全て存在することが事前にわかっているので #find_by で参照のみしています。

このコードの実行後、テーブルは次のようになりました。

テーブルに一意のインデックスが複数存在する場合は要注意

MySQL のドキュメントには次のように書かれています。

一般に、一意のインデックスが複数含まれているテーブルに対して ON DUPLICATE KEY UPDATE 句を使用することは避けるようにしてください。

ON DUPLICATE KEY UPDATE 構文を、一意のインデックスが複数存在するテーブルに対して使うことは非推奨のようです。

これはなぜか？　(ここからはドキュメントからは確証を得られなかった部分もあるため、筆者の解釈も含みます)

companies テーブルを再び例として使います。既に id=1で foo 株式会社が登録されている場合、次の2つのクエリは同等となります。

INSERT INTO companies (id, name, employee_count) VALUES (1, 'foo株式会社', 10) ON DUPLICATE KEY UPDATE employee_count=VALUES(`employee_count`);

UPDATE companies SET employee_count=10 WHERE id = 1;

ここでもし name も UNIQUE インデックスが貼ってあったとすると、次のようなクエリに変わります。

UPDATE companies SET employee_count=10 WHERE id = 1 OR name = 'foo株式会社';

OR でそれぞれをつなげて対象を絞り込んで更新するようになります。さらに、もし複数レコードが条件に一致しても1つのレコードだけが更新されるようです。つまり、複数の一意インデックスが存在する場合 ON DUPLICATE KEY UPDATE 構文で更新する場合にどのレコードが更新対象になるかが不明瞭になるということです。

companies テーブルの例で言えば id = 1 OR name = 'foo株式会社' の条件に一致するレコードは現時点では1つしか存在しないので実質問題は発生しません。しかし、テーブル構造によっては OR でつながって探索された結果、複数のレコードがヒットして期待しないレコードが更新対象となることは考えられるでしょう。

特に Rails を使っているケースでは通常 id が PRIMARY KEY となるので、他に1つでも UNIQUE インデックスが貼られたカラムを追加した場合はこの条件に当てはまってしまうので、上記の仕様を理解した上で使うようにするのが良さそうです。